In this post, we're covering 19 of those hidden clicks across the ribbon, the grid, the formula bar, the status bar, and pivot tables. Each one is a small move that can save you real time on everyday tasks.

Download the Excel File and PDF guide

Complete the form below to instantly access the Excel file and PDF guide.

Video Tutorial

Watch on YouTube & Subscribe to our Channel

Ribbon Tips

1. Hide the Ribbon

Double-click any ribbon tab to collapse the ribbon and free up vertical screen space. Click the tab once to temporarily peek at it, then click away to hide it again. Double-click to dock it back in place.

2. Ribbon Dialog Buttons

Those tiny arrow icons in the corner of each ribbon group are actually buttons. Clicking them opens dialog boxes like Format Cells, Page Setup, and more. The most useful ones live on the Home tab, but the Page Layout tab has a handy Page Setup launcher for print settings.

3. Add Buttons to the Quick Access Toolbar

If there's a button you use often on a tab you don't visit much, right-click it and choose “Add to Quick Access Toolbar.” It will then appear at the top of the screen regardless of which tab you're on, ready for a single click any time.

Grid and Formula Tips

4. Autofit All Columns at Once

Click the triangle in the top-left corner of the grid to select all cells. Then hover between any two column headers until you see the resize cursor and double-click. Every column on the sheet autofits instantly.

5. Fill Handle Options

Most people know you can left-click and drag the fill handle to copy a value down. But if you right-click and drag instead, Excel automatically opens a menu when you release the button. From there you can choose Copy Cells, Fill Series, Fill Formatting Only, and more.

6. Function ScreenTip Links

While editing a formula, the screentip shows the function signature below the cell. You can click the function name to open its Excel help page. Hover over any argument name to select all the text for that argument, which is much faster than manually selecting it, especially on a laptop trackpad.

7. Move the Function ScreenTip

The screentip can block the row below the formula you're editing. Just hover over the border of the screentip, left-click and hold, then drag it out of the way. This works on Windows, Mac, and the Web version.

8. Function Arguments Window

Click the fx button next to the formula bar to open the Function Arguments window. It lists every argument for the active function. Click inside any argument field and a plain-English description appears at the bottom, which is a great way to understand a function you haven't used before.

9. Expand the Formula Bar

Long or multi-line formulas often look cut off in the default formula bar. Hover over the bottom border of the formula bar until you see a double arrow, then drag it down to reveal the full formula. Use Ctrl+Shift+U to toggle it quickly.

Status Bar Tips

10. Sheet Activate Window

When a workbook has dozens of tabs, scrolling through them is painful. Right-click either of the sheet navigation arrows in the bottom-left corner to open the Activate window, which shows a vertical scrollable list of every sheet. Double-click any sheet name to jump there instantly.

11. Navigation Pane

On Microsoft 365, click the sheet count number next to the navigation arrows to open the Navigation Pane. It lists all sheets plus every table, pivot table, and chart inside each one. Click any object to navigate directly to it.

12. Copy Status Bar Stats

When you select a range of numbers, the status bar shows stats like Sum, Average, and Count. Left-click any of those stats to copy that value straight to your clipboard. It pastes as a hard-coded number, but it's great for quick reconciliations.

13. Enable Stats

If your status bar isn't showing the stats you need, right-click anywhere on it to open the Customize Status Bar menu. Toggle any stat on or off with a single click.

14. Zoom Dialog

Click the zoom percentage in the bottom-right corner to open the Zoom dialog. Choose a preset level and double-click it to apply and close in one move. To jump back to 100% with a keyboard shortcut, press Alt, W, J.

Bonus tip: Double-click any of the items in the window apply them and close the window, so you don't have to click the OK button.

15. Restore Scroll Bar Size

If the horizontal scroll bar has been resized to show more sheet tabs, you can double-click the resize handle to snap it back to its default width. The same trick works on the Name Box if you've accidentally made it wider than you want.

Tables and Pivot Table Tips

16. Apply and Clear Table Formatting

When you apply a table style over existing cell formatting, the results often look messy. Instead of left-clicking the style, right-click it and choose “Apply and Clear Formatting.” This strips any existing manual formatting and applies the table style cleanly.

17. Select a Table Column

When writing formulas that reference an Excel Table, hover over the top half of a column header until the cursor turns into a down arrow, then click. This inserts a structured reference to that entire table column automatically.

Here's an example using the FILTER function with a table column reference:

=FILTER(Table1[Name],Table1[Coffee Cups]=0)

18. Expand and Collapse Pivot Fields

Pivot tables show small expand and collapse buttons next to grouped row items. Those tiny buttons can be hard to click, especially when zoomed out. Just double-click anywhere on the row label itself to expand or collapse it.

19. Pivot Table Classic Layout

Right-click a pivot table, go to PivotTable Options, click the Display tab, and check “Classic PivotTable layout.” This switches to the older-style layout where you can drag fields directly onto the grid, making it faster to build and rearrange the report on the fly.

Summary

Excel's best-kept secrets are often just one unexpected click away. From collapsing the ribbon to free up screen space, to right-clicking the fill handle for smarter fill options, to double-clicking pivot row labels to expand groups without fumbling for tiny buttons, each of these 19 features takes seconds to learn and pays off every time you use it. Download the free PDF guide linked below to keep this list handy, and let us know in the comments which one was new to you.

Link to post: 19 Hidden Excel Features You Didn’t Know You Could Click

In this post, I'm walking through 7 hidden buttons and controls you can add to your spreadsheets right now to make them faster to navigate, easier to update, and a lot more user-friendly. We'll go from simple checkboxes all the way to custom data entry forms.

Download the Excel Files

Complete the form below to instantly access the Excel files.

7 Hidden Buttons.xlsxDownload

7 Hidden Buttons – Including Userform.xlsmDownload

The xlsm version of the file contains macros and the userform. Macro enabled files have an additional setting you must change when downloaded from the internet.

After downloading the file, you will need to Unblock it.

Right-click the file in File Explorer > choose Properties > then check the Unblock checkbox > Press OK > Open the file.

Video Tutorial

Watch on YouTube & Subscribe to our Channel

Button 1: Checkboxes for True/False Data

Typing “Yes” or “No” down an entire column is tedious and inconsistent. Checkboxes solve this instantly. Select the cells in your column, go to the Insert tab, and click Checkbox. Excel fills each cell with a checkbox that stores TRUE when checked and FALSE when unchecked.

