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

This article breaks down 17 top changes. Each section explains what changed, why it matters, and how to use it right away.

1. PivotTable Auto Refresh (BETA)

The New Excel Updates from 2025 include PivotTable Auto Refresh to keep reports current without manual refreshes.

Auto refresh means the pivot table updates when the source data changes. This prevents sending outdated reports and cuts down on manual steps.

• Great for dashboards and scheduled reports.
• Reduces the chance of human error when data changes frequently.

How to use it:

1. Insert or select a PivotTable.
2. Enable the auto refresh option in the PivotTable settings if available.
3. Confirm that the source table or range is a proper Excel table to ensure smooth updates.

2. Clean Data with Copilot

The New Excel Updates from 2025 add a Clean Data tool that leverages Copilot to find and fix inconsistencies.

This feature highlights issues like extra spaces, inconsistent formats, and similar data problems. You can preview changes and apply them automatically.

• Saves time on manual cleaning tasks.
• Includes an undo option if you want to revert fixes.

Quick steps to use Clean Data:

1. Go to the Data tab and open Clean Data.
2. Review the suggested inconsistencies Copilot finds.
3. Click Apply to fix the issues or Undo to revert.

3. COPILOT Function (BETA)

The New Excel Updates from 2025 introduced the COPILOT function to bring AI directly into formulas.

This function can read text in a cell and return structured outputs such as categories or ratings. It unlocks new types of calculations that used to be very difficult.

• Use it for sentiment, classification, summarization, and custom rule outputs.
• Works row by row to fill adjacent cells based on content.

How to apply it:

1. Type the COPILOT function or use formula generation for help.
2. Reference the cell with the text you want Copilot to analyze.
3. Adjust the prompt or parameters inside the function as needed.

4. Explain Formulas with Copilot

The New Excel Updates from 2025 let Copilot explain formulas directly on the grid so you never feel lost with complex logic.

Right click a formula and choose Explain This Formula. Copilot will open a pane with a plain language explanation and step by step breakdown.

• Great for audits, handoffs, and learning formulas faster.
• Helps teams document logic without external notes.

Steps to get an explanation:

1. Right click the cell with the formula.
2. Select Explain This Formula.
3. Read the breakdown and suggested fixes if available.

5. Formula Completion with Copilot (BETA)

The New Excel Updates from 2025 include formula completion where Copilot suggests entire formulas based on column headers and context.

Type equals in a cell and Copilot will propose a formula for the entire column. Click the suggestion to apply it across the range.

• Saves time on repetitive column formulas.
• Helps reduce syntax errors and inconsistent logic.

To use formula completion:

1. Click a cell under a header and type =.
2. Choose the suggested formula from the Copilot popup.
3. Apply it to the column with a single click.

6. Formula Generation with Copilot

The New Excel Updates from 2025 added a formula generation box where you describe what you need and Copilot writes the formula.

Simply type a task description, and Copilot creates the formula. It supports complex operations like XLOOKUP, conditional logic, and nested functions.

• Ideal for non-experts or when building complex formulas quickly.
• Reduces trial and error when crafting multi-step formulas.

How to generate a formula:

1. Open the formula generation box in the formula bar area.
2. Describe the calculation or lookup you want.
3. Review the generated formula and click Keep It to apply.

7. Fix Formula Errors with Copilot

The New Excel Updates from 2025 make fixing formula errors easier with Copilot error assistants.

When a formula returns an error, a Copilot button appears. Click it to open a task pane that explains the error and suggests fixes.

• Speeds up debugging for complex sheets.
• Often shows the exact cell or argument causing the issue.

Use the error fixer like this:

1. Click the Copilot button next to the error indicator.
2. Read the explanation and recommended changes.
3. Apply fixes directly or adjust the formula manually.

8. Power Query Get Data Dialog

The New Excel Updates from 2025 include a revamped Get Data dialog with an integrated search for data sources.

The dialog makes it faster to find the right connector. There is a search box and a clearer layout for all supported sources.

