Who is this course for?

This course is suitable for anyone who does not already have a good grounding in Excel.  There are no specific prerequisites for this course, but you should be familiar with using a computer in a Windows environment. ​​​​​

What will I learn?

• Understanding Data Types and Formatting
  Entering data accurately into a spreadsheet requires an understanding of how Excel stores different data types.  For example, is the data a number, a date or a piece of text?  Excel has a selection of predefined formats that you can apply to each of these data types, for example when you enter a value, you can choose to format is to a currency or a specific number of decimal places.  Once you have understood the content of this section of the course you will be able to display data in a variety of ways that will make your spreasheets look professional and be easy to read.  You will be able to ensure that data is stored in a way that can be used in formulae, whether you are working with numbers, currencies or even date and time calculations.

• Entering Data Efficiently
  Excel has some useful features that allow for speedy data entry, such as Autofill and Autcomplete.  This section will show you some top tips to make dealing with large data sets super efficient.

• Creating Simple Formulas & Functions
  In this course we cover creating simple formula using mathematical operators such as addition, subtraction, multiplication and division.  You will understand the use of brackets in formula as well as the specific syntax that Excel requires to perform calculations.  Don't worry about your level of maths knowledge, Excel has a vast resource of prewritten functions to help you.  In this course you will gain confidence on finding and using the correct function for your task and gain experience in using functions such as SUM, AVERAGE, COUNT, MAX and MIN.
  Once you have completed this section of the course you we be able to create spreadsheets that help you perform common tasks such as budgetting, calculating timesheets, recording absences and so on.​

• ​​​Performing a Simple Sort
  Excel has some useful options for sorting large amounts of data, but you need to master a few simple rules to make sure your data is sorted correctly.  This section of the course looks at how to set out your data so that Excel understands the scope of your database and how to set up appropriate headings.
  Once you have mastered these techniques you will be able to sort data alphabetically, in number or date order.

• Saving & Printing a Workbook
  In this section we look at the saving your work, setting up the Autosave facility and saving the spreadsheet so that it is accessible to others.  You will be able to convert spreadsheets into a PDF format to share with customers or post in a shared area of your network or online.
  We will look at different printing options, such as adding page titles and numbering or even adding you company logo to each page.

• Moving and Copying Data
  This part of the course looks at the way that Excel deals with the Cut, Copy and Paste options, which differs slightly from other Microsoft Applications.  On completion of this section you will be able to confidently move data around between spreadsheets (even if the data contains formulae).  You will be able to copy data into other applications such as Word or PowerPoint and even create links between the applications so that they will update your data automatically.

• Creating Simple  Charts
  In this section we look at laying out your data in a way that makes it easy to convert into a stunning visual chart using options such as Pie, Bar or Column Chart.  Converting your data into charts will give you a way of communicating your information to others as well as a dashboard to keep track of changing trends.  You will also be able to copy or link your data into other applications such as Word or PowerPoint.

Who is this course for?

This course is for users who have already mastered the basics of Excel.   You should already be confident with entering and formatting data, as well as be able to input a simple formula.

What will I learn?

• Absolute and Relative References
  One of the key factors to improve the efficiency of your Excel fomulae is understanding when and how to use relative and absolute cell references.  By default Excel generally assumes that your cell references need to change when you copy or move a formula, so a fomula referencing the cell A1 would become A2, A3 and so on as you move down a row, or it would become B1, C1 or D1 as you moved across columns.  Most of the time you will want this to happen so that you only have to create a formula once and then you can copy it across multiple columns and rows.  Sometimes it is useful to fix a formula so that you are always looking at a specific row, column or cell and this is done by creating what is called an Absolute Reference.  Absolute References can be very time saving, because you can store a value in a single cell for use all over your spreadsheet and only have one cell to update when that value changes. 

• Named Ranges
  Named Ranges can be used in a similar way to Absolute References, in that you can fix your formula to always look at a specific cell.  In addition it allows you to give your cell a meanful name, such as VAT or TAX, so that you can then use that name in a formula or function.  Named Ranges can also be used to name large areas of a spreadsheet so that you can navigate and select data easily.  Once you have mastered the section on Named Ranges you will be able to make your formula significantly more meaningful and easy to use.

• Count Functions
  In this section we explore functions such as COUNT, COUNTA and COUNTIF.  These functions allow you to count how many times a given value or text element appears in a range of cells.  