Because the underlying value is a boolean, you can use these cells directly in formulas. One quick pro tip: select multiple checkbox cells and press Spacebar to check or uncheck them all at once.

Checkboxes are available in Microsoft 365 (desktop and web). If you're on an older version, the next technique is a great alternative for controlled data entry.

Button 2: Data Validation Dropdown Lists

Freehand typing in a Category column is a recipe for mismatched data. One person types “Meals & Entertainment,” another types “Meals and Entertainment,” and your SUMIFS formula misses half the records. Dropdown lists lock the input to an approved set of values.

Select the cells in your Category column, go to the Data tab, and click Data Validation. Under Allow, choose List, then point the Source to your category list on a separate sheet. Any new rows added to the table automatically inherit the dropdown.

Button 3: Slicers for One-Click Filtering

The standard table filter dropdown requires multiple clicks: open the menu, uncheck Select All, pick your item, click OK. A Slicer turns that into a single button click. Your data must be in an Excel Table first (Insert, Table), then go to Insert and click Slicer. Choose the field you want to filter by and click OK.

The Slicer appears as a panel of clickable buttons on the sheet. Click a category to filter, click the clear button to show everything again. Slicers also connect to Pivot Tables and Pivot Charts, making them a go-to tool for interactive dashboards.

Button 4: Navigation Shapes for Multi-Tab Workbooks

Scrolling through 9 sheet tabs to find the right report frustrates users. You can turn any shape into a navigation button. Go to Insert, Illustrations, Shapes, and draw a rounded rectangle at the top of your sheet. Type a label, then right-click the shape and choose Link.

In the link dialog, go to Place in this Document and select the target sheet. Click OK. Now clicking the shape jumps directly to that sheet. Add one button per report tab, and your workbook suddenly feels like a proper app.

Button 5: Macro Buttons and VBA UserForms

Shapes can also run macros. Right-click any shape, choose Assign Macro, select your macro from the list, and click OK. Now the shape is a clickable trigger for any automated process in your workbook.

In this example, the button opens a custom VBA UserForm for structured expense entry. The form includes dropdown lists, a date field, an amount field, and a checkbox for reimbursable status. Clicking Add New Row writes the entry directly into the table. These forms are fully customizable inside the Visual Basic Editor (Developer tab, Visual Basic button).

AI Coding for Excel Course

I explain more about creating macros and userforms in my AI Coding for Excel Course.

This course is designed to help you build real automation inside Excel using AI tools like Copilot, Claude, and ChatGPT. You'll unlock AI's superpower, writing code, to save HOURS with repetitive Excel and Office tasks.

The course covers three powerful coding languages: VBA, Office Scripts, and Python in Excel.

Button 6: Column and Row Grouping

Hiding and unhiding columns through the right-click menu gets old fast. Grouping is a better approach. Select the columns you want to collapse, go to Data, and click Group. Excel adds a small minus button above the grouped columns. Click it to collapse the group, click the plus to expand it.

For structured financial reports with monthly detail and subtotals, use Auto Outline instead. Go to Data, Group dropdown, and choose Auto Outline. Excel analyzes the layout and creates groupings for both rows and columns automatically. The numbered buttons in the top-left corner let you expand or collapse all levels at once.

Button 7: Spin Buttons for Scenario Analysis

When someone needs to test different values repeatedly, like a reimbursement cap, making them retype a number every time slows things down. A Spin Button fixes this. You'll need the Developer tab first. If it's not visible, right-click anywhere on the ribbon, choose Customize the Ribbon, scroll down on the right side, check Developer, and click OK.

On the Developer tab, click Insert and choose the Spin Button control. Draw it next to your input cell. Right-click the button and select Format Control. Set your current value, min, max, and incremental change (10 in this example), then link it to your target cell. Now the up and down arrows increment the value without any typing.

Summary

Each of these seven buttons can make your spreadsheets more fun to use and easier navigate. Plus, they help prevent data entry errors and save time with repetitive tasks.

You don't necessarily need to add all seven to every single workbook. Start with the one that matches your biggest pain point today, and you'll immediately feel the difference.

Link to post: 7 Hidden Buttons That Make Excel Easier to Use

Complete the form below to instantly access the Excel files.

Video Tutorial

Watch on YouTube & Subscribe to our Channel

In this tutorial, we take the dashboard design we created with AI tools in the previous video and build it out completely in Excel, step by step.

We start with the raw data and walk through creating every Pivot Table that powers the dashboard, from the KPI cards to the charts. From there, we build out each chart, including a top 5 items chart, a sales-over-time chart with a week/month toggle, and a customer mix chart. We also connect a slicer so one click filters everything on the dashboard at once.

The best part is that none of this requires complex formulas or VBA. Every piece of the dashboard is powered by Pivot Tables, so it stays fully dynamic and updates with the click of a button as new data comes in.

If you haven't seen the first video where we designed this dashboard with AI, check that out after watching this one, to see how the look and layout came together.

Link to post: How to Build an Interactive Excel Dashboard with Pivot Tables

In this post, we'll do a full line chart makeover using Pivot Tables, a slicer, and three modern Excel array functions: TRIMRANGE, DROP, and HSTACK.

The result is an interactive chart where one click highlights any single trend against the rest of the group, and the whole thing expands automatically when new data arrives.

Download the Excel Files

Complete the form below to instantly access the Excel files and Excel Formula Prompting Guide.

Video Tutorial

Watch on YouTube & Subscribe to our Channel

The Setup: A Pivot Table for Chart Data

The scenario here is an e-bike rental shop that tracks a weekly health score for each bike in their fleet. The maintenance team wants to see how those scores trend over time and quickly spot which bike might need attention.

We start by building a Pivot Table with Week in the Rows area, Bike ID in the Columns area, and Health Score as the Values. This gives us one health score per bike per week in a clean grid, which is exactly what a line chart needs.

Before building the chart, remove the Grand Totals from both rows and columns. Right-click anywhere in the Pivot Table, go to PivotTable Options, and turn them off. Clean data in means a clean chart out.

Next, rename this sheet “All Chart Data.” Then duplicate it by holding Ctrl and dragging the tab to the right. Rename the copy “Selected Data.” The slicer will connect only to the Selected Data pivot table, filtering it down to one bike at a time while the All Chart Data sheet always shows the full fleet.

