How To Automate Your Workflow

Are you ready to save hours of manual work each week? In this guide, we’ll explore how to automate your workflow in Excel using tools like Power Query, Pivot Tables, and Pivot Charts. Best of all, you don’t need to be a coding wizard to get started. By the end, you’ll know how to effortlessly update your data, summaries, and charts with just a click. Let’s dive in!

Video

Step-by-step Guide

Manual tasks like copy-pasting data, creating formulas, and updating charts can be time-consuming and error-prone. Automation not only saves time but also ensures your reports are accurate and up to date. We’ll use Power Query, PivotTables, and PivotCharts to achieve this, so even if you’re new to these features, you’re in the right place.

Exercise 1: Importing Data with Power Query

The Manual Way

Traditionally, importing data from multiple files into a single workbook involves opening each file, selecting the data, copying it, and pasting it into your report. It’s a tedious process that must be repeated every time new data is added.

Automating with Power Query

Here’s how Power Query simplifies this process:

Load Data from Folder:

• Go to Data > Get Data > From File > From Folder.
• Browse to the folder containing your files and click Open.

Combine Files:

In the resulting dialog box, click Combine > Combine & Load To.

The resulting dialog …

Note: Although the column order can vary between files, you’ll want to ensure the column labels are consistent across files. Power Query will handle column alignment even if the order differs.

Load the Data:

In the resulting dialog, choose to load the data into a table on a new or existing worksheet:

Click OK and bam, power query retrieves and aligns the data from the files in the specified folder:

With Power Query set up, you can refresh your data anytime files are added to the folder (or removed from the folder), eliminating the need for repetitive manual tasks.

Exercise 2: Summarizing Data with Pivot Tables

The Manual Way

Let’s say you want to calculate total amounts for each month manually. Here’s what it looks like:

1. Use the SUM function for each month’s data range.
2. Add these totals to create a grand total.

While this works, it’s time-consuming and prone to errors, especially if the data shifts or gets sorted.

Automating with Pivot Tables

Pivot Tables make summarizing data a breeze:

Insert a Pivot Table:

• Select a cell in your data range and go to Insert > Pivot Table.
• Choose to place the Pivot Table in an existing worksheet.

Set Up the Pivot Table:

• Drag the Date field to the Rows area to group data by month.
• Drag the Amount field to the Values area to calculate totals.

Format the Table:

• Adjust column widths and number formats as needed.
• Rename the columns to something intuitive, like “Month” and “Total.”

Once set up, you can refresh the PivotTable when the source data is updated. No need to rewrite formulas or worry about errors.

Exercise 3: Visualizing Data with Pivot Charts

The Manual Way

Creating a chart manually involves selecting the data, inserting a chart, and formatting it. But when new data is added, you must update the chart range manually.

Automating with Pivot Charts

Pivot Charts are linked to Pivot Tables, so they update dynamically:

Insert a Pivot Chart:

• Select a cell in your Pivot Table and go to Insert > Pivot Chart.
• Choose to place the chart in an existing worksheet.

Configure the Chart:

• Drag the Date field to the Axis area and the Amount field to the Values area.
• Format and label the chart as desired.

When new data is added, simply click Refresh All, and both the Pivot Table and Pivot Chart will update automatically.

The Real Magic: Refreshing for Future Periods

Here’s where automation truly shines:

• To add a new month’s data, place the file in the folder connected to Power Query.
• Click Refresh All, and Power Query pulls in the new data.
• Click Refresh All and your PivotTable and PivotChart update instantly—no manual steps required!

Need to remove a month? Just delete the file from the folder and refresh. Want to add several months? Drop the files into the folder and refresh. It’s that simple.

Conclusion

By using Power Query, PivotTables, and PivotCharts, you can automate your workflow in Excel and save countless hours. While setup might take a bit of time initially, the benefits for future periods are well worth it. If you have questions or want to share your own tips, let us know in the comments below!

Happy automating!

File

FAQs

1. What is Power Query, and how does it work?

Power Query is a tool in Excel that helps you import, transform, and load data from various sources. It automates repetitive data preparation tasks, saving time and reducing errors.

2. Can I use Power Query to import non-Excel files?

Yes! Power Query supports multiple file types, including CSV, TXT, and even databases like SQL Server.

3. Are PivotTables hard to learn?

Not at all. Pivot Tables are intuitive and Excel provides prompts to guide you. With practice, you’ll find them indispensable for summarizing data.

4. What happens if my data columns are inconsistent?

Power Query aligns columns based on their labels. Ensure that column headers are consistent across your files to avoid issues.

5. Do PivotCharts offer customization options?

Yes, PivotCharts offer extensive formatting options. You can change chart types, styles, and colors and even add slicers for interactivity.

6. Can these techniques be applied in older Excel versions?

Power Query is available in Excel 2016 and later. PivotTables and PivotCharts have been around for much longer, so they’re available in most versions.

7. Will these automations work in shared workbooks?

Yes, but ensure all users have access to the shared folder and are familiar with the refresh process.

8. How do I troubleshoot issues with refreshing data?

Check the file paths and ensure the source files haven’t been moved or renamed. Also, verify that column labels match.

9. Can I schedule data refreshes?

While Excel doesn’t natively support scheduling, you can use Power Automate or VBA to create scheduled refreshes.

10. What’s the best way to learn more about these features?

Check our our Masters 1 course here.