Complete Excel Course: From Beginner to Superhero in 9 Hours

Learn Advanced Excel skills to make you an Excel Superhero
4.8 (15 ratings) Instead of using a simple lifetime average, Udemy calculates a
course's star rating by considering a number of different factors
such as the number of ratings, the age of ratings, and the
likelihood of fraudulent ratings.
146 students enrolled
$19
$70
73% off
Take This Course
  • Lectures 100
  • Length 9 hours
  • Skill Level All Levels
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 12/2014 English

Course Description

Become an Excel superhero. Learn the skills to find the solution to any Excel problem.

This course starts with the basics of formulas and managing spreadsheets and quickly moves to advanced Excel techniques required for creating powerful, dynamic and visually appealing spreadsheets.

It covers;

  • New Excel 2013 features such as Flash Fill, chart filters and the internal data model.
  • Using PivotTables to summarise large amounts of data.
  • Over 30 different Excel functions to handle any problem including INDEX, VLOOKUP, LEN, MID, IF, MATCH, and INDIRECT.
  • Visualising results with charts.
  • Conditional Formatting to highlight data comparisons, progress and issues.
  • An introduction to the PowerPivot, DAX formulas and Power View features of Excel 2013.
  • And much much more.

You will receive;

  • Support from the Computergaga team.
  • Lifetime access.
  • Exercises throughout to re-enforce the content.
  • Fun and no-nonsense lessons.

Don't delay. Superhero's take action. Grab a coffee and let's get started. Your journey towards becoming an Excel superhero starts today.

What are the requirements?

  • A basic understanding of Excel. This course begins from about mid-introduction level.
  • A version of Microsoft Excel. The course is performed using Excel 2013, but any version is ok.

What am I going to get from this course?

  • Learn some incredible Excel skills and techniques
  • Learn the most popular Excel formulas for business (over 30 covered in the course)
  • Be equipped to find a solution to any Excel problem
  • Understand the new features to Excel 2013

What is the target audience?

  • Those who want to be amazing at Excel and are willing to accept the responsibility of being an Excel superhero

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Introduction
Introduction
Article
Article

This lecture contains the exercise files used throughout the course. Use them to follow along and take the exercises. Each file is named after the lecture number and title.

Section 2: Working with Windows
04:47

In this lecture we look at the Freeze Panes feature to fix parts of your worksheet to ensure they are visible at all times. This is typically used for column headings so that you can see what data relates to as you scroll down the worksheet. This tutorial covers;

  • Freezing the column headings in a worksheet
  • Freezing rows and columns in a worksheet at the same time

Applying Freeze Panes is essential for large worksheets to make them easier to navigate and work with.

04:19

Splitting the panes of a worksheet allows you to reference different areas of the worksheet at the same time. The lecture covers;

  • Splitting panes to make comparing records in a large worksheet easier
  • Using a shortcut to split panes in one movement of the mouse
03:08

If you need to work on multiple Excel files at the same time, you may benefit from making them all visible on screen at once. Excel provides features to easily do this. This tutorial covers;

  • Arranging multiple windows on screen at the same time
  • Understanding the 4 different ways of viewing multiple workbooks on screen
  • How to return to the single window display
Section 3: Sorting and Filtering
08:18

Lists in Excel can be very large. In fact Excel worksheets can contain as many as 1,048,576 rows of data.

Sorting enables you to easily change the order of a list. A list could be sorted in date order, or alphabetical ,or even by the order amount as largest to smallest.

This tutorial covers;

  • The fastest way to sort a list in Excel
  • Sorting lists by different data types such as date, number or text
  • A common problem when sorting records
  • Sorting a list of records by multiple levels
11:05

A key technique used to manipulate and analyse a large list of data in Excel, is filtering. Filtering records in Excel is easy, yet incredibly powerful and versatile.

A list can be filtered to only show the necessary records ready for printing, exporting or to view aggregate totals.

