Advanced Pivot Table Tasks

Creating a Slicer

Now in Excel 2016, you can multi-select slicers by clicking the button on the label as shown above. 

1. Click anywhere in the PivotTable to show the PivotTable Tools on the ribbon.
   
2. Click Analyze > Insert Slicer. 
   
3. In the Insert Slicers dialog box, check the boxes of the fields you want to create slicers for.
4. Click OK. A slicer appears for each field you checked in the Insert Slicers dialog box. 
5. In each slicer, click the items you want to show in the PivotTable.

To choose more than one item, hold down Ctrl, and then pick the items you want to show.


Tip: To change how the slicer looks, click the slicer to show the Slicer Tools on the ribbon, and then apply a slicer style or change settings on the Options tab. 

Using the Slicer Tools Tab

To modify a slicer, click on the slicer and you will see the Slicer Tools ribbon containing Options tab.

Custom And Advanced Features In Excel

Customizing Excel By Changing Options

Before you start Microsoft Office Excel, you can make sure that a specific workbook or a workbook template or worksheet template that has custom settings opens automatically when you start Excel. If you no longer need a specific workbook to open, you can stop it from being opened when you start Excel.

If a workbook that is opened when you start Excel contains automatic macros, such as Auto_Open, those macros will run when the workbook opens. If needed, you can prevent them from running automatically when you start Excel.

Tips: You can also customize the way that Excel starts by adding command-line switches and parameters to the startup command. 

Automatically start Excel with a blank workbook

In Excel 2016 and later, Excel defaults to showing the Start screen with recent workbooks, locations, and templates upon starting. This setting can be changed to instead bypass this screen and create a blank workbook. To do so:

1. Click File > Options. 
2. Under General, and then under Startup options, check the box next to Show the Start screen when this application starts. 
3. Click OK.

Automatically open a specific workbook when you start Excel

To automatically open a specific workbook when you start Excel, you can place that workbook in the XLStart folder, or you can use an alternate startup folder in addition to the XLStart folder

Locate the XLStart folder

Any workbook, template, or workspace file that you place in the XLStart folder is automatically opened when you start Excel. To find out the path of the XLStart folder, check the Trust Center settings. To do so:

1. Click File > Options. 
2. Click Trust Center, and then under Microsoft Office Excel Trust Center, click Trust Center Settings. 
3. Click Trusted Locations, and then verify the path to the XLStart folder in the list of trusted locations.

Use an alternate startup folder

1. Click File > Options > Advanced.
2. Under General, in the At Startup, open all files in a box, type the full path of the folder that you want to use as the alternate startup folder.

Because Excel will try to open every file in the alternate startup folder, make sure that you specify a folder that contains only files that Excel can open.

Note: If a workbook with the same name is in both the XLStart folder and the alternate startup folder, the file in the XLStart folder opens.

Stop a specific workbook from opening when you start Excel

Depending on the location of the workbook that is automatically opened when you start Excel, do any of the following to make sure that the workbook no longer opens upon startup.

• If the workbook is stored in the XLStart folder, remove it from that folder. 
• If the workbook is stored in the alternate startup folder, do the following:

1. Click File > Options > Advanced.
2. Under General, clear the contents of the At startup, open all files in box, and then click OK. 
3. In Windows Explorer, remove any icon that starts Excel and automatically opens the workbook from the alternate startup folder.

 Tip: You can also right-click that icon, click Properties, and then remove any references to the workbook on the Shortcut tab.

Note: For more information about locating the startup folder, see Locate the XLStart folder.

Automatically open a workbook template or worksheet template when you start Excel

You can save workbook settings that you frequently use in a workbook template, and then automatically open that workbook template every time that you start Excel.

1. Do one of the following: 
   
2. Click File > Save As. 
3. In the Save as type box, click Template.
4. In the Save in box, select the folder where you want to store the template.
   
   
   
5. In the File name box, do one of the following:
   