• Saves time when connecting to databases, files, and web sources.
• Makes onboarding easier for new users who are unsure where to start.

How to connect quickly:

1. Click Data then Get Data.
2. Use the search box to find the connector you need.
3. Follow the connector-specific prompts to load the data.

9. Power Query Editor for Web

The New Excel Updates from 2025 brought Power Query Editor to the web version of Excel for the first time.

You can now create and edit queries from a browser. Right-click menus and familiar transformations are available online.

• Works well for Teams and SharePoint workflows.
• Removes the dependency on the desktop app for many automation tasks.

How to use the web editor:

1. Open Excel for the web and click Get Data.
2. Choose Launch Power Query Editor.
3. Edit transformations, then apply and load changes.

10. Agent Mode in Excel (Frontier)

The New Excel Updates from 2025 introduced Agent Mode so Excel can perform multi-step tasks automatically.

Agent Mode can build dashboards, run complex transformations, and chain tasks across the workbook. It can save hours on repetitive workflows.

• Supports models like ChatGPT and Claude as backends.
• Best for multi-step tasks that require logic, formatting, and charting.

Agent mode workflow:

1. Describe the task or choose a prebuilt agent.
2. Let the agent run through the steps and make changes.
3. Review the output and accept or refine the results.

11. Text Analysis with Copilot

The New Excel Updates from 2025 enable Copilot to analyze text and return summaries like sentiment breakdowns.

Use this for product reviews, survey responses, and customer feedback. Copilot provides counts and qualitative summaries for mixed, negative, neutral, and positive text.

• Great for quick sentiment reports without external tools.
• Can be combined with the COPILOT function for row level analysis.

How to run text analysis:

1. Select the text range you want to analyze.
2. Invoke Copilot and request a sentiment or theme summary.
3. Use the results to drive charts or pivot summaries.

12. PivotTable #SPILL Error Handling (BETA)

The New Excel Updates from 2025 improved PivotTable behavior when cells block the pivot from expanding.

Instead of showing an intrusive warning, Excel now highlights the obstructing cells so you can remove or move them. This is cleaner and less confusing.

• Makes pivot troubleshooting faster.
• Reduces accidental overwrites when cleaning sheets.

To resolve a spill block:

1. Select the highlighted obstructing cells.
2. Delete or move the contents blocking the pivot output.
3. Refresh the PivotTable to permit the spill to occur.

13. Dark Mode

The New Excel Updates from 2025 introduced a true dark mode that also changes the worksheet fill color to a dark background.

Turn on the black Office theme and then use Switch Modes on the View tab to enable dark sheets with light text.

• Easier on the eyes during late work sessions.
• Reduces screen glare and can be easier for presentations in low light.

How to enable dark mode:

1. Go to File then Options and choose the Black theme.
2. Open the View tab and click Switch Modes.
3. Confirm the sheet background and text colors suit your needs.

14. Python Editor in Excel

The New Excel Updates from 2025 added a built-in Python editor accessible from the Formulas tab.

The editor has syntax highlighting, screen tips, and output previews in a task pane. You can edit code while interacting with workbook cells.

• Useful for advanced analytics and custom data transformations.
• Bridges Python workflows and Excel without exporting files.

Quick Python workflow:

1. Open the Python editor from the Formulas tab.
2. Write or paste your Python code and add cell references if needed.
3. Run the code and view outputs in the task pane or the sheet.

15. View Side by Side on Mac (BETA)

The New Excel Updates from 2025 give Mac users the ability to view worksheets side by side with synchronous scrolling.

This feature makes comparisons and multi-sheet audits much easier on macOS. It mirrors a common desktop convenience that many professionals rely on.

• Helps reconcile data between two sheets.
• Great for reviewing changes or matching layouts.

How to use side by side view:

1. Open the two workbooks or windows you want to compare.
2. Choose View Side by Side and enable Synchronous Scrolling.
3. Scroll one workbook to move both at the same pace.

16. Value Tokens for Rich Data (BETA)

The New Excel Updates from 2025 add value tokens to show when a formula references a rich data type like stocks.