This tutorial covers;

  • Filtering a list of records by a text, date and numerical columns
  • Filter a list using multiple columns
  • How to instantly recognise a filtered list when opening a spreadsheet
  • Quickly removing all the filters from a table and resetting it to normal
  • Viewing totals for a filtered list such as the sum, count or average for a column
04:03

In addition to sorting and filtering records in a list by value, you can also sort and filter by the colour of the cell or font.

This technique works awesomely well with Conditional Formatting (covered later in this course) as a way of identifying and then working with records that meet certain criteria.

4 questions

Open the sorting_and_filtering.xlsx file from the previous tutorials and use the Excel sorting and filtering tools to answer these questions.

Section 4: Getting Started with Formulas
09:43

Get started with learning to write formulas in Excel. Formulas are the foundation of Excel's incredible power. To become an Excel superhero, you must be confident in being able to learn and work with any formula.

This tutorial covers;

  • Writing formulas using basic arithmetic (addition, subtraction, multiplication and division)
  • Referencing cells and using constants in a formula
  • The rule of BODMAS that determines the order of calculation
04:56

When writing formulas it is common to need to calculate percentages. For example you may need to calculate a discount for a client, add VAT to a product price or calculate a 20% tax savings. This tutorial covers;

  • Calculate the % of a value
  • Increase a value by a certain percentage
  • Decrease a value by a certain percentage
  • Calculate what % one value is of another
11:19

Excel has over 450 functions in its library. A function is a built-in formula. Knowing how to write your formula is a very important step on the way to becoming an Excel superhero, but most of the time you will be working with the built-in functions of Excel.

This tutorial covers;

  • The 5 aggregate functions of Excel (SUM, COUNT, AVERAGE, MAX and MIN)
  • Understanding function syntax making you ready for any function
04:52

The COUNTA and COUNTBLANK functions are two variations of the COUNT function covered earlier in this course. The COUNT function only counts numbers within a range. COUNTA is used to count all the non-empty cells in a range, and the COUNTBLANK function is used to count the empty cells in a range.

This tutorial covers;

  • Ways of finding a function when needed in a given situation
  • An example of the COUNTA function being used
  • An example of the COUNTBLANK function being used
08:02

The Excel function wizard can be used to assist you in writing a formula. It provides explanations for the parts of the function, results for the information your providing and will also make minor corrections to mistakes.

This tutorial covers;

  • The COUNTIF function
  • Using the function wizard to help find the correct function to use in a given situation
  • Using the function wizard to troubleshoot and correct a mistake in our formula
05:33

When referencing a cell from a formula you are using relative cell references. This ensures that when you copy the formula, the reference moves with you. Dollar signs are used to fix a cell reference, making it an absolute reference. This tutorial covers;

  • An example where absolute cell references are used
  • A shortcut to changing the cell reference type
  • An introduction to mixed references
07:30

Creating a defined name can provide an easier way of referencing cells in an Excel workbook, especially when referencing across multiple sheets. It provides a meaningful name that is unique on that workbook.

It can be recalled quickly when writing formulas and also used to select ranges quickly, making it awesome with larger lists.

This tutorial covers;

  • The benefits of using defined names
  • How to create a defined name
  • Naming conventions and a common mistake when naming ranges
  • Using a defined name to quickly select a range
  • How to view, edit and delete existing defined names
  • Using a defined name when writing a formula
Exercise 1: Writing Formulas
Article
Exercise 2: Mixed References
Article
Exercise 3: Using Functions
Article
Section 5: Using Logical Functions
12:12

The IF function is used to test a condition and then take an alternative action for whether the condition is met or not. For example, if a customer orders over a £500 then apply a 10% discount, otherwise do not.

It is a logical function of Excel, and is sometimes referred to as a conditional function or a decision making function.

This tutorial covers;

  • How to write the IF function in Excel
  • Understanding the functions syntax
  • An example applying a discount to a customer if they order over a certain quantity
  • An example showing testing a cell contain text before applying a discount
  • An example that tests to see if a date has passed, and then takes a different action whether it has or not