6. Click Save.
7. Click File > Close.

Prevent automatic macros from running when you start Excel

Automatic macros (such as Auto_Open) that have been recorded in a workbook that opens when you start Excel will automatically run as soon as the workbook opens.

• To prevent macros from automatically running, hold down SHIFT while you start Excel.

Using Custom AutoFill Lists

Use a custom list to sort or fill in a user-defined order. Excel provides day-of-the-week and month-of-the year built-in lists, but you can also create your own custom list.

Learn more about custom lists

To understand custom lists, it is helpful to see how they work and how they are stored on a computer.

Comparing built-in and custom lists

Excel provides the following built-in, day-of-the-week, and month-of-the year custom lists.

Note: You cannot edit or delete a built-in list. 

You can also create your own custom list, and use them to sort or fill. For example, if you want to sort or fill by the following lists, you'll need to create a custom list, since there is no natural order.

A custom list can correspond to a cell range, or you can enter the list in the Custom Lists dialog box.

Note: A custom list can only contain text or text that is mixed with numbers. For a custom list that contains numbers only, such as 0 through 100, you must first create a list of numbers that is formatted as text. 

Create a custom list

There are two ways to create a custom list. If your custom list is short, you can enter the values directly in the popup window. If your custom list is long, you can import it from a range of cells.

Enter values directly

Follow these steps to create a custom list by entering values:

1. Click File > Options > Advanced > General > Edit Custom Lists. 
2. In the Custom Lists box, click NEW LIST, and then type the entries in the List entries box, beginning with the first entry. 
3. Press the Enter key after each entry. 
4. When the list is complete, click Add. The items in the list that you have chosen will appear in the Custom lists panel. 
5. Click OK twice.

Create a custom list from a cell range

Follow these steps:

1. In a range of cells, enter the values that you want to sort or fill by, in the order that you want them, from top to bottom. Select the range of cells you just entered, and follow the previous instructions for displaying the Edit Custom Lists popup window. 
2. In the Custom Lists popup window, verify that the cell reference of the list of items that you have chosen appears in the Import list from cells field, and then click Import. 
3. The items in the list that you have chosen will appear in the Custom Lists panel. 
4. Click OK twice.

Note: You can only create a custom list according to values, such as text, numbers, dates or times. You cannot create a custom list for formats such as cell color, font color, or an icon. 

Delete a custom list

Follow these steps:

1. Follow the previous instructions for displaying the Edit Custom Lists dialog.
2. In the Custom Lists box, choose the list that you want to delete, and then click Delete.

How custom lists are stored

Once you create a custom list, it is added to your computer registry, so that it is available for use in other workbooks. If you use a custom list when sorting data, it is also saved with the workbook, so that it can be used on other computers, including servers where your workbook might be published to Excel Services and you want to rely on the custom list for a sort.

However, if you open the workbook on another computer or server, you do not see the custom list that is stored in the workbook file in the Custom Lists popup window that is available from Excel Options, only from the Order column of the Sort dialog box. The custom list that is stored in the workbook file is also not immediately available for the Fill command.

If you prefer, add the custom list that is stored in the workbook file to the registry of the other computer or server and make it available from the Custom Lists popup window in Excel Options. From the Sort popup window, in the Order column, select Custom Lists to display the Custom Lists popup window, then select the custom list, and then click Add.

What Is Text To Speech?

It is used to hear the speech that corresponds to the text in a group of cells, do the following: Select a group of cells to that you want to hear as audible speech. Choose how the computer will read back your data by clicking By Rows or By Columns .

Use text-to-speech commands in Excel

1. Next, to the Quick Access Toolbar, click Customize Quick Access Toolbar, then click More Commands.
   
2. In the Choose commands from list, select All Commands. 
3. Scroll down, and for each text-to-speech command that you want to use, click any of the Speak Cells commands, and then click Add. 
4. Click OK. 
5. When you want to use a text-to-speech command, click it the Quick Access Toolbar.