Using TRIMRANGE to Pull the Pivot Table into a Spill Range

Create a new sheet called “Chart” (or “Chart Data”). This is where we'll build the combined data source for our chart. We want to pull the live Pivot Table data from both sheets into this one range, and we'll start with TRIMRANGE.

Click in cell A1 of the Chart sheet and enter a TRIMRANGE formula that references every cell on the All Chart Data sheet. Clicking the top-left corner of a sheet tab creates a reference to all rows on that sheet.

=TRIMRANGE('All Chart Data'!1:1048576)

Once entered, TRIMRANGE spills the full Pivot Table data onto the Chart sheet. You'll notice it looks right at first glance, but there's a problem at the top: a header row filled with zeros has come along for the ride. That happens because the Pivot Table column headers are numeric Bike IDs, and Excel treats them as zeros in the spill output. We'll clean that up next.

Cleaning the Data with DROP

The spill range includes an unwanted header row of zeros at the top. The DROP function removes it cleanly without any manual adjustments.

Wrap the TRIMRANGE formula inside DROP and tell it to remove the first row. This strips the zero-filled header and leaves only the real Pivot Table data.

=DROP(TRIMRANGE('All Chart Data'!1:1048576),1)

Combining Both Pivot Tables with HSTACK

Now we need to add the Selected Data Pivot Table alongside the All Chart Data. HSTACK joins two arrays side by side in a single spill range.

Edit the formula in cell A1 and wrap the existing DROP+TRIMRANGE expression inside HSTACK as the first array. For the second array, apply the same DROP+TRIMRANGE pattern to the Selected Data sheet.

=HSTACK(DROP(TRIMRANGE('All Chart Data'!1:1048576),1),DROP(TRIMRANGE('Selected Data'!1:1048576),1))

Removing the Duplicate Week Column

The Selected Data Pivot Table includes its own Week labels column, which creates a redundant column in the middle of the combined range. The DROP function accepts a columns argument to cut it out.

Update the second DROP call to also drop 1 column from the left. This removes the Week labels from the Selected Data array before HSTACK joins it to the right.

=HSTACK(DROP(TRIMRANGE('All Chart Data'!1:1048576),1),DROP(TRIMRANGE('Selected Data'!1:1048576),1,1))

Building the Chart on the Spill Range

Cut the slicer from the Selected Data sheet and paste it onto the Chart sheet. When the slicer filters the Selected Data Pivot Table, only the last column in the spill range changes. Everything else stays the same.

Select the entire spill range, go to Insert, and insert a regular 2D Line chart (not a Pivot Chart). Once inserted, click Switch Row/Column under Chart Design so the weeks appear along the bottom axis.

Styling the Chart for Clarity

Select each background line (all bikes except the highlighted one) and change the Shape Outline to a light gray. If a line is hidden behind others, use the dropdown in the Format tab to select it by name.

For the selected bike line, change the outline to a bright color like green, increase the line weight, and add circular markers through Format Data Series. Setting the marker fill to white gives it a bike-chain look that works nicely for this dataset. Remove the legend if you prefer a cleaner look.

The Chart Expands Automatically with New Data

Because the chart source is a spill range, it updates whenever the spill range changes. To add new weeks, paste new rows into the source data table. The Pivot Tables extend automatically since the source is an Excel Table.

Right-click either Pivot Table and choose Refresh. Both pivot tables update at once. The TRIMRANGE formula picks up the new rows, DROP and HSTACK rebuild the combined range, and the chart adds the new week with zero manual intervention.

Taking It Further: A Dark Mode Version

The same chart looks completely different with a dark background, and it is easier to achieve than it sounds. Change the chart area and plot area fill to a dark color, then update all font colors to white or light gray so labels and axis values remain readable. The gray background lines stay subtle, and the bright green selected line pops even more against a dark canvas.

The slicer gets the same treatment. Right-click the slicer, open Slicer Styles, and duplicate an existing style. From there you can control the background, font color, and border for every element, including selected and unselected items. Turning off the slicer header in Slicer Settings also removes the clear and multi-select buttons, keeping the UI focused on single-bike selection. The end result is a dashboard-quality chart that fits right into a dark-themed report.

Summary

This technique turns a cluttered multi-line chart into a focused, interactive visualization by combining two Pivot Tables into one dynamic source range.

TRIMRANGE captures only the used data on each pivot sheet. DROP strips unwanted header rows and the duplicate week column. HSTACK joins both arrays side by side so the selected bike always appears as an extra series the chart can highlight independently.

Connect a slicer to the Selected Data pivot table, style the background lines gray and the selected line a bold color, and you have a chart that practically reads itself. Add new data to the source table, refresh, and the chart grows with it automatically.

And if you want to take the presentation up a notch, the same chart translates beautifully into a dark mode layout with a custom slicer style.

Link to post: Excel Line Chart Makeover: From Ugly to Awesome

In this post, I'll show you a three-step AI workflow to rapidly prototype a dashboard design using Claude, Gemini, and ChatGPT, so you can iterate in minutes instead of days and get buy-in from your team before you build a single PivotTable.

Download the Excel Files

Complete the form below to instantly access the Excel files and Excel Formula Prompting Guide.

Video Tutorial

Watch on YouTube & Subscribe to our Channel

Step 1: Generate a Fake Dataset with ChatGPT

Before you can design anything, you need data to work with. But uploading real company data to an AI tool is often a non-starter due to privacy policies. The solution is to generate a fake dataset that closely mimics your real data.

Jump into ChatGPT (or any large language model) and describe your data without sharing anything sensitive. Be specific about the columns, the volume of rows, any seasonality or trends, and any specific dimension values like employee names or product categories. The more context you give, the more realistic the output.

One prompt tip that works well: end every prompt with “What questions do you have about this project before you get started?” This stops the model from making silent assumptions and gives you a chance to clarify upfront.

Once ChatGPT generates the CSV file, download it and do a quick spot check in Excel. Scroll to the bottom to confirm the row count, then turn on filters to verify that dimension columns like Employee or Product have the right number of unique values.

Step 2: Design the Dashboard with AI

This is the core of the workflow. Instead of jumping straight into Excel, use an AI tool to build an interactive web page mockup of your dashboard first. This lets you iterate on the design instantly with plain English, no rebuilding charts, no reformatting cells.