Article

The AND and OR logical functions of Excel are used to test multiple conditions. They are commonly used with various Excel features including the IF function to perform more advanced decision making.

Any Excel function can be nested within another for more powerful calculations.

This tutorial covers;

  • An explanation of the AND and OR logic and the syntax of the two functions
  • Examples of the AND and OR functions in use
  • An introduction to the art of nesting functions in Excel
11:46

As awesome as the The IF function is, it can only test one condition, and perform one action if the test is true and another if the test is false. If we need the function to perform a more advanced condition, or perform multiple actions we will need to embed other functions for these tasks. This is a technique known as nesting functions.

This tutorial covers;

  • Nesting the AVERAGE function within the IF function
  • Adding the AND function to the formula to test multiple conditions
Section 6: Using Lookup Functions
13:15

The VLOOKUP function is a lookup and reference function. It searches for a record of data in a list and returns information from that record. For example, it could search a list of orders and return the value of an order that you ask for.

VLOOKUP stands for a vertical lookup. It gets this name because it looks down columns.

This tutorial covers;

  • An explanation of the VLOOKUP function and when you would use it
  • Learning the syntax of the VLOOKUP function
  • Using the VLOOKUP function to return the price and units in stock for a particular product in a list
  • A recap on the use of an absolute cell reference
  • A demonstration of the 'Range Lookup' argument
06:03

The VLOOKUP function is commonly used to find unique entries such as an order, employee or service and return information about that entry.

However, it can also be used to find data within a range. To do this the final argument of the VLOOKUP function known as 'Range Lookup' needs to be omitted or set to True.

This tutorial covers;

  • A detailed explanation of the 'Range Lookup' argument
  • An example of the VLOOKUP function returning exam grades by setting range lookup to True
  • Using the Function Wizard for assistance in understanding a function
05:05

The HLOOKUP function is used to search for a record of data along a row and return information about that record from a different row. For example, it could search a list of employees and return the start date of the one you search for.

This tutorial covers;

  • An explanation of the HLOOKUP function and when you would use it
  • Learning the syntax of the HLOOKUP function
  • Using the HLOOKUP function to return the price and units in stock for a particular product in a list
07:31

The MATCH function can be used to return the column index number for the VLOOKUP function. By nesting the MATCH function within the column index number of VLOOKUP, the function can automatically find the necessary column number, making the function more flexible and durable.

This tutorial covers;

  • An understanding of the MATCH function
  • The benefits of using it with VLOOKUP
  • How to use MATCH to return the column index number
10:02

The INDEX and MATCH functions together provide an extremely flexible lookup formula. Look for a record or entry in any column of a table and return related information from a different column of that table. This formula solves some of the limitations of VLOOKUP.

This tutorial covers;

  • The purpose of the INDEX and MATCH functions individually.
  • The advantages of this combination over the more common VLOOKUP.
  • Using INDEX and MATCH to create a reverse lookup that can look to it left.
  • Create a two way lookup that finds the row and column of the data for a very flexible formula.
08:21

The #N/A error message is returned when functions such as VLOOKUP and MATCH cannot find what you asked them too. This message can be hidden if required, or an alternative action taken such as looking on a different sheet for it, or displaying different text such as “Not Found”.

This tutorial covers;

  • Using the ISNA function to detect the presence of a #N/A error.
  • Combining this with the IF function to take the appropriate action.
  • Displaying text in the cell instead of the error.
  • Using the new IFNA function.
Exercise 1: Lookup Functions
Article
Section 7: Protecting and Sharing Excel Files
02:37

If a workbook is saved to a shared drive you may want to prevent unauthorised access to the file. Encrypt the file with a password to ensure that only authorised personnel may open the file.

04:44

A workbook can be protected to lock the structure and/or windows of a workbook.

Protecting the structure locks sheet related activity such as users unhiding, renaming, deleting or inserting sheets. Protecting the windows prevents users from using features such as Freeze Panes and Arrange All.