Note: You can use the text-to-speech commands in Excel 2007 and 2003 by pointing to Speech on the Tools menu, and then clicking Show Text to Speech Toolbar.

Play back worksheet data

Note: You'll need to configure your computer audio to hear a speech. 

Play back a group of cells

To hear the speech that corresponds to the text in a group of cells, do the following:

1. Select a group of cells to that you want to hear as audible speech. 
2. Choose how the computer will read back your data by clicking By Rows or By Columns.
3. Click Speak Cells.
4. To correct an error, click Stop Speaking, and then use your mouse and keyboard to make the necessary changes. 
5. Click Speak Cells to make the computer continue to read back your selection.

Tip: You can select a specific range of cells for the computer to read back to you. Or, you can click

Speak Cells without selecting any cells and Excel will automatically expand the selection to include the neighboring cells that contain values.

Play back after every cell entry

1. Click Speak On Enter. 
2. Enter data in any cell. Press Enter and the computer will read back the contents of the cell.

Note: If you hide the Text To Speech toolbar and you did not turn off Speak On Enter , the computer continues to read back each cell entry that you make. Click Speak On Enter to turn it off. 

Using Detect And Repair

If an Office application such as Word or Excel isn’t working correctly, sometimes restarting it will fix the problem. If that doesn’t work, you can try repairing it. When you’re done, you might need to restart your computer.

To repair your Microsoft Office application in Windows 8 or 10 :

1. Go to Windows > Settings > Apps > Apps & Features. 
2. Select your Microsoft Office from the list. 
3. Click Modify.

Assigning a Macro to a Control

If you add a form control to a worksheet and right click on it, the menu that appears will often have an Assign Macro option. (The label control and the group box control do not have a macro option available on the menu.)

Before you assign a macro, you should make sure that the purpose of the button is clear. You can change the text that appears on a button control by choosing Edit Text from the menu. For this example, the button text is changed to Highlight.

If you select the Format Control option from the pop-up menu, you can change the font type and size of the button’s text.

If you select the Assign Macro option, you will display the Assign Macro dialogue box.

You can choose a macro to assign to the button control by selecting one from the list of available macros and clicking OK. The macro you choose will be activated when you click the button it is assigned to.

You can also record a new macro for the button control by clicking the Record button in the Assign Macro dialogue box. (This button will appear when you type a new macro name.) Clicking Record will display the Record Macro dialogue, discussed in Section 2.

In the Record Macro dialogue, you can give your macro a name, a shortcut key, specify where it will be stored, and supply a description for it.

When you click OK, the macro will begin recording. For this example, the cell range D3: D9 containing people’s names is selected from the worksheet, and the background color for the selected range is shaded yellow. Clicking the stop (white square) button in the lower left of the Excel screen will stop the macro from recording. The new macro will be activated when the Highlight button control is clicked.

Here is the original list.

Here is the list after clicking the Highlight button. The button has activated the assigned macro and the list is highlighted in yellow.

You can change the macro assigned to a given form control by right-clicking the control and choosing the Assign Macro option from the menu. When the Assign Macro dialogue box is displayed, select the macro of your choice from the list and click OK. The macro you choose will now be the one that is assigned to the form control.

You can assign macros to other form controls, like lists boxes or combo boxes, but these macros will often require Visual Basic programming to handle and manipulate the values captured by the controls.

Using Form Control

There are many ways to apply form controls in Excel. They can be used to make selections and trigger simple calculations or to provide a basic interface that allows a user to interact with the workbook data.

When using form controls in your workbook, keep in mind that one type of form control may be better suited for a task than another. Here are a few examples to give you a better idea of what you can do with form controls.

This is a group box control, containing two radio button (option button) controls. Radio buttons are used when you want to make a mutually exclusive selection; if you select one radio button, any others will be deselected.