The key is a well-structured prompt. Describe who the audience is, what charts you want to include, how simple or complex the layout should be, and that the final output will eventually live in Excel. Then ask the AI to output a web page so you can see and interact with the design right away.

Designing with Claude

Claude is a great starting point. Attach your fake CSV file, paste in your dashboard prompt, and Claude will read the data, plan the layout, and write the full HTML web page in just a few minutes. The result renders directly in the Claude interface.

If something doesn't look right, just say so in plain English. For example, you might ask Claude to use a single color in the team member bar chart, or add a chart that analyzes weather conditions. Claude will rewrite just the relevant parts of the HTML and update the preview instantly.

Designing with Gemini

Gemini from Google has a Canvas feature that works similarly. Enable Canvas under the Tools menu, attach your CSV, paste the same prompt, and Gemini will generate an interactive web page dashboard with a code view and a live preview side by side.

Designing with ChatGPT

ChatGPT also has a Canvas feature, available under the More submenu in the prompt box. Attach your file, enable Canvas, and paste your prompt. In testing, ChatGPT occasionally returns a console error on the first try, but clicking the error message and using the Fix Bug button resolves it quickly.

Compare the Designs Side by Side

One of the best parts of this approach is that every run produces a slightly different result. That is actually a feature, not a bug. Run the prompt a few times across different tools and you end up with several distinct design options to choose from or mix and match.

Paste screenshots of each mockup into a PowerPoint slide deck and share it with your manager or team before building anything in Excel. Getting alignment on the design early saves a huge amount of rework later.

Step 3: Build the Dashboard in Excel

Once you have a design direction approved, it is time to build it in Excel. You can do this manually using PivotTables and PivotCharts, use AI to generate a starting point, or do a combination of both.

Microsoft Copilot can generate a working Excel dashboard directly from your data. It creates PivotTables on a source sheet and connects them to charts and KPI cards on a dashboard sheet. The result may need some visual cleanup, but it is a solid foundation to build from.

Whether you use Copilot or build it yourself, PivotTables and PivotCharts are the right foundation for an Excel dashboard. They make it easy to update as new data comes in and keep your formulas simple.

Summary

The hardest part of building a dashboard is not the formulas or the charts. It is the design decisions.

This three-step AI workflow helps takes that friction away.

It also allows you to share the designs with your boss, coworkers, or clients to gather feedback and quickly iterate.

Let me know which design is your favorite in the comments, and I'll do a follow-up tutorial on how to build it in Excel.

Link to post: How to Use AI to Design Your Excel Dashboard (Claude, Gemini, and ChatGPT)

In this post we cover seven practical scenarios where XLOOKUP either simplifies your formula or does something VLOOKUP simply cannot. We also look at how Copilot can help you convert existing VLOOKUP formulas in seconds.

Download the Excel Files

Complete the form below to instantly access the Excel files and Excel Formula Prompting Guide.

Video Tutorial

Watch on YouTube & Subscribe to our Channel

1. Exact Match Lookup

The most common lookup scenario is finding an exact match. Let's look up a Product ID and return its price from a product table.

With VLOOKUP, you select the entire table range, then specify a column index number to tell Excel which column to return.

=VLOOKUP(C4,$F$5:$I$21,4,FALSE)

Now let's write the same lookup with XLOOKUP. Instead of selecting the whole table, you pick the lookup column and the return column separately. XLOOKUP also defaults to exact match, so there is no need to add FALSE as a fourth argument.

=XLOOKUP(C4,$F$5:$F$21,$I$5:$I$21)

Why Separate Ranges Are Better

Because XLOOKUP references the lookup column and the return column independently, inserting a column between them does not break the formula. VLOOKUP, on the other hand, relies on a hardcoded column index number. Insert a column and that number is suddenly wrong.

Watch Out: Range Lengths Must Match

One thing to be aware of with XLOOKUP is that the lookup_array and return_array must be exactly the same length. If they are not, you will get a #VALUE! error. The easiest way to avoid this is to use Excel Tables, which automatically keep both column references the same height.

2. Error Handling

When VLOOKUP cannot find the lookup value, it returns a #N/A error. The standard fix is to wrap the formula in IFERROR.

=IFERROR(VLOOKUP(C4,$F$5:$I$21,4,FALSE),"Not Found")

XLOOKUP handles this more cleanly. The fourth argument, if_not_found, lets you specify what to display when the lookup value is not found, with no wrapper function needed.

=XLOOKUP(C4,$F$5:$F$21,$I$5:$I$21,"Not Found")

One important distinction: XLOOKUP's if_not_found only triggers when the lookup value is not found. If a different error is causing the problem (like a mismatched range length), XLOOKUP will still return that error. That is actually a good thing. It means you are not accidentally hiding real formula problems.

3. Looking Left

VLOOKUP can only return values to the right of the lookup column. If you need to return a value from a column to the left, you either need a CHOOSE hack or an INDEX/MATCH formula. XLOOKUP has no such restriction.

In this example the Product ID is in column G and we want to return the Category from column F, which is to the left. With XLOOKUP, we simply set the return_array to the Category column.

=XLOOKUP(C4,$G$5:$G$21,$F$5:$F$21)

4. Horizontal Lookups

XLOOKUP replaces HLOOKUP for horizontal lookups too. Just set the lookup_array to a header row and the return_array to the data row you want to pull from. The formula structure is identical to a vertical lookup.

=XLOOKUP(C4,$F$4:$K$4,$F$7:$K$7)

5. 2-Way Matrix Lookup with Nested XLOOKUP

To look up both a row and a column at the same time, nest two XLOOKUP formulas. The inner XLOOKUP finds the correct row of data, and the outer XLOOKUP finds the correct column within that row.

=XLOOKUP(C4,$F$12:$K$12,XLOOKUP(C5,$E$13:$E$16,$F$13:$K$16))

The inner XLOOKUP returns the entire row for the matched region. The outer XLOOKUP then finds the right column within that row. It is a clean replacement for INDEX/MATCH in a matrix lookup scenario.

6. Find the Last Match

By default, XLOOKUP searches from top to bottom and returns the first match. Setting the search_mode argument to -1 reverses the search direction, returning the last match instead.

This is useful when you have a transaction log and need the most recent entry for a customer. You can also return multiple columns in one formula by specifying a multi-column range as the return_array.

=XLOOKUP(C4,Invoices[Customer],Invoices[[Date]:[Status]],,,−1)

