Module 1: Managing Data1
Consolidating Information1
Configuring Data Validation7
Converting Text to Columns12
Using FlashFill16
Transposing Data17
Module 2: Outlining and Grouping Data19
Outlining Data19
Showing and Hiding Outline Details22
Grouping Data25
Creating Subtotals28
Removing Outlining and Grouping33
Module 3: Analyzing Data35
Using Goal Seek35
Using Data Tables38
Module 4: Using Solver43
Setting up the Worksheet43
Running Solver46
Generating Reports and Scenarios with Solver54
Modifying Constraints58
Setting Solver Options60
Module 5: PivotTable Features63
Creating a Basic PivotTable63
Creating a Basic PivotChart68
Using the PivotTable Fields Pane74
Adding Calculated Fields78
Sorting Pivoted Data81
Filtering Pivoted Data86
Using Slicers91
Creating Timelines94
Module 1: Managing Data
In this Module, we will learn how to:
Consolidate information
Remove duplicate values
Configure data validation
Convert text to column
Consolidating Information
To begin, open Module 1-1-1, Module 1-1-2, and Module 1-1-3 using Microsoft Excel 2016.
Consolidating workbooks means combining, condensing, and summarizing data from multiple sources into one destination. For example, a large company may have sales or profit data broken down into several different geographical locations and stored in different workbooks. Each workbook would have the same layout and structure, but the data in each will be different. With Excel, you can consolidate the different workbooks, summarizing and totaling the data in one place to get a unified view of your information.
To consolidate workbooks in Excel, you first need to open the workbooks that contain the data you want to consolidate and the destination workbook. It is a good idea to set up the destination worksheet to be just like the worksheets that will supply the data, so that the destination worksheet has the same labels and uses the same layout and cell locations for the same types of data. (This is not mandatory, but it will make the consolidation process a lot easier.)
Amongst the workbooks that you current have open, Module 5-1-1 will serve as the destination workbook. You will notice that all three workbooks have a similar structure in terms of columns, rows, and labels:
With the destination workbook open (Module 5-1-1), select cell B4 and then click Data → Consolidate:
This action will display the Consolidate dialog:
The Function menu contains a variety of functions that you can use to combine data. In this example, the default SUM function is used.
The Reference box is where you enter the ranges of data to be consolidated. You can enter the ranges manually or by selecting them from the different workbooks with your mouse or keyboard. (The latter method is recommended as it is much easier.) Once a range is entered, click Add to add it to the “All references” box, which will contain all the workbook cell references that are to be consolidated.
For this example, click in the Reference field. Select the cell range B4:E15 in the Module 5-1-2 workbook:
This range of cells will now appear inside the Reference text box. Click the Add button to add these cells to the “All references” box:
Select cells B4:E15 in the Module 5-1-3 workbook: Click Add:
The Consolidate dialog will look like the image below. Click OK:
The data from the two tables will now be added together in the destination workbook:
(You may need to adjust the column widths to see all the data.) Save the changes that you have made to Module 1-1-1. Close Microsoft Excel 2016 without saving your changes to the other open workbooks.
Configuring Data Validation
To begin, open Module 1-3 using Microsoft Excel 2016.
Data validation is used to control the type of data that users are able to input into a particular cell. For example, if a cell requires only whole positive numbers, you can enforce this rule using data validation.
To apply data validation to a particular cell or range of cells, you first need to select the cell(s) in question. For this example, select cells C2:C5:
Next, click Data → Data Validation (not the drop-down):
The Data Validation dialog will now be displayed:
On the Settings tab of this dialog, you can use the Allow drop-down menu to select the type of data that you only want to allow in these selected cells. For this example, select “Whole number:”
After a specific data type has been specified, you can then tailor it even further. For example, when “Whole number” is selected, you specify a maximum or minimum value. For this example, enter 10000 as a minimum and 100000 as a maximum. Click the Input Message tab:
On the Input Message tab, you can specify the message that will be displayed when the cell with the validation rule is selected. This is a good place to explain the rules to workbook users. Enter the title and input message shown in the image below. Click the Error Alert tab:
The Error Alert tab contains controls to customize the alert dialog box that will be displayed if a user enters a value that is outside the validation rules you specified. For this example, enter the title and error message shown in the image below. Click OK:
The data validation rules have now been applied to cells C2:C5. Click any of these cells to see the input message you created:
Type 5000 into C2 and press Tab:
The dialog that you just created will now be displayed. Click Retry:
Tip: Clicking Cancel will clear the cell in question of any data.
The previous cell will then be selected. This time, type “20000” and press Tab. The adjacent cell will be selected and no error message will be displayed because the data you entered falls within the validation rules you set:
Save the changes that you have made to the current workbook and close Microsoft Excel 2016.
Converting Text to Columns
To begin, open Module 1-4 using Microsoft Excel 2016.
If you have a cell in your workbook that contains a lot of text, you have the ability to separate it into separate columns automatically if there is a logical character that separates the text (such as a comma, semi-colon, or period). For example, in the sample worksheet, you can see that the Name column contains information that is probably better suited to two separate columns:
To convert this text to columns, you first need to select the cells in question. For this example, select cells C3:C6:
Next, click Data → Text to Columns:
This action will open the Convert Text to Columns Wizard. In the first stage of the wizard, you need to choose between delimited and fixed width data types. Delimited data types are those that are separated by characters (as the sample data is). Fixed width can be used when data is separated by one or more spaces. For this example, ensure that Delimited is selected and then click Next:
In the next stage of the wizard, you can select the delimiter type that the selected data uses. Since commas are used, uncheck the Tab box and check the Comma box. As soon as you make this change, the “Data preview” section will show what the data will now look like. Check the Space checkbox, since a space also appears between these two sets of information. Click Next:
In the final stage of this wizard, you have the option of specifying the format this data will be in, where it will go, and more. For this example, leave the remaining settings unchanged and click Finish:
A new column of data containing only the first names of staff will now appear adjacent to the column that you had previously selected:
Save the changes that you have made to the current workbook and close Microsoft Excel 2016.
Using FlashFill
Flash Fil is a data tool which will l automatically combine, extract or transform data based on a patterns of sample data entry. For example, you can use Flash Fill to separate first and last names from a single column or combine first and last names from two different columns.
You only need to provide a couple examples of the results you want. Excel will guess the pattern and fill in the rest of the data for you.
Flash Fill was able to:
- Extract the first letter from the first name and transform it to lower case.
- Transform the last name and company name to lower case.
- Combine these with periods, @ symbols and .com extensions to create the email address.
The Flash Fill command can be found in the Data tab of the ribbon. Go to the Data tab ➜ Flash Fill in the Data Tools section.
The Flash Fill command can be found in the AutoFill option after the copy and paste actions completed.
Select the Flash Fill worksheet.
To extract the customer’s first name into column D using FlashFill.
- In cell D3, enter John.
- In cell D4, type Ja, you will see the AutoComplete will display Janet in the cell
- For the subsequent cells below, FlashFill will display a list of names based on the detect pattern on cell D3 and D4.
- If the pattern filled by FlashFill is your desire result, press Enter to accept.
Transposing Data
Everyone has different preferences in reading at data. Some will create Excel spreadsheets where the main fields go across horizontally in a row. Others put the data vertically. Sometimes these preferences lead to a scenario where you want to transpose Excel data. Transpose is an Excel function that allows you to switch Excel columns to rows without retyping the data.
Select the Transpose worksheet.
The example records are arranged horizontally. In order to perform Filter on records, these records should be converted into vertical (column).
Transpose Columns & Rows Using Copy & Paste.
Select the celss from A1 to G3.
From the Home tab, select Copy (or Ctrl+C).
Select the new cell where you would like to copy your transposed data. Example, select cell A6.
From the Home tab, select Paste (lower section) and hover mouse to Transpose. You can see the data layout change
Module 2: Outlining and Grouping Data
In this Module, we will learn how to:
Outline data
Show and hide details of outlined data
Group data
Create subtotals
Remove outlining and grouping
Outlining Data
To begin, open Module 2-1 using Microsoft Excel 2016.
Excel’s automatic outlining feature makes outlining a worksheet easy. Automatic outlining works best with numerical data organized into groups and sub groups by formulas or functions. For example, the sample workbook contains monthly sales data, organized into quarterly and yearly totals using formulas:
As you can see, it can be difficult to discern the quarterly and yearly totals at a glance because they blend in with the other data in the worksheet. To help get around this problem, you can outline the data. To start, click Data → Group → Auto Outline:
The results of Excel’s automatic outlining feature will now be displayed:
You will see that outline group indicators (the thick black lines that look like large brackets) and collapse buttons (marked with a minus sign) are now part of the worksheet:
Save the changes that you have made to the workbook and then close Microsoft Excel 2016.
Showing and Hiding Outline Details
To begin, open Module 2-2 using Microsoft Excel 2016.
In the sample worksheet, each quarter has been grouped with the other quarters. As well, each year has been grouped together. (In this case, there is only one year.)
Click the collapse buttons beside each quarter:
Now, only the quarterly and yearly totals are displayed:
Using this method, you can quickly hide the details of your spreadsheet and only view the totals. However, there is an even faster way of hiding and showing the details of an outlined spreadsheet.
Tip: When working with outlines, keep in mind that only the data that is visible on your screen will be included if the worksheet is printed.
At the top of the worksheet, there are numbered buttons arranged in a row. Clicking on these buttons will expand or collapse all of the rows in a worksheet to show or hide the details. For example, click on the number 3 button:
You will now see the full details of your worksheet:
Click on the number 2 button:
This action will collapse the worksheet to only the second level of detail for the rows and/or columns. In this example, this means that only the quarterly totals will be shown:
Click on the number 1 button:
This action will collapse the worksheet to only the first level of detail for the rows and/or columns. In this case, only the year total will be displayed:
Save the changes that you have made to the current workbook and close Microsoft Excel 2016.
Grouping Data
To begin, open Module 2-3 using Microsoft Excel 2016.
While the automatic outlining capabilities in Excel 2016 work very well with numerical data, it is not so effective when working with non-numerical values or data that has no distinctive totals. Luckily, you have the option to manually group your data.
In the sample workbook, there are no formulas or numeric totals, so you will need to group the data manually. For this example, let’s begin by grouping the members in the East Division together. Select cells A5:E7 and then click Data → Group → Group:
This action will display a dialog box that asks you if you want to group by rows or columns. For this example, ensure that Rows is selected and then click OK:
An outline indicator and a collapse/expand button will be created for the new group:
Follow the same procedure for cells A9:E11 and again for cells A13:E15. Your worksheet will now look like this:
Clicking the number 1 button will display the highest level view (lowest detail) of the manual groupings:
Save the changes that you have made to the current worksheet and close Microsoft Excel 2016.
Creating Subtotals
To begin, open Module 2-4 using Microsoft Excel 2016.
Yet another outlining technique available in Excel 2016 is the Subtotals feature. Using this tool, you can create automatic subtotals and grand totals for numeric data that is organized with clear column and row headings. For example, the sample workbook contains sales information for different regions and time periods:
To use the subtotal feature, you first need to select a range of cells that you would like to work with. For this example, select A3:F15. Next, click Data → Subtotal:
This action will open the Subtotal dialog box:
In the “Add subtotal to” section, you can select the columns you want totals for. For this example, ensure that only the names are selected. Click OK:
Your worksheet will now look like this:
You will see that new subtotals for each region have been created, as well as a grand total that adds all of the values together. In addition, Excel has automatically grouped this data together in a numbered outline. The outline commands work exactly the same as they do elsewhere in Excel: the lower the number on the button you click, the less detail will be shown.
To remove subtotals, select the range of data in question and then click Data → Subtotal. This action will display the Subtotal dialog box. In this dialog, click the Remove All button:
Save the changes that you have made to the current workbook and close Microsoft Excel 2016.
Removing Outlining and Grouping
To begin, open Module 2-5 using Microsoft Excel 2016.
To remove the outlining from a worksheet, click Data → Ungroup → Clear Outline:
The outlining for the current worksheet will be removed:
If you would like to remove individual groupings from your current worksheet, select a cell inside the group. Next, click Data → Ungroup:
This action will open the Ungroup dialog box. Choose the object type you would like to ungroup (rows or columns) and then click OK:
The selected grouping will then be removed.
Save the changes that you have made to the current workbook and close Microsoft Excel 2016.
Module 3: Analyzing Data
In this Module, we will learn how to:
Use Goal Seek
Use data tables
Using Goal Seek
To begin, open Module 3-1 using Microsoft Excel 2016.
Goal Seek is another useful what-if analysis tool built into Excel. With Goal Seek, you can set a formula to a value (goal) that you would like to attain, and then specify one of the cells that the formula references as a cell that Excel should adjust in order to reach that goal.
Let’s look at the sample workbook:
The total profit is calculated by subtracting total income (B4) from total costs (B7). Total income itself is calculated by multiplying units sold by price per unit (B2*B3). Total costs are calculated by multiplying units sold by material per unit, plus labor: (B5*B2)+B6.
At just a glance, you can see that this company is losing money. Using Goal Seek, you can find a breakeven point based on the changes to a cell that is referenced (directly or indirectly) by the profit formula (B8).
To start, click to select B8 and then click Data → What-If Analysis → Goal Seek:
This action will open the Goal Seek dialog:
As you can see, the “Set cell” field has already been filled in with cell B8. Inside the “To value” text box, you need to enter the goal you are trying to reach. For this example, type “0,” as this is the breakeven point:
Next, in the “By changing cell” you need to enter the cell(s) that can be changed. For this example type B2 (the number of units required). Click OK:
You will see numbers in the worksheet change as Excel searches for the appropriate number of units to sell in order to reach the specified target value. As this is happening, the Goal Seek Status dialog box will be open. This dialog will report when a solution has been found and that solution will be displayed directly on the worksheet:
Tip: If you are working with a very large set of data, Goal Seek may take some time to complete. You will not be able to work with Excel while it is processing the data. If you need to use Excel during this time, you can click the Pause button in the Goal Seek Status dialog to pause the work. You can also click the Step button to proceed through the calculations one step at a time.
In this example, Excel found that setting the units sold (B2) to 300 met the stated goal without having to adjust any of the other variables. Inside the Goal Seek Status dialog, click OK:
The new values will be left in the worksheet. Save the changes that you have made to the current workbook and close Microsoft Excel 2016.
Using Data Tables
To begin, open Module 3-2 using Microsoft Excel 2016.
A data table consists of a range of cells on a worksheet. In this range, you can change one or two variables to see how this change will affect the results of any formulas. This process allows you to perform a kind of what-if analysis on the data. While there are two types of data tables you can create (one-variable and two-variable), for the purpose of this exercise we will focus on the one-variable type.
The sample worksheet is currently calculating annual interest rate amounts for a loan at a 2.5% interest rate. This is calculated by multiplying the outstanding debt (B2) by the current interest rate (B3), and then adding the outstanding debt to the total (B2):
Below this information is the data that you will use to create a data table. The total debt formula is displayed in cell B7, followed by different interest rates:
Select this range of cells (A7:B16) and then click Data → What-If Analysis → Data Table:
This action will open the Data Table dialog box:
Click inside the “Column input cell” text box and then click on the current interest rate (B3), since this is single variable we will be changing:
Click OK:
You will now see how the different annual interest rates in the table affect the total debt that will be accrued:
Click Formulas → Show Formulas:
You can see that Excel has entered a formula using the Table function in the range you selected:
You should also note that each instance of this formula uses the interest rate percentage in B3 to determine the possible outcome. Save the changes that you have made to the current workbook and close Microsoft Excel 2016.
Module 4: Using Solver
In this Module, we will learn how to:
Set up a worksheet for Solver
Run Solver
Generate reports and scenarios with Solver
Modify Solver constraints
Set Solver options
Note that Velsoft is not responsible for the content of external links.
Note that Velsoft is not responsible for the content of external links.
Setting up the Worksheet
To begin, open Module 5-1 using Microsoft Excel 2016.
In order for Solver to work properly, the worksheet must be set up in a specific way. Most importantly, you must have a solid understanding of the problem that you are trying to solve.
The sample workbook is for a business that assembles and sells furniture. This business currently sells two types of dining tables: one made from maple and another from mahogany. While the maple table is of lower quality, it is easier to manufacture and sell. The mahogany table, on the other hand, is of a much better quality, but the price is much higher and it is more time intensive to produce. From examining the worksheet, you can see that it takes three hours of rough carpentry to assemble the maple table, while the mahogany table takes four hours. After the table has been assembled, it takes two hours of finish work to get the mahogany table to showroom standards, but only one with the maple table:
The problem that you need to solve is to figure out how many of each type of table can be constructed to maximize weekly total revenue for the company, as well as what the maximum total revenue will be.
What complicates this problem is that the business employs one part-time finish carpenter who is only available for a total of 30 hours per week. The two rough carpenters employed by the company are full-time employees who are available for a combined total of 80 hours a week. In addition, the lumber yard that supplies the company can only provide enough mahogany for up to 10 tables a week. All of these factors are constraints and are outlined in the lower right-hand corner of the worksheet:
Click Formulas → Show Formulas:
Now, the worksheet will display all of the formulas that are included in it:
Cells B3 and C3 outline the total work hours required to build a maple table, while cells B4 and C4 outline the total work hours required to build a mahogany table. The revenue from the maple tables (F3) is calculated by multiplying the number of tables assembled by the sale price. The revenue from the mahogany tables (F4) is calculated by a similar formula. Notice that in both cases, the revenue formulas depend on the number of tables assembled (D3 and D4). Because of this, the total revenue formula (F5) is dependent on D3 and D4.
The formula we want to optimize (also known as the objective formula) represents the total revenue (F5):
The cells that will change to maximize the total revenue formula represent the quantities of each type of table assembled (D3 and D4):
The constraints for this problem are shown in the lower right-hand corner. It is not necessary to label the constraints as they are shown here, but clearly identifying them on the worksheet is a big help when entering them into Solver:
The constraints include that rough work hours cannot exceed 80 hours per week, while finish work hours cannot exceed 30 hours per week. In addition, the number of mahogany tables must not exceed 10 per week.
Close Microsoft Excel 2016 without saving your changes.
Running Solver
Click Data → Solver:
This action will open the Solver Parameters dialog:
Next, since the purpose of this exercise is to find the maximum total revenue, ensure that the Max radio button is selected:
Now, you need to tell Solver what the changing variable cells will be. Click inside the By Changing Variable Cells text box and then individually click on cells D3 and D4 while holding down the Ctrl key. It is important to select these cells individually, not as a range:
The next step is to add the constraints. To start, click the Add button:
This action will display the Add Constraint dialog box:
To add the first constraint, click in the Cell Reference text box and then click the cell with the formula you want to constrain. For this example, begin by clicking on G3, which contains the formula for calculating total finish work for the maple table:
Next, click in the Constraint text box and then click the cell that contains the appropriate constraint value on the worksheet. In this case, select cell H9, which contains the value 30:
Finally, you need to use the drop-down menu in the center of the dialog to specify the type of relationship that is required between these two cells. In this case, the constraint should read as G3 <= H10. In other words, total finish work hours should be less than or equal to 30:
Click Add to add this new constraint to the solver:
The fields in the Add Constraint dialog box will now be blank and ready to receive information about other constraints. Use the same process to enter the following constraints:
- Total rough work hours less than or equal to 80: H3 <= H8
- Number of mahogany tables less than or equal to 10: D4 <= H10
Click OK when you are finished to return to the Solver Parameters dialog box. Here, you will see all of the constraints entered into the Solver Parameters dialog. No matter what order you entered the constraints in, this dialog sorts them alphabetically by cell reference:
Solver is now ready to find a solution. Click Solve:
After a moment, the Solver Results dialog will appear and the values that Solver used to optimize its results will be visible in the worksheet:
Inside the Solver Solution dialog that now appears, you will see that the Keep Solver Solution radio button is select by default. If you click OK in this dialog with that option selected, the new (optimum) values will remain in the worksheet. If you don’t like the results that Solver delivers, select the Restore Original Values radio button and click OK to return to the figures as they were before.
For this example, leave the default settings unchanged and click OK:
Once you have been returned to the Solver Parameters dialog, click Close. Save the changes that you have made to the current workbook and close Microsoft Excel 2016.
Generating Reports and Scenarios with Solver
Once Solver has been configured and run, you have the option to save the results as a scenario. This allows you save the solution without having to overwrite the existing data.
To save Solver results as a scenario, you must first run Solver. Click Data → Solver:
With the Solver Parameters dialog now open, click Solve:
Once a solution to the problem has been found, the Solver Results dialog will be displayed. Click Save Scenario:
This action will open the Save Scenario dialog box. For this example, type “Module 4 Complete” as the name and then click OK:
The scenario will be saved using the name you entered and will now be available from within the Scenario Manager.
Back in the Solver Results dialog, you will also see several report types listed in the top right-hand corner. To generate a report, click on any of the choices. (You can choose more than one report.) For this example, click to select the Answer report type. Click OK:
The new report will now appear on its own worksheet. Click the “Answer Report 1” tab:
The report will now be displayed:
Save the changes that you have made to the current workbook and then close Microsoft Excel 2016.
Modifying Constraints
Constraints are key to Solver operating correctly because they define the parameters that Solver must work within. Often these constraints are dependent upon a number of factors and can change quite often. Luckily, Excel 2016 allows you to modify Solver’s constraints.
To begin, open the Solver Parameters dialog by clicking Data → Solver:
Inside the Solver Parameters dialog, you will see commands to modify the constraints listed here in a number of ways:
To add a new constraint, click the Add button. To edit a constraint, click on it to select it and then click Change. This action will open the Change Constraint dialog:
This dialog is identical to the Add Constraint dialog; however, it contains all of the current values for that constraint. You may change these values as you wish and then click the OK button to apply any new changes.
Delete the last constraint listed by selecting it and then clicking Delete:
This action will immediately delete the selected constraint. (You cannot recover a constraint after it has been deleted.) The Reset All button will clear all of the constraints, variables, and options in the Solver Parameters dialog.
Click Close in the dialog. Save the changes you have made to the current workbook and close Microsoft Excel 2016.
Setting Solver Options
Open the Solver Parameters dialog by clicking Data → Solver:
The current worksheet already has Solver configured. Inside the Solver Parameters dialog, you will see a menu that allows you to select a solving method. By default this menu is set to GRG Nonlinear, but you have the option of selecting the Evolutionary or Simplex LP solving methods:
To understand these solving methods, you have to know a bit about formulas. The formula in the furniture company example (C3*D3+C4*D4) uses basic multiplication and addition. This means that it is a linear formula. (In other words, this formula would appear in a straight line if it was graphed.) On the other hand, a more complex formula like 3*(POWER(H11,3))+3 is a non-linear formula. This is because this formula will look like a curve when it is graphed.
This means that if you use Solver with a non-linear formula (such as formulas with exponents), you should use the default GRG Nonlinear solving method. If you use Solver with linear formulas, it is recommended that you use the Simplex LP solving method. Evolutionary Solver can be used with any formula or function.
For more advanced calculation options, click the Options button to the right of this drop-down menu:
This action will open the Options dialog, in which you can find controls to modify the functionality of all solving methods:
Briefly review all three tabs. Click Cancel when you’re done:
You will now be returned to the Solver Parameters dialog. Click Close. Close Microsoft Excel 2016 without saving any changes that you may have made.
Module 5: PivotTable Features
In this Module, we will learn how to:
Create a basic PivotTable
Use the PivotTable Fields pane
Add calculated fields to a PivotTable
Sort and filter pivoted data
Creating a Basic PivotTable
To begin, open Module 5-1 using Microsoft Excel 2016.
PivotTables are designed to make it easy to tabulate and summarize data in your worksheet. In order to create a PivotTable, you first need to select the range of data that you want to base the table on. For this example, select the cell range A2:F20:
Next, click Insert → PivotTable:
This action will open the Create PivotTable dialog box:
The range of data that you just selected will be displayed within the Table/Range text box:
You also have the option of selecting data from an external data source (such as another workbook or even a database) by selecting the “Use an external data source” radio button and clicking Choose Connection.
Lower in the Create PivotTable dialog, you can see options to choose where the new PivotTable will be placed:
For this example, leave the default settings unchanged and click OK:
A new PivotTable will be created on a new worksheet. This worksheet will automatically be displayed with the PivotTable area in the upper left-hand corner:
Now that the PivotTable has been created, you can start adding information to it. Do this by checking the boxes beside the Month, Salesman, Region, and Profit fields in the PivotTable Fields pane:
The PivotTable will now be populated with the data that you selected:
As you can see in the following image, the profit has been organized by Month and by Salesman, with a total profit for each Salesman in the Sum of Profit column. Because Region has been checked in the PivotTable Fields pane, you can also see a profit breakdown by region for each salesman:
Save the changes that you have made to the current workbook and close Microsoft Excel 2016.
Creating a Basic PivotChart
In Excel 2016, you can create a PivotChart from scratch, or you can create a PivotChart based on an existing PivotTable. To create a PivotChart from scratch, first select the range of data that you would like to work with. For this example, select cells A2:F20:
Next, click Insert → PivotChart → PivotChart:
This action will open the Create PivotChart dialog:
At the top of the dialog, you will see that the Table/Range text box contains the data range that you just previously selected. You can also select a table/range from an external data source (just like when creating PivotTables):
The lower portion of this dialog lets you choose where the new PivotChart will be placed:
For this example, leave the default settings unchanged and click OK:
A new worksheet will be created and opened automatically. You will see a PivotTable area, a PivotChart area, and the PivotChart Fields pane:
From here, you may then start building the PivotChart.
You can also create a PivotChart based upon an existing PivotTable. Switch to worksheet “Sheet2” by clicking on its tab:
Here, you will see an existing PivotTable:
Click anywhere inside of this PivotTable. Next, you can choose any of the charts from the Insert tab. For this example, click Insert → Column → Stacked Column:
Tip: If you are unsure what kind of chart would be suitable for your data, click Insert → Recommended Charts for some suggestions.
The new PivotChart, based upon the existing PivotTable, will appear in the middle of the current worksheet:
Save the changes that you have made to the current workbook and close Microsoft Excel 2016.
Using the PivotTable Fields Pane
The PivotTable Fields pane will be displayed once a PivotTable has been inserted. In the sample workbook, you can see it on the right-hand side of the Excel 2016 window:
If you cannot see this pane, click PivotTable Tools – Analyze → Field List:
The Fields section lists all of the various fields that you can add to the PivotTable. If a field has a checkmark beside it, this indicates that the field is already part of the PivotTable. In this example, you can see that Month, Salesman, Region, and Profit are all part of the PivotTable:
In the upper right-hand corner of this section, you can see the Tools drop-down command. Click this command to see different layout options for the PivotTable Fields pane:
As you can see, there are five preset layouts that you can choose from. To apply a new layout, click on one of the options listed. (Generally, it is best to choose a layout that contains both the Fields and Areas sections.) Click the Tools button again to close the menu.
The Areas section of this pane allows you to specify what fields appear as filters, columns, rows, and values:
Close Microsoft Excel 2016 without saving any changes.
Adding Calculated Fields
You can add calculated fields to PivotTables that perform calculations on the data in the table. For example, in the sample worksheet, you could create a calculated field that will calculate a bonus for each employee that is 3% of the profit total.
To start, click to select any cell in the PivotTable. Next, click PivotTable Tools – Analyze → Fields, Items, & Sets → Calculated Field:
This action will open the Insert Calculated Field dialog:
First you need to set a name for this new field. Type “Bonus” into the Name field:
Next, you need to enter the formula for this new field. In this case you need to multiply the Profit field by 3%, so type “= Profit * 3%” into the Formula text box:
Tip: Note the list of available fields in the lower portion of this dialog. Clicking on a field will automatically insert it into the Formula text box.
With the calculated field now complete, click Add to save it:
Click OK to close the dialog:
The new calculated field will now automatically appear in the PivotTable:
Save the changes that you have made to the current workbook and close Microsoft Excel 2016.
Sorting Pivoted Data
To sort data in a PivotTable, click the arrow in the top left-hand corner of the table:
This menu will feature a few sorting options:
Choosing the “Sort A to Z” option will sort the table rows in ascending order. Choosing “Sort Z to A” will sort the table rows in descending order. If there are multiple row fields in the table, you can specify which field to sort using the “Select Field” drop-down menu:
For expanded sort options, click More Sort Options:
This action will open the Sort dialog:
Again, you will see options to sort data in ascending or descending order. In addition, once you select a radio button, you can click the corresponding menu to choose a field to sort by. There is also a button on the bottom of the Sort dialog labeled More Options. If you click this button, another dialog will appear with an AutoSort option. If this option is selected, the data will be sorted automatically every time the PivotTable is updated.
For this example, click the “Ascending (A to Z)” radio button and then choose “Sum of Profit” from the drop-down menu:
Click OK:
You will now be returned to the Excel 2016 window. The “Sum of Profit” values will be sorted in ascending order based on the profit values:
Save the changes that you have made to the current workbook and close Microsoft Excel 2016.
Filtering Pivoted Data
To filter PivotTable data, click the arrow in the top left-hand corner of the PivotTable. You will see a list of filtering commands:
The list of checkboxes represents each of the unique identifiers for the table rows (months in this case). There is also a checkbox that allows you to select or deselect all items.
To filter out the data for a given month, just clear the corresponding checkbox. For example, deselect the “Month 3” checkbox and then click OK:
The data for “Month 3” is no longer displayed in the PivotTable:
Keep in mind that when a row is filtered out, any formulas that rely on that information will be affected. For example, in this case the Grand Total formula is only calculating the data from Month 1 and Month 2, not Month 3.
The appearance of the drop-down menu in the upper left-hand corner of the PivotTable will also change when a filter has been applied:
Remove the applied filter by clicking on this drop-down menu and clicking Clear Filter From “Month:”
The filter will immediately be removed. A more direct way in which you can filter a PivotTable is to use search terms. Click the arrow in the top left-hand corner of the PivotTable. Type “Month 1” into this search box and click OK:
Now only data from Month 1 will be displayed:
Save the changes that you have made to the current workbook and close Microsoft Excel 2016.
Using Slicers
Slicers can be very helpful when working with PivotTable and PivotChart data. As you know, the main purpose of PivotTables and PivotCharts is to help you analyze information and find patterns or trends that might be difficult to spot in a large volume of raw data. Slicers take this concept to the next level.
Tip: Slicers are not compatible with Excel 2007 or earlier.
To create a Slicer, first click anywhere in the PivotTable to display the PivotTable Tools tabs. Next, click PivotTable Tools – Analyze → Insert Slicer:
Now you will see the Insert Slicers dialog with all of the fields from your PivotTable. Place checkmarks to select the fields that you want to use as slicers. For this example, select only Name and TOTAL. Click OK:
Slicers will then be stacked on top of your worksheet:
Tip: You may move and rearrange slicers so that they do not overlap by clicking and dragging them by their headers.
Now, to see what is going on with a particular field, just click on that entry in a Slicer. For example, to see sales made by Ashley, click on Ashley in the Name Slicer:
When you click on a label (Ashley in this case), you will see that some labels in the other Slicers will have their original shading, while others will be shaded differently than they were before. The items with their original shading are items that are associated with Ashley. This indicates that these values are Ashley’s total weekly sales for six weeks’ worth of work. You will also see that the PivotTable has changed when the slicer was used, and now only shows the data for Ashley.
Tip: You can select more than one field label at a time from any Slicer by holding down the Ctrl key while clicking on them.
Clear the Name Slicer by clicking on the filter symbol in the upper right-hand corner of the Slicer box:
Save your workbook as Module 5 Complete.
Creating Timelines
While working with PivotTables, you have the ability to filter dates using timelines. Similar in execution and concept to Slicers, timelines allow you to select periods of time quickly and easily.
Tip: You must have a PivotTable or PivotChart in your workbook to create a timeline. Timelines are a new feature for Excel 2013.
To create a timeline, place your cursor inside a PivotTable and click Insert → Timeline:
The Insert Timelines dialog will appear with each column that uses the Date number format. Click Date and then click OK:
The Timeline will then appear on your worksheet, organized by months. By clicking on any one of the months listed, you can filter data associated with that month. Click NOV:
You can also sort by years, quarters, months, and days. Click the drop-down menu near the upper right-hand corner of the Timeline pane to view and select these options:
Timelines also feature a contextual tab:
Save the changes that you have made to the current workbook and close Microsoft Excel 2016.
Programme scope, delivery arrangements and suitability are confirmed during enquiry.