Pivot Tables for Administrative Assistants

Power of Pivot Tables: A Comprehensive Guide for Administrative Assistants


“Looking to streamline your administrative tasks? Dive into our user-friendly guide to Pivot Tables, tailored for administrative assistants. From data preparation to in-depth analysis, we break down the process into simple steps. Learn how to make informed decisions, optimize budgets, and become a vital data-driven asset in your organization. No prior experience with Pivot Tables? No problem! Our guide is designed with beginners in mind. Start transforming your data into actionable insights today!”


You are an administrative assistant managing scheduling, expenses, and supplies for different departments in a company. You want to have an overview of monthly expenses for office supplies and understand how the costs are distributed among different departments.

1. Prepare your Row Data

  • Make sure your data is organized in a table format with clear headers for each column.
  • Clean the data to remove any blanks or inconsistencies.

data rate_pivot Table

2. Insert a Pivot Table

  • Select your data base
  • Go to the “Insert” tab on the Excel ribbon and click “PivotTable.”
  • Choose where you want to place the pivot table (a new worksheet is often a good choice) and click “OK.”

pivot table select

Appears in a new sheet.

new sheet

3. Build your Pivot Table

  • Drag and drop the fields you want to analyze into the appropriate areas (Rows, Columns, Values) in the PivotTable Field List.
  • Let check out each fields.
  • When  Department is in “Filters” it will appear as:

department in filter

  • in Columns:

column

  • Rows: I personally think Row is the best way to see for my data.

row

  • and Values:

value

4. Insert a Pivot Chart:

  • Click anywhere inside the pivot table.
  • I will put “Department: on the “Row”
  • also want to know the cost per month, so add “Month” from my Fields to “row”
  • the Value that I want to know is the Total Cost, so drag that to “Values”

total cost

5. Customize Your Pivot Chart:

  • You can now modify the chart by adding titles, labels, and formatting as needed.
  • Use the various chart tools available on the Excel ribbon to change the style, layout, and design of your pivot chart.
  • Okay I want to make my Values in Currency $$
  • Click the drop down arrow on my Values “Value Field Settings”

value field

  • Then you can change the value to SUM, Count, Average..  Anything that you want!
  • I am going to change the Format to Currency, so Click “Number Format”

value field sum

  • There! Currency!

currenvy

  • Here is the Result

currency result

6. Filter and Analyze:

  • Utilize the pivot table and chart filters to analyze specific subsets of your data.
  • Remember, changes to the pivot table will be reflected in the pivot chart, and vice versa.

7. Save Your Work:

  • Don’t forget to save your workbook to preserve your pivot table and chart.

Pivot charts provide a dynamic way to visualize and interact with your data. By following these steps in Microsoft Excel, you can create a pivot chart that helps you understand trends, patterns, and insights within your data. Whether you’re working with sales figures, survey results, or any other dataset, pivot charts are a powerful tool to have in your data analysis toolkit.

Leave a Comment