3 Essential Excel Tricks for Next-Level Data Analysis

Excel has been the analytics workhorse for decades, and it‘s showing no signs of slowing down. Despite the rise of Python, R, and specialized AI/ML platforms, Excel still shines for ad-hoc analysis and data exploration. In fact, a recent survey found that 63% of data analysts use Excel as part of their workflow on a weekly basis.[^1]

As an AI and machine learning practitioner, I‘m a big proponent of using the right tool for the job. And while advanced techniques like deep learning and NLP require specialized tools, Excel remains my go-to for quick, iterative analysis. Its flexibility and built-in functionality make it the Swiss Army knife of the data world.

In this post, I want to share three Excel tricks that I consider essential for any analyst looking to take their skills to the next level:

1. Pivot tables for summarizing data
2. VLOOKUP for combining data sources
3. Goal seek for solving problems backwards

For each one, I‘ll provide step-by-step instructions, expert tips, and real-world examples. My goal is for you to come away with a newfound appreciation for Excel‘s potential and some practical techniques you can implemented immediately.

Pivot Tables: Summarizing Data Like a Pro

Pivot tables are hands-down the most powerful tool in Excel for summarizing and slicing data. They allow you to transform a flat dataset into a dynamic, multi-dimensional report in just a few clicks. If you learn only one advanced Excel skill, make it pivot tables.

Here‘s a quick crash course:

1. Start with your data in a tabular format, with headers in the first row and no blank rows or columns.
2. Select any cell in the data and go to Insert > PivotTable.
3. In the Create PivotTable dialog, verify the data range and choose where to place the pivot table. Click OK.
4. The PivotTable Fields pane will appear on the right. Drag and drop fields from the top section into the Rows, Columns, Filters, and Values areas below.
5. Numeric fields placed in the Values area will automatically be summed, but you can change the aggregation to an average, min, max, or count.

And that‘s it! You now have an interactive summary table that you can rearrange on the fly by dragging fields between the different areas. It‘s like having a dynamic dashboard without needing to know any code.

Let‘s look at a concrete example. Say you have an online store and want to analyze sales data to identify trends and top-performing products. Your raw data might look something like this:

Order ID	Product	Category	Date	Price	Qty	Total
1001	Widget	Gadgets	2023-01-15	9.99	2	19.98
1002	Gizmo	Gadgets	2023-01-16	14.99	1	14.99
1003	Thingamabob	Widgets	2023-01-16	24.99	3	74.97
1004	Doodad	Widgets	2023-01-17	7.99	5	39.95
1005	Widget	Gadgets	2023-01-18	9.99	1	9.99

To summarize this data with a pivot table, you could drag the Category and Product fields to the Rows area, Date to the Columns area, and Total to the Values area. The result would be a table showing total sales by category and product, broken down by date:

	2023-01-15	2023-01-16	2023-01-17	2023-01-18	Grand Total
Gadgets	19.98	14.99		9.99	44.96
Widget	19.98			9.99	29.97
Gizmo		14.99			14.99
Widgets		74.97	39.95		114.92
Thingamabob		74.97			74.97
Doodad			39.95		39.95
Grand Total	19.98	89.96	39.95	9.99	159.88

Right away, you can see that the Widgets category had the highest sales overall, driven by strong sales of the Thingamabob product on 1/16. You can also see daily totals in the last row.

But the real magic of pivot tables is that you can instantly change the view by moving fields around. Want to see sales by product, with date breakdowns? Just drag Product to the Rows area and Category to the Columns area. Want to filter for a specific date range? Drag the Date field to the Filters box and select the range you want.

Some other pivot table tricks to try:

• Double-click a value cell to drill down and see the underlying raw data
• Use calculated fields to add new columns based on custom formulas
• Create a PivotChart to visualize the data
• Insert slicers to add interactive filtering buttons

Once you get comfortable with pivot tables, you‘ll start to see applications everywhere. I use them all the time to crunch numbers for ad-hoc analysis and reporting. And while pivot tables can‘t replace more advanced statistical methods, they‘re often the first tool I reach for when exploring a new dataset.

Combining Data with VLOOKUP

Speaking of exploring data, analysts often need to combine information from multiple sources to get a complete picture. That‘s where VLOOKUP comes in. This function lets you look up a value in one table and fetch the corresponding value from another table.

The syntax is:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value: The value to search for, usually a reference to a cell containing a unique ID.
• table_array: The data range where the lookup value will be searched in the leftmost column, and a corresponding value returned from another column to the right.
• col_index_num: The index number of the column in table_array to return a value from, counting from the left.
• [range_lookup]: Optional. FALSE returns an exact match, TRUE allows for approximate matches. If omitted, TRUE is assumed.

Continuing our e-commerce example, let‘s say you have two separate tables: one with order details, and another with customer information.

Order Data:

Order ID	Customer ID	Order Date	Total
1001	4829	2023-01-15	19.98
1002	2941	2023-01-16	14.99
1003	1847	2023-01-16	74.97
1004	2941	2023-01-17	39.95
1005	3822	2023-01-18	9.99

Customer Data:

Customer ID	Name	Email
1847	John Smith	[email protected]
2941	Jane Doe	[email protected]
3822	Bob Johnson	[email protected]
4829	Alice Brown	[email protected]