A small icon appears in the formula bar to indicate the cell contains a rich data type. This makes it easier to spot linked data and convert it if needed.

• Clarifies when a cell is linked to a richer object rather than plain text or numbers.
• Helpful when debugging formulas that use data types like Stocks or Geography.

Tips for working with value tokens:

1. Hover or inspect the token to view details about the rich data type.
2. Use the data type cards to expand fields into your worksheet.
3. Convert to plain values when you need static outputs.

17. Show Changes Extended

The New Excel Updates from 2025 extended the Show Changes history to one year so edits are easier to track over long projects.

This replaces the older 60 day limit. Now you can review changes made over the past year directly from the Review tab.

• Ideal for quarterly or annual reports maintained by multiple collaborators.
• Makes audits and rollbacks more manageable.

How to view changes:

1. Open the Review tab and click Show Changes.
2. Browse the list of edits and select an entry to view details.
3. Restore or note changes as needed for your version control.

Final thoughts

The New Excel Updates from 2025 bring AI and usability upgrades that matter to professionals. They cut repetitive work and reduce errors.

Start with the features that solve your biggest pain points. Try Copilot for text analysis and formula help. Use Agent Mode for multi-step automation. Enable power features like Power Query on the web for team workflows.

Which of the New Excel Updates from 2025 would save you the most time? Pick one and experiment. Small changes add up quickly.

Link to post: 17 New Excel Updates from 2025 That Will Save You Hours

So, in this guide we look at how to scrape video data with AI and Python into a clean, analyzable table and then build a compelling timeline chart in Excel.

The approach is simple in concept. Crop the area that contains the scoreboard. Extract one frame per second. Use OCR or an AI vision API to pull text from each frame. Clean and fill gaps in Excel. Then build a pivot-based line chart that tells the story. Each step below includes concise instructions, tips, and a matching screenshot so you can follow along visually.

Video Tutorial

Watch on YouTube & Subscribe to our Channel

Downloads

Section 1 — Capture and crop the scoreboard

This process starts with a video. I did a screen capture of the replay on YouTube using Camtasia, but you can use any video file source that you have.

Edit the video to isolate just the scoreboard region. Working with less visual noise makes OCR and AI extraction far more accurate. Keep the clip as small as the scoreboard so processing time and cost stay low.

Recommended workflow:

1. Open the recording in any editor.
2. Set the project canvas to the scoreboard pixel size.
3. Move and crop the footage so only the scoreboard remains visible.
4. Mute audio and export a short MP4 of that cropped region.

Why this matters:

• Smaller frames reduce processing time.
• Removing background and overlays improves OCR accuracy.
• Exporting a clean MP4 simplifies the next step: frame extraction.

Tip: If the scoreboard appears in the same place for most of the recording, one static crop works. If the layout shifts, create multiple crops and process each separately.

Section 2 — Extract one frame per second with Python

Convert the cropped MP4 into individual images. One image per second is a good balance between temporal resolution and processing cost. At 40 minutes this yields 2,400 frames. There are a few frames missing at the beginning and we'll fill those in later.

Simple steps to extract frames:

1. Install Python and ffmpeg or use OpenCV.
2. Create a Python script that reads the MP4 and writes one JPG per second.
3. Run the script from your command prompt. The output folder will contain sequential images.

Example considerations:

• Name files with second timestamps so they are easy to match to a timeline.
• Store images in a single folder to simplify batch OCR calls.
• Confirm the total seconds you expect so you can detect missing frames early.

The result is a folder containing an image for each second of the video. This step will allow us to then extract the data from each image. 

Section 3 — Extract text: local OCR versus AI vision API

There are two main options for extracting text from the images. Each has pros and cons.

Option A: Local OCR tools

Free and offline. Tools like Tesseract or other open-source OCR libraries are appealing. They are fast and have no API cost.

• Pros: Free, private, runs locally, no per-call fee.
• Cons: Lower accuracy on stylized fonts, takes tuning, may produce many errors.