This tutorial covers;

  • Hiding a worksheet
  • Applying workbook protection to prevent users from unhiding the worksheet
09:18

Worksheet protection can be used to protect different elements of a spreadsheet. It can be used to protect your formulas and prevent users from unhiding columns among other things.

This tutorial covers;

  • Unlocking the cells of a spreadsheet you do not want to protect
  • Setting the cells containing formulas to hidden
  • Protecting the worksheet with a password
  • A demonstration of the improved usability of a protected worksheet
05:38

Excel spreadsheets have exclusive access by default. This means that only one person at a time can modify them. Sharing a workbook will allow multiple people to edit a spreadsheet at the same time.

This tutorial covers;

  • Sharing a workbook so more than one user can open it at the same time
  • Viewing what users have that spreadsheet open
  • Modifying the settings for workbook sharing
  • Setting a workbook back to exclusive access
Section 8: Using Conditional Formatting
09:49

Apply Conditional Formatting to your spreadsheets to format cells based on a condition. For example, you could format cells red if a deadline is missed, a task is due to start, or a target is achieved.

This tutorial covers;

  • The different types of rules you can create using Conditional Formatting.
  • Formatting the cells that contain a date in the past.
  • Adding a second conditional format to format cells if the date occurs within the next 2 weeks.
05:34

Data bars and icon sets are visual indicators that can be applied to bring our attention to values that meet certain criteria.

This tutorial covers;

  • Applying icon sets to a range of values to visualise targets being achieved.
05:25

If you are going to be using conditional formatting on a spreadsheet you will need to be able to manage the rules. Excel makes it easy to be able to move, edit or delete conditional formatting rules that exist on a spreadsheet.

This tutorial covers;

  • Viewing all the conditional formatting rules on a worksheet.
  • Editing an existing rule.
  • The importance of the order that conditional formatting rules run.
06:23

Applying conditional formatting to individual cells is fantastic, but if your data was quite wide, formatting the entire row will make it more visible.

To format the entire row we will need to create the rule a little bit different to the way we have been doing.

This tutorial covers;

  • The benefits of formatting the entire row.
  • Using a formula for a conditional formatting rule.
  • Using a mixed reference in the formula.
Exercise 1: Using Conditional Formatting
Article
Exercise 2: Highlight Unmatched Items
Article
Exercise 2 Answer: Highlight Unmatched Items
06:37
Section 9: Validating Data Entry
02:31

If certain text entries need to be a specific number of characters, you can apply validation to a prevent entries that are not that particular text length.

This tutorial looks at validating data entry to be exactly 5 characters in length.

02:26

You can validate date entries in Excel to ensure that they are within a certain range or only a date that occurs this week etc.

This tutorial covers;

  • Setting a data validation rule to prevent the entry of future dates
  • Using the TODAY function for validating date entries
04:25

Drop down lists provide an easy and user friendly way for people to enter text data. This ensures that the data is valid. Entries that do not appear in the list will not be allowed.

This tutorial covers;

  • Creating a data validation list by entering the values yourself
  • Creating a data validation list by referring to a range of cells on another sheet
  • Using the drop down list to enter data
05:16

Messages can be created to assist the user with their data entry. They can be used to warn the user of problems and explain common data entry issues.

This tutorial covers;

  • Setting up input messages to inform the user of how the data should be entered
  • Setting up error alerts to prevent incorrect data entry and explain where they may have gone wrong
  • Change the style of the error alert to warn the user, but not prevent them from entering the data
06:37

You can create a drop down list that is dependent upon the selection made in another list. By doing this a large list can be shortened by only displaying the relevant options.

This tutorial covers;

  • Creating a drop down list of products which is dependent on what product category was chosen
  • Using the INDIRECT function
  • Using defined names in a workbook
Article

Exercise 1 Answer: Validating Entry of Dates
02:15
Article

Exercise 2 Answer: Validating Entry of Values
02:05
Section 10: Analysing Data
07:45

