Excel PRO TIPS: 75+ Tips to go from Excel Beginner to Pro
4.7 (1,721 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
15,183 students enrolled

Excel PRO TIPS: 75+ Tips to go from Excel Beginner to Pro

Excel tips & hands-on demos to become an Excel POWER USER. Learn Excel formulas, pivot tables, charts, analytics & more!
4.7 (1,721 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
15,183 students enrolled
Last updated 6/2020
English [Auto]
Current price: $121.99 Original price: $174.99 Discount: 30% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 10.5 hours on-demand video
  • 3 articles
  • 4 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • Build tools to help you automate, streamline, and absolutely revolutionize your workflow with Excel
  • Explore 75+ unique tips, tools and case studies that you won't find in ANY other Excel course, guaranteed
  • Get LIFETIME access to resources, Excel project files, quizzes, and 1-on-1 expert support
  • Master Excel's powerful data analysis tools like Goal Seek, Scenario Manager, Solver, and Analysis ToolPak
  • Practice with fun, interactive, and highly effective lessons from a best-selling Excel instructor
  • Become an absolute Excel POWER USER
  • Microsoft Excel 2007-2019 or Office 365 (Note: some tips may not apply to all versions of Excel)
  • Experience with formulas & functions, basic charts, and PivotTables strongly encouraged (but not required)
  • This course is designed for PC users (some tips & tools may not be available for Mac)

See why this is one of the TOP-RATED Excel courses on Udemy:

"At first I just said to myself, "Wow, Excel is capable of that? It's amazing!" Then as I continued through the course I told myself "This guy is doing magic!", and now I feel like I'm capable of doing the same. I'm definitely buying his other Excel courses!"

- Judit Bekker

"One of the very best Excel classes I've taken - great instructor, awesome production, very comprehensive and exceptionally useful. Well worth your time if you want to become an Excel pro!"

- Ernie Atkins

"I've been using Excel for over 20 years and consider myself to be an Excel "power user". As with every single course by Chris, I go in with pretty solid knowledge of the material (formulas, VBA, Power Query, etc) and walk out twice as powerful as I walked in. This course is no different -- I PROMISE you, you will find absolute gems in this course. You will never regret an investment in your future with Maven Analytics!"

-Ryan Hughes



This course is NOT an introduction to Excel.

It's not about comprehensive, 101-style deep dives into Excel's core capabilities, or about showing off cheesy, impractical Excel "hacks". It's about featuring some of the most powerful and effective tools and techniques used by Excel professionals, and sharing them through crystal clear demos and unique, real-world case studies.

Unlike most Excel courses, this one is non-linear and constantly evolving. This means that you can jump to different sections or individual lectures freely, and explore brand new Excel tips added on a regular basis.  Each video is designed to be an independent and self-contained Excel demo, to help you learn these tools and techniques in quick, bite-sized lessons.

The best part? I'm including a downloadable Excel workbook and solution file containing every single Excel demo and dataset covered in the course -- ranked by difficulty (1-5 stars), organized by category, and hyperlinked for quick and easy access.


The Excel tips and techniques covered in this course fall into six categories:

  • Excel Productivity Tips (navigation, flash fill, cell protection, advanced sorting & filtering, etc.)

  • Excel Formatting Tips (freeze panes, row/column grouping, advanced custom formats, etc.)

  • Excel Formula Tips (formula auditing tools, fuzzy lookups, detecting duplicates, randomization, etc.)

  • Excel Visualization Tips (sparklines, filled maps, custom templates, Excel form controls, etc.)

  • Excel Pivot Table Tips (slicers & timelines, custom layouts, value calculations, etc.)

  • Excel Analytics Tips (outlier detection, monte carlo simulation, forecasting, CUBE functions, etc.)


While the level of difficulty varies considerably, the Excel demos generally start simple and become progressively more complex within each section. There are no strict prerequisites for this course, but keep in mind that some demos may be challenging without a strong foundational knowledge of the underlying Excel concepts (like Excel Pivot Tables or formulas, for example).

It's time to start working smarter, not harder. If you're looking to maximize your efficiency, supercharge your productivity, and become an absolute Excel POWER USER, this is the course for you.

See you in there!

-Chris (Founder, Maven Analytics)


Most students in this course enroll in our full EXCEL LEARNING PATH, designed to help you build a deep, expert-level Excel skill set:

  1. Advanced Excel Formulas & Functions

  2. Data Visualization with Excel Charts & Graphs

  3. Data Analysis with Excel PivotTables

  4. Excel Power Query, Power Pivot & DAX

  5. Excel Pro Tips for Power Users (you are here)

Looking for the full business intelligence stack? Search for "Maven Analytics" to browse our full course library, including Excel, Power BI, MySQL, and Tableau courses!

NOTE: Most Excel demos are compatible with Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019 or Office 365 (some may be unavailable in Excel for Mac or Excel Online)

*** LIFETIME access includes Excel Pro Tip PDF eBook, Excel project files, 1-on-1 expert support, and a 100% guarantee ***

Who this course is for:
  • Excel users looking to level-up and master the same tools and techniques used by Excel MVPs
  • Anyone hoping to maximize productivity, work more efficiently, and take their career to a new level
  • Students looking for an engaging, hands-on, and highly interactive approach to Excel training
  • Anyone looking to pursue a career in data analysis or business intelligence
  • This course is NOT recommended for absolute beginners (some prior experience with Excel is encouraged)
Course content
Expand all 93 lectures 10:31:04
+ Getting Started
4 lectures 05:49
READ ME: Important Notes for New Students
DOWNLOAD: Course Resource Files
+ Excel Productivity Tips
16 lectures 01:41:14

Excel's footer bar is a useful little tool, providing quick access to basic summary stats like Sum, Count, and Average. But we can do better! In this tip I'll show you how to customize your footer to display additional options like Numerical Count, Max or Min, as well as various indicators (macro recording, caps lock, etc).

Customizing the Excel Footer

If you aren't using CTRL shortcuts, you aren't working efficiently in Excel. In this tip I'll demonstrate how to supercharge your productivity by using CTRL, SHIFT, and Arrow keys to select data and navigate through worksheets, tables and ranges.

Navigating Workbooks with CTRL Shortcuts

Listen, it's time to give that mouse of yours a rest. ALT key tips are powerful shortcuts that allow you to quickly access virtually any tool in the Excel ribbon without a single click. In this demo I'll share some of my personal favorite key tips, like adding filters, clearing formats, and inserting PivotTables.

Accessing Ribbon Tools with ALT Key Tips

Did you know that Excel's "Go To" menu contains a secret set of options that allow you to select certain types of objects or cells? Well now you do. In this tip I'll show you how to quickly find worksheet cells containing blanks, formulas, constants, data validation rules, or conditional formats.

Accessing Excel's "Go To" Special Options

Identifying and removing blank rows in a cell range can be a serious headache, especially if you aren't using the proper tools. In this tip I'll demonstrate how to use Excel's Go-To Special options to quickly select and highlight blanks in a matter of seconds.

Removing Blank Rows in Excel Worksheets

Data validation is a pretty incredible tool for building dynamic Excel models and dashboards. In this demo, we'll use the data validation "list" option to add interactive drop-down menus to a custom-built property cost calculator.

Preview 07:11

Excel's autofill tool is a nifty little feature, but FLASH FILL takes the game to an entirely new level. In this tip, we'll use Flash Fill to automatically detect and apply complex patterns in our dataset.

Filling Patterns with Autofill & Flash Fill

Many Excel users don't realize that the ribbon (as well as the quick access toolbar above it) is completely customizable. Wish you could build your own tab to centralize all of your go-to tools? Wish granted. In this tip I'll show you how to add or remove tools from the ribbon or quick access toolbar, rearrange existing options, and even design your own custom tabs and tool sets.

Customizing the Excel Ribbon Tools

So you have a big report due by the end of the day, but Gary from IT accidentally sent over the data as a .txt file. Not to worry! In this tip, I'll show you two ways that you can split merged text fields from .txt or .csv files into nice and clean tabular formats.

Splitting Text to Columns in Excel

Have you ever needed to make quick visual comparisons between two separate Excel files? If so, meet your new best friend: synchronous scrolling. In this demo I'll show you how we can use this tool to sync the scrollbars of two open files, in order to explore them simultaneously.

Synchronous Scrolling Across Multiple Workbooks

Identifying unique values in Excel is nothing to write home about, and there are a few different ways to do it (including the "Remove Duplicates" option, Power Query, or PivotTables). But if you need to extract those uniques into a new list without modifying the original, advanced filters is the way to go.

Extracting Unique Values from a List in Excel

Listen, we need to talk about those formulas of yours. Sure, you understand that Sheet2!$B$17:$G$8239 contains your monthly sales goals, but to other users it just looks like jibberish. In this tip, I'll show you how to streamline your formulas and simplify references using named ranges and tables.

Using Named Range & Table References in Excel Formulas

Listen, it's time to stop data scoundrels once and for all. If you're working with sensitive data or shared, complex worksheets, cell protection is a no-brainer. In this tip I'll walk through a few protection options, including locking cells and hiding formulas from view.

Preview 08:09

Looking to take your sorting skills to the next level? You've come to the right place. Most users are comfortable sorting rows using standard tools or column headers, but we can do better. In this tip I'll show you how to define multiple levels of sorting criteria, how to sort based on text, values, or formats, and even how to apply custom rules to sort columns instead of rows.

Multi-Level Row & Column Sorting

Did you know that Excel's Advanced Filter tool includes an option to reference a custom criteria range? After ignoring that option for years, I finally decided to play around with it a bit -- and it's actually pretty incredible! In this tip I'll show you how to use the criteria range to define complex combinations of filtering rules that would be impossible otherwise.

Configuring Advanced Filter Criteria in Excel
QUIZ: Excel Productivity Tips
10 questions
+ Excel Formatting Tips
15 lectures 01:30:58

Anyone who has taken my Excel courses knows that I'm a stickler for shortcuts, and formatting is no exception. In this tip, I'll share some of the most common number formatting shortcuts using CTRL and SHIFT.

Excel Formatting Shortcuts

If you've ever tried to manually drag or shape workbook objects into precise locations, you know how maddening and frustrating it can be (especially if you're a perfectionist like me). Well I have good news -- in this tip I'll show you some incredibly useful techniques, like using ALT to snap objects to the grid, or SHIFT to limit movement to horizontal or vertical planes.

Snapping Objects to the Excel Grid

Sometimes when I'm building fancy tools or dashboards in Excel, I like to remove some of the workbook elements that give it that classic "spreadsheet" look and feel. In this tip, I'll show you how some simple adjustments -- like hiding gridlines, headers, or the formula bar -- can go a long way when it comes to designing clean and polished user-facing reports.

Hiding Excel Workbook Elements

The next time you find yourself manually applying the same formatting rules over and over, just say no. In this tip, I'll show you how Excel's format painter can be used to copy all of the formatting attributes of a given cell or range (including custom conditional formatting rules), and apply them to new cells.

Replicating Formats & Styles with Excel's Format Painter

Colors and borders are more than just simple stylistic tools; when used strategically, they can be incredibly useful for organizing and visualizing data in Excel. In this demo, we'll use fill effects and white borders to transform a raw range values into a clean and polished report.

Color & Border Design Tips in Excel

If you've ever found yourself endlessly scrolling through large spreadsheets trying to match values to their column headers, this demo is for you. In this tip, we'll walk through three different ways to freeze Excel worksheet panes in order to keep the most important information in view.

Preview 05:58

Most Excel users center text across multiple cells by merging them together and aligning to center. But we aren't most Excel users, we are POWER USERS. In this tip, I'll show you a hidden formatting option to center text across a selection, without the headaches that go along with merging cells.

Centering Across a Selection

Certain Excel tools, like data bars and icon sets, include native options to hide text from view. However, there's a more flexible and universal technique to make text invisible using a custom number format (;;;). In this demo, I'll show you how this approach can be used with conditional formatting color scales to visualize monthly temperature trends over time.

Creating an "Invisible" Format

There are a few data types that can be notoriously difficult to work with in Excel (i.e. dates, zip codes, phone numbers, etc.), but it's time to show Excel who's boss. In this tip, I'll share a few options for wrangling zip codes and phone numbers into the exact formats you need.

Zip Codes & Phone Numbers

When it comes to building reports in Excel, sometimes less is more. One common mistake is to include everything in one view, which often serves no purpose other than to overwhelm and confuse the end users. In this tip, I'll show you how to use grouped rows and columns to consolidate your data and draw attention to the most important information.

Grouping Worksheet Columns & Rows

I have a love/hate relationship with Excel error messages. Sure, they help make sure my formulas are defined properly, but sometimes errors aren't actually a bad thing (like #DIV/0, when the denominator could be zero). In this tip, I'll demonstrate how to wrap formulas in an IFERROR statement to help customize those errors and design polished user-facing reports.

Formatting Excel Errors with IFERROR

MDY, DMY, MMDDYYYY, oh my! As a power user, you're no stranger to Excel date values and date/time formats, but what happens when you encounter a format so strange that Excel has no choice but to surrender and treat it as text? No worries, in this demo I'll show you how to "force" dates into acceptable formats using text functions like LEFT, MID and RIGHT.

Converting Excel Text to Dates

To the average Excel user, standard formatting options and conditional rules are more than enough. But not for us. If you're bold enough to go rogue and define your own formula-driven formatting rules, this tip is for you. In this demo we'll use formulas to apply dynamic date-based formats based on deadlines in our "To-Do" list.

Creating Dynamic Formats with Excel Formulas

Fun fact: there's an entire hidden syntax that Excel uses to define custom number formats, allowing you to create virtually any sort of format you can imagine. Round off thousands and add a "K" label? No problem. Add symbols for negatives and placeholders to maintain alignment? Done. In this tip, I'll introduce you to this secret formatting language and walk through a few common examples.

Preview 10:37
QUIZ: Excel Formatting Tips
10 questions
+ Excel Formula Tips
16 lectures 02:01:14

At one point or another, every Excel pro has encountered a workbook so massive and so complex that even simple workbook updates grind things to a complete halt. In this tip, we'll take a moment to talk about calculation modes, and when it might be appropriate to switch from automatic (default) to manual.

Manual vs. Automatic Calculation Modes in Excel

I'll admit it -- I spent over 10 years working with Excel almost every day before I discovered formula line breaks. In this demo, I'll show you how a simple ALT-ENTER command is all it takes to transform your nested formulas from a tangled mess into clear and intuitive lines of code.

Adding Line Breaks to Excel Formulas

Raise your hand if you've ever Googled conversion rates and hard-coded them into your formulas (i.e. Celsius to Fahrenheit, Inches to Centimeters, Dollars to Pounds, etc.). Well kiss those days goodbye, because in this tip I'll show you how to use Excel's CONVERT function to instantly apply just about any sort of measurement conversion you can imagine.

Converting Units of Measurement with Excel Formulas

Let's get real. Real-TIME, that is. Excel's volatile TODAY() and NOW() functions are great tools for building reports that evolve and update based on the current point in time. In this tip, we'll practice building some simple project trackers using date and time functions.

Building Real-Time Tools with TODAY & NOW Functions

Here's the scenario: you've spent the past 852 hours building a incredible, fancy-pants Excel model, but now -- mere moments before you present it to the entire company -- you see the dreaded #REF! error where you should be seeing projected Q4 profits. What do you do? If you're a power user, you flash a confident smile, turn to your trusted formula auditing tools, and quickly identify the source of the problem to a thunderous round of applause. If you aren't a power user, you quietly curl into the fetal position until you are promptly fired. In this tip, I'll walk through some of Excel's most useful formula auditing and error checking tools (trace precedents/dependents, evaluate formula, etc.).

Preview 11:18

When it comes to extracting and analyzing filtered values from souce data, most Excel users turn to the trusty PivotTable. But what if you know exactly how you want to present your data, and need more formatting flexibility than a pivot can accommodate? In this tip, I'll show you how to use conditional stats functions like SUMIFS, COUNTIFS and AVERAGEIFS to create your own custom, pivot-style dashboards and reports.

Designing Pivot-Style Reports with Excel Formulas

While counting individual characters in a cell is quite simple (using the LEN function), counting entire words requires a bit more creativity. In this tip, we'll practice combining LEN, SUBSTITUTE, and TRIM functions to build our very own word counter in Excel.

Counting Words in Excel with LEN & SUBSTITUTE Functions

Ready to take your data validation skills to the next level? In this tip we'll explore a common challenge: creating a drop-down list that is dependent upon another. In this case we'll use the INDIRECT function to populate a list of Olympic sports, based on the season that a user has selected from a separate list.

Configuring Dependent Drop-Down Menus with INDIRECT

Hyperlinks are a great way to help users quickly navigate to different workbook locations, but the syntax can be quite tricky to master. In this demo, we'll build a handy "table of contents" sheet that dynamically links out to each tab in the workbook.

Creating Custom Hyperlinks Between Excel Worksheets

Although not quite as common as their "exact match" counterparts, approximate (aka "fuzzy") match lookups can be an incredibly powerful tool to keep in the arsenal. In this tip, I'll show you how to use an approximate match VLOOKUP function to assign discount rates based on sales quantity thresholds.

Using Approximate Match VLOOKUP Functions

Let's be honest, we've all had dreams of being a rockstar. Well I have good news and bad news: the bad news is that I can't help you realize that dream. The good news is that I can teach you how to use functions like OFFSET and RANDBETWEEN to build a band name generator in Excel, so at least you'll be prepared in case that day comes.

P.S. I've already trademarked The Belligerent Pipsqueaks.

Preview 06:26

For many years, a battle has raged within the Excel community about which formula is better: VLOOKUP or INDEX/MATCH. My stance is pretty neutral (there are applications for both), but there are some unique and interesting benefits to INDEX & MATCH. In this demo, we'll compare and contrast the two approaches.

Combining INDEX & MATCH Functions

I've always said that SUMPRODUCT is like the Ebenezer Scrooge of Excel: mysterious, unforgiving, and deeply misunderstood. But deep down, it's just a friendly function trying to help you do awesome things in Excel. For this tip, we'll combine SUMPRODUCT with COUNTIF to iterate through rows and calculate the number of matching items between two lists in Excel.

Finding Matching Items Across Lists in Excel

Extracting duplicate values from a list in Excel is pretty simple (using the Advanced Filter), and removing them altogether is even simpler (using the Remove Duplicates tool), but counting duplicate values using cell formulas alone poses a much more formidable challenge. In this expert-level tip, I'll share one solution using a powerful combination of SUMPRODUCT and COUNTIF functions.

Counting Duplicate Values with SUMPRODUCT

For those of you looking to REALLY master lookup and reference functions, here's a challenge for you: in cases where you have multiple matching values between tables, how can you tell Excel to return the last match, rather than the first? In this expert-level tip, we'll explore one solution using a combination of INDEX, MATCH, and COUNTIF functions.

Using Many-to-Many Lookups in Excel
QUIZ: Excel Formula Tips
10 questions
+ Excel Visualization Tips
13 lectures 01:35:15

By default, Excel charts are tied to the underlying grid, meaning that they move and stretch as the underlying rows and columns are modified. But what if you want to fix the exact position of your visuals, and prevent them from distorting as you make changes to the rest of the worksheet? In this tip, we'll adjust the chart properties to essentially "disconnect" them from the grid.

Configuring Excel Chart Properties (Move & Size)

When building reports or dashboards in Excel, you may choose to hide the actual source data from view. However, in some cases this will cause the actual chart itself to disappear as well. In this tip, I'll show you how to quickly adjust your chart settings to persist even when the underlying source data is hidden.

Managing Hidden Chart Source Data in Excel Worksheets

For those of you working with the latest versions of Excel (2016, 2019, Office 365), visualizing geospatial data just got a whole lot easier. In this tip, we'll practice plotting state-level population and income data using Excel's new Filled Map visual, and discuss some important considerations along the way.

Visualizing Geospatial Data with Filled Maps (Excel 2016+ or Office 365)

Most people don't think of Excel as a "data visualization" platform, but then again most people have never ventured beyond basic, default templates. The fact is, Excel offers an insane amount of creative freedom if you know what you're doing. In this tip, I'll show you how even a few basic adjustments to colors, labels and titles can help bring your visuals to life.

Customizing Excel Charts to Tell a Story

Every time I think about sparklines, Shakespeare comes to mind: "though she be but little, she is fierce". That's because even though sparklines are so tiny that they live within the confines of a single cell, they can be incredibly effective tools for visualizing patterns and trends. In this tip, we'll use them to quickly compare how five individual sales reps performed against monthly goals.

Adding Sparklines to Worksheet Cells

There are two things in this world that drive me nuts: ice cubes that unpredictably dislodge in the middle of a sip, and people who don't customize their Excel visuals. In this tip, I'll demonstrate how easy it is to personalize a basic chart, save it as your own custom template, and apply it to other visuals with the click of a button.

Designing Custom Chart Templates in Excel

Would you believe me if I told you that one of my all-time favorite visuals in Excel doesn't involve a single chart, object, or number? I hope so, because that would be a very strange thing to lie about. This tip is all about using color scales and custom number formats to create an incredibly simple -- yet highly effective -- heat map effect.

Preview 07:11

Histograms are absolutely essential tools for visualizing the distribution of values, based on the frequency of observations falling within specific ranges or "bins". In this Excel demo we'll look at the heights and weights of 2,000 olympic athletes, and analyze how those values are distributed across the sample as a whole, as well as by gender and sport.

Analyzing Distribution with Histograms

One of the key principles of analytics is that numbers should always be presented in context. In other words, that fact that you sold 50 widgets this week means nothing -- is that more or less than you sold last week? How does that compare against benchmarks? In this tip, we'll discuss the importance of creating context using clear and measurable goals, and practice building custom gauge charts to visualize pacing.

Goal Pacing with Custom Excel Gauge Charts

One of my favorite things about data viz in Excel is that you can mash together all sorts of chart types to create "Frankenstein"-style visuals. Generally this sort of experimentation will lead to ugly, confusing, or just plain ridiculous results, but with a bit of practice you'll begin to discover some truly interesting and powerful combinations. In this tip, we'll practice combining a line chart with a 100% stacked column to draw attention to specific time periods within our sample.

Highlighting Time Periods with Combo Charts

If you're looking in becoming a legitimate data viz rockstar in Excel, there's one concept that you need to wrap your head around: dynamic source data. In other words, charts don't have to reference a fixed range of cells; instead, they can reference flexible ranges defined by functions like OFFSET, MATCH and COUNTA. In this tip, I'll show you how to combine named ranges with data validation lists to let users dynamically select which series to display in a chart.

Configuring Dynamic Chart Source Ranges

Looking to develop pro-quality Excel models, while impressing your friends at the same time? Read on. This tip is all about designing dynamic, interactive tools using something called form controls. In this demo we'll visualize average wine ratings and prices by country, and add a list box to allow users to filter the view down to specific wine varieties.

Preview 11:48
QUIZ: Excel Visualization Tips
10 questions
+ Excel Pivot Table Tips
15 lectures 01:36:37

Sometimes the simplest tips can be the most helpful ones. In this demo, we'll take a closer look at our trusty PivotTable field list, and explore some helpful customization options like adjusting the layout and changing the field sort order.

Customizing the Pivot Table Field List

If you've ever worked with text fields in a PivotTable, you've likely encountered a case where your column stretches far beyond the width of the Excel window in order to accommodate the longest string. It doesn't sound like a big deal, but this "autofit" behavior can be extremely annoying when you're trying to quickly slice and dice your data. In this tip, I'll share a simple solution to prevent your pivots from automatically adjusting column widths.

Autofitting Column Width in Pivot Tables

There are a few things in this world that will forever be shrouded in mystery: Stonehenge, the Egyptian pyramids, and the fact that Excel PivotTables default to "Compact Form". For those of you who haven't ventured beyond the default PivotTable layouts, prepare to be rocked. In this tip, I'll discuss why I absolutely despise Compact Form, and share some great use cases for working with Outline or Tabular layouts instead.

Pivot Table Outline & Tabular Layouts

PivotTables are typically used for aggregating and filtering numerical values, but don't you dare assume that's all they're good for! Pivots are incredibly useful tools for understanding data distribution and composition as well, using counts (or frequencies) of non-numerical fields. In this tip, we'll tell our values to take a hike and see what we can learn about our data using purely text-based fields.

Counting Non-Numerical Fields Using Pivots

To those of you who have ever worked with date fields in a PivotTable, I feel your pain. Sometimes they seem to have a mind of their own -- automatically grouping when you don't want them to, creating new fields that you don't need, changing formats unexpectedly, and so on. In this tip we'll demystify the process of grouping and ungrouping dates, adjust Excel's default behavior, and explore some alternative approaches.

Grouping & Ungrouping Dates with Pivot Tables

By default, PivotTables allow you to filter a given field based on either labels (text) or values, but not both. Want to show customers named "Tim" who purchased at least 1,000 bananas? Not with default filters, you won't. But not to worry, in this tip I'll show you a simple way to activate multiple filters in your pivots, and use both text- and value-based criteria to explore your data.

As for why Tim bought more than a thousand bananas, Excel may not be able to help us there...

Enabling Multiple Pivot Table Filters

When you first begin working with PivotTables in Excel, you start to identify some common patterns -- numerical fields go in the Values pane, text-based dimensions go into Filters, Rows or Columns, and so on. And while those patterns hold true in many cases, there are some incredibly interesting things you can do by thinking outside the box. In this tip we'll pull wine prices (a numerical field) into our rows pane and wine names (a text field) into our values pane, and use grouping tools to analyze the frequency of wines that fall into each price range.

Preview 06:50

Using pivots without value calculations is like driving a Ferrari but never taking it out of second gear. Sure, you can do quite a bit by simply aggregating and summarizing your values, but calculations like Difference From, % of Parent, Running Total, and Rank allow you to explore your data from entirely different perspectives. In this tip, we'll explore several of the most common PivotTable value calculations.

Adding Value Calculations to Pivot Tables

I'll be honest, 99% of you will never need to use this tip (but for the 1% of you who will, this is a lifesaver). While PivotTables will hide rows containing no data (as they should), you have the option to not only force them to be displayed, but also customize how they are formatted. In this tip, we'll modify our IMDb Movie PivotTable to display Gross Revenue values by Country and Genre, even in cases where no revenue was actually generated.

Configuring Pivot Table Errors & Empty Items

I love slicers and timelines because they sound complex and tend to impress people way more than they should. The thing is, a slicer is just a basic filter dressed up to look fancy, and a timeline is just a slicer designed to work with dates. In this tip, we'll practice inserting slicers and timelines, linking them to multiple pivots, and creating a user-friendly way to interact with our Olympic athlete dataset.

Configuring Pivot Table Slicers & Timelines

Conditional formatting is a powerful visualization tool, which becomes exponentially more powerful when combined with the flexibility of a PivotTable. In this tip, we'll review several ways to customize how conditional formats react to changes in the table layout, and practice some advanced techniques using color scales and data bars.

Advanced Pivot Table Conditional Formatting

What would you say if I told you that you can create a PivotTable from a table, then DELETE the table or source data itself with no impact to the pivot? I imagine that you'd react with confusion and perhaps violence, which is understandable. But it's entirely true -- in this tip I'll introduce you to something called the Pivot Cache, and demonstrate how it can be used to remove (and later revive) your underlying source data.

Removing & Reviving Pivot Table Source Data

Have you ever wondered how Excel knows how to sort month names or days of the week? Probably not, but you're thinking about it now. It sounds simple, until you realize that there is no consistent logic (like ascending/descending values or alphabetical ordering) that tells Excel that Wednesday should always fall between Tuesday and Thursday. In this tip, we'll take a look at Excel's custom sort lists, and practice defining one of our own.

Preview 05:23

For those of you managing complex PivotTables with multiple calculated items and fields, this next tip is for you. In this demo we'll explore a case where two calculations overlap, use the Solve Order tool to determine priority, and then generate a documentation sheet using the List Formulas option.

Solve Order & List Formula Reports
QUIZ: Excel Pivot Table Tips
10 questions
+ Excel Analytics Tips
13 lectures 01:59:09

I'm normally pretty skeptical of Excel tools that encourage laziness, but I have to admit that I've been pretty impressed with the Quick Analysis feature. In this tip, I'll show you how Excel offers intelligent shortcuts and tips based on a selection of data, including conditional formats, visualizations, and calculated fields.

Excel Quick Analysis Tools

Excel is a powerful platform for business modeling: change inputs A, B, and C and note the impact to output D. But what if there are specific combinations of inputs that you'd like to quickly access without manually changing multiple cells? In this tip, we'll use Excel's Scenario Manager to define several pre-set combinations of input values (Percent Down, Interest Rate, and Term Length), and see how each "scenario" impacts the model output.

Comparing Outcomes Using the Scenario Manager

When it comes to solving simple optimization models in Excel (based on a single, value-based input and a single, formula-based output), Goal Seek is a great tool to keep in the arsenal. In this tip, we'll use Goal Seek to calculate the quantity of sales required to break even or hit specific profit targets.

Solving Simple Optimizations with Goal Seek

When it comes to producing simple forecasts based on historical data, Excel's Forecast Sheet tool is a great place to start. In this demo we'll practice forecasting both seasonal and non-seasonal trends, using Apple stock prices and average monthly temperatures in Barcelona.

Creating Basic Forecasts in Excel

Statistically speaking, outliers are defined as data points which fall more than 1.5 times the interquartile range above the 3rd quartile or below the 1st quartile. In plain English, we're talking about abnormally small or large values in a data set. For this tip, we'll use some basic Excel stats functions (like MEDIAN and QUARTILE) to define and conditionally format outliers in our sample.

Preview 09:24

One of Excel's lesser known "What-If Analysis" tools, Data Tables simply don't get the credit that they deserve (which I imagine is due in part to their misleading and completely unimaginative name). Simply put, data tables allow you to generate an entire array of results based on a range of input values. In this tip, we'll use a data table to see how monthly mortgage payments compare based on 15 different interest rates from 3% to 10% -- without writing a single formula!

Generating Automated Data Tables

Power Query is one of Excel's most important and impactful developments over the past 20+ years, and it's easy to see why. Power Query allows you to connect to virtually any type of source data (flat file, database, web source, etc), shape and transform it within the Query Editor, and load it to Excel worksheets or the data model for further analysis. In this tip we'll take a high-level pass through some basic Power Query options, and practice creating and modifying a query to a local csv file.

Connecting & Transforming Data with Power Query

There are two major milestones that Excel users typically reach as they progress towards "Power User" status: the first is learning how to connect data using functions like VLOOKUP, OFFSET, INDEX and MATCH, and the second is realizing that there's a much better way. In this tip, I'll show you how to use data models and table relationships to connect data without writing a single formula.

Creating Relational Data Models in Excel

When it comes to CUBE functions, they will be ignored by 99.9% of Excel users but absolutely cherished by the other 0.1%. In this tip, I'll show you how to use functions like CUBESET, CUBEVALUE and CUBEMEMBER to retrieve filtered values from a data model, and build custom reports and dashboards without relying on Power Pivot.

Accessing the Data Model with CUBE Functions

Monte Carlo Simulation is a method used to predict the probability of given outcomes based on repeated random sampling. In other words, it's a way to generate and analyze thousands (or even millions) of potential outcomes in order to understand which ones are most or least likely to actually occur. In this tip, we'll play with a fully-functional roulette simulator (along with Data Tables and randomized inputs) to demonstrate how to build our own simulation models in Excel.

Building a Monte Carlo Simulation in Excel

If you ever find yourself in a situation where your life depends on solving a complex optimization model in Excel, here's what to do: 1) stay calm, 2) activate the Solver add-in, and 3) effortlessly optimize your way to freedom. In this tip, we'll use Solver to determine the optimal shipping strategy to minimize costs based on a number of decision variables and real-world constraints.

Preview 12:16

Looking to dive into some advanced analytics techniques like correlation, covariance, regression or t-tests? Good news: these options (along with many others) are all available in Excel using a built-in tool known as the Analysis ToolPak. In this tip, we'll preview some basic options like correlation analysis and descriptive statistics to explore a sample of 5,000 Olympic athletes.

Excel Analysis ToolPak (Preview)
QUIZ: Excel Analytics Tips
10 questions
+ Wrapping Up
1 lecture 00:47
More from Maven Analytics