To include customer names in your sales analysis, you can use VLOOKUP to join the two tables on the Customer ID field. The formula would look like this:

=VLOOKUP([@[Customer ID]], Customers!A:C, 2, FALSE)

This looks for the Customer ID in the first column of the Customers table (A:C), and returns the corresponding value from the second column (Name). The FALSE argument specifies an exact match.

Drag this formula down, and you‘ll see the customer names populate next to each order:

Order ID	Customer ID	Customer Name	Order Date	Total
1001	4829	Alice Brown	2023-01-15	19.98
1002	2941	Jane Doe	2023-01-16	14.99
1003	1847	John Smith	2023-01-16	74.97
1004	2941	Jane Doe	2023-01-17	39.95
1005	3822	Bob Johnson	2023-01-18	9.99

Now you‘re ready to create a pivot table to analyze sales by customer, or build a dashboard with customer-level metrics. The possibilities are endless!

Some VLOOKUP tips:

• Make sure the lookup column is the leftmost one in your table array
• Lock the table array with absolute references (e.g. $A$1:$C$5) so you can copy the formula without it shifting
• If you need to return values from multiple columns, use the INDEX/MATCH combo instead
• For large tables, use a named range for the lookup array to make your formulas more readable

VLOOKUP is an essential tool for combining disparate datasets, which is a common task in AI and ML workflows. While specialized libraries like Pandas (Python) and dplyr (R) are more efficient for big data, VLOOKUP gets the job done for small-to-medium datasets in Excel.

Solving for Inputs with Goal Seek

The first two Excel tricks we‘ve covered are useful for summarizing and combining data – key steps in the data preprocessing pipeline for any AI/ML project. But what about going in the other direction and using outputs to solve for inputs? Enter Goal Seek.

Goal Seek is a what-if analysis tool that finds the input value needed to achieve a desired formula result. It‘s useful for solving problems backwards, like determining what sales figures you need to hit a revenue target.

To use Goal Seek:

1. Set up your formulas, with one cell each for inputs, outputs, and calculations.
2. Go to Data > What-If Analysis > Goal Seek.
3. In the Goal Seek dialog:

• Set the "Set cell" to your formula cell
• Enter the "To value" you want the formula to equal
• Set "By changing cell" to your input cell

4. Click OK. Excel will iterate through values for the input cell until the formula matches your goal.

Let‘s look at an example. Say you‘re a sales manager tasked with hitting a monthly revenue target of $100,000. You know your average deal size is $8,000, and you want to figure out how many deals your team needs to close to reach the goal.

First, set up the basic formula:

Revenue = # of Deals × Average Deal Size

In Excel:

Metric	Value	Formula
Revenue Target	$100,000
# of Deals	10
Average Deal Size	$8,000
Actual Revenue	$80,000	=B3*B4

To use Goal Seek:

• Set cell: B5 (Actual Revenue)
• To value: 100000
• By changing cell: B3 (# of Deals)

Excel will calculate that you need 12.5 deals (rounded to 13) to hit $100,000:

Metric	Value	Formula
Revenue Target	$100,000
# of Deals	12.5
Average Deal Size	$8,000
Actual Revenue	$100,000	=B3*B4

Some limitations of Goal Seek:

• It only solves for one input at a time. For multiple inputs, use the Solver add-in.
• The formulas can‘t be overly complex. Nested IF statements and array formulas may cause issues.
• Goal Seek is not as powerful as dedicated optimization libraries like scipy (Python) for solving complex problems.

That said, for quick back-of-the-envelope analysis and prototyping, Goal Seek is a valuable tool to have in your Excel arsenal.

Recap & Next Steps

We‘ve covered three key Excel techniques for leveling up your data analysis game:

1. Pivot tables for summarizing data
2. VLOOKUP for combining datasets
3. Goal Seek for solving problems backwards

But don‘t stop there – Excel has a ton of other powerful features worth exploring, like:

• Power Query for data cleaning and ETL
• Power Pivot for data modeling and DAX formulas
• Advanced charting with PivotCharts and sparklines
• Macros and VBA for automating repetitive tasks

And if you really want to geek out, check out the new LAMBDA function that lets you create your own custom functions in Excel formulas – no VBA required! The Excel team at Microsoft is constantly evolving the tool to make it more powerful for modern data analysis.

Of course, there‘s no substitute for hands-on practice. The best way to build your Excel skills is to start using these techniques in your day-to-day work. Keep an eye out for opportunities to use pivot tables, VLOOKUP, and Goal Seek in your current projects. Over time, you‘ll build the muscle memory to reach for them without a second thought.

I also recommend bookmarking the official Microsoft Excel documentation for reference. The site has comprehensive guides on every function and feature, along with helpful tutorials and templates.

And don‘t forget to check out the many great Excel courses and certificate programs out there. If you‘re looking to really boost your resume, the Microsoft Office Specialist certification is widely recognized by employers.

In closing – don‘t underestimate Excel. It may not have the same buzz as Python or Tableau, but it remains an essential tool for data analysis at organizations of all sizes. Mastering Excel will serve you well in any data-related role, and give you a solid foundation for learning more advanced tools down the road.

Do you have a favorite Excel trick not covered here? Let me know in the comments – I‘m always looking to learn from my fellow data nerds!

[^1]: "The State of Data Science 2021." Anaconda. https://www.anaconda.com/state-of-data-science-2021