Microsoft Excel Pro Tips: Go from Beginner to Advanced Excel

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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!

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.

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.