The subtotals feature of Excel enables you to insert subtotal rows at particular points within a list. A selection of different aggregate functions can be used for the subtotal rows including sum, count and average.

This tutorial covers;

  • Applying subtotals to a list to sum values by each salesperson in the list.
  • Using the outline buttons to view the totals at different levels of the outline.
  • Applying multiple subtotals.
05:11

The SUMIF function will enable you to sum all the values in a list dependent on a condition. For example, sum all the orders for a specific product, or for a particular month.

This is possibly the most popular analysis function of Excel. For counting and averaging, the COUNTIF and AVERAGEIF functions can be used respectively.

This tutorial covers;

  • Writing a SUMIF function to sum all the order amounts where the order was taken by a specific salesperson.
  • Using a drop down list to make the formula interactive.
05:57

The SUMIFS and COUNTIFS functions can be used to sum and count values based on multiple conditions.

This tutorial covers;

  • Understanding the SUMIFS and COUNTIFS functions syntax
  • Using them to sum or count the order amounts for orders by a particular salesperson, and for a particular product category
06:18

The SUBTOTAL function can be used to perform a function on a filtered list. The SUBTOTAL function will ignore the hidden rows on filtered lists. Functions that SUBTOTAL can perform include sum, count, average and more.

This tutorial covers;

  • Using the SUMIFS and COUNTIFS functions to find the total and count of orders in a list.
  • Filtering the list of orders to see the functions working.
Exercise 1: Applying Subtotals
Article
Exercise 2: Using SUMIFS
Article
Section 11: Working with Charts
05:06

Charts are hugely important for reporting and therefore also Excel. Unfortunately some people do not have much experience or the necessary knowledge to work with charts in Excel.

Excel 2013 provides improved descriptions and previews of charts before you apply them, helping you to choose the right one. It also includes a new feature called recommended charts which display your selected data in various charts so you can pick the one you want.

This lesson covers;

  • Viewing different chart descriptions and previews of selected data.
  • Using the Recommended Charts feature in Excel 2013.
  • The Quick Analysis feature of Excel 2013 for fast chart creation.
09:40

Column charts are used to compare values across different categories. They are probably the most common type of chart.

This tutorial covers;

  • Creating a column chart to compare profit between different months.
  • Formatting some of the common elements of a column chart.
  • Adding data labels to display the values on the chart.
  • Removing elements from a column chart.
10:01

Pie charts are used to display contributions to a total. They can only handle one series of data and the values must be positive.

This tutorial covers;

  • Creating a pie chart to display the sales of 5 different categories of product.
  • Editing elements of a pie chart such as the title and the legend.
  • Working with data labels in a pie chart.
  • Separate one of the data points (pie segments) from the chart.
07:15

A combo chart enables you to display two different chart types in one chart. They can be used when your charts source data contains different types of data. A secondary axis can be created allowing the two different data types to co-exist on one chart.

This tutorial covers;

  • Creating a combo chart with a secondary axis to display different data types on one chart.
  • Modifying the scale of the axis on a combo chart to provide more detail.
03:14

Chart filters is a new feature to Excel 2013. They enable you to filter a chart by data series and by category.

This lesson will cover;

  • Using the Chart Filter feature of Excel 2013.
  • Filtering a pie chart and a column chart.
14:57

Make a chart interactive so that users can select what information they want to see in the chart. This can help save space on a spreadsheet as you would not need separate charts.

This tutorial covers;

  • Creating an interactive chart so that users can select what is displayed from 3 different options.
  • How to show the Developer tab on the Ribbon.
  • Adding a combo box form control to a worksheet.
  • Setting the combo box parameters.
  • Using the INDEX function and ROW functions.
  • Creating a line graph.
  • Moving a chart between worksheets.
05:31

A Sparkline is a small chart that fits inside a single cell. It’s a great technique for reporting dashboards as it enables us to visualise data whilst preserving space on a worksheet.

This tutorial covers;

  • Inserting a Sparkline to compare data across different months.
  • Formatting the Sparkline.
  • Changing the Sparkline chart type.
