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.

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

Appears in a 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:

• in Columns:

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

• and Values:

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”

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”

• 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”

• There! Currency!

• Here is the 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.