Excel Pro Tips: 75+ Tips to go from Excel Beginner to Pro [v]

1. Course Structure & Outline

The author shows you the course structure and the outline of this course in detail.

2. Setting Expectations

The author tells you how you can go about this course.

1. Introduction

It is an introduction for Excel Productivity Tips.

2. Customizing the Excel Footer

Excel's footer bar is a useful little tool, providing quick access to basic summary stats like Sum, Count, and Average. In this tip, you will learn 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).

3. Navigating Excel Workbooks with CTRL Shortcuts

In this tip, you will learn how to supercharge your productivity by using CTRL, SHIFT, and Arrow keys to select data and navigate through worksheets, tables and ranges.

4. Accessing Excel Ribbon Tools with ALT Key Tips

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, the author will share some of his personal favourite key tips, like adding filters, clearing formats, and inserting PivotTables.

5. Accessing Excel's "Go To" Special Options

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, you will learn how to quickly find worksheet cells containing blanks, formulas, constants, data validation rules, or conditional formats.

6. Removing Blank Rows in Excel Worksheets

In this tip, the author will demonstrate how to use Excel's Go-To Special options to quickly select and highlight blanks in a matter of seconds.

7. Creating Drop-Down Menus in Excel with Data Validation

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.

8. Filling in Patterns with Autofill & Flash Fill

In this tip, we'll use Flash Fill to automatically detect and apply complex patterns in our dataset.

9. Customizing the Excel Ribbon Tools

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 your go-to tools? In this tip you will learn 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.

10. Splitting Text to Columns in Excel

In this tip, you will learn two ways that you can split merged text fields from .txt or .csv files into nice and clean tabular formats.

11. Synchronous Scrolling Across Multiple Excel Workbooks

In this demo, the author will show you how we can use this tool to sync the scrollbars of two open files, in order to explore them simultaneously.

12. Extracting Unique Values from a List in Excel

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 unique into a new list without modifying the original, advanced filters is the way to go.

13. Using Named Range & Table References in Excel Formulas

In this tip, you will learn how to streamline your formulas and simplify references using named ranges and tables.

14. Protecting Cells & Formulas in Excel Workbooks

In this tip, the author will walk through a few protection options, including locking cells and hiding formulas from view.

15. Multi-Level Row & Column Sorting

Most users are comfortable sorting rows using standard tools or column headers, but we can do better. In this tip you will learn 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.

16. Configuring Advanced Filter Criteria in Excel

Did you know that Excel's Advanced Filter tool includes an option to reference a custom criteria range? In this tip you will learn how to use the criteria range to define complex combinations of filtering rules that would be impossible otherwise.

1. Introduction

In this section, the author introduces about Excel Formatting Tips.

2. Excel Formatting Shortcuts

In this tip, the author shares some of the most common number formatting shortcuts using CTRL and SHIFT.

3. Snapping Objects to the Excel Grid

In this tip, you will learn some incredibly useful techniques, like using ALT to snap objects to the grid, or SHIFT to limit movement to horizontal or vertical planes.

4. Hiding Excel Workbook Elements

In this tip, you will learn how to do 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.

5. Replicating Formats & Styles with Excel's Format Painter

The next time you find yourself manually applying the same formatting rules over and over, just say no. In this tip, you will learn 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.

6. Colour & Border Design Tips in Excel

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

7. Freezing Panes in Excel Worksheets

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.

8. Centering Across a Selection

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

9. Creating an "Invisible" Format

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, you will learn how this approach can be used with conditional formatting colour scales to visualize monthly temperature trends over time.

10. Zip Codes & Phone Numbers

There are a few data types that can be difficult to work with in Excel (i.e. dates, zip codes, phone numbers, etc.), but in this tip, the author shares a few options for wrangling zip codes and phone numbers into the exact formats you need.

11. Grouping Worksheet Columns & Rows

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, you will learn how to use grouped rows and columns to consolidate your data and draw attention to the most important information.

12. Formatting Excel Errors with IFERROR

In this tip, the author demonstrates how to wrap formulas in an IFERROR statement to help customize those errors and design polished user-facing reports.

13. Converting Excel Text to Dates

In this demo, you will learn how to "force" dates into acceptable formats using text functions like LEFT, MID and RIGHT.

14. Creating Dynamic Formats with Excel Formulas

In this demo we'll use formulas to apply dynamic date-based formats based on deadlines in our "To-Do" list.

15. Advanced Excel Number Formats

In this tip, the author introduces you to formatting language and walk through a few common examples.

1. Introduction

In this section, the author introduces about Excel Formatting Tips.

2. Manual vs. Automatic Calculation Modes in Excel

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.

3. Adding Line Breaks to Excel Formulas

In this demo, you will learn 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.

4. Converting Units of Measurement with Excel Formulas

In this tip, you will learn how to use Excel's CONVERT function to instantly apply just about any sort of measurement conversion you can imagine.

5. Building Real-Time Tools with TODAY & NOW Functions

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.

6. Excel Formula Auditing Tools

In this tip, the author walks through some of Excel's most useful formula auditing and error checking tools (trace precedents/dependents, evaluate formula, etc.).

7. Designing Pivot-Style Reports with Excel Formulas

In this tip, you will learn how to use conditional stats functions like SUMIFS, COUNTIFS and AVERAGEIFS to create your own custom, pivot-style dashboards and reports.