The group box control allows you to have different groups of radio buttons for different purposes. If radio buttons are in a group box, selecting one will only deselect the other radio buttons in the same group box, rather than all other radio buttons.

The image above will give you a better idea of how option buttons work. With the 5-year plan selected, the price is 1000. You can see the value returned from the radio button control in cell F8. Now, look at the formula for the active cell in the formula bar. If the value in F8 = 1 (meaning if the first option button in the grouping been selected), 1000 will be displayed. If the value in F11 is not 1, 10000 will be displayed.

The following example demonstrates the use of checkboxes. In this example, there are three checkbox controls. Checkboxes return a true or false value indicating if they have been checked or not. You can see that the product1 and product3 checkboxes have been selected, and the total is 4000 dollars.

Here, you can see how the checkboxes work. The true and false values from the checkboxes are entered in H1: H3 (the linked cells).

Cells I1: I3 contain IF functions that display the values from column E corresponding to the checkboxes (you can see the IF function for I1 in the formula bar). If the corresponding value in column H is true, the value from column D will be shown in the cell with the IF function. If the value from column H is not true, zero will be shown. Cell E12 sums the values in I1: I3 for a total.

This is an example of a combo box control. This combo box allows you to select a given month from a drop list. When a selection is made, the profit for that month is shown.

 This image shows how the combo box works. Notice the VLOOKUP function in the formula bar.

When a month is selected from the combo box, Excel puts the index value of the month in the combo box cell link G1. (The index is 1 because Jan is the first month.) Cells E5: G16 contains a lookup table with profit figures for each month. Cell C8 contains a VLOOKUP formula that looks up the index value from G1 in the lookup table. VLOOKUP then displays the value from column 3 of the look-up table corresponding to the index given from the combo box. In this example, the resulting value is 12000.

Excel Form Controls?

A form control is a kind of user interface component that can be added to a worksheet. You can add a single form control to a worksheet, or a group of form controls. Some of the form controls that you can choose from include list boxes, check boxes, radio buttons, scroll bars, combo boxes, and control buttons.

You can use form controls to design a workbook that encourages users to make certain choices and selections. Operations can then be performed based on the selections made. Form controls can also be used to provide data options or choices for the user.

Essentially, form controls let you manage what kind of data a user can enter or manipulate and what happens in your worksheet after a user has performed specific actions. You can even combine form controls to create a simple user interface or Form for interacting with a workbook.

The following is a list of the main form controls available in Excel 2016.

Adding a Control to a Worksheet

In Excel 2016, it is fairly easy to add a form control to a worksheet. 

• The first step is to display the Developer Ribbon and then display the options under the Insert button.

• Under the heading Form Controls, you will see a panel of control icons like the ones described above. If you single click on one of these icons, your mouse pointer will turn into a cross as you move the mouse over the grid area of the worksheet. 
• To add a control, choose it by clicking on it, and then drag the mouse pointer to draw out the control on the worksheet.
• To add a list box, for example, you would perform a single left click on the list box button under the Form controls heading, and then move your mouse pointer to the location of your choice. When you find the right spot, click on it, and a list box control will be placed at your chosen location.
• You can resize the list box by mouse dragging the small circles positioned around its border. If you click on an empty cell, the circles will disappear and the list box will be embedded in the worksheet.

• When you right click on a form control, it can be removed (cut), copied, grouped, resized, relocated, or have its Format Controls modified. 
• If you right click on the list box and then click the Format Control option on the pop-up menu, a Format Control dialogue box will appear.

• Under the Size tab, you will find options to modify the size of the control. Under the Protection tab, you can choose to lock or unlock the control (used with protected worksheets).

• Under the Properties tab, you can modify how the control object is positioned when cells are moved or resized. You can also specify whether or not you want the control to be printed with the worksheet. 
• The Alt Text tab has options for displaying an alternative text for the control.
• Finally, under the Control tab, you will find options for specifying the input range, (a specific cell range containing the data that will appear in the list), and the cell link, which is the cell that will receive an index value corresponding to the item chosen from the list. When you make a selection from a list box, Excel will place the index value into the specified cell link.