Section 12: Importing Data into Excel
06:17

Data can easily be imported from a text file into Excel. A text file is typically created by exporting data from some other system, or is downloaded from a website.

The columns of a text file are typically separated by a delimiter character. Excel will use this character to know how to structure the data across columns.

This lesson covers;

  • Importing data from a text file.
  • Using the Text Import Wizard.
09:19

As more and more information is stored online, it is useful to know how to import data from a webpage.
Depending on the structure of the webpage, you may be able to import a particular part of that page.

A connection is established between the webpage and the spreadsheet after import. This can be refreshed to update the information in the spreadsheet.

This lesson covers;

  • Importing data from a webpage on the Internet.
  • Modifying the properties of the data connection.
  • Managing all existing data connections in a spreadsheet.
  • A neat shortcut for maximising windows.
04:57

Import data from a table or query in an Access database into Excel.

After importing the data, a connection is established. This means that there is a link between the spreadsheet and the Access table or query. The connection can be refreshed to update the information in the spreadsheet.

This lesson covers;

  • Importing data from a query in the Northwind database.
  • Managing the existing connection with the query.
  • Naming the table of imported data.
  • Refreshing the connection to the query.
Exercise: Importing Data From a Text File
Article
Section 13: Cleaning and Preparing Data
05:03

Text to Columns can be used to split a cell’s content into multiple columns. This tool can help you organise poorly structured data that you have been sent, or imported.

This tutorial covers;

  • Inserting columns to prepare for new data.
  • Using the Text to Columns feature to split an employee’s first name and last name into separate columns.
04:30

The CONCATENATE function can be used to join text together from multiple ranges.

This tutorial covers;

  • Using CONCATENATE to join an employee’s first name and last name together from two different cells.
  • Inserting your own text strings into the function.
12:35

Excel provides over 25 text functions to help you work with text entries in a spreadsheet. Three functions called LEFT, MID and RIGHT can be used to extract text characters from a cell, and insert them into a different column.

This tutorial covers;

  • Using the LEFT function to extract characters from the beginning of a cell
  • The TRIM function to remove any spaces from the start and end of a cell
  • Using the RIGHT function to extract characters from the end of a cell
  • Using the MID function to extract characters from the middle of a cell
  • A function called LEN that returns the number of characters in a cell (length)
  • Determining the starting position and/or number of characters for the MID function by being clever in our formulas .
07:46

The MID function is used to extract characters from the middle of a cell. To do this the function needs to know what character to start with, and how many to extract.

If the starting character is not always the same number, the FIND function can be used to return this. The FIND function will need a character to look for to identify the starting position.

This tutorial covers;

  • Understanding how the MID and FIND functions can be used together.
  • The syntax of the FIND function.
  • Using this combination to extract a year that follows a film title in a cell.
03:23

The VALUE function is used to convert text that represents a number to a number. If you are extracting a number from a text entry, you will need to convert it to a value to make it useable.

This tutorial covers;

  • How you can recognise text when stored as a number.
  • Using the VALUE function to convert the year from the previous tutorial.
08:41

The Flash Fill tool is a new feature to Excel 2013. This feature makes many tasks in Excel very simple.

This lesson covers using Flash Fill to;

  • Splitting firstname and lastname into separate cells.
  • Joining firstname and lastname into the same cell.
  • Extracting a name from an email address.
  • Reversing names in a cell.
  • Changing the case of text to upper, lower or proper case.
  • Extracting a piece of text from the middle of a cell.
  • Extracting the year from a date.
Exercise 1: Text to Columns
Article
Exercise 2: Using Text Functions
Article
Section 14: Formatting a Range as a Table
11:16

This lesson looks at formatting a range of cells as a table. This is a wonderful new feature to make your tables of data easier to handle and to reference.

This lesson covers;

  • The benefits of using tables.
  • How to format a range as a table.
  • Changing the style of your table.
  • Naming your table for simple referencing.
  • How to quickly navigate to a table and to view existing tables on a spreadsheet.
