- Contact Us
Have you used pivot tables in Excel? Did you know they can help you convey your data in a clear, concise way? We recently published a Quick Starter's Guide to Excel with tips on formulas, common shortcuts, filters and more. This Guide is meant to take it one step further with tips on pivot tables, transposing, and graphs so you can get the most out of your data with tools that can break it down, rearrange it and let it tell a story.
Pivot tables are one of the most powerful tools in Excel and can help you summarize large amounts of data and report on that data in a meaningful way.
To create a pivot table:
1. Select the table or cells(including headers) containing the data you want to use.
2. From the Insert tab, click the Pivot Table button.
3. The Create Pivot Table box will appear. Choose your settings, then click OK. In the example, we'll use Table 1 as our source data and place the Pivot Table on a new worksheet.
4. A blank pivot table and Field List will appear on a new worksheet.
5. Once you create a pivot table, a list will pop up with fields to add. Each field is a column header from the selected data. From the field list, check the box for each field you want to add. In the example, we want to know the total amount sold by each salesperson, so we'll check Salesperson and Order Amount.
6. The selected fields will be added to one of the four areas below the Field List. In the example, the Salesperson field has been added to the rows area, while the Order Amount has been added to the values area. However, you can click, hold, and drag a field to the desired area.
7. The pivot table will calculate and summarize the selected fields. In our example, the pivot table shows the amount sold by each salesperson.
Ta Da! A pivot table!
*Pivot Table images provided by GCF Global Education.
Transposing is the ability to turn a row of data into a column or a column into a row. This is an easy trick when you have massive amounts of data and need a different view.
To transpose data:
1. Select the data you want to rearrange, including any row or column labels, and select Copy.
2. Then select an area of the spreadsheet you wish to place the new table and right click.
3. Once you right click, a menu will appear. Choose the Transpose button.
Once you have transposed and have your new table, you can then delete your old table.
Bonus: If you have formulas in your table Excel will automatically transfer those formulas to your new table when you click Transpose.
*Transpose image provided by Microsoft Office Support
Graphs are a great way to turn your data points into a visual. It can make your information easy to ready and consume and add a little something extra to a presentation or report.
To insert a chart or graph:
1. Select the cells you want to chart, including the column titles and row labels. These cells will be the source data for the chart.
2. From the Insert tab, click the desired Chart command. In our example, we'll select Column.
3. Choose the chart type you would like from the drop-down menu.
4. The graph will then be inserted into the worksheet.
*Graph images provided by Microsoft Office Support.
To get even more advanced training in these particular Excel skills, check out iACT's eLearn Excel courses. These in-depth courses detail how pivot tables, transposing, graphs and more can be used to their potential, giving you the ability to capture and display data in the best way to advance your business.