To select an input range, put your cursor in the input range data field and use your mouse to select the range of data to be displayed in the list box. 

• To select the cell link, use the Tab key or click your mouse to place your cursor in the Cell Link field. 
• Next, use your mouse to select the cell that the list box output will be placed in. You can also type the cell references for the input range and the cell link directly if you wish. When you are finished setting up the list box, click OK to implement your modifications.
• In this image, you can see that the Input range D3: D9 has been entered as well as the cell link, B8.

• Here you can see that the list box contains the names of the people in cells D3: D9.
  
• The list of names can be navigated by the arrow buttons on the right side of the list box. You can also see the index number of a selected item from the list in cell B8. Put another way, Julie has been selected from the list box, and the number corresponding to Julie’s order in the original cell range (3) is visible in the linked cell (B8).
• You can just as easily have the source list or linked cell on a separate worksheet from the form control if you wish.

The process described here is basically the same for any of the form controls. The only differences are the control property modifications, and data source and link options which vary from control to control.

Remember, the best way to get familiar with the different form controls is to experiment with them.

Macros

Macros

Recording Macros

It is not difficult to create a macro in Excel 2016, but first, you should make sure that the Developer tab is accessible. 

• To do this, click the Excel Options button on the File and then click the Customize Ribbon option on the left of the Excel options window. 
• Under the Top Options for working with Excel option, you will see a checkbox next to the Developer.

• If you check this box and click OK in the options window, the Developer tab will appear at the top of the Excel screen. If you click the Developer tab, you will see the Developer Ribbon.

• This Developer Ribbon is your gateway to creating and editing macros in Excel 2016. To record a macro, click the Record Macro button in the Code button group.
• This will display the Record Macro dialogue box.

• Here you can give your new macro a name (do not use spaces) and choose the location where it should be stored. Your options are to store it with this workbook, another workbook, or a special macro workbook.

This special macro workbook is called the personal macro workbook. A macro that is stored here can be run in an any Excel workbook on the same computer. If you are creating a macro that you think you would like to use in multiple workbooks you should consider saving it in the personal macro workbook.

• You can also enter a brief description of the macro you are about to create in the bottom text area (labeled Description). By default, the macro will be called Macro1 (if this is your first macro) and it will be saved in the current workbook.
• If you click OK, you will see a button appear in the lower left corner of the Excel screen in the status bar.
• The small square is the stop button, which will stop the macro from recording.
• When you see these buttons, every keystroke or Excel action that you take with the mouse will be translated into VBA (Visual Basic for Applications) code by Excel. This code is what makes up your macro.
• When you are finished performing the actions or keystrokes you wanted to record, you can stop recording the macro by clicking the small blue square in the status bar in the lower left.
• The sequence of Excel operations or procedures that you recorded will be replayed (meaning the actions will be performed) every time you run the macro.

If you record a macro and then try to save your workbook, you may see an alert like the following.

• If you click the Yes button, the macros created and stored in this workbook will not be saved. To keep these macros, you must specify a macro-enabled workbook in the Save As type field in the Save As dialogue box.
• If the macro is stored in the personal macro workbook, you can run the macro in any workbook you open (given the appropriate security settings), even if the open workbook was not saved as a macro-enabled workbook. 
• If you choose to store a macro in the personal macro workbook, you will get the following message when you close Excel.

• Clicking the Save button will commit the macro you created to the personal macro workbook. Macros in this personal macro workbook will be available for use when you open Excel.

Saving to Macros Enabled Workbook

When working with macros in Excel 2007 to 2016, you save and open workbooks in a new macro-enabled workbook format (.xlsm) that provides added security. When you create a macro, you must use a macro-enabled format to save your workbook or the macro won't be saved.

