Excel Tutorials

In this short tutorial, you’ll learn how to save a Pivot Table and Chart as a template for later use and how to update this template with new information using the “Refresh” feature within the PivotTable menu.

1. If for example, you have created a great PivotTable and Chart that truly reflects the data relationships that you want to present to management and you will like use the same template but with completely different raw data. You can create a template out of the current PivotTable and Chart and in essence create a shell that can absorb the new data but present it in the same way as the current PivotTable and chart. So first, open the file with the current PivotTable and Chart and go to File>Save As. Next, in “Save as type” go down to Template (*.xlt) and select it. Give it another name, for example, it would be good to include the term “template” in the actual name to help you find it faster in the future.
2. Next, open the workbook containing the new raw data. Select the range with the new data and copy all the data. Then, open the template file, go to the spreadsheet where the original raw data is stored and paste the new data in exactly the same range location. Then click on the PivotTable spreadsheet and click on the “Refresh Data” button and all the data will be refreshed. The row and column headings will remain the same as these were constant and the only thing that will change is the raw data. The PivotTable chart will also be automatically refreshed with all the new data. Finally, you can save this file under a different name.

Great! Now you can appreciate the true value of PivotTables and not fear them any longer.

Posted in Pivot Tables | Comments (0)

In this tutorial, you’ll learn how to quickly make unforeseen changes to pivot table data and quickly update that change in the pivot table and Chart.

1. Once you create a pivot table and Chart that works for you, save it and file it in an accessible place. If you have to make a change in the future but don’t want to go through the trouble of creating a new pivot table and Chart, open the file with the original pivot table and Chart.
2. Go to the spreadsheet containing the range of data where you need to make the change. Go to the cell or cells and make the change, then go to the pivot table spreadsheet and within the floating pivot table menu, c+lick on the red exclamation point button – if you place the cursor just over it you’ll see this button is called “Refresh Data.” By clicking on this button, the change within the original data spreadsheet will be automatically refreshed in the Pivot Table and Chart.

Well Done! Always remember the magic of the “Refresh” button when using pivot tables as it will save you lots of time and effort.

Posted in Pivot Tables | Comments (0)

In this tutorial, I’ll show you how to create a chart from a Pivot Table.

1. Next, you can create a chart just as quickly as you formatted the table.
2. Go to the floating pivot table menu and click on the button labeled “Chart Wizard” or click on the pivot table drop down menu and select the “Chart Wizard.”
3. By just clicking on the “Chart Wizard” option, Excel creates a chart in a second and you will see it saved in a separate spreadsheet labeled “Chart1” – which you can rename later.

Fantastic! Now you know just how easy it is to create a Pivot Table Chart.

Posted in Pivot Tables | Comments (0)

In this tutorial, you’ll learn how to format a pivot table using the feature of “Autoformat.”

1. Now that you have your new table created it’s time to style it and make it look professional and the fastest way to do this is to use the pivot table floating menu bar – you’ll see it somewhere on your spreadsheet area. Click on the first button, labeled “Format Report” or you can click on the pivot table drop down menu and select “Format Report.”
2. Now the “auto format” pop up window appears showing you various report templates that you can use. Check them out, select one and click OK.
3. You will see the table instantly formatted like the auto format template that you just selected.

Great Job! Now that you know how to create a simple pivot table and format it, I’ll show you how to create a pivot table Chart in just a couple of easy steps in the following tutorial.

Posted in Pivot Tables | Comments (0)

In this short tutorial, you’ll learn how to create a simple Pivot Table in a couple of steps. With the help of the wizard feature, Excel will actually walk you through the essential steps to build a Pivot Table from scratch.

1. Open the excel file with the table or range containing the data that you want to include in the pivot table.
2. Go to Data>pivot table and Pivot Chart Report and select it.
3. The “Pivot Table and Pivot Chart Wizard” pop up window will appear next. Under “Where is the data that you want to analyze” click on “Microsoft Office Excel list or database.” Next, click the Pivot Table option under “What kind of report do you want to create?” and click Next.
4. Now you need to select the data area that you want to include in the pivot table, so highlight the range of data or the table and click Next.
5. Finally, click on the option to create the new pivot table in a new spreadsheet within the workbook and click Finish.

Now you will see the fields where you are going to actually click and drop the items that you want to include in the pivot table. From the “pivot table Field List” window you can select each item and drag it to the “Drop Items” areas to build the table. This is the step where most of you get frustrated but you just have to start clicking, dragging and dropping until you have the view that actually satisfies your needs – there is no right or wrong way of doing this, you can always click the undo button, so drag and drop until you have the table that meets your specific requirements.

Well Done! Now in the next tutorial, I will show you how to auto format your newly created pivot table in a couple of seconds.

Posted in Pivot Tables | Comments (0)