Microsoft Excel Tips and Tricks You Need to Use Pivot Tables

By Robert Half on February 6, 2014 at 8:00am

Picture this: Your new boss needs to present her annual budget to the executive team for sign-off, and she’s asked you to format her Microsoft Excel spreadsheet so it looks sharp. What do you do? You try to piece together the key learnings from past Excel training sessions.

But if your mind goes blank, don’t fret. Professionals in administrative jobs often need to format reports, and we’ve got the Microsoft Excel tips and tricks you need to get the job done.

Meet the pivot table

One of the most useful Excel tips and tricks is the pivot table. When considering a format to best display your data, pivot tables are a wonderful option to work with. These tables summarize the data in your spreadsheet, extracting the most important points from large sets. This allows you to readily view your data from different perspectives.

Proper use of pivot tables also allows users to display and print relevant data in an organized format. However, these tables can be hard to work with because of the way they are laid out on the screen. You can save a lot of time by selecting the easiest layout for your task before creating your report.

A good source of Excel tips and tricks specific to pivot tables is ... well, Microsoft itself. Microsoft has created handy tutorials for Excel 2010, Excel 2013 and Excel 2016. There are three layout options for pivot tables in Excel:

  • Outline form outlines data in the standard format for pivot tables.
  • Compact form minimizes the need to scroll by showing all fields in one column. This layout contains field nesting for related fields.
  • Tabular form lays out data in table form. This format makes it easy to copy cells to other spreadsheets.

Keep in mind that outline form can be hard to work with because data is not easily seen or formatted. This format displays all available data, including subtotals that may not be relevant or necessary, making the report harder to follow. It may also display so much data that users have to scroll repeatedly to view relevant cells.

With that in mind, here’s another of our handy Microsoft Excel tips and tricks: Before doing anything else, click anywhere in your pivot table to display the Design tab. Click Report Layout from the Layout group in that tab and then choose a layout more conducive to your needs. For example, use the compact layout rather than outline layout to display a cash flow report so you don’t clutter your report with subtotals and other irrelevant data.

Clean up your report with more Excel tips

One of the challenges of working with reports is that they can overwhelm you with data. If you see a lot of data cluttering up your screen, you may not know where to begin! Other Excel tips and tricks can help you overcome that.

Eliminate clutter by setting Excel to hide unnecessary information. You can hide rows and columns by clicking anywhere in the pivot table to display the Options tab. Then, go to Show/Hide in that tab. You can also hide fields you are not using, turn off display of subtotals you don’t need to see, and hide empty rows and columns by going to the appropriate area in the Options tab.

Use styles and banding for reports

Excel comes with a variety of style options for reports. Look at the available styles by clicking anywhere in the pivot table to display the Design tab and selecting the Style Gallery option. Choose a style that is easy on your eyes. Using a style will automatically format certain elements of the entire report, such as fonts, alignment and how numbers are displayed.

You may also want to use banding to make reports easier to read. Banding makes alternate rows different colors so you can see data more clearly. Automatic banding can be added to the report by choosing Banded Rows or Banded Columns from the Pivot Table Style Options group in the Design tab.

Excel at administrative jobs

Using these Excel tips and tricks to add your own formatting touches can help you make your reports easier for your manager and others to comprehend — and make you look like a tech-savvy star.

More From the Blog...