If you open a macro-enabled workbook, a Security Warning message states that the workbook contains macros. This protects you from possible harm – some macros might contain viruses or other hazards. You can choose to enable the content if the workbook is from a trusted source.

Saving a macro-enabled workbook:

1. Click the File tab and then choose Save As. 
2. Enter a name and select a location for your workbook. 
3. Click the Save as Type drop-down arrow. Select Excel Macro-Enabled Workbook (*.xlsm) extension to the filename then Save.

Note: If you create a macro in a workbook and neglect to save the workbook as a macro-enabled workbook, you see a warning message telling you that the macro will not be retained. 

Closing and Re-Opening a Macro Workbook

To close a macro workbook is the same method of closing a normal workbook. Just simply click on the close button at the top right corner of the Excel application.

Or the second method, click on the File tab go to Close command.

To re-open a macro workbook, will be the same as how you would open a normal Excel file. The only difference is you see a security warning message.

• Browse the file in windows explorer → double click on the file. 
• Or method 2 will be File tab → Open → browse for your file → Open.
• When the file opens, you will see the “SECURITY WARNING” message.

Security Warning Message

The next time you retrieve your file, you will see a “SECURITY WARNING” message band. This means you will have the option of choosing to allow or not allow the macro to run.

Notice how the security alert appears below the ribbon. If you click the Enable Content button. 

Once again, clicking the Enable Content button will allow you to run the macro.

Playing a Macro

When you play a macro, the actions or keystrokes you chose for your macro will be performed in the sequence in which they were recorded.

To play a macro, click the Macros button on the Developer Ribbon.

When you click this button you will see the Macro dialogue box.

Here you can see the macros available for use. Note that macros saved in the personal macro workbook will have Personal.XLSB! in front of their name (such as the macro selected in the list).

• To play the selected macro, click the Run button. 
• To view or edit the Visual Basic code for the selected macro, click the Edit button. 
• To delete the selected macro, click the Delete button.

When you click the Run button, the Macro dialogue box will disappear, and the sequence of instructions that was recorded in the macro will be performed on the current workbook.

Note: You can see the macros from this workbook and from the personal macro workbook because the “All open workbooks” option is selected in the bottom drop list of the macro dialogue box. If you select the “This workbook” option from the list or the “Personal.xlsb” option, only the macros corresponding to the specific option you select will be shown.

Assigning a Keystroke to a Macro

There are two ways to assign a shortcut key to a macro. You can assign a shortcut key when you are first recording your macro or you can assign a shortcut key to an existing macro.

• To assign a shortcut to a macro when you record it, click the Record Macro button on the Developer Ribbon. When you see the Record Macro box, just enter a letter in the Shortcut Key data field.
• Here you can see that the letter d has been assigned as a shortcut key for this macro. Once the macro has been recorded, pressing Ctrl + d will run it. This avoids the process of picking a macro from the Macro dialogue box every time you want to run it. If you press Shift + D when you enter the shortcut key, the shortcut will then become Ctrl + Shift + D for the macro.
  
• If you store the macro in the personal macro workbook, the shortcut will be carried with it wherever you use the macro.
• To assign a shortcut to an existing macro, click the Macro button to display the Macro dialogue box. In the Macro dialogue box, select the name of the macro you want to assign a shortcut key to and then click the Options button. 
  

• This will open the Macro Options dialogue box for the macro you selected.
  

There is a small data field that can be used to enter a shortcut key for this macro. You can enter a letter or use a Shift key/letter combination as before. If you click OK, the shortcut key will be assigned to the macro.

Understanding about Pivoting Consolidated Data

Data consolidation refers to the collection and integration of data from multiple sources into a single destination. During this process, different data sources are put together or consolidated, into a single data store.

Because data comes from a broad range of sources, consolidation allows organizations to more easily present data, while also facilitating effective data analysis. Data consolidation techniques reduce inefficiencies, like data duplication, costs related to reliance on multiple databases and multiple data management points.