• Text Functions
  There are a number of useful functions in the Text category in Excel, these allow you to create a formula that makes changes to text data such as names and addresses.  We look at functions such as CONCAT (formerly CONCATENATE) which allows you to join text together from multiple cells.  We also look at options such as Text to Columns which allows you to split data from one cell into seperate columns.  We also look at functions that change case such as UPPER, LOWER and PROPER.

• Rounding Functions
  This is a particularly important section to understand, as it is a common misconception that formatting cells to a specific number of decimal places causes the numbers to be rounded up or down.  In actual fact this is not the case, in order to round a number you need to understand the use of the ROUND, ROUNDUP or ROUNDDOWN functions.

• Using Date and Time Functions
  There are a number of useful functions in Excel's Date and Time functions category.  For example there is a function called TODAY which always displays the current date and updates itself automatically.  When used in conjuction with date and time calculations this can be extremely helpful when calculating the number of days an invoice is overdue or the age of an employee, for example.

• Linking Data to Word or PowerPoint
  In this section we look at setting up links between Excel and other applications such as Word or PowerPoint.  In this way you can have a single point of data entry that will automatically update all of your relevant charts, reports and presentations without the need for multiple data entry or copying and pasting.

• Using Format as Table
  Format as table is a relatively new feature in Excel which allows you to specify that a particular range of cells belongs together as a table.  This has numerous benefits, including improved formatting, easy sorting and filtering and adding totals to columns automatically.  It is also important to understand if you are going on to use more advanced features such as Pivot Tables as it effects how your Pivot Table data updates.

• Creating a Custom Chart
  Excel has a selection of predesigned charts that are relatively easy to use, but in this section we explore options that enable you to customise these standard features to suit you needs.  For example you might want to create a chart using specific colours or fonts that match you company logo.  You might need to add specific labels, such as percentages to your chart, or you may need your chart to always appear on a particular scale.  All of these options can be set within a chart and then the chart can be saved as a Chart Template for future use.

Who is this course for?

This course if for delegates who already have a good grasp of Excel formula and simple functions but would like to explore more complex functions and work with data across multiple worksheets and workbooks.

What will I learn?

• Linking Data Between Worksheets
  This section of the course looks at creating formula to connect different worksheets and workbooks together.  With the knowledge from this module you will be able to create sheets that collate data automatically together from multiple sources into a dashboard or summary sheet.

• Creating a 3-D Formula
  Creating a 3-D Formula allows you to summarise data from multiple identical sheets within the same book.  For example you might have data from multiple stores or departments that you would like to summarise in a single sheet using a function such as SUM or AVERAGE.  In this section we look at grouping sheets together so that headings and/or formulae can be added to multiple sheets at the smae time.  This also allows you to format multiple sheets and in the later versions, set the print options for all your sheets at once.

• Protection
  If you have created a complex spreadheet, particularly one that links data from other locations, is it prudent to add a level of protection to the sheet to prevent data being accidentally overwritten.  In this section we look at adding password protection to prevent users from changing formula, moving or deleting sheets, rows or columns.  We also look at protecting confidential data by adding password protection that prevents others from even opening the file itself.

• IFS and Lookup Functions
  In this module we look at decision making functions such as IF, AND and OR.  With these functions we can create calculations that only work when specific criteria are met.  For example, we could offer a discount on an order when the customer spends over a certain amount.

• Using Nested Functions
  Usually, when creating an Excel function, each function operates in its own separate cell.  We can however, improve the efficiency of our functions by putting one function inside another, this is called "Nesting".  It can prove a little difficult as it involves multiple sets of brackets, but in this section we will look at the prompts and help that Excel gives you, to minimise any confusion.

• Creating Custom Number Formats
  In the Intoductory courses we explored how to apply different formats to the spreadsheet that make data appear as a number, a currency or a date, for example.  At this level we learn how to customise Excels pre-exisiting formats so that we can display exactly the formatting we want.  For example, we might want the date to display the day of the week as well as the date, or we might want negative values to appear in brackets rather than with a minus sign.