03:56

When referencing the cells of a table from a formula, Excel displays structured references. This is a new style of referencing and can take some getting used to. However it makes your formulas more readable and makes cross sheet referencing easier.

This lesson covers;

  • Using structured references in a SUMIF function.
  • The ease of referencing across different sheets with structured references.
02:18

If you don't wish to have your data in a table anymore, it can be converted back to a normal range.

This lesson covers;

  • Converting your table back to a normal range.
  • Best practice to remove formatting from the table before converting.
Section 15: Working with PivotTables
11:39

PivotTables make it easy to summarise large lists of data and drill to details. They make complicated lists of data readable and are Excel's main reporting tool.

This lesson covers;

  • Creating a PivotTable from a list formatted as a table.
  • Displaying different fields in the PivotTable.

04:23

By default, PivotTables sum the values that you ask it to calculate. They also remove the formatting of the values. Fortunately we can change the formatting and the function that our PivotTable uses.

This tutorial covers;

  • Changing the function used to calculate the values in the PivotTable.
  • Formatting the values of the PivotTable.
  • The difference between formatting the field and the cells.
03:57

You can sort PivotTable data to order it the way you would like. Sorting PivotTable data is similar to way we sorted lists at the start of this course, but with a little difference.

This tutorial covers;

  • Sorting the fields of a PivotTable.
  • Sorting a PivotTable by different fields of its outline.
06:01

You can group any numerical field in a PivotTable. Grouping fields enables you to summarise data at the level that you want.

The most popular use of this is to group date fields into specific time periods such as years, quarters or months.

This tutorial covers;

  • Displaying a date field in the PivotTable.
  • Grouping a date field into years and months.
  • Using the column labels area of the PivotTable.
  • Using new fields created by the PivotTable as a result of the grouping.
05:41

PivotTables are typically used to summarise your values using an aggregate function such as SUM, COUNT or AVERAGE.

However you can ask a PivotTable to show your values as a percentage, the difference of another field or a previous value, or even as a ranking. There are lots of possibilities for how you can present your results.

This tutorial covers;

  • Showing values as a percentage of the column total.
  • Ranking the values in the list.
03:52

PivotTables have two types of filtering. You can apply filters to individual fields, or to the entire PivotTable report.

This tutorial covers;

  • Filtering by the fields included within the PivotTable.
  • Applying the report filter.
  • Filtering a PivotTable by multiple criteria for powerful reporting.
05:34

Although PivotTables are linked to the data used to create them, they do not update automatically when data is changed. PivotTables need to be refreshed to update them.

This tutorial covers;

  • Refreshing a PivotTable, or all the PivotTables in the workbook.
  • A demonstration of refreshing a PivotTable to include new orders added to the bottom of the data source.
  • Setting the option to refresh a PivotTable when opening the file.
  • Viewing the option to enable show details.
03:48

Create a PivotChart to visualise the data that has been produced by the PivotTable.

PivotCharts are just like normal charts, but they are based on a PivotTable to chart more complicated data. The other key difference is that a user can interact with them directly to produce different reports.

This tutorial covers;

  • Creating a column chart from a PivotTable.
  • Changing some basic formatting options on the PivotChart.
  • Filter the data in the PivotChart.
  • Moving a PivotChart onto a different sheet.

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Alan Murray, Founder of Computergaga

Alan Murray is a Microsoft Office trainer and consultant. He has been training and consulting for the past 15 years for businesses around the world.

He is the founder of Computergaga and regularly writes on the Computergaga blog to share tips, tutorials and templates.

Alan uses a fun and relaxed style of training that gets to the point, and uses real world practical examples uncovered from his experience of training and developing software for businesses.

Files are provided to follow along and exercises used to recap on topics covered. Alan is always eager to help and will be there for you when needed. He will do his best to answer any question or query related to his courses within 48 hours.

Ready to start learning?
Take This Course