8. Counting Words in Excel with LEN & SUBSTITUTE Functions

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.

9. Configuring Dependent Drop-Down Menus with INDIRECT

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.

10. Creating Custom Hyperlinks Between Excel Worksheets

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.

11. Using Approximate Match VLOOKUP Functions

In this tip you will learn how to use an approximate match VLOOKUP function to assign discount rates based on sales quantity thresholds.

12. Randomizing a Selection with OFFSET & RANDBETWEEN Functions

The author teaches you how to use functions like OFFSET and RANDBETWEEN to build a band name generator in Excel.

13. Combining INDEX & MATCH Functions

There are some unique and interesting benefits to INDEX & MATCH. In this demo, we'll compare the two approaches.

14. Finding Matching Items Across Lists 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.

15. Counting Duplicate Values with SUMPRODUCT

Extracting duplicate values from a list in Excel is 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, the author shares one solution using a powerful combination of SUMPRODUCT and COUNTIF functions.

16. Using Many-to-Many Lookups in Excel

In this expert-level tip, we'll explore one solution using a combination of INDEX, MATCH, and COUNTIF functions.

1. Introduction

The author introduces the section Excel Visualization Tips.

2. Configuring Excel Chart Properties (Move & Size)

In this tip, we'll adjust the chart properties to essentially "disconnect" them from the grid.

3. Managing Hidden Chart Source Data in Excel Worksheets

In this tip, the author shows you how to quickly adjust your chart settings to persist even when the underlying source data is hidden.

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

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.

5. Customizing Excel Charts to Tell a Story

In this tip, you will learn how even a few basic adjustments to colours, labels and titles can help bring your visuals to life.

6. Adding Sparklines to Worksheet Cells

In this tip, we'll quickly compare how five individual sales reps performed against monthly goals.

7. Designing Custom Chart Templates in Excel

In this tip, the author demonstrates 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.

8. Building Excel Heat Maps Using Colour Scales

This tip is all about using colour scales and custom number formats to create an incredibly simple - yet highly effective - heat map effect.

9. Analysing Distribution with Histograms

Histograms are 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 analyse how those values are distributed across the sample, as well as by gender and sport.

10. Goal Pacing with Custom Excel Gauge Charts

One of the key principles of analytics is that numbers should always be presented in context. 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.

11. Highlighting Time Periods with Combo Charts

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.

12. Configuring Dynamic Chart Source Ranges

In this tip, you will learn how to combine named ranges with data validation lists to let users dynamically select which series to display in a chart.

13. Creating Interactivity with Excel 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.

1. Introduction

In this section, the author introduces Excel Pivot Table Tips.

2. Customizing the Pivot Table Field List

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.

3. Autofitting Column Width in Pivot Tables

In this tip, the author shares a simple solution to prevent your pivots from automatically adjusting column width.

4. Pivot Table Outline & Tabular Layouts

In this tip, , the author discusses why I absolutely despise Compact Form, and share some great use cases for working with Outline or Tabular layouts instead.

5. Counting Non-Numerical Fields Using Pivots

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.

6. Grouping & Ungrouping Dates with Pivot Tables

In this tip we'll demystify the process of grouping and ungrouping dates, adjust Excel's default behaviour, and explore some alternative approaches.

7. Enabling Multiple Pivot Table Filters

In this tip, the author shows you a simple way to activate multiple filters in your pivots and use both text- and value-based criteria to explore your data.

8. Grouping Values into Buckets with Pivot Tables

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 analyse the frequency of wines that fall into each price range.

9. Adding Value Calculations to Pivot Tables

In this tip, we'll explore several of the most common PivotTable value calculations.

10. Configuring Pivot Table Errors & Empty Items

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

11. Configuring Pivot Table Slicers & Timelines

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.

12. Advanced Pivot Table Conditional Formatting

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 colour scales and data bars.

13. Removing & Reviving Pivot Table Source Data

In this tip, the author introduces you to something called the Pivot Cache and demonstrate how it can be used to remove (and later revive) your underlying source data.

14. Adding Custom Sort Lists in Excel

In this tip, we'll look at Excel's custom sort lists, and practice defining one of our own.

15. Solve Order & List Formula Reports

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.

1. Introduction

This section introduces us to Excel Analytics Tips.

2. Excel Quick Analysis Tools

In this tip, the author shows you how Excel offers intelligent shortcuts and tips based on a selection of data, including conditional formats, visualizations, and calculated fields.

3. Comparing Outcomes Using the Scenario Manager

Excel is a powerful platform for business modelling: 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.

4. Solving Simple Optimizations with Goal Seek

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.

5. Creating Basic Forecasts in Excel

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

6. Building an Outlier Detection Tool 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.

7. Generating Automated Data Tables

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!

8. Connecting & Transforming Data with Power Query

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.

9. Creating Relational Data Models in Excel

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, the author shows you how to use data models and table relationships to connect data without writing a single formula.

10. Accessing the Data Model with CUBE Functions

In this tip, the author shows 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.

11. Building a Monte Carlo Simulation in Excel

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.

12. Advanced Optimization with Solver

In this tip, we'll use Solver to determine the optimal shipping strategy to minimize costs based on several decision variables and real-world constraints.

13. Excel Analysis ToolPak (Preview)

In this tip, we'll preview some basic options like correlation analysis and descriptive statistics to explore a sample of 5,000 Olympic athletes.