• Predefined Conditional Formatting
  Conditional Formatting is a very useful feature of modern versions of Excel that allow you to apply specific formats such as font or background colour dependant upon the value of the cell.  For example you could add a format that made all invoices over 60 days old turn red, or have today's date turn blue.  Once you have applied these automatic formatting methods you can then use the Sort and Filter options to find the data you are looking for easily.  For example I could ask the filter to only display invoices that are red or have all orders that are blue sorted to the top of the sheet.

Who is this course for?

Delegates attending this course should already have a good grounding in using formulae and functions.  This course will improve your spreadsheet design by helping you control the types of data that can be added as well as controlling the formatting of your data to produce more uniformly professional results.

What will I learn?

• Mixed Referencing
  In previous courses we have looked at the difference between formulae created with Relative and Absolute cell referencing.  In the Intermediate Level 2 course we look at combining these two options to create fomulae in the most efficient manner possible so that any repetition is kept to a minimum.

• Worksheet Specific Named Ranges
  This section delves more deeply into the use of Named Ranges to set constant values within a spreadsheet.  For example, you could set the value of VAT, either within a cell or within the spreadsheet itself, making it an easy process to update all your formulae simultaneously when there is a rate change.  In the newer versions of Excel it is also possible to have a Named Range that only applies to a specific sheet, rather than the entire book.

• Data Validation
  Applying data validation options to your spreadsheet allows restrictions to be set on individual cells, preventing the wrong type of data being added.  For example, you can specifiy that the user must enter a date, a number or some text.  You can also set criteria on those cells, perhaps to restrict the range of values or the number of characters that are permitted.  You can prevent invalid data from being added or you can just display a warning message.  You can also use Data Validation to create a dropdown list, to speed up data entry.

• Data Validation with Indirect
  Combining Data Validation with a function called INDIRECT, allows you to set more than one dropdown list that relates together.  For example, you could have a list of countries in one list, once the user picks a country from the first list, the options from the second list will automatically change to the items available in that country.

• Custom Conditional Formatting
  Conditional Formatting allows the user to specifiy particular formatting changes dependant on the value of the cell.  For example, you could set up a rule that all values over a certain amount change to a blue background, or you could add traffic light symbols to a cell that change when certain targets are met.  Although there are a vast array of preset options, you can customise these to produce more complex results.  For example, you could make the contents of one cell control the formatting of another, or you could create a rule that compares two cells and changes their formatting if they do not match.

• Sorting by Colour
  You have always been able to sort data alphabetically, numerically or in date order in Excel.  Using Conditional Formatting you can apply specific formats to cells, such as background colour and then use the sorting option to display the colours you have chosen in a particular order.

• Sorting Using a Custom List
  We cover the creation of custom lists in the Introduction Level 1 course.  It allows you to store a list of options in Excel itself, rather than an individual spreadsheet.  This is very helpful if you repeatedly need to create spreadsheets with the same headings regularly and it also allows you to sort data in a specific order other than alphabetically.  For Example, I could create a list of stores, and rather than sorting them alphabetically I could choose to sort them in the order that the deliveries are made.

• AutoFilter
  Autofilter allows you to filter data from a data list by applying criteria to a column or columns.  You could filter by a date range, a numerical range or for a specific piece of text.  Autofilter has existed in Excel for many years but there are more sophisticated options in the later versions that are worth becoming familiar with.  As well as sorting by colour, you can now also filter by colour.  So if you had applied a Conditional Format that made all invoices older than 60 days red, for example, you could then filter out all the records that are not red.

• Styles and Templates
  Keeping the formatting of your spreadsheet uniform throughout can be a bit of a challenge.  You may want to use the same font and font styles for headings, and perhaps a slightly different font style for sub headings.  Perhaps you have a particular preference for how you like your currency to be displayed.  All of these options can be set up as a named Style, and then you are able to pick your desired choice from a list.  This will ensure that you have identical formatting throughout your entire book, as well as making it easy to make any changes in the future.  You can also add your Styles to a Template so that all new books you create already have your prefered Styles available.  You can also create Templates that have your company logo and contact details and use the prefered house styles.  This could be saved in a shared area of your network so that the whole company has access to them.

Who is this course for?

This course is designed for users who need to use Excel to produce reports and data summaries.  Attendees should already have a good grounding in Excel formulae and functions.  Where our Intermediate level courses are aimed at creating spreadsheets, the Advanced levels assume that you already have the data in some form, but need to learn how to manipulate it to produce the most meaningful results.

What will I learn?