Pivoting Consolidated Data

Setting up the source data

Each range of data should be arranged in cross-tab format, with matching row and column names for items that you want to summarize together. Do not include any total rows or total columns from the source data when you specify the data for the report. The following example shows four ranges in cross-tab format.

Page fields in data consolidations

Data consolidation can use page fields that contain items representing one or more of the source ranges. For example, if you're consolidating budget data from the Marketing, Sales, and Manufacturing departments, a page field can include one item to show the data for each department, plus an item to show the combined data. The following example shows a consolidated PivotTable that has one-page field and multiple items selected.

Using named ranges

If the range of data is likely to change the next time that you consolidate the data (that is, the number of rows may change), consider defining a name for each source range in the separate worksheets. Then use those names when you consolidate the ranges in the master worksheet. If a source range expands, you can update the range for the name in the separate worksheet to include the new data before you refresh the PivotTable.

Other ways to consolidate data

Excel also provides other ways to consolidate data that work with data in multiple formats and layouts. For example, you can create formulas with 3D references, or you can use the Consolidate command (on the Data tab, in the Data Tools group).

Consolidate multiple ranges

You can use the PivotTable and PivotChart Wizard to consolidate multiple ranges. In the wizard, you can choose between using no page fields, a single page field, or multiple page fields.

Consolidate data without using page fields

To combine the data from all the ranges and create a consolidation that does not have page fields, do the following:

1. Add the PivotTable and PivotChart Wizard to the Quick Access Toolbar. To do that:
   
2. Click a blank cell (that is not part of a PivotTable) in the workbook.
3. Click on the PivotTable Table and PivotChard wizard icon on the Quick Access Toolbar.  
4. On Step 1 page of the wizard, click Multiple consolidation ranges, and then click Next. 
5. On Step 2a page of the wizard, click I will create the page fields, and then click Next. 
6. On Step 2b page of the wizard, do the following: 
   
7. On the Step 3 page of the wizard, select between adding the PivotTable to a new or an existing worksheet, and then click Finish.

Tip: If the cell range is in another workbook, open the workbook first to make it easier to make select the data.

Consolidate data by using a single page field

To include a single page field that has an item for each source range, plus an item that consolidates all the ranges, do the following:

1. Add the PivotTable and PivotChart Wizard to the Quick Access Toolbar. To do that:
   
2. Click a blank cell (that is not part of a PivotTable) in the workbook. 
3. On Step 1 page of the wizard, click Multiple consolidation ranges, and then click Next. 
4. On Step 2a page of the wizard, click Create a single page field for me, and then click Next. 
5. On Step 2b page of the wizard, do the following: 
   
6. Click Next.
7. On the Step 3 page of the wizard, select between adding the PivotTable to a new or an existing worksheet, and then click Finish.

Tip: If the cell range is in another workbook, open the workbook first to make it easier to make select the data. 

Consolidate data by using multiple page fields

You can create multiple page fields and assign your own item names for each source range. This lets you create partial or full consolidations; for example, the one-page field that consolidates Marketing and Sales apart from Manufacturing, and another page field that consolidates all three departments. 

To create a consolidation that uses multiple page fields, do the following:

1. Add the PivotTable and PivotChart Wizard to the Quick Access Toolbar. To do that:
   
2. Click a blank cell (that is not part of a PivotTable) in the workbook.
3. On Step 1 page of the wizard, click Multiple consolidation ranges, and then click Next. 
4. On Step 2a page of the wizard, click I will create the page fields, and then click Next. 
5. On Step 2b page of the wizard, do the following: 
   
6. On the Step 3 page of the wizard, select between adding the PivotTable to a new or an existing worksheet, and then click Finish.

The consolidate function in Excel allows an analyst to combine information from multiple workbooks into one place. The Excel consolidate function lets you select data from its various locations and creates a table to summarizes the information for you.