7. Closest Match Lookup

XLOOKUP can also do approximate match lookups, which is useful for tiered rate tables like commission schedules. Setting match_mode to -1 tells XLOOKUP to return the exact match or the next smaller value.

One big advantage over VLOOKUP here: XLOOKUP does not require the lookup table to be sorted. VLOOKUP's approximate match mode breaks if the data is out of order. XLOOKUP handles it correctly either way.

=XLOOKUP(C4,$I$7:$I$11,$K$7:$K$11,,−1)

8. Use Copilot to Convert VLOOKUP to XLOOKUP

If you have a workbook full of VLOOKUP formulas, you do not have to update them one by one. Copilot in Excel can handle the conversion for you. Open the Copilot pane and describe what you want.

A prompt like this works well: “Please change the formulas on this sheet that use VLOOKUP to XLOOKUP and use the if_not_found argument within XLOOKUP instead of the IFERROR function that is wrapped around VLOOKUP.” Copilot will show you the original and updated formulas before applying the changes.

When Not to Use XLOOKUP

XLOOKUP is the right choice in almost every situation, but there is one important exception: when the people using your file are on an older version of Excel. XLOOKUP requires Excel 2021 or Microsoft 365.

Microsoft did add limited backwards compatibility for Excel 2016 and 2019, but it is view-only. Users on those versions can see the results of an XLOOKUP formula, but they cannot edit existing ones or create new ones.

If your file is shared with colleagues or clients on older Excel versions, stick with VLOOKUP or INDEX/MATCH to keep the formulas fully editable for everyone.

Summary

XLOOKUP is a genuine upgrade from VLOOKUP in almost every scenario. It is more readable, more resilient to structural changes in your data, and it replaces HLOOKUP, IFERROR-wrapped VLOOKUP, and even INDEX/MATCH in most cases.

The three required arguments are all you need for everyday lookups, and the optional arguments unlock powerful capabilities like reverse search, closest match, and multi-column returns. If you are on Excel 2021 or Microsoft 365, there is no reason to keep writing VLOOKUP.

Link to post: XLOOKUP: Everything You Need to Know to Upgrade from VLOOKUP

In this tutorial, we'll cover three approaches: a formula-based solution using UNIQUE and SUMIFS, the newer GROUPBY function, and Pivot Tables. At the end, we'll compare them so you can pick the right tool for your situation.

Download the Excel Files

Complete the form below to instantly access the Excel files and Excel Formula Prompting Guide.

Video Tutorial

Watch on YouTube & Subscribe to our Channel

Start by Converting Your Data to an Excel Table

Before building any summary report, convert your data range into an Excel Table. Click any cell in your data, go to the Home tab, click Format as Table, and choose a style.

Tables give you automatic banded rows, built-in filters, and structured references that make your formulas more readable. More importantly, when you add new rows to the bottom of a table, Excel automatically extends the range. Your summary reports stay accurate without any manual adjustments.

If you are not familiar with Excel Tables yet, check out the linked video in the description. It covers everything you need to know before moving forward.

Formula Method 1: UNIQUE and SUMIFS

The first formula-based approach uses two functions together: UNIQUE to extract a distinct list of categories, and SUMIFS to calculate totals for each one.

Step 1: Get a Unique List with UNIQUE