• Advanced Filter
  In the Intermediate levels we introduced the Automatic Filter, which is useful for hiding data that is not immediately relevant to our report.  However, the downside of this option is that, while the data is hidden, it can easily be unhidden again.  With the Advanced FIlter it is possible to copy the data we do want automatically to a different location.  

• Automatic Subtotals
  The Automatic Subtotals option within Excel, allows the user to add subtotals by automatically detecting a change in data from one row to another.  For example if I wanted to add up the total turnover for each region, I could ask the subtotals option to create a SUM function everytime it detects a change in the Region column.  

• Pivot Tables & Pivot Chart Reports
  The Pivot Tables option in Excel, allows the user to summarise data on a separate sheet, rather than inserting additional rows as Automatic Subtotals will do.  It is a very powerful tool for extracting data from large data sets and can even create a relevant chart for you at the same time.  The original data remains linked to the summary table and can be easily refreshed if any changes have been made at the source.  

• Goal Seek & Solver
  The Goal Seek option is in a section described as "What If Analyses".  Excel can seek for the optimal value within a spreadsheet, based on the formula that already exist.  You could ask Goal Seek to find the optimum selling price based on your desired profit margin for example.  The only limitation with this method is that you can only change one value in order to seek for your perfect result.  If you want to adjust multiple vaule to seek for your desired outcome, you need to use an add in tool called Solver, which works in a similar way, but has significant additional functionality.  Solver allows for complex data modelling within Excel.

• Scenario Manager
  Scenario Manager can be used by itself or in conjunction with Goal Seek and Solver. to produce reports comparing values which you specify within your spreadsheet.  Rather than seeking for an optimum selling price, you can create multiple versions of the report with different selling prices and compare any related values such as profit, shipping costs or staffing levels etc.

• Macros
  In this course we only have time for a brief overview of Macros, which is explored in more detail in the Excel Advanced Level 2 course.  A Macro in Excel is a way of automating a task that you are likely to do repeatedly.  It uses Visual Basic programming code, although you need not have any programming experience to make it perform simple tasks.  

Who is this course for?

This course is for users who want to automate their spreadsheets using recorded Macros.  You should be familar with Excel formula and Functions and understand reporting options such as Pivot Tables.

What will I learn?

• Recording a Macro
  Recording a Macro is a surprisingly simple process, you switch on the Macro recorder, perform the list of tasks you want it remember and then switch it off again.  Excel converts that information into Visual Basic code, which is the programming language that controls the software.  You don't need any previous programming experience to do this, although you can access the code and make changes to it if you wish.

• The Personal Macro Workbook
  It is important to make a decision about where you wish to store the Visual Basic code that Excel creates.  Sometimes its sensible to store the code in one particular spreadsheet, sometimes you need to be able to access the code in all of your spreadsheets, in which case you need to understand how to access your Personal Macro Workbook.

• Recording a Macro with Relative Referencing
  In previous courses we have looked at the difference between Absolute and Relative Referencing as it pertains to Formulae and Functions.  In this section we look at how that relates to Macros.  While some tasks are required in a specific cell location, i.e. cell C1, sometimes we want the action to perform in a location relative to where your cursor is now, i.e. go along 2 columns and then down 1 row.

• Assigning Macros to Buttons
  You can make you code run by assigning a keyboard shortcut key to it or by creating a button, either on your Quick Access Toolbar or on the spreadsheet itself.  Everytime the keyboard shortcut is used or the button clicked, your tasks will be perormed automatically.

• Adding Macro Buttons to the Ribbon
  If you have multiple Macros within Excel, you might want to create a seperate tab for them on the ribbon.  You can then group your macros together into sensible groups and add an an appropriate icon for each task.

• Editing Visual Basic Code
  Once you become familiar with Macros you may wish to edit the Visual Basic code directly, and in this section we will look at the code editor and make corrections to our Macro without the need to rerecord it.  It is also possible to copy and paste code from one book to another as well as make notes in your code for future reference.

• Creating User-Defined Functions
  Although there is an extensive library of Functions tha already exist within Excel, sometimes it is necessary to create your own.  If you want all your staff to create a Formula in a particular way you can do this by setting up your own User-Defined Functions within Excel.  In order to do this you will need to learn a small amount of Visual Basic programming code, but your trainer will give you some examples to take away that you can use as a Template to design your own functions in the future.