Total_HCM_Product_Guide_Landscape

Total HCM Product Guide

One unified cloud platform to help you manage your entire workforce.

Free Product Guide

An Excel Guide to Pivot Tables, Transposing and Graphs

8/9/18, 8:14 AM

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

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.

Pivot table select data

2. From the Insert tab, click the Pivot Table button.

Pivot table command 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.

Pivot table menu options

4. A blank pivot table and Field List will appear on a new worksheet.

Pivot table field menu to select data

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.

Pivot table column header selection

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.

Pivot table selection header values

7.  The pivot table will calculate and summarize the selected fields. In our example, the pivot table shows the amount sold by each salesperson.

Pivot table summarized fields

Ta Da! A pivot table!

Created pivot table

*Pivot Table images provided by GCF Global Education. 

Transpose

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.

Right click menu 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

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.

Select data for graph

2. From the Insert tab, click the desired Chart command. In our example, we'll select Column.

Insert tab graph command button

3. Choose the chart type you would like from the drop-down menu.

Graph menu options

4. The graph will then be inserted into the worksheet.

Insert graph created

*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. 

 

 

 

 

 

 

Subscribe by Email

No Comments Yet

Let us know what you think