close
Microsoft Excel Pivot Tables: What Are They, and How Can You Create Them?
One most powerful and intimidating feature of Microsoft Excel is the pivot tables. It’s powerful because it can help you save lots of time by summarizing and making sense of large sets of data. But it’s rather intimidating because you aren’t exactly an Excel guru. But the good news is here!
Learning to create pivot tables in Excel could be much easier than you were made to believe. But before can embark on the process of creating pivot tables, it’s important you understand what you have in your hands, and why you need to learn to use them.
What Are Pivot Tables?
The seemingly endless rows and columns of figures staring back at you can be quite intimidating and confusing. But a pivot table can provide a summary of the figures in a chart that lets you see the trends on your information.
It’s called a pivot table because it can let you rotate the data in the table, so you can view it from different perspectives. You aren’t doing anything to your data when you pivot it. You are only reorganizing the data to reveal more useful information from the tables.
How Do You Create Pivot Tables?
Pivot tables help you examine the similarities, differences, highs, and lows from large sets of data. First, you need to know how to create them.
Follow this step by step guide to create a useful pivot table:
Step 1: Enter Data Into Rows and Columns
The first step involves preparing data for your pivot table. This is because every pivot table requires a basic Excel table, which you can easily create by entering data into a specific set of rows and columns. Use the topmost column to group your values by what they represent.
For example, you can create an Excel table for the various records of your business, including expenses and sales, and the various dates on which these items occur. You could have a column for dates, another for total sales, and another for total expenses.
Step 2: Sort Your Data Using Specific Attributes
The next step is to sort the data in your Excel sheet to make it easier to manage when you turn on the pivot table. Click the Data tab in the navigation bar, and choose the Sort icon below it. You can choose to sort your data by any column, and in any particular order.
Once you have selected the column to sort your table by, choose whether to sort from largest to smallest or from smallest to largest. Click Ok on the Sort window to apply the sorting procedure. You will realize that your rows have been reordered to reflect the new operation.
Step 3: Highlight the Cells You Want to Include in the Pivot Table
The next step is to highlight the data you wish to summarize in the pivot table. To do this, click Insert in the navigation bar, and select the Pivot Table icon. Alternatively, click anywhere in your Excel sheet, and select Pivot Table, then enter your range of cells manually.
Another option box will open to let you choose whether to open the pivot table in a new worksheet or to keep it in the current sheet. You can also highlight your range of cells, and select Recommended Pivot Tables appearing to the right of the pivot table icon. This will let you open a pivot table with pre-set suggestions for organizing each row and column.
Step 4: Drag and Drop a Field Into the Row Labels Area
Once Excel creates a blank pivot table for you, the next step is to drag and drop a field, which you labeled according to the column headers in your worksheet, into Row Labels. For example, if you want to organize your business records by total sales data, just click and drag the title of the column to the Row Labels area.
Step 5: Drag and Drop a Field Into the Values Area
Having established what you want to organize your data by, you need to add some values into the pivot table. You can accomplish this task by dragging the appropriate fields into the Values area. Once you have added your fields, the pivot table is complete and ready for analysis.
Step 6: Fine-Tune Your Calculations
While Excel keeps your formulae and calculations even when you transfer your values by default, you can change them if you want. For example, you can change values to averages, minimums, and maximums. If you are using a Mac, click on the “I” next to the figure in the Values area. Then, select the appropriate option, and click OK.
But if you’re on the Windows platform, just click on the small upside-down triangle beside the value, and select Value Field Settings to get the menu.
If you find that your pivot table isn’t sorted the way you would have liked, go back to the Sort function. You may also need to include other data from other sources. If this is your case, make good use of the VLOOKUP function.
You can also watch this video for more details.
Why Do You Need Pivot Tables?
You are probably wondering why your business would need to create pivot tables in the first place. Here are some compelling reasons you would want to create these tables for your firm:
- Compare sales of different products: Quite convenient and easier when dealing in various products
- Display product sales as percentages of all sales: Easier to manipulate values, so you can apply percentages as you compare different values of product sales against total sales
- Merge duplicate data: Find any duplicate data and merge it to avoid confusion or inaccurate calculation results
- Obtain employee headcounts for different departments.
- Add default values to empty cells: Empty cells can be such a problem. Pivot tables can help you avoid them by filling them with default values.
Work With Pure I.T.
If you are looking for a managed service provider to handle your IT and accounting tasks reliably and affordably, consider partnering with Pure I.T. With years of experience and professional staff, you can be sure of excellent results. Need more information? Please contact us today!