Practical observation: Esports-style scoreboard fonts and small pixel sizes can confuse local OCR. That leads to misread player names and numbers that require heavy manual cleanup.

Option B: AI vision via an API

Using a modern vision model through an API produces higher accuracy for stylized text. A simple prompt can instruct the model to return just two fields per image: the player name and the score. The model can also follow rules for when no scoreboard is visible.

Key points:

• You send each frame to the API with a prompt describing the expected output.
• The response is parsed into a CSV or JSON line per frame.
• There is a modest cost per call. For 2,400 frames the example ran for $2.20 using OpenAI's api.

Alternative providers: Google’s Gemini and other vendors may offer free tiers that work well if cost is a concern. The tradeoff is potential latency and integration differences.

When to choose API: If the scoreboard uses a nonstandard font or you tried local OCR and found many errors, the AI vision API is likely the faster path to clean data.

Section 4 — Import and prepare raw text in Excel

Once you have a CSV or JSON with one row per second, import it into Excel and convert the range to an Excel table. This makes formulas and lookups easier.

Immediate cleanup tasks:

1. Convert the imported range to a table with Insert Table or Control T.
2. Run a uniqueness check on the name column to find OCR errors.
3. Create a corrected-names mapping so all variants point to a single canonical name.

Use these functions to speed cleanup:

• UNIQUE to list distinct name strings.
• SORT to display them alphabetically for quick review.
• XLOOKUP to replace misspelled names with corrected values.

Small manual steps are acceptable. Correct the most frequent errors by pasting the canonical name next to the variants and then use XLOOKUP to replace them throughout the dataset.

Section 5 — Convert timestamps and build a full timeline

Broadcast overlays often show time as minutes counting down. To create a timeline you need a consistent seconds-based axis that counts up or down as desired. Convert and reverse the timestamps so they align with your analysis.

Quick conversion formula approach:

1. If the overlay shows minutes, multiply by 60 to get seconds.
2. For a 40-minute match, set a base of 40 * 60 = 2,400 seconds and subtract the overlay seconds to reverse the direction.
3. In Excel, use a formula that spills down the column to create a full list of second values from start to finish.

Example formula idea:

• StartValue = total_seconds (for a 40-minute match, 2400).
• SecondsAtRow = StartValue – (overlay_minutes * 60*24).

Create a new worksheet that enumerates every second and the set of player-name slots for each second. This is the master timeline that the chart will use.

Section 6 — Populate scores and fill gaps

Not every second will have visible scores. Camera cuts or presenter close-ups leave gaps. Fill those gaps logically so the timeline remains continuous.

Step-by-step fill strategy:

1. Concatenate second and player into a lookup key in the original extracted table.
2. On the master timeline, use XLOOKUP to return the score for each second/player pair.

1. After the lookup, convert formulas to values to lock the data.
2. Use a conditional fill formula to pull the next nonblank score for each player when a row is blank.

How the conditional fill formula works:

• If the current cell has a score, keep it.
• If blank, filter the column for the next nonblank value for the same player and take the first result.
• This uses FILTER and TAKE to return the next available score down the timeline.

Why this matters: Filling gaps preserves the continuity of each player’s score series. The resulting full time series is essential for a smooth line chart without breaks.

Section 7 — Build the pivot table and create the line chart

A pivot table is the simplest way to pivot a long table into a wide format suitable for a multi-series line chart. It also handles aggregation cleanly if your extracted data had multiple entries per second.

Chart creation steps:

1. Insert a pivot table from the filled master table onto a new sheet.
2. Place the formatted time in Rows, player names in Columns, and the filled score in Values.
3. Sort the time descending if your axis should start from the match start.
4. Insert a Pivot Chart and choose the line chart type.

Formatting tips for clarity:

• Hide unnecessary buttons and the default legend.
• Delete vertical axis and gridlines for a cleaner look.
• Use data labels at the right-most point to show the final score and align player names with line ends.
• Desaturate non-top players to gray and keep the top contenders in color.
• Set the horizontal axis interval to 300 seconds for 5-minute ticks.