Click an empty cell and type =UNIQUE(. The only required argument is the range you want to deduplicate. Select the Category column in your table and press Enter.

Excel returns a spill range, a dynamic list of all unique category values. If a new category appears in your source data, this list updates automatically.

Step 2: Calculate Totals with SUMIFS

In the cell next to your UNIQUE results, type =SUMIFS(. For the sum range, select the Revenue column. For the criteria range, select the Category column. For the criteria, reference the first cell of your UNIQUE spill range.

Instead of typing the cell address, select all the cells in the spill range and Excel will write H2# for you. The hash symbol tells Excel to reference the entire spill range, not just a single cell. One formula calculates totals for every category automatically.

One important note: the UNIQUE function requires Excel 2021 or later. If you or your team are on an older version, skip ahead to the Pivot Table section, which works on all versions.

Formula Method 2: The GROUPBY Function

If you are on Microsoft 365, the GROUPBY function creates the entire summary report in a single formula. No need to combine UNIQUE and SUMIFS separately.

Type =GROUPBY( and fill in three arguments: the row fields (your Category column), the values (your Revenue column), and the function, which is SUM. Press Enter.

GROUPBY returns a complete summary table with unique categories, summed revenue for each, and a grand total row at the bottom. There is also a related PIVOTBY function that adds a column fields argument, letting you break data out across multiple columns.

Build a Summary Report with a Pivot Table

Pivot Tables have been in Excel since 1994 and work on every version. They require no formulas at all. You build them with drag and drop in seconds.

How to Create a Pivot Table

1. Click any cell inside your table.
2. Go to the Insert tab and click PivotTable.
3. Choose where to place the pivot table, then click OK.
4. In the PivotTable Fields pane, drag Category to the Rows area.
5. Drag Revenue to the Values area.

Excel instantly builds a summary with unique categories, summed revenue, and a grand total row. No formulas required.

Make It Interactive with Charts and Slicers

Go to the Insert tab and click PivotChart to create a chart connected directly to your pivot table. Any changes to the pivot table automatically update the chart.

Add Slicers to make your report interactive. A slicer is a visual filter. Click a slicer button to filter both the pivot table and the chart at the same time. This is how you turn a simple summary report into a full dashboard.

Formulas vs. Pivot Tables: Which Should You Use?

There is no single right answer. The best method depends on your situation. Here are the two factors that matter most.

Formatting

Formula-based results return unformatted numbers. You have to manually apply number formatting, bold the totals row, and add any color coding you want. If the data changes and rows shift, your manual formatting may end up in the wrong place.

Pivot Tables apply formatting automatically as you build them. The Design tab gives you one-click style options, and the formatting stays locked to the right rows as the data changes.

Handling New Data

When you add rows to your Excel Table, formula-based reports update automatically. New categories appear and totals recalculate without you doing anything.

Pivot Tables require a manual refresh. Right-click inside the pivot table and choose Refresh, or use the keyboard shortcut Alt+F5. If you forget this step, your report will show stale data, which can cause problems when sharing with others.

Microsoft announced an auto-refresh feature for Pivot Tables, but it was pulled from beta while they worked out some issues. In the meantime, you can set up automatic refresh with a macro if this is a recurring problem.

When to Use Each Approach

• Use UNIQUE and SUMIFS or GROUPBY when your data changes frequently or when multiple users update the source file and you need reports that always reflect the latest data automatically.
• Use Pivot Tables when data updates are infrequent (weekly or monthly), when you want to save time on formatting, or when you need interactive dashboards with charts and slicers.
• Use Pivot Tables if your team is on older versions of Excel, since UNIQUE and GROUPBY require Excel 2021 or Microsoft 365.

Which technique will you be using? Let us know in the comments below. And if you want to see more ways to automate Excel reports, check out the related videos linked in the description.

Link to post: How to Create Summary Reports in Excel: Formulas vs. Pivot Tables

Download the Excel Files

Complete the form below to instantly access the Excel files and Excel Formula Prompting Guide.

Video Tutorial

Watch on YouTube & Subscribe to our Channel

Join the AI Literacy for Excel Course

What is the LET function?

The LET function lets you create named variables inside a formula. These variables store intermediate results. This reduces repeated calculations and can greatly improve performance in large workbooks. The use of AI and LET function in Excel often shows up when a lookup or calculation is repeated several times in a single formula.

Key points about LET

• LET creates a name for a value inside the formula.
• It helps avoid repeating expensive calculations like XLOOKUP.
• It improves readability if used with clear variable names.

Why AI recommends the LET function

AI models prioritize efficiency. When an AI sees a formula that repeats the same calculation, it suggests LET to avoid duplicated work. For example, if a formula calls XLOOKUP twice, an AI will often rewrite the formula to run XLOOKUP once and store the result in a LET variable.

This is why AI and LET function in Excel often appear together. The AI is optimizing for performance and redundancy. That optimization makes sense technically. It does not always make sense for people who need to read or maintain the workbook.

Step-by-step example: From XLOOKUP to LET

This example uses a sales table and a products table. The goal is to return a product weight and show the word bulk if the weight is over 40.

1. Build the basic XLOOKUP

Start with a simple lookup to return the weight.

1. Write XLOOKUP to find Product ID in the products table.
2. Return the weight column from that table.
3. Confirm the value displays correctly. =XLOOKUP([@ProductID], Products[ProductID], Products[Weight])

This formula returns the weight. It is simple and easy to understand.

2. Add the IF test

Next, wrap the XLOOKUP in an IF to show the word bulk when weight > 40.

1. Use IF with a logical test that compares the XLOOKUP result to 40.
2. If true, return “bulk”. If false, return the weight. =IF(XLOOKUP([@ProductID], Products[ProductID], Products[Weight])>40, “bulk”, XLOOKUP([@ProductID], Products[ProductID], Products[Weight]))

This works. But the lookup runs twice. That is slow on large datasets. This is where LET helps.

3. Optimize with LET

LET stores the XLOOKUP result in a variable. The formula then reuses that variable in the IF test and the result.

=LET(wt, XLOOKUP([@ProductID], Products[ProductID], Products[Weight]), IF(wt>40, "bulk", wt))

Now the XLOOKUP runs once. The value is stored in wt. The IF uses wt for both the check and the return. That reduces calculation time and avoids duplicated logic.

That short example shows the two main benefits of combining AI and LET function in Excel:

• Performance: fewer repeated calculations.
• Clarity: intermediate results can be named for readability.

When to use LET and when to avoid it

LET is great for performance and for formulas with repeated expressions. But it's not always the best choice for shared workbooks. Decide based on the audience and the file purpose.

Use LET when

• Formulas repeat expensive calculations, like multiple lookups.
• The workbook handles large datasets and performance matters.
• Advanced users will maintain or update the workbook.

Avoid LET when

• You will share the workbook with novice Excel users.
• Readability for less technical users is more important than micro-optimizations.
• Change tracking or auditing is done by people unfamiliar with LET.

Remember that AI and LET function in Excel are tools. The right tool depends on the context. If colleagues will ask how the formula works, a simpler approach may save time in the long run.

Alternatives to LET: Prompting AI and using helper columns

If you want AI to avoid LET, tell it in your prompt. For example:

• Request no LET or LAMBDA in generated formulas.
• Ask for helper columns for complex calculations.
• Specify your Excel version and the skill level of your users.

AI will then provide formulas that may repeat a lookup. That is acceptable in most cases. If the dataset is huge and performance slows, use helper columns instead of LET.

Helper columns explained

Helper columns store intermediate results in cells. They make formulas easy to follow. They also avoid LET if your users do not know it.

1. Place the XLOOKUP result in a helper column named Weight.
2. Use a second column with an IF test to return “bulk” or the weight.
3. Hide the helper column if you want a cleaner sheet.

Helper columns are essentially the spreadsheet equivalent of LET. The difference is the value is stored in a cell, not a variable inside the formula. For many teams, helper columns are easier to explain and maintain.

Practical tips and best practices

Here are clear, practical rules for using the LET function and for working with AI-generated formulas.

1. Know your audience.
   • Choose LET for performance and experienced users.
   • Choose helper columns for novice users and easier debugging.
2. Keep variable names meaningful.
   • Use short but clear names like wt for weight.
   • Avoid cryptic names if others will read the file.
3. Use line breaks to improve readability.
   • Press Alt+Enter inside the formula bar to add lines.
   • Line breaks do not change how the formula calculates.
4. Hide helper columns when you use them.
   • Group the helper columns (Data > Group) to collapse them.
   • That keeps your sheet tidy while retaining the benefits.
5. Prompt AI carefully.
   • Include instructions like “Do not use LET or LAMBDA.”
   • Ask for helper column recommendations if needed.

These tips help you balance the strengths of AI and LET function in Excel with real-world maintainability.

Checkout my articles on the LET and LAMBDA functions to learn more about these powerful Excel features.

Final Thoughts

AI and LET function in Excel together solve repeated-calculation problems elegantly. They improve performance and reduce duplication. But they can also make formulas look intimidating. The right choice depends on your team, your data size, and how much you value performance versus immediate readability.

When in doubt, follow this rule:

• Use LET when performance matters and users understand it.
• Use helper columns and clear prompts to AI when you need simplicity.

The combination of AI and LET function in Excel is one of many ways to write better formulas. Use the method that reduces errors, saves time, and keeps your coworkers happy.

Join the AI Literacy for Excel Course

If you're feeling a bit overwhelmed or behind with all the changes in AI and Excel, then our AI Literacy for Excel Course will help get you up to speed quickly.

In the course you will learn how to use AI to save tons of time with your everyday Excel tasks.

And don't worry, you don't have to share sensitive data to benefit from AI. In the program I explain how to use AI to help with spreadsheet design, process automation, formula writing, and much more.

Click here to learn more and join the program

Link to post: Why AI Writes Crazy LET Formulas in Excel and How to Fix

I picked five functions that together replace a large number of smaller, niche functions. These choices focus on common tasks: lookups, logical tests, statistics, reporting, formatting, and data cleanup.

Video Tutorial

Watch on YouTube & Subscribe to our Channel

Download the Excel File

How I chose the 5 Excel Functions

My goal was simple. Pick functions that handle everyday data-analysis work. Each function should be versatile and combine multiple tasks into one formula.

I grouped needs into five categories. Each category gets a single function that handles most scenarios. That makes it easier to learn and to build reliable spreadsheets.

1. FILTER — The lookup plus logical swiss army knife

FILTER is the single function I use for lookups and many logical tests. It returns matching rows or values from an array based on criteria you set.

Why FILTER makes the cut

• Returns all matching results instead of just the first match.
• Handles multiple criteria using simple expressions and arithmetic for AND logic.
• Works as a dynamic range, so results auto-spill and update automatically.
• Can be easier to read and understand for some users, compared to XLOOKUP or VLOOKUP.
• The formula is short and only requires two arguments (return array, filter criteria).

=FILTER(I5:J20,G5:G20=B10)

Common uses

Here are typical scenarios where FILTER replaces other functions.

1. Lookup multiple rows for a customer and return several columns. Use FILTER instead of XLOOKUP when duplicates matter.
2. Implement tiered logic. For example, find which bonus tier a sales amount falls into by testing min and max columns.
3. Combine with other functions to create dynamic reports and extracts.

FILTER for Logical Tests

Filter can also be used in place of logical functions like IF or IFS. It can even handle AND and OR logic for multiple conditions.

The filter criteria below tests if the sales amount is greater than or equal to values in the minimum range and less than or equal to values in the maximum range, and returns the bonus amount.

(SalesAmount>=MinRange)*(SalesAmount<=MaxRange)

FILTER is a very versatile function that should be in every Excel user's tool belt.

2. AGGREGATE — One function to handle most statistical needs

AGGREGATE is the statistical workhorse I chose. It handles sum, average, count, and many other calculations, and it includes options to ignore hidden rows and errors.

Why AGGREGATE belongs in the 5 Excel Functions

• Supports many operations via a function number parameter.
• Can ignore hidden rows, errors, or nested subtotal/aggregate results.
• Works well inside tables and filtered views.

How AGGREGATE works

AGGREGATE takes a function number, an option number for what to ignore, and the array or range. The option lets you choose whether to ignore hidden rows, errors, or nested functions.

When to prefer AGGREGATE

• When you need calculations that respect filters and hidden rows.
• When your worksheet contains subtotal rows and you need to avoid double counting.
• When you want one formula to replace a set of SUM, AVERAGE, COUNT, SMALL, LARGE, etc.

Limitations

AGGREGATE can feel heavy when you only need a simple SUM or COUNT.

3. PIVOTBY — Build dynamic summary reports with a formula

PIVOTBY creates pivot-like summaries inside the grid. It builds cross-tab reports with a single function. It returns unique row and column headers and performs aggregation for each intersection.

Why PIVOTBY is part of the 5 Excel Functions

• Generates a pivot-style table with formulas instead of the PivotTable tool.
• Combines the work of UNIQUE, SUMIFS, and manual layout into one function call.
• Supports sorting and total rows with minimal setup.

Practical benefits

• Quickly prototype a report without creating a PivotTable object.
• Use in dashboards where formulas are preferable to PivotTables.
• Works well with dynamic data that changes shape often.

When to still use a PivotTable

PivotTables are powerful and user-friendly. They are still the best choice for complex, interactive reporting. Use PIVOTBY when you want formula-based outputs or programmatic control inside the worksheet.

4. TEXT — Formatting, grouping, and linked labels

TEXT converts numbers and dates into custom text formats. It is surprisingly flexible for grouping by month, producing labels, and creating formatted strings on dashboards.

Why TEXT makes the shortlist of 5 Excel Functions

• Extract month numbers, month names, weekdays, or years using number formats.
• Format numbers to show millions, thousands, currency, or other custom views.
• Produce readable labels for charts and text boxes that are linked to cell values.

Examples

1. Return month number: =TEXT(A2,”M”)
2. Return month name: =TEXT(A2,”mmmm”)
3. Format millions: =TEXT(Sales,”#,##0,,””M”””)

You can link a text box to a formatted cell so the visual label updates automatically. That is handy for scorecards and dashboards.

Tips

• Keep formatting codes in a reference row so you can reuse them quickly.
• Remember TEXT returns text, so use VALUE() or multiply by 1 if you need to convert back to a number.

5. REGEXEXTRACT — Clean and parse text with patterns

REGEXEXTRACT pulls parts of text using regular expressions. It replaces many text functions by matching patterns rather than relying on position.

Why REGEXEXTRACT is one of the 5 Excel Functions

• Extracts first or last names, company names, zip codes, and more.
• Can trim trailing spaces and find numbers inside long text strings.
• Combines the roles of TEXTBEFORE, TEXTAFTER, TEXTSPLIT, and TRIM in many cases.

How to use REGEXEXTRACT

1. Type =REGEXEXTRACT(
2. Select the text cell you want to parse.
3. Provide the regular expression pattern in quotes or via a helper cell.
4. Close and press Enter. The matching group returns the extract.

For example, to get the first name before the first space you could use a pattern that captures characters up to the space. If building the pattern feels hard, there are quick ways to get started.

How to build patterns faster

• Use an AI assistant to generate regex patterns from a short description of the data.
• Create a library of common patterns and store them in a small table for reuse.
• Test patterns on sample strings to ensure they match the parts you expect.

REGEX brings a small learning curve, but it pays off with major flexibility for messy data. Plus, we can have AI write the regex codes and patterns for us.

Final thoughts on the 5 Excel Functions

Choosing only 5 Excel Functions forces you to pick versatile tools. FILTER, AGGREGATE, PIVOTBY, TEXT, and REGEXEXTRACT cover lookups, statistics, reporting, formatting, and cleanup.

They are not perfect for every edge case. But together they replace many niche functions and reduce complexity. Learning these five gives a powerful foundation for everyday spreadsheet work.

Download the Excel workbook from the section at the top to see the full list of functions that these 5 replace.

Which five would you choose? Share your list and a short reason.

Thanks so much!

Link to post: 5 Excel Functions That Replace 41 Others

Skill level: Beginner to Advanced

Download the Excel Files

Video Tutorial

Watch on YouTube & Subscribe to our Channel

Challenge Overview

In this Excel challenge, your task is to order pizzas for a party. You want to order just the right number of pizzas to stay on budget, satisfy all guests, and have the perfect amount of leftovers.

This challenge comes from the Weekly Challenges inside our Elevate Excel Training Program. The program also includes an all-access pass to our online course library, new AI Literacy for Excel course, community forum, live Q&A's and more.

Right now, you can try Elevate Excel for free during our limited time offer.

The Problem and Assumptions

Start with a simple table of guests. Each row should include:

• Guest name
• Up to three preferred toppings
• Number of slices they will eat

Assumptions for this Excel Planning exercise:

• A pizza has 12 slices.
• Half-and-half pizzas are allowed. Each half contains 6 slices.
• Topping order should not change the pizza type. For example, mushroom + olive is the same as olive + mushroom.

Keep the inputs clean. Use dropdowns for toppings when possible. This reduces typos and makes grouping easier during analysis.

The Solution

The solution to the challenge is explained below.

It uses modern functions like TEXTJOIN, SORT, UNIQUE, SUMIF, ROUNDUP, and FILTER. Each section includes a screenshot to match the steps. Follow the numbered steps, and you will have an automated pizza planner that scales with party size.

1. Build a consistent pizza type column

Create a column that represents each person’s pizza type. This column normalizes toppings and makes it possible to group identical pizzas.

Steps:

1. Use the TEXTJOIN function to combine the up to three topping cells into a single string.
2. Ignore empty topping cells so single- and double-topping pizzas work.
3. Sort the items in that row so topping order does not matter.

Example formula pattern:

=TEXTJOIN(", ", TRUE, [Topping1], [Topping2], [Topping3])

To normalize order, wrap the SORT function inside TEXTJOIN. Because toppings are laid out across a row, use the by_column argument:

=TEXTJOIN(", ", TRUE, SORT([ToppingRange],,TRUE))

This produces one value per row like Cheese or Olive, Mushroom. Use that column as the canonical pizza type for grouping.

2. Summarize slices per pizza type

Now use a summary area to show all pizza types and total slices needed for each type. This is a key part of Excel planning: convert individual demands into aggregated demand.

Steps:

1. Use the UNIQUE function to list the distinct pizza types from the joined type column.
2. Use SUMIF to total the slices for each pizza type.

Example:

=UNIQUE([TypeColumn])

=SUMIF([TypeColumn], [UniqueType]#, [SlicesColumn])

Notes:

• Use the spill operator (#) when referencing a dynamic UNIQUE range in subsequent formulas.
• This approach works in most modern Excel versions. If you prefer, a pivot table or GROUP BY can be used instead. But UNIQUE + SUMIF keeps everything formula-driven and dynamic.

3. Convert slices to half-pizzas and full pizzas

Because half-and-half pizzas are allowed, convert the total slices per pizza type into halves. Half a pizza equals 6 slices.

Steps:

1. Divide the slices per type by 6 to get the number of halves needed.
2. Round up that result because any fractional half still requires ordering a full half.
3. Sum the halves across all pizza types. Divide by 2 to convert halves to whole pizzas.

Formulas to use:

=ROUNDUP([SlicesPerType#]/6, 0)

=SUM([HalfPizzasRange#]) / 2

Why round up?

• If you need 1.2 halves, you must order 2 halves. ROUNDUP forces full-half counts.
• This ensures there are enough slices for each topping combination.

Excel Planning here helps avoid ordering too little. It gives a clear number of half pizzas for each topping and a total pizza count. If decimals remain after dividing halves by 2, that indicates an extra half pizza rather than a partial full pizza.

4. Calculate leftovers

Leftovers are easy once you have the half counts. Each half contains 6 slices. Multiply halves by 6 and subtract the slices that guests will eat.

Formula:

LeftoversPerType = HalfPizzasPerType# * 6 - SlicesPerType#

Then sum across types to get total leftover slices.

Example outcome from a small party: nine leftover slices. That is useful for covering unexpected hunger or for next-day lunch.

Quick rules for Excel Planning and leftovers:

• Decide how many leftovers you want. If you prefer zero leftovers, adjust the rounding policy and be cautious.
• Leftover slices can help if a guest eats more than expected.

5. Advanced: Create a dynamic split-pizza order list

To make orders easier to place with a vendor, generate a list of which half goes with which topping. Use the FILTER function and dynamic arrays to build those lists automatically.

Concept:

• Start with the joined pizza types and their half counts.
• For any type with 2 halves, that represents one whole pizza. For an odd half count, one half will pair with another odd half to make a split pizza.
• Use FILTER to extract only halves that need pairing and list them side by side.

Benefits:

• Quick packing list for the pizza place.
• Visual confirmation of how halves will be combined.
• Automatic updates when any guest’s slice count changes.

Because dynamic arrays propagate automatically, changing a single cell in the guest table updates the entire order. That is powerful Excel Planning. For example, if a guest jumps to 22 slices, the sheet recalculates and shows the new 6.5 total pizzas and which half needs pairing.

Final Thoughts

Good Excel planning turns messy inputs into clear decisions. The pizza planner described here is a compact example of that approach. It replaces guesswork with formulas to give a precise count of halves, whole pizzas, and leftovers.

And ultimately, your guests will be satisfied.

Try Elevate Excel Today

As I mentioned before, this challenge comes from the Weekly Challenges inside our Elevate Excel Training Program.

The program also includes

• An all-access pass to our online course library (over 22 courses)
• Community forum
• Live Q&A meetings
• Weekly Challenges
• New AI Literacy for Excel Course
• And a lot more

Right now you can try Elevate Excel for free during our limited time offer.

Link to post: Excel Challenge: Building a Dynamic Ordering System