These tweaks make the chart readable and visually focused on the leaders and the late-match drama. The end result is a timeline that tells the story of place changes over time.

Section 8 — Automate, alternatives, and cost considerations

Once the process is defined it can be automated. Python scripts can call the vision API, write CSV output, and even push the cleaned CSV directly into Excel or Google Sheets for further processing.

Automation checklist:

1. Frame extraction script: one image per second.
2. Batch API caller: send each image with a prompt and save the structured response.
3. Import routine in Excel: consume the CSV and run cleanup macros or formulas.

Cost notes:

• API-based extraction is inexpensive at scale for single projects. Example: 2,400 calls for roughly a few dollars.
• Local OCR is free but often requires more manual cleanup time.
• Choose based on accuracy needs and the value of your time.

Alternatives:

• Use Google’s vision APIs or Gemini for generous free tiers.
• Combine local OCR with AI post-processing to correct predictable errors.
• Use Gemini's Canvas feature instead of Excel to create the chart. See my previous video on Data Visualization with Gemini and Nano Banana Pro for more details.

Conclusion

Converting on-screen scores into an insightful Excel dashboard is entirely possible with a clear, repeatable workflow. The key steps are crop, extract frames, use the right extraction method, clean and fill data in Excel, and finally build a pivot line chart that tells the story.

Small automation investments go a long way. A few Python scripts and a short API integration can save hours of manual transcription and produce a better final product. The process pairs well with Excel skills and provides a powerful way to extract value from any recorded broadcast that shows a scoreboard.

If you want to implement this end-to-end, start by capturing a short clip and extracting one minute of frames. Run a few OCR checks to compare methods. That brief experiment will reveal which extraction path gives the best balance of accuracy, speed, and cost for your data.

Link to post: Turn Videos into Excel Dashboards (AI + Python)

The good news is that with a few simple techniques, you can drastically improve the look and usability of your financial reports and other spreadsheets. We’ve compiled seven key Excel formatting battles, complete with pro tips, to help you create clear, professional, and efficient reports.

Video Tutorial

Watch on YouTube & Subscribe to our Channel

Downloads

The Big 7: Excel Formatting Battles

1. Currency vs. Accounting Number Format

When dealing with monetary values, Excel gives you two main formatting options: Currency and Accounting. While they look similar, their differences can affect the readability of your financial statements.

Currency Format (Ctrl + Shift + 4)

• The currency symbol (e.g., $) is placed directly next to the number.
• A negative value is typically shown with a dash or a red font, but can be customized in the Format Cells dialog box.
• Zero values are displayed as “0.00”.

Accounting Format

• The currency symbol is aligned to the far left of the cell.
• The numbers are aligned to the right. This clean separation makes reading columns of numbers much easier.
• Negative values are enclosed in parentheses.
• Zero values are displayed as a dash (-).

For most financial statements, the Accounting Format is generally preferred because the aligned symbols reduce clutter, making the numbers easier to read quickly. However, one place it might not work is in a chart, where a zero displayed as a dash can be less clear than “0.00.”

2. Header Wrapping: Wrap Text vs. Multiple Rows

Creating clear column headers is essential, especially when your report includes detailed period descriptions. You have to ensure the full header is visible without making your columns excessively wide.

Using Wrap Text

The standard “Wrap Text” feature automatically puts text on multiple lines based on the column width. A superior alternative is to manually insert a line break.

1. Double-click the cell to enter edit mode.
2. Place the cursor where you want the line break.
3. Press Alt + Enter (Windows) or Option + Return (Mac) to insert the break.
4. Hit Enter to accept the change.

This technique forces a line break so the header always appears exactly as you intend, regardless of the column width.

Using Multiple Header Rows

Another approach is to simply use two rows for your headers, splitting the period (like “FY Q1”) and the data description (like “Actuals”). This is simpler, but it can create problems if you need to use features like sorting, filtering, or turning your data into a true Excel Table, which often prefers a single header row.

3. Highlighting Data with Conditional Formatting

Manually changing cell colors to highlight key data points is a slow and inflexible process. The best way to visually draw attention to important numbers is by using Conditional Formatting.

1. Select the cells in the variance column you want to evaluate.
2. Go to the Home tab and click Conditional Formatting.
3. Choose Highlight Cell Rules, then select Greater Than…
4. In the dialog box, enter the threshold, such as 10%.
5. Choose a pre-set format (like “Green Fill with Dark Green Text”) or select Custom Format.
6. Click OK.

Conditional Formatting is powerful because it's dynamic. If the underlying values change, the formatting automatically updates to reflect whether the cell meets the rule's criteria.

4. Centering Headers: Merge & Center vs. Center Across Selection

This is one of the biggest formatting debates in Excel. How do you center a title across a range of columns?

Merge & Center (Avoid This)

While the Merge & Center button is easy to use, it should be avoided by professionals. It turns a range of cells into one large cell. This can cause numerous issues:

• It makes selecting single columns difficult, as the selection expands to the merged cell's width.
• It can interfere with copying, pasting, sorting, and other key Excel features.

Center Across Selection (The Pro Choice)

This technique achieves the same visual result without merging the cells.

1. Select the cells you want to center your header across.
2. Press Ctrl + 1 (Windows) or Cmd + 1 (Mac) to open the Format Cells dialog box.
3. Go to the Alignment tab.
4. Under the Horizontal dropdown, select Center Across Selection.
5. Click OK.

The header will appear centered, but each underlying cell remains separate and fully functional for all other Excel operations.

5. Hiding Details with Groups

Long, detailed reports can be hard to consume. You can improve readability by using Groups to collapse and expand sections of detail, making the report easier to navigate.

1. Select the detailed rows you want to group (e.g., all individual revenue line items).
2. Go to the Data tab.
3. In the Outline section, click Group. The keyboard shortcut is Shift + Alt + Right Arrow.
4. A small outline structure will appear to the left of your rows.

You can now click the minus sign to collapse the rows or the plus sign to expand them. This makes it easy for a user to see the summary data while still having access to the underlying detail with a single click.

6. Displaying Numbers in Thousands or Millions

Typing “K” or “M” at the end of a number to signify thousands or millions is a common mistake. Excel sees these as text values, meaning you cannot use them in calculations.

Instead, use a Custom Number Format to display the unit indicator while keeping the underlying value as a true number.

1. Select the cells you want to format.
2. Press Ctrl + 1 (Windows) or Cmd + 1 (Mac) to open the Format Cells dialog box.
3. Go to the Custom category.
4. In the Type box, enter the custom format.

Custom Format Examples:

• Thousands (K): \$#,##0,"K" (The single comma divides the number by 1,000)
• Millions (M): \$#,##0.0,,"M" (The two commas divide by 1,000,000; .0 adds a decimal place)

The number will display as, for example, “$132K” in the cell, but the formula bar will show the actual value (132,000), allowing it to be used in all calculations.

7. Formatting as a Table

If you’re working with raw data, manually applying bold headers, fill colors, and borders is tedious. Formatting as a Table is the fastest and most flexible solution.

1. Select any cell in your data range.
2. Go to the Home tab and click Format as Table.
3. Choose your desired style.
4. Confirm the range and that “My table has headers” is checked, then click OK.

Key Benefits of Excel Tables:

• Instant Formatting: Headers are automatically bolded, banded rows are applied, and filters are added.
• Automatic Expansion: Adding new rows or columns automatically applies the formatting and includes the data in the table range.
• Total Row: You can instantly insert a Total Row (from the Table Design tab) to quickly summarize data with built-in functions like Sum or Average.
• Structured References: Formulas created within the table automatically reference the column names, making them easier to read and copy down the column.

These formatting tips are essential for anyone who wants to create professional, efficient, and easy-to-understand Excel reports. Incorporating them into your workflow will save you time and greatly improve the quality of your output.

Please see the video above for visuals of each of the tips.

What's your favorite Excel formatting shortcut or best practice? Let us know in the comments below.

Link to post: 7 Pro Tips for Cleaner Excel Reports