As Excel files grow, they get harder to use. More tabs, more data, more chances for someone to type “Miscellaneous” wrong or hunt through a 10-tab workbook just to find the report they need.

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 FilesComplete the form below to instantly access the Excel files.

7 Hidden Buttons.xlsxDownload7 Hidden Buttons – Including Userform.xlsmDownloadThe 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 Tutorialhttps://youtu.be/9cHo7HLQK1YWatch on YouTube & Subscribe to our Channel

Button 1: Checkboxes for True/False DataTyping “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 ListsFreehand 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 FilteringThe 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 WorkbooksScrolling 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 UserFormsShapes 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 CourseI 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.

Join AI Coding for ExcelButton 6: Column and Row GroupingHiding 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 AnalysisWhen 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.

SummaryEach 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

View Details

Download the Excel FilesComplete the form below to instantly access the Excel files.

Water Sports Rentals Sales Dashboard – BEGIN.xlsxDownloadWater Sports Rentals Sales Dashboard – FINAL.xlsxDownload Video TutorialWatch 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

View Details

A line chart with eight overlapping lines is basically a plate of spaghetti. Everyone can see something is happening, but nobody can tell what.

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 FilesComplete the form below to instantly access the Excel files and Excel Formula Prompting Guide.

Line Chart Makeover – BEFORE.xlsxDownloadLine Chart Makeover – AFTER.xlsxDownload Video Tutorialhttps://www.youtube.com/watch?v=Ko34S2tWaukWatch on YouTube & Subscribe to our Channel

The Setup: A Pivot Table for Chart DataThe 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 RangeCreate 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.

Here's a quick look at the TRIMRANGE function. It returns the used portion of a range, trimming away any empty rows or columns at the edges. The function arguments are:* range: the range to trim, which can reference an entire sheet * row_trim_mode: controls trimming of empty rows from the top and/or bottom (optional) * col_trim_mode: controls trimming of empty columns from the left and/or right (optional) 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 DROPThe spill range includes an unwanted header row of zeros at the top. The DROP function removes it cleanly without any manual adjustments.

Before diving in, a quick look at the DROP function. It returns an array with a specified number of rows or columns removed from the beginning or end. The function arguments are:* array: the array or range to drop from * rows: number of rows to drop from the top (use a negative number to drop from the bottom) * columns: number of columns to drop from the left (use a negative number to drop from the right) (optional) 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 HSTACKNow 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.

A brief word on the HSTACK function. It appends arrays together horizontally, returning a combined array with more columns. The function arguments are:* array1: the first array or range * array2, …: one or more additional arrays to append to the right of the previous array (optional) 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 ColumnThe 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 RangeCut 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 ClaritySelect 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 DataBecause 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 VersionThe 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.

SummaryThis 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

View Details

Designing an Excel dashboard is one of those tasks that trips up even experienced analysts. The data work is the easy part. The design part, figuring out which charts to use, how to lay them out, and what your audience actually needs to see, that is where most people get stuck.

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 FilesComplete the form below to instantly access the Excel files and Excel Formula Prompting Guide.

AI Prompts for Excel Dashboard Design – Excel Campus.docxDownloadAI Prompts for Excel Dashboard Design – Excel Campus.pdfDownloadwater_sports_rental_sales_2026.csvDownload Video TutorialWatch on YouTube & Subscribe to our Channel

Step 1: Generate a Fake Dataset with ChatGPTBefore 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 AIThis 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 ClaudeClaude 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 GeminiGemini 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 ChatGPTChatGPT 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 SideOne 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 ExcelOnce 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.

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

View Details

XLOOKUP has been available for over five years, and it was built to replace VLOOKUP. But a lot of people are still writing VLOOKUP formulas out of habit.

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 FilesComplete the form below to instantly access the Excel files and Excel Formula Prompting Guide.

VLOOKUP to XLOOKUP Upgrade – Follow Along.xlsxDownloadVLOOKUP to XLOOKUP Upgrade – FINAL.xlsxDownloadVLOOKUP to XLOOKUP Upgrade Companion Guide – Excel Campus.pdfDownload Video TutorialWatch on YouTube & Subscribe to our Channel

Exact Match LookupThe 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.

Before diving in, a quick look at the VLOOKUP function. It searches the first column of a range for a value and returns a result from a specified column number in that range. The function arguments are:* lookup_value: the value to search for * table_array: the range containing both the lookup column and the return column * col_index_num: the column number within table_array to return a value from * range_lookup: FALSE for exact match, TRUE for approximate match (optional)

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

Here is a quick refresher on the XLOOKUP function. It searches a range or array for a match and returns a corresponding item from a second range or array. The function arguments are:* lookup_value: the value to search for * lookup_array: the range or array to search in * return_array: the range or array to return a value from * if_not_found: value to return if no match is found (optional) * match_mode: 0 = exact match (default), -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard (optional) * search_mode: 1 = first to last (default), -1 = last to first, 2 = binary ascending, -2 = binary descending (optional)

=XLOOKUP(C4,$F$5:$F$21,$I$5:$I$21) Why Separate Ranges Are BetterBecause 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 MatchOne 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.

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

A brief word on IFERROR. It returns a custom value when a formula produces an error, and the original result when it does not. The function arguments are:* value: the formula or expression to evaluate * value_if_error: the value to return if value produces an error

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

Looking LeftVLOOKUP 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 LookupsXLOOKUP 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 XLOOKUPTo 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.

Find the Last MatchBy 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 LookupXLOOKUP 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 XLOOKUPIf 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 XLOOKUPXLOOKUP 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.

SummaryXLOOKUP 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

View Details

When your boss asks for a summary report, you want something that's easy to build and easy to update. Excel gives you several ways to do this, and each method has its trade-offs.

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 FilesComplete the form below to instantly access the Excel files and Excel Formula Prompting Guide.

Excel Summary Reports – Follow Along.xlsxDownloadExcel Summary Reports – FINAL.xlsxDownload Video TutorialWatch on YouTube & Subscribe to our Channel

Start by Converting Your Data to an Excel TableBefore 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.

Convert Data to an Excel TableFormula Method 1: UNIQUE and SUMIFSThe 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 UNIQUEClick 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 SUMIFSIn 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 FunctionIf 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 TablePivot 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 Table1. 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 SlicersGo 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.

FormattingFormula-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 DataWhen 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

View Details

The combination of AI and LET function in Excel is powerful. It can make formulas faster and easier to manage. But it can also produce formulas that look complicated and scare colleagues. This guide explains what the LET function does, why AI often recommends it, and practical ways to decide when to use it and when to avoid it.

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

Prompting Tips for Writing Formulas with AI – Excel Campus.pdfDownloadAI and LET Function – BEGIN.xlsxDownloadAI and LET Function – FINAL.xlsxDownload Video TutorialWatch 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 functionAI 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 LETThis 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.

Build the basic XLOOKUPStart with a simple lookup to return the weight.
Write XLOOKUP to find Product ID in the products table.
Return the weight column from that table.
Confirm the value displays correctly. =XLOOKUP([@ProductID], Products[ProductID], Products[Weight])

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

Add the IF testNext, wrap the XLOOKUP in an IF to show the word bulk when weight > 40.
Use IF with a logical test that compares the XLOOKUP result to 40.
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.

Optimize with LETLET 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 itLET 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 columnsIf 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 explainedHelper columns store intermediate results in cells. They make formulas easy to follow. They also avoid LET if your users do not know it.

Place the XLOOKUP result in a helper column named Weight.
Use a second column with an IF test to return “bulk” or the weight.
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 practicesHere are clear, practical rules for using the LET function and for working with AI-generated formulas.

Know your audience.
- Choose LET for performance and experienced users.
- Choose helper columns for novice users and easier debugging.
Keep variable names meaningful.
- Use short but clear names like wt for weight.
- Avoid cryptic names if others will read the file.
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.
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.
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 ThoughtsAI 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 CourseIf 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

View Details

Imagine you had to pick only 5 Excel Functions to solve most spreadsheet problems. Which ones would you keep?

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 TutorialWatch on YouTube & Subscribe to our Channel

Download the Excel File 5 Excel Functions – BEGIN – Excel Campus.xlsxDownload5 Excel Functions – FINAL – Excel Campus.xlsxDownload How I chose the 5 Excel FunctionsMy 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.

FILTER — The lookup plus logical swiss army knifeFILTER 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 usesHere are typical scenarios where FILTER replaces other functions.

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

FILTER for Logical TestsFilter 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.

AGGREGATE — One function to handle most statistical needsAGGREGATE 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 worksAGGREGATE 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.

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

PIVOTBY — Build dynamic summary reports with a formulaPIVOTBY 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 PivotTablePivotTables 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.

TEXT — Formatting, grouping, and linked labelsTEXT 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.

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

REGEXEXTRACT — Clean and parse text with patternsREGEXEXTRACT 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 REGEXEXTRACT1. 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 FunctionsChoosing 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

View Details

Bottom line: Take on this Excel challenge to plan the perfect order for a team lunch or party.

Skill level: Beginner to Advanced

Download the Excel Files Week 35 Challenge – Party Planning – BEGIN.xlsxDownloadWeek 35 Challenge – Party Planning – Solution.xlsxDownload Video TutorialWatch on YouTube & Subscribe to our Channel

Challenge OverviewIn 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 AssumptionsStart 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 SolutionThe 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.

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

Steps:

Use the TEXTJOIN function to combine the up to three topping cells into a single string.
Ignore empty topping cells so single- and double-topping pizzas work.
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.

Summarize slices per pizza typeNow 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:

Use the UNIQUE function to list the distinct pizza types from the joined type column.
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.
Convert slices to half-pizzas and full pizzasBecause half-and-half pizzas are allowed, convert the total slices per pizza type into halves. Half a pizza equals 6 slices.

Steps:

Divide the slices per type by 6 to get the number of halves needed.
Round up that result because any fractional half still requires ordering a full half.
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.

Calculate leftoversLeftovers 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.
Advanced: Create a dynamic split-pizza order listTo 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 ThoughtsGood 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

View Details

The New Excel Updates from 2025 bring powerful AI features and quality of life improvements. These updates are designed to speed up reporting, reduce errors, and make complex tasks easier.

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

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:

Insert or select a PivotTable.
Enable the auto refresh option in the PivotTable settings if available.
Confirm that the source table or range is a proper Excel table to ensure smooth updates.
Clean Data with CopilotThe 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:

Go to the Data tab and open Clean Data.
Review the suggested inconsistencies Copilot finds.
Click Apply to fix the issues or Undo to revert.
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:

Type the COPILOT function or use formula generation for help.
Reference the cell with the text you want Copilot to analyze.
Adjust the prompt or parameters inside the function as needed.
Explain Formulas with CopilotThe 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:

Right click the cell with the formula.
Select Explain This Formula.
Read the breakdown and suggested fixes if available.
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:

Click a cell under a header and type =.
Choose the suggested formula from the Copilot popup.
Apply it to the column with a single click.
Formula Generation with CopilotThe 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:

Open the formula generation box in the formula bar area.
Describe the calculation or lookup you want.
Review the generated formula and click Keep It to apply.
Fix Formula Errors with CopilotThe 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:

Click the Copilot button next to the error indicator.
Read the explanation and recommended changes.
Apply fixes directly or adjust the formula manually.
Power Query Get Data DialogThe 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:

Click Data then Get Data.
Use the search box to find the connector you need.
Follow the connector-specific prompts to load the data.
Power Query Editor for WebThe 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:

Open Excel for the web and click Get Data.
Choose Launch Power Query Editor.
Edit transformations, then apply and load changes.
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:

Describe the task or choose a prebuilt agent.
Let the agent run through the steps and make changes.
Review the output and accept or refine the results.
Text Analysis with CopilotThe 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:

Select the text range you want to analyze.
Invoke Copilot and request a sentiment or theme summary.
Use the results to drive charts or pivot summaries.
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:

Select the highlighted obstructing cells.
Delete or move the contents blocking the pivot output.
Refresh the PivotTable to permit the spill to occur.
Dark ModeThe 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:

Go to File then Options and choose the Black theme.
Open the View tab and click Switch Modes.
Confirm the sheet background and text colors suit your needs.
Python Editor in ExcelThe 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:

Open the Python editor from the Formulas tab.
Write or paste your Python code and add cell references if needed.
Run the code and view outputs in the task pane or the sheet.
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:

Open the two workbooks or windows you want to compare.
Choose View Side by Side and enable Synchronous Scrolling.
Scroll one workbook to move both at the same pace.
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:

Hover or inspect the token to view details about the rich data type.
Use the data type cards to expand fields into your worksheet.
Convert to plain values when you need static outputs.
Show Changes ExtendedThe 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:

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

Final thoughtsThe 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

View Details

In this post I share 10 simple Excel tips that will help you navigate faster, clean data quickly, and build more usable spreadsheets.

Each tip is short and practical, and designed to help save you time with everyday Excel tasks.

Video TutorialWatch on YouTube & Subscribe to our Channel

Download the Example File Simple Excel Tips.xlsxDownload10 Excel Tips & Shortcuts – Excel Campus.pdfDownload 1. Navigation TricksQuick navigation matters. You can jump to the last used cell in a column or row without keyboard shortcuts. This is one of the most underrated Excel tips.

How it works:

Hover the mouse over a selected cell border until the cursor becomes a crosshair.
Double-click the bottom border to jump to the last used cell in the column.
Double-click the right border to jump to the last used cell in the row.

Want to select instead of just jump?

Hold Shift and double-click the border to select the range from the active cell to the last used cell.

Note the caveat. If there are blank cells, the jump stops at the last non-blank cell before a blank. In that case, repeat double-click or use keyboard shortcuts like Ctrl+Down.

Clipboard MagicThis tip helps when you need to copy many ranges or items and paste them across sheets. It is one of the efficient Excel Time-Saving Tips And Hacks for repetitive copy and paste tasks.

Steps to enable and use:

Go to Home tab and open the Clipboard panel.
Click Options and enable “Collect Without Showing Office Clipboard”.
Copy multiple ranges or items. They appear in the Clipboard panel.
Switch to the destination sheet and click each item in the panel to paste it.

Benefits:

No need to constantly switch between sheets.
Maintains formatting when you paste directly from the panel.

Try this with dashboards or report assembly. It saves dozens of small clicks.

Convert Dates with Find & ReplaceOne classic cleanup task is converting dates stored as text into real date values. This trick is a quick Excel Time-Saving Tips And Hacks method that often works instantly.

How to run it:

Select the column or range that contains the text dates.
Press Ctrl+H to open Find and Replace.
Enter the separator character in Find what and in Replace with. For example, type a dash in both fields.
Click Replace All. Excel reevaluates the cell content and converts recognisable strings into dates.

Important note. This works when the text dates already match your regional format. If dates are ambiguous or in a different format, use the next tip. Checkout our full-length tutorial on this find and replace technique for more details.

Fix Regional Date Formats With Text To ColumnsIn this example, the dates are in the Day-Month-Year format, but I am in the U.S., which has a default of Month-Day-Year format. The find and replace trick doesn't work in this scenario because there is a mismatch in the regional formats.

Therefore, we can use Text to Columns.

Follow these steps:

Select the date column first.
Go to Data tab and choose Text to Columns.
In step one choose Delimited and click Next.
Click Next again in step two without changes.
In step three choose the actual format of the text dates. For example, choose DMY if your data is day-month-year.
Click Finish. Excel converts the text into proper date values that follow your regional settings.

Use this when dates show incorrect months or years after a simple replace. Text To Columns gives you control and predictable results.

Here's an in-depth tutorial on using Text to Columns for fixing dates.

The Fastest Way to Duplicate A SheetCreating a copy of a sheet is common when building monthly reports. The drag+Ctrl method is one of those simple Excel hacks that will speed up your workflow.

How to duplicate:

Left click and hold the sheet tab you want to copy.
Drag it to the right or left where you want the copy.
Hold the Ctrl key. A small plus icon appears on the cursor.
Release the mouse button to create a duplicate sheet.

You can copy multiple selected sheets using the same technique. This beats the slower right-click Move Or Copy dialog.

Elegant Workbook NavigationWant a dynamic table of contents with live thumbnails? Use Paste Special Linked Picture and hyperlink the picture to the sheet.

Steps:

Select the area you want to use as a thumbnail and press Ctrl+C to copy.
Go to your index sheet. Right-click and choose Paste Special, then choose Linked Picture.
Move and resize the image as needed.
Right-click the picture, choose Link, then Link To Place In This Document and pick the sheet to jump to.

Benefits:

Thumbnails update automatically when the source sheet changes.
Clickable images make navigation intuitive for users.

To polish the look, use Picture Format and Crop to match tile sizes. Add a border if you want a consistent design.

Quickly Add or Remove BordersBorders from exports can clutter a report. Use keyboard shortcuts to remove or add borders fast. This is a practical member of the Excel Time-Saving Tips And Hacks list.

Shortcuts to remember:

Ctrl+Shift+- removes all borders in the selected range.
Ctrl+Shift+7 adds an outer border around the selected cells.

These shortcuts are fast and consistent. The dash key is usually between zero and equals on US keyboards.

Select Blank Cells to Flag Missing DataFlagging missing data is an essential step in the data cleanup process to improve accuracy and eliminate errors.
Press Ctrl+G or F5 to open Go To. Click Special.
Select Blanks and click OK. Excel highlights all blank cells in the active range.
Apply a fill color like yellow to indicate missing values.

The visual flag helps colleagues know exactly where data needs entry. It is a simple but effective communication method.

Here are some keyboard shortcuts for selecting entire columns with blank cells.

Insert Blank RowsOften, times we need to add blank rows between existing rows so we can insert new data or do calculations.

The easiest way to do this is to:

Select multiple rows.
Right click the row headers and choose insert (keyboard shortcut: Ctrl++).
The shortcut is hold the Ctrl key and press the plus key on the number pad. If you are on a laptop that doesn't have a numberpad, then press Ctrl+Shift+=
Adding Cell Notes or CommentsInserting multiple rows and leaving notes for colleagues is another common task. Use selection and shortcuts to do it fast.

Insert rows:

Select the number of rows you want to insert.
Right-click and choose Insert or press Ctrl+Plus.
Excel inserts that many blank rows above the selection.

Add notes or comments:

Press Shift+F2 to add a note to the active cell.
In Microsoft 365, press Ctrl+Shift+F2 to add a modern comment. You can @mention coworkers and assign tasks right from the comment.

Short notes speed handoffs. Modern comments improve collaboration in shared workbooks.

Final ThoughtsLearning these simple techniques will make you faster and more confident in Excel. And they will compound into huge time savings over the long term.

Try one tip per day and add it to your toolkit. When you combine these habits, you will build cleaner, faster, and more professional spreadsheets.

If you want to learn about other tools in Excel like Power Query, macros, and VBA, then check out our free class called the Modern Excel Blueprint. You will learn the tools and techniques that are the most critical in the modern AI era.

Link to post: 10 Excel Tricks That Will Save You HOURS

View Details

Microsoft’s latest Excel feature will change how we work with spreadsheets.

The COPILOT Function in Excel brings AI directly into formulas. In this article I walk through 17 real-world uses. These examples come from my experiments and templates. They show how the COPILOT Function in Excel can automate data cleanup, planning, and everyday analysis.

Video TutorialWatch on YouTube & Subscribe to our Channel

Downloads COPILOT-Function-Examples.xlsxDownload Introduction: Why use the COPILOT Function in Excel?The COPILOT Function in Excel lets you prompt a large language model (ChatGPT) from a cell. You pass context ranges, give simple instructions, and get structured results back. That result can be a single value or a spill range of many values.

This changes tedious tasks into a few prompts. It is powerful for messy data. It is also helpful for creative tasks like meal planning and trip itineraries.

1 Text Parsing: Extract items from messy listsOne of the first things I tried was parsing messy lists. The COPILOT Function in Excel is excellent at extracting items separated by inconsistent characters.

How I set it up1. Type =COPILOT( 2. Enter your prompt in quotes. Example: “please extract only the items from the following data and create a comma-separated list for each row.” 3. Add a comma and reference the range containing the messy text. 4. Close the formula and press Enter.

When you reference a whole range, COPILOT returns a spill range. This is efficient. It reduces the number of AI calls and helps stay within usage limits of the COPILOT Function in Excel.

2 Return Multiple Columns: Shape output into columnsThe COPILOT Function in Excel can also return multiple columns. You just adjust the prompt to ask for separate columns.

Prompt tips* Ask COPILOT to “parse the following data.”, [data range], “into the following columns”, [header row] * Pass your headers as a second context range to guide output names. * Reference the entire input range to get a full spill table.

This makes it possible to transform messy lists into structured datasets. You avoid long nested formulas and manual cleanup.

3 Potluck Planner: Balance practical suggestionsPotlucks are a fun test case. They reveal how AI can balance categories and fill gaps. Use the COPILOT Function in Excel to categorize signups and recommend missing dishes.

Workflow1. Have a signup sheet with items people plan to bring. 2. Use COPILOT to classify existing items into categories like Appetizer, Main, Dessert. 3. Use COPILOT again to suggest items for blank rows based on current variety.

The COPILOT Function in Excel dynamically recalculates when you edit referenced cells. That means recommendations update as people change their entries.

We can also reference the Spill Ranges from COPILOT in other formulas. In this example I used the new GROUPBY function to create a quick summary report that counts the number of dishes by category.

4 Meal Planner: Cook with what you haveUse the COPILOT Function in Excel to convert an ingredients list into seven meal suggestions. The prompt includes quantities and a note about pantry essentials.

Setup steps1. List ingredients and quantities in a table. 2. Write a prompt: “Please give me a list of seven meals based on the following ingredients. If quantity is zero, assume the item is unavailable.” 3. Reference the ingredient table as the context.

When quantities change, COPILOT recalculates the meal list. This makes the solution live and practical for weekly planning.

5 Splitting Names: Tackle difficult name formatsSplitting full names into title, first, middle, and last is a classic headache. The COPILOT Function in Excel handles diverse formats well.

Best practices* Reference header cells to tell COPILOT which columns you want. * Include a rule in the prompt: “If a component doesn't exist, skip that cell.” * Review outputs. LLMs make best guesses, so validate edge cases.

This approach saves hours when names include titles, suffixes, or multiple middle names. The COPILOT Function in Excel can spot patterns and place components in the correct columns.

6 Trip Planner: Build an itinerary from stops and preferencesPlanning family trips becomes easier. Pass a list of stops, days, dietary needs, and interests to COPILOT.

How it works1. Create tables for stops, days, interests, and food preferences. 2. Write a prompt: “Please create an itinerary for a road trip based on the following stops and number of days.” 3. Reference all tables as context so COPILOT can make tailored recommendations.

The result is a day-by-day table with activities and meal suggestions. It’s dynamic. Change preferences and the COPILOT Function in Excel updates the plan.

7 Categorize Bank Statements: Automate expense mappingBank statements often have inconsistent descriptions. Use COPILOT to assign categories for uncategorized items. Pair it with XLOOKUP and FILTER to create an efficient pipeline.

Pipeline steps1. Use XLOOKUP to map known descriptions to categories. 2. Use FILTER and ISNA to list descriptions that return errors. 3. Pass those unique uncategorized descriptions to COPILOT and ask it to classify them into your category list. 4. Paste confirmed results back into the category mapping table and let XLOOKUP recalculate.

This method reduces manual categorization. It’s ideal for monthly bank statement processing. The COPILOT Function in Excel fills in gaps fast.

8 Sentiment Analysis: Score product reviewsThe COPILOT Function in Excel can analyze text sentiment. It returns structured outputs like a 1–5 rating and a positive/negative flag.

Prompt pattern1. Reference the reviews range. 2. Pass a small header range that includes “Rating” and “Sentiment” to guide output. 3. Ask COPILOT to return both a numeric rating and a positive or negative tag.

Combining COPILOT with simple Excel formulas lets you show stars or summaries for reporting. This is useful for product and customer feedback workflows.

9 Seating Planner: Assign tables by interestsAssigning seats for events is a complex optimization problem. COPILOT handles it by grouping attendees with similar interests.

How to use it1. Collect survey responses with interests and constraints. 2. Ask COPILOT to assign table numbers so people sit with others who share interests. 3. Use the results to generate printed seating charts or name tags.

The COPILOT Function in Excel won’t always be perfect. But it delivers a smart first pass that saves a lot of manual effort.

10 Standardize Dates: Normalize Multiple Date FormatsDates arrive in many shapes. COPILOT can standardize them to any format you choose.

Simple setup1. In your prompt, specify the target date format (for example, yyyy-mm-dd). 2. Reference the column containing mixed date strings. 3. Ask COPILOT to return standardized dates in the specified format.

This eliminates messy text-date parsing formulas and speeds up downstream analysis. The COPILOT Function in Excel recognizes many regional formats too.

11 Standardize Company Names: Clean Brand VariationsCompany names often appear in multiple variations. COPILOT can propose a canonical name for each unique variant.

Efficient approach1. Create a unique list of company name variants using UNIQUE. 2. Pass that list to COPILOT and ask for standardized names. 3. Use XLOOKUP to map original names back to the corrected names.

Keep COPILOT from processing thousands of rows by operating on unique values. That protects your usage limits and speeds up the workflow.

12 Extract Order Details: Parse complex order stringsOrders often contain product, color, size, and quantity in one cell. COPILOT can parse those into separate columns.

Why this helps* Reduces manual parsing and formula complexity. * Handles multi-word colors and product names correctly. * Creates structured data for inventory and sales analysis.

When you give clear column headers as context, COPILOT aligns parsed values to the correct fields.

13 Categorize Support Issues: Map tickets to topicsCustomer support tickets are messy. Use the COPILOT Function in Excel to assign each ticket to one of your predefined categories.

Steps1. List your allowed categories in a table. 2. Reference the ticket descriptions and the category list in the prompt. 3. Ask COPILOT to return the best category for each ticket without creating new categories.

This speeds up the triage process and generates data you can use in dashboards and SLAs.

14 Semantic Duplicates: Harmonize Product VariantsSemantic duplicates are variations that mean the same thing. For example, “iPhone 15 Pro” vs “iPhone 15 – Pro”. COPILOT can identify and standardize them.

Use case1. Collect all product name variants into a unique list. 2. Ask COPILOT to identify semantic duplicates and return a standard name for each. 3. Map the original entries to the standardized names for reporting.

This is critical when product analytics depend on consistent naming. The COPILOT Function in Excel understands context and human variations.

15 Shorten Job TitlesHR lists include long job titles with seniority and contract notes. Use COPILOT to extract core roles so you can group and count employees.

How I ran it1. Define a prompt: “Remove seniority levels and contract type info. Keep only the core role.” 2. Reference the column of job titles. 3. Return a clean role column to use in pivot tables or group by formulas.

After cleanup, you can quickly create headcount summaries. The COPILOT Function in Excel makes grouping straightforward.

16 Address Cleanup: Standardize to USPS or local styleAddresses are a classic data mess. COPILOT can standardize them to USPS style or to your preferred local format.

Practical tips* Specify the address style in the prompt (for example, “USPS with abbreviations”). * Reference the full address column as context. * Ask COPILOT to return standardized address components or a single formatted address string.

Use the results to improve geocoding, mail merges, and logistics.

17 Customized Emails: Recommend products and draft outreachUse the COPILOT Function in Excel to generate personalized email copy. Base suggestions on historical purchase combinations.

Workflow1. Create a table of product combinations customers have bought. 2. Ask COPILOT to draft an email recommending complementary products that the customer has not purchased. 3. Use lookups to fill in customer names and their purchase history for mass personalization.

This creates sales-ready drafts you can review, personalize, and send. The COPILOT Function in Excel helps scale outreach without losing context.

Practical Tips and Best PracticesWorking with the COPILOT Function in Excel is straightforward. Follow a few best practices to get reliable results and avoid wasted calls.

Key recommendations1. Reference ranges instead of single cells when possible to reduce API calls. 2. Pass small header ranges as context to guide labels and output structure. 3. Operate on UNIQUE lists for large datasets to limit processing and stay within limits. 4. Validate results. Treat COPILOT outputs as intelligent suggestions that may need minor corrections. 5. Keep prompts concise and explicit. Clear instructions produce more predictable outputs.

These practices conserve quota and improve reliability. They also make it easier to integrate output into formulas like XLOOKUP, FILTER, and GROUP BY.

Limitations and Things to WatchThe COPILOT Function in Excel is in beta at the time of this posting. There are usage limits and availability constraints to keep in mind.

There may be hourly call limits or row limits per request.
Large language models can make plausible errors. Always check critical outputs.
Complex edge cases may still require manual intervention or refined prompts.
Privacy: be cautious about sending sensitive data to any external model depending on your organization’s policies.

Microsoft provides a support page with detailed usage limits.

Final Thoughts and Next StepsThe COPILOT Function in Excel is a powerful tool for professionals. It turns messy, manual tasks into a few prompts and frees time for higher-value work.

My recommendation is to start small. Try it on a unique list or a single cleanup task. Learn prompt patterns and then scale. The COPILOT Function in Excel is best used as an augmentation to your existing formulas and processes.

It's also good to keep your prompts clear, reference ranges, and validate outputs. These simple rules help you get reliable results. Then try it on your toughest repetitive task and see what it can do.

If you build repeatable patterns, you can combine COPILOT outputs with formulas like XLOOKUP, FILTER, and GROUP BY to create robust automations and workflows.

Leave a comment below and let us know how you will be using the new function.

Thanks again and have a nice day! 🙂

Link to post: COPILOT Function in Excel: 17 Practical Ways to Save Time and Clean Data

View Details

Power Query can feel intimidating when you first open it. With its many buttons, unfamiliar errors, and unique formula language, it can quickly overwhelm new users. However, mastering Power Query is well worth the effort. This powerful Excel tool will save you a ton of time by automating data import, cleaning, and transformation tasks.

In this Power Query tutorial, we will cover seven essential tips to make Power Query easier to use and help you get started on the right foot. Whether you are new to Power Query or looking to sharpen your skills, these tips will guide you through common challenges and powerful features.

Video Tutorialhttps://youtu.be/xxQnsQz5aPcWatch on YouTube & Subscribe to our Channel

Downloads Power Query Made Easy Files.zipDownload Understanding What Power Query Is and Is NotThe first step in using Power Query effectively is understanding its purpose. Power Query is designed to bring data into Excel and clean it up. It automates the process of collecting and transforming data so that you can analyze it more easily.

Power Query is not a replacement for formulas, pivot tables, or charts. Instead, it complements these tools by handling the tedious and repetitive task of data preparation.

Studies show that data professionals spend nearly 80% of their time collecting, organizing, and cleansing data. Power Query helps automate this crucial first step.

Tip 1: Import and Combine Data from Multiple FilesOne of Power Query’s standout features is its ability to import data from many sources, including CSV files, databases, and folders. If you have multiple CSV files with similar data, Power Query can combine them into a single table.

Here’s how to combine CSV files stored in a folder:

Go to the Data tab in Excel and find the Get & Transform Data section.
Choose From Folder and select the folder containing your CSV files.
Power Query will show a preview of all files in the folder. Click Combine and Transform to stack the files on top of each other.
Review the preview and click OK to open the Power Query Editor.

Once combined, all the data from each CSV will be stacked vertically in one table. This is perfect for consolidating weekly or monthly reports.

Tip 2: Clean Data by Removing Blank Rows and Unnecessary ColumnsAfter importing your data, the next step is to clean it up. Power Query makes this simple with built-in filters and transformation tools.

To remove blank rows, which Power Query calls null values:

Click the filter dropdown on any column.
Uncheck the null option to exclude blank rows.

To remove unwanted columns, just right-click the column header and select Remove, or press the Delete key.

After cleaning, click the Close & Load button on the Home tab to output the cleaned data to a new worksheet.

Tip 3: Use the Applied Steps Window as a Personal Time MachineOne of Power Query’s most valuable features is the Applied Steps pane on the right side of the editor. This pane records every transformation you make as sequential steps.

This works like a personal time machine, allowing you to:

Click any step to see what the data looked like at that point.
Delete or reorder steps to fix mistakes.
Modify steps if your data or requirements change.

When you refresh your query, Power Query reruns all these steps on the new data. For example, if you add a new CSV file to the folder, simply refresh your query, and Power Query will automatically include the new data without repeating your manual work.

Tip 4: Manage Data Types Carefully to Avoid ErrorsData types are crucial in Power Query and a common source of errors, especially for new users. Each column in Power Query has a data type, such as whole number, text, or decimal number.

Power Query tries to detect data types automatically when you create a query, but it doesn’t always get it right. For example, a column with dashes (“-“) might be detected as text instead of numbers, causing errors when you try to convert it.

To fix this:

Identify problematic values like dashes or other placeholders.
Use the Replace Values feature on the Transform tab to replace these with zeros or appropriate values.
Change the data type of the column to the correct type after cleaning.

Taking the time to ensure your data types are correct will save you headaches when analyzing the data later with formulas or pivot tables.

Tip 5: Know Which Ribbon Tab to Use for Your TransformationsPower Query has two main tabs in its editor ribbon: Transform and Add Column. They contain many similar buttons but serve different purposes.

If you want to modify existing columns, use the Transform tab. If you want to create new columns based on calculations or extractions, use the Add Column tab.

For example, to calculate a week number from a date:

Using the Transform tab’s Week of Year option will replace the date column with week numbers.
Using the Add Column tab’s Week of Year option will add a new column with the week number, preserving the original date column.

Choosing the right operation helps keep your data organized and avoids accidental overwrites.

Tip 6: Use AI-Powered ‘Column From Examples' to Extract DataPower Query has a neat AI feature called Column From Examples that lets you create new columns by providing a few examples of the desired output. This is perfect for extracting parts of text or numbers without writing complex formulas.

For instance, if you have tracking numbers and want to extract the carrier code (the first three letters), you can:

Select the column with tracking numbers.
Go to the Add Column tab and choose Column From Examples > From Selection.
Type a few examples of the carrier codes in the new column (e.g., “USP”, “DHL”).
Power Query will detect the pattern and fill in the rest automatically.
Click OK to add the new column.

This feature is a great time saver and requires no coding.

Tip 7: Explore the Power Query Formula Language (M Code)Behind every button click in Power Query, code is generated in a formula language called M. You can view and edit this code in the formula bar or the Advanced Editor.

While you don’t need to know M code to use Power Query effectively, understanding it can help you perform more advanced transformations and troubleshoot errors.

For example, you might see functions like Text.Middle used to extract part of a text string. The code is similar to Excel formulas but tailored for data queries.

To view the code:

Enable the formula bar from the View tab if it’s not visible.
Click on any applied step to see its code.
Use the Advanced Editor to see or edit the entire query script.

Don’t worry if this seems complex at first. Power Query is still a powerful no-code or low-code tool because most transformations can be done with just clicks.

Bonus Tip: Use Unpivot to Reshape Data for AnalysisOne of the most powerful features of Power Query is its ability to unpivot data. Unpivoting converts columns into rows, which is often needed to prepare data for pivot tables or analysis.

Imagine you have a report with months across the top and products down the left side. This layout is difficult to analyze with formulas and pivot tables.

With unpivot:

Load your data into Power Query by selecting it and choosing From Table/Range.
Select the columns you want to keep (e.g., Product).
Use the Unpivot Other Columns option on the Transform tab.
Power Query will convert the month columns into a single Month column with corresponding values.
Rename columns as needed and load the data back to Excel.

This one-click transformation replaces complex VBA macros and gets your data into a perfect tabular format for pivot tables.

ConclusionThis Power Query tutorial covered seven essential tips to help you get started and become more efficient with this powerful Excel tool:

Understand Power Query’s role in data import and cleaning.
Import and combine data from multiple files effortlessly.
Clean data by removing blanks and unnecessary columns.
Use the Applied Steps pane to manage and automate transformations.
Handle data types carefully to avoid errors.
Choose the right ribbon tab for transformations and adding columns.
Leverage AI-powered features like Column From Examples.
Dive into M code for advanced control when ready.

Power Query is a fantastic no-code or low-code solution that can save you hours of repetitive work. Once you set up your queries, you can refresh them with a click to update your reports with new data.

If you want to deepen your Power Query skills, consider exploring online training courses.

The Power Query Jumpstart Course is a crash course for beginners that will help you get up and running with Power Query in under an hour.

The Power Query Pro Course is an in-depth training program that will teach you how to automate complex data cleanup tasks to save you hours of time and thousands of dollars.

Leave a comment below and let us know how you'll be using Power Query.

Thanks, and have a great day! 🙂

Link to post: Power Query Tutorial: 7 Essential Tips to Master Data Transformation in Excel

View Details

The function keys are often the most overlooked set of keys on the keyboard. Usually just sitting up there collecting dust.

But in Excel, the function keys are extremely useful. So, in this post I’m sharing 27 Excel function keys shortcuts that will save you time, simplify your tasks, and unlock hidden features you might not know about.

Whether you’re a beginner or a seasoned Excel user, these tips will help you work smarter, not harder.

Video TutorialWatch on YouTube & Subscribe to our Channel

DownloadDownload our FREE Excel Shortcuts PDF with over 270 shortcuts for Windows, Mac, and Web

Click here to Download the Free Guide

F1: Help, Ribbon Control, and Full Screen ModeControl + F1: Hide or Show the RibbonWhen you want to declutter your Excel window and see more rows, pressing Control + F1 hides the ribbon. This frees up space and makes it easier to focus on your data. Press the same keys again to bring the ribbon back.

Control + Shift + F1: Toggle Full Screen ModeThis shortcut hides not only the ribbon but also the title bar and quick access toolbar, putting Excel into full screen mode. It’s perfect when you want to maximize your workspace. Press Control + Shift + F1 again to exit full screen mode.

Alt + F1: Instantly Create a ChartNeed a quick visual? Select your data and press Alt + F1 to create a chart right away. If your data is in a Pivot Table, this shortcut creates a Pivot Chart instead, saving you several steps.

F1: Get Help for FunctionsWhile editing a formula, place your cursor inside a function and press F1. Excel will open the help menu for that specific function, guiding you through its usage and parameters.

F2: Edit Cells and Rename Files QuicklyF2: Edit Cell ContentsF2 is one of the most popular shortcuts in Excel. It lets you edit the active cell directly, showing the formula or content inside. After editing, press enter to save or escape to cancel.

Control + F2: Edit in Formula BarIf you prefer editing in the formula bar, press Control + F2 to focus there. This is especially handy for longer formulas or when you want better visibility while editing.

F2: Rename Files in File ExplorerHere’s a little pro tip: outside Excel, in File Explorer, pressing F2 lets you quickly rename the selected file. It’s a universal shortcut that comes in handy beyond just Excel.

Note for Laptop UsersOn laptops or compact keyboards, you might need to press the FN key along with function keys. Some keyboards have a function lock to toggle this behavior.

A quick Google search for your specific model will help you set this up.

F3: Manage Named Ranges with EaseControl + F3: Open Name ManagerThe Name Manager is a great tool when working with named ranges, Lambdas, or Excel tables. Press Control + F3 to open it. Here, you can:

View all named ranges
Edit names by double-clicking
Rename tables or ranges you forgot to name properly

This shortcut keeps your workbook organized and makes working with complex formulas much easier.

F4: Absolute References and Repeat Last ActionUse F4 to Toggle Absolute and Relative ReferencesWhen writing formulas, you often need to lock cell references. Select the cell or reference in the formula, then press F4 to add dollar signs, making it absolute (e.g., $G$2). Press F4 repeatedly to cycle through:

Absolute reference (e.g., $G$2)
Mixed reference row locked (e.g., G$2)
Mixed reference column locked (e.g., $G2)
Relative reference (e.g., G2)

This is essential for copying formulas correctly across cells.

F4 to Repeat Last ActionAnother handy use of F4 is repeating your last action. For example, if you fill a cell with yellow color, select another cell and press F4 to apply the same fill color. You can keep pressing it to repeat the action on multiple cells.

F5: Navigate and Select Cells QuicklyF5 or Control + G: Open Go To DialogPressing F5 or Control + G opens the Go To dialog box. This is great for jumping to any cell, named range, or table in your workbook. Simply type the reference or select from the list and hit enter.

Go To Special: Select Specific CellsWithin the Go To dialog, click Special to select cells based on criteria such as:

Cells with formulas
Blank cells
Constants
Conditional formats

This feature is incredibly useful when auditing or editing complex worksheets.

Alt + F5 and Control + Alt + F5: Refresh DataWorking with Pivot Tables or Power Query? Press Alt + F5 to refresh the selected Pivot Table or query. To refresh all tables and queries in the workbook, use Control + Alt + F5.

If you forget these, you can also find the shortcut by hovering over the Refresh All button on the Data tab.

F6: Navigate Between Workbook ElementsPress F6 to Cycle Through Excel AreasF6 cycles focus through different parts of the Excel window:

Once: Moves to sheet tabs at the bottom
Twice: Moves to the name box
Subsequent presses: Cycle through other UI elements

Use arrow keys to navigate tabs and hit enter to select.

Control + F6: Switch Between Open WorkbooksIf you have multiple Excel workbooks open, press Control + F6 to switch between them. It’s a handy way to toggle without reaching for your mouse.

Alternatively, Control + Tab also toggles between workbooks, and many users prefer it, especially if they use Alt + Tab to switch between applications.

F7: Spell Check and ThesaurusF7: Run Spell CheckPress F7 to launch the spell check window. This tool scans your worksheet for misspelled words and offers suggestions. You can double click a suggested correction to apply it.

Spell check also works inside shapes and text boxes, which many users overlook.

Shift + F7: Open ThesaurusIf you want to find synonyms or alternate words, select text within a cell and press Shift + F7. This opens the thesaurus for quick word suggestions.

F8: Extend Selection and Macro WindowF8: Toggle Extend Selection ModeSometimes when selecting cells, you might accidentally hit F8. This puts Excel into extend selection mode, where selecting one cell extends the selection range. The status bar will show “Extend Selection”.

Press F8 again or Escape to turn off this mode and return to normal selection.

Alt + F8: Open Macro WindowPress Alt + F8 to open the macro dialog box. Here you can:

Run existing macros
Edit macros
Manage macros in your personal macro workbook or any open workbook

This is a powerful tool for automating repetitive tasks in Excel.

F9: Manual Calculation and Formula EvaluationF9: Calculate Workbook ManuallyIf your workbook is set to manual calculation mode to improve performance, press F9 to calculate all formulas in the workbook. This is faster than switching back to automatic calculation.

Shift + F9: Calculate Active WorksheetTo calculate only the current worksheet, use Shift + F9. This saves time if you’re working with large workbooks.

F9: Evaluate Parts of a FormulaWhen editing a formula, select a part of it (like a function or expression) and press F9 to see the result. This replaces the selected part with its calculated value temporarily. Press Escape if you don’t want to keep the change.

Note: In Microsoft 365, Excel can now show calculation results as screen tips when you select parts of a formula, reducing the need to press F9.

Alt + F10: Manage Shapes and ObjectsOpen the Selection PanePress Alt + F10 to open the Selection Pane. This pane lists all shapes, charts, slicers, and other objects on the sheet.

You can:

Select objects or groups
Rename groups or individual shapes for easier identification
Toggle visibility of objects on and off

This is especially useful when working with complex dashboards.

Alt + F11: Open the VBA EditorAccess the Developer WorldOne of the most powerful shortcuts is Alt + F11, which opens the Visual Basic for Applications (VBA) editor. Here you can write macros to automate tasks not possible with regular Excel functions.

Learning VBA can save you hours of repetitive work and unlock advanced capabilities.

Watch Out for MistakesIf you forget to press Alt and just press F11, Excel will insert a new chart sheet based on the selected data. It’s easy to fix by right-clicking and deleting the unwanted sheet.

Shift + F11 and F12: Insert New Sheet and Save AsShift + F11: Insert a New WorksheetPress Shift + F11 to quickly add a new worksheet to your workbook. It’s faster than right-clicking and choosing “Insert”.

F12: Open Save As WindowF12 opens the Save As dialog, allowing you to rename your file, choose a save location, or change the file type. This shortcut also works across other Office apps like Word and PowerPoint.

Alt + F12: Open Power Query EditorIf you use Power Query for data transformation, Alt + F12 opens the Power Query editor window directly, saving you time navigating through the ribbon.

Conclusion: Unlock Your Excel Potential with Function KeysExcel function keys shortcuts are a hidden treasure trove for anyone looking to boost their productivity. From editing cells with F2 to managing macros with Alt + F8 and navigating complex workbooks with F6, these shortcuts save time and simplify your work.

Remember, the best way to get comfortable with these shortcuts is to practice them regularly. Start by mastering a few that fit your workflow, then gradually add more to your toolkit.

Which Excel function keys shortcuts are your favorites? Have I missed any gems? Leave a comment and share your tips.

Don’t forget to download the free Excel shortcuts guide packed with over 270 shortcuts for Windows, Mac, and the web. It’s a great resource to take your Excel skills to the next level.

Happy Exceling!

Link to post: 27 Excel Function Key Shortcuts that Save Time & Unlock Hidden Features

View Details

If you've ever spent hours cleaning up repetitive data in Excel, you know how tedious and error-prone it can be. The good news is that Excel has built-in tools that can help us automate those boring tasks and save yourself tons of time.

In this article, I will show you how to automate Excel with VBA and ChatGPT using Excel's hidden Macro Recorder and AI-powered code improvement. This combination will help you work like a pro, eliminating manual errors and speeding up your workflow.

Let's dive into a practical example where we take raw order data and transform it into a clean, formatted report with just a click.

Video TutorialWatch on YouTube & Subscribe to our Channel

Downloads Macro Recorder.zipDownload Understanding the Problem: Manual Data CleanupEvery week, you might receive raw order data that requires cleaning and formatting before it can be used for reporting. This often involves multiple repetitive steps such as:

Converting raw data into a proper Excel table
Inserting header rows for titles and dates
Sorting data by specific columns
Applying conditional formatting to highlight important values

Doing these tasks manually is time-consuming, boring, and prone to errors. This is especially true when you have to repeat them weekly or monthly. The key is to automate this entire process so you can focus on more valuable work.

Step 1: Enabling the Developer Tab and Recording a MacroThe first step is to use Excel's Macro Recorder, a hidden but powerful tool. It records your actions and converts them into VBA code that can be run automatically later.

If you don't see the Developer tab on your ribbon, here is how to enable it:

Right-click anywhere on the ribbon and select Customize the Ribbon.
In the right pane, check the box next to Developer.
Click OK to enable the Developer tab.

Once enabled, go to the Developer tab and click Record Macro. You will be prompted to name your macro (no spaces allowed, so use underscores). Choose to store it in the Personal Macro Workbook so it will be available across all your Excel files.

Now, every action you perform in Excel will be recorded as VBA code until you stop the recording.

Step 2: Recording the Data Cleanup ProcessWith recording started, perform the following steps to clean up your data:

Select any cell in your raw data and convert it to an Excel table (Insert tab → Table).
Insert three blank rows at the top to create space for the report title and date.
Type your report title in cell A1 and format it with a bold font size of 16.
In the new header rows, add the word “Date” and use the TODAY() function to insert the current date.
Replace the TODAY() formula with its static value to avoid automatic updates.
Sort the table by the Product ID column to group similar products.
Apply conditional formatting to the Quantity column to highlight values greater than 8 with a red fill.

Once done, stop the macro recording by clicking the stop button either in the Developer tab or the status bar at the bottom.

Step 3: Viewing and Testing the Recorded MacroNow that you have a macro recorded, let's see the code behind it:

Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
In the Project window, find the PERSONAL.XLSB file and open the relevant module (e.g., Module1).
Review the recorded VBA code. It will contain all the steps you performed, but usually with hard-coded cell and range references.

To test the macro, open a new Excel workbook with raw order data and run the macro by pressing Alt + F8, selecting your macro, and clicking Run. The macro will perform all the cleanup steps automatically.

Step 4: Identifying Limitations of the Macro RecorderWhile the macro recorder is amazing for capturing your workflow, it has some limitations:

Hard-coded references: The macro uses fixed cell ranges and sheet names, which break when your data size changes or sheet names differ.
Static ranges: The table range won't automatically expand to include new rows added to your data.
Sheet name dependencies: If the macro references a specific sheet name, it will cause errors if run on a differently named sheet.

These issues mean you often need to manually update the macro code to accommodate new data or different files, which defeats the purpose of automation.

Step 5: Using ChatGPT to Make Your Macro DynamicThis is where AI comes in to save the day. Instead of writing complex VBA code yourself, you can use ChatGPT or similar AI tools to clean up and improve your recorded macro.

Here's how to do it:

Copy the entire recorded macro code from the VBA editor.
Paste the code into ChatGPT with a prompt asking to make the macro more dynamic and flexible.
Answer any clarifying questions ChatGPT may have about your data or desired functionality.
Receive a refactored version of the macro that uses variables, dynamic ranges, and better programming practices.

The improved macro will:

Automatically detect the size of your data and adjust the table range accordingly.
Use variables instead of hard-coded values for sheet names and ranges.
Handle inserting header rows and formatting in a more adaptable way.

Step 6: Implementing and Testing the Improved MacroOnce you get the improved macro code from ChatGPT, paste it back into your VBA editor in a new module or overwrite your existing macro (changing the macro name if needed to avoid conflicts).

Then, test the macro on new raw data files with varying row counts to verify that:

The table expands to include all rows.
Header rows and formatting are correctly applied.
Sorting and conditional formatting behave as expected.

This dynamic macro lets you automate your data cleanup process with confidence that it will work on different files without manual code edits.

Step 7: Leveraging the Personal Macro Workbook for ConvenienceStoring your macros in the Personal Macro Workbook means they are available whenever you open Excel, regardless of which file you are working on.

You don't need to save macros inside each individual file.
You can run your macros on any open workbook instantly.
You can create custom buttons or tabs on the ribbon to run your favorite macros quickly.

This setup streamlines your workflow and makes macro automation more accessible across your Excel projects.

Checkout my video series on the Personal Macro Workbook and Custom Ribbon Macro Buttons to learn more about this awesome feature of Excel.

Final Thoughts on Automate Excel with VBA and ChatGPTUsing the Macro Recorder combined with AI tools like ChatGPT is a game changer for automating Excel tasks. The Macro Recorder captures your manual steps quickly, and ChatGPT helps you transform that recorded code into dynamic, reusable VBA scripts.

This approach:

Saves you hours of repetitive work
Reduces errors caused by manual cleanup
Allows you to handle varying data sizes and formats without rewriting code
Enables you to run powerful macros on any file from your Personal Macro Workbook

While AI tools are not perfect and some VBA knowledge is helpful, this combination makes macro automation accessible to more users than ever before.

If you want to take your Excel skills to the next level, consider exploring online VBA training courses to complement your AI-assisted automation journey.

What Excel task will you automate next? Feel free to share your ideas or questions in the comments below. If you found this guide helpful, be sure to like and subscribe for more tips on working smarter with Excel.

Link to post: Automate Excel with VBA and ChatGPT

View Details

Bottom Line: While XLOOKUP is a powerful function, it has limitations. In this post, we’ll explore five alternative techniques that can enhance your lookup capabilities in Excel.

Skill Level: Intermediate

Watch the TutorialWatch on YouTube & Subscribe to our Channel

Download the Example FileUse this file to follow along with the examples in the video.

5 XLOOKUP Alternatives.zipDownload

Why Look Beyond XLOOKUP?XLOOKUP is great for simple, single-match lookups, but there are cases where you need more flexibility—like returning multiple matches, handling missing values, or performing lookups across multiple datasets. That’s where these five powerful alternatives come in.

1. XMATCH – Find the Row NumberUnlike XLOOKUP, which returns the lookup value, XMATCH returns the position of that value in a range. This is useful when you need to dynamically reference row numbers in other formulas.

How It Works:

Use XMATCH to locate the row position of a value. This returns the row number of the dataset, not necessarily the sheet row number.
To find the exact row number in the worksheet, combine XMATCH with the ROW function.

When to Use: Quickly locate where a value appears in a dataset for further analysis.

Bonus: You can wrap XLOOKUP in the ROW function to return the row number of the sheet for the matching value. This works because XLOOKUP actually returns a range instead of a value.

2. COUNTIF – Count MatchesXLOOKUP stops at the first match, but what if you need to count how many times a value appears? COUNTIF is the perfect tool for this.

How It Works:

COUNTIF scans a range and counts the occurrences of a given value.
If no match is found, it returns 0, making it easy to check for missing data.

When to Use: Find duplicate entries or quickly verify if a value exists multiple times in a dataset.

Pro Tip: If COUNTIF returns a zero, then the value does not exist in the lookup range.

3. FILTER – Return Multiple MatchesUnlike XLOOKUP, which returns just one result, FILTER can return all matching values dynamically.

How It Works:

FILTER extracts all rows where a condition is met.
It creates a spill range, displaying multiple matches automatically.

Tip: If working inside a table, use ARRAYTOTEXT to display the results in a single cell.

When to Use: When you want to retrieve all employees assigned to a project or all orders from a single customer, for example.

4. Pivot Tables – Compare Multiple ListsFor quick comparisons—such as checking which customers appear in multiple datasets—a Pivot Table is the best tool.

How It Works:

If we have two datasets, such as a list of volunteers from last year and a list from this year, and we want to know which volunteers are found in both lists:

Stack your datasets into a single table, making sure there is a column that identifies the year.
Create a Pivot Table with Year in the Columns area and Emails in the Rows area as well as the Values area.
The count values indicate how many times the person appears in the list, so you can easily spot multiples.

When to Use: Instantly identify returning customers or employees who worked in multiple years.

Pro Tip: Checkout my post on How to Compare Two Sheets in Excel: Bank Reconciliations Made Easy for more details on this technique.

5. Power Query – Automate Lookups Across Large DatasetsFor large datasets or recurring lookups, Power Query offers a no-formula approach.

How It Works:

Use Merge Queries in Power Query to combine tables based on a common column.
Select only the fields you need and output a clean dataset.

Tip: If you're unfamiliar with Power Query, check out our Power Query Pro Course to master data automation.

When to Use: Best for regular data imports where lookup values change frequently.

Which Alternative Should You Use?

| Scenario | Best Alternative | | --- | --- | | Find the row number of a match | XMATCH | | Count occurrences of a value | COUNTIF | | Return multiple matching results | FILTER | | Compare lists and identify multiples | Pivot Tables | | Automate lookups in large datasets | Power Query |

Each of these methods extends Excel’s lookup capabilities beyond what XLOOKUP alone can do.

ConclusionMastering these alternatives will level up your Excel skills, making your lookups more powerful and flexible.

Which method will you try first? Leave a comment below and let us know!

PS – I mentioned in the video that I have a helpful post on Excel Tables. You can access that here: Excel Tables Tutorial Video

Link to post: 5 XLOOKUP Alternatives I Can’t Live Without

View Details

Bottom Line: Walk through five essential macros I use daily to clean up data, format sheets, and improve workflow efficiency—saving hours every week.

Skill Level: Intermediate

Watch the TutorialWatch on YouTube & Subscribe to our Channel

Download the Example FileThe workbook I use in the video can be downloaded by clicking below. This file also contains the code for all 5 macros that we cover in this post (and a few extra).

Every Day Excel Macros Files.zipDownload

1. Fill Down Blank Cells InstantlyBlank cells can be a problem when working with data, especially for pivot tables and formulas that depend on complete datasets. Instead of manually filling them in, this macro quickly finds each blank cell in a column and fills it with the last non-blank value above it. The result is a clean, fully populated column with values converted to static entries to prevent accidental changes.

Quickly fills gaps in exported data
Prevents errors in calculations and analysis

2. Automatically Format a Sheet TitleClick to enlargeA properly formatted sheet title makes a workbook more readable, especially when sharing reports. This macro inserts a new row at the top, merges and centers the title, applies bold formatting, and uses the sheet name as the default title. Instead of manually adjusting headers every time, you can format them instantly with one click.

Creates a polished, professional look
Standardizes formatting across multiple sheets

3. Create a Table of Contents for Easy NavigationLarge workbooks with dozens of sheets can be frustrating to navigate. This macro automatically generates a Table of Contents on a new sheet, listing all sheet names with clickable hyperlinks. It even adds a link taking you back to the TOC that you can paste onto each sheet, allowing you to return to the main index in one click.

Saves time navigating large workbooks
Provides an easy reference for collaborators

BONUS! If you prefer a table of contents that has an image tile of each page, for a more visual-based navigation experience, I have a post that explains it in more detail.

And I also have a post on a macro for a table of contents that updates automatically when changes are made to the workbook.

4. Instantly Format Pivot ChartsClick to enlargeExcel’s default pivot charts often require tedious manual adjustments to look polished. This macro applies a clean, professional format instantly—it sets a descriptive title, adjusts spacing, applies a consistent color scheme, and removes unnecessary gridlines and clutter. With a single click, your charts go from raw data visuals to boardroom-ready presentations.

Standardizes pivot chart formatting for a polished look
Eliminates the need for repetitive manual adjustments

5. Backup Your Workbook with One ClickBefore making major edits, it’s always a good idea to save a backup. This macro duplicates the current file, appends a timestamp to the filename, and saves it in the same folder—no need to manually rename anything. It’s a quick and effortless way to create a safety net.

Protects against accidental data loss
Provides an automatic version history

Customize Your Own My Macros TabClick to enlargeIf you're using macros regularly, adding them to a custom “My Macros” tab in Excel can make your workflow even more efficient. This allows you to access your favorite macros with a single click—no more opening the VBA editor or hunting through menus.

Use this tutorial to make the customized tab: How to Add Macro Buttons to the Excel Ribbon or Quick Access Toolbar

I also have a tutorial on how to use ChatGPT to help with the macro writing process.

Do More with Macros and VBAMacros like these are built using VBA (Visual Basic for Applications), a computer programming language developed by Microsoft. If you'd like to learn more about using VBA to accomplish amazing things in Excel, I recommend our in-depth training program, The VBA Pro Course. It's got practical and valuable information whether you are just starting with VBA or already have plenty of expertise.

ConclusionThese five macros help automate common Excel tasks, saving hours of manual work. Which one will you try first? Or do you have a favorite macro of your own? Drop a comment below!

Link to post: 5 Excel Macros I Use Every Day to Save HOURS

View Details

Bottom Line: ChatGPT can be a valuable assistant for automating Excel tasks, but understanding Excel's built-in tools like VBA is essential to getting the best results.

Skill Level: Intermediate

Watch the TutorialWatch on YouTube & Subscribe to our Channel

Download the Example File Automate Excel with ChatGPT.zipDownloadODA Prompting Framework – Excel Campus.pdfDownload

AI in Excel: Hype or Help?There’s a lot of debate around AI tools like ChatGPT and their ability to assist with Excel tasks. Some users love how they streamline workflows, while others find them unreliable or error-prone. This post explores how ChatGPT can help automate Excel, its limitations, and a three-part prompting framework to improve results.

Creating a VBA Macro with ChatGPTIn the video example, our goal is to export multiple Excel sheets as CSV files automatically. Instead of writing a VBA macro from scratch, we’ll use ChatGPT to generate the code.

Generating the VBA Code1. Providing the Prompt – The initial request asked ChatGPT to generate a VBA macro to export selected sheets as CSV files.

Copying the Code – We copied the AI-generated VBA code into the Visual Basic Editor (Alt + F11) and placed it in a new module (Alt + IM).
Testing the Macro – Before running the code, I recommend two precautions:
1. Reading through the code/comments to understand the logic.
Saving a backup copy of the file in case the macro causes unexpected issues.

Troubleshooting ErrorsWhen running the macro, an error appeared:

Runtime Error 1004 – “You cannot copy or move a group of sheets that contain a table.”

By debugging the macro, it became clear that Excel tables were preventing the sheets from being copied. After multiple attempts to get ChatGPT to resolve the issue, the final fix required a manual code adjustment: selecting each sheet before copying.

So this is a good example of how AI can often save a ton of work, but human knowledge and experience is still needed to cross the finish line.

The ODA Prompting FrameworkTo improve ChatGPT’s performance with Excel automation, use the ODA Framework:

O – Overview: Provide a brief explanation of the task.
D – Details: Give step-by-step instructions to minimize assumptions.
A – Ask Questions: Request clarification before ChatGPT generates a response.

Example of a Better Prompt

“I would like to get your help with writing a VBA macro for Excel.
The goal of the macro is to export or save each selected sheet to a CSV file. Each sheet contains an Excel Table, so the macro will need to run the following steps to work around Excel limitations when selecting multiple sheets with Tables.

The macro should prompt the user on where to save the CSV files.

Loopthrough the selected sheets.

Select an individual sheet.

Copy that sheet to a new workbook.

Save the file as a CSV file.

“Do you have any questions on this macro?”

This new prompt follows my ODA framework.

By following this framework, ChatGPT generated more accurate responses and even included filename validation—something not initially considered.

Final Thoughts: Is ChatGPT Useful for Excel Automation?ChatGPT can be a powerful assistant for automating Excel tasks, but it's important to understand both its strengths and its limitations.

Where ChatGPT Excels:

Generating Starter Code: It can quickly produce VBA macros and scripts, saving time on repetitive coding tasks.
Explaining Concepts: AI-generated responses can help clarify coding principles, making it easier to learn new techniques.
Speeding Up Problem-Solving: Instead of manually searching for answers online, you can get quick solutions and troubleshooting tips.

Potential Challenges:

Debugging Required: AI-generated code isn't always flawless, so having some VBA knowledge is helpful for troubleshooting.
Manual Review Needed: For critical tasks, reviewing and testing the code is essential to ensure it functions correctly.
Handling Complex Business Logic: AI may not fully grasp specific workflows or nuanced business rules, requiring human oversight.

Microsoft calls its AI “Copilot,” and that’s exactly how it should be used—as an assistant, not a replacement for Excel expertise.

Need Help with VBA?If this tutorial has you thinking that you could use some help upping your VBA game, we've got you covered. Register for my free Excel Blueprint Webinar to get started.

ConclusionAI tools like ChatGPT can save time in Excel, but they still require a human touch to ensure accuracy. Have you tried using AI to automate Excel? Leave a comment below and share your experience!

Link to post: Automating Excel with ChatGPT: Can AI Really Help?

View Details

Bottom Line: Master these essential Excel features to become a power user and streamline your workflows with automation, formulas, and AI-driven tools.

Skill Level: Intermediate to Advanced

Watch the TutorialWatch on YouTube & Subscribe to our Channel

Download the Example FileFollow along with the same workbook used in this post.

Excel Power User Checklist.xlsxDownload Excel Power User ChecklistIf you want to level up your Excel skills, this checklist is for you. These 15 essential Excel features will help you analyze data faster, automate tedious tasks, and build powerful reports with ease.

Whether you’re managing financial models, databases, or dashboards, these tools will transform the way you work in Excel. Let’s dive in!

1. Simple FormulasEvery Excel user needs to understand basic formulas. Simple arithmetic operations like addition, subtraction, multiplication, and division form the foundation of Excel calculations. You can enter a formula as simple as =A1+B1 or use built-in functions like SUM to speed up your work.

Mastering simple formulas is essential before diving into more advanced techniques.

2. FunctionsFunctions are formula templates that Excel has prewritten. Understanding functions is the foundation of Excel proficiency. Key functions every power user should know include:

SUM, AVERAGE, COUNT – Essential functions for basic calculations.
MAX & MIN – Identify the highest and lowest values in a dataset.
COUNTIF & SUMIF – Apply conditions to count or sum specific values.

These basics are just the tip of a very large iceberg when it comes to functions. Here are 50 top functions that you can print and use as a checklist as you increase your proficiency and master each function.

50 Excel Functions for Data Analysts.pdfDownload3. Data Types & Number FormattingExcel supports various data types, including numbers, text, and Boolean (TRUE/FALSE) values. Applying the right number formats ensures accuracy and readability. Use Ctrl + 1 to open the Format Cells window and customize your number formats.

4. Excel TablesExcel Tables make working with data easier by enabling automatic filtering, sorting, and structured referencing. They expand dynamically when new data is added, ensuring formulas update automatically.

Additionally, the Table Design tab provides quick formatting options, a Total Row for automatic calculations, and the ability to insert Slicers for easier filtering. While tables are great for organizing data, they may not be ideal for extremely large datasets or certain VBA applications.

To learn more about Excel Tables, start here: Excel Tables Tutorial Video

5. Conditional Formatting for Quick InsightsConditional Formatting helps you visually analyze data by highlighting key patterns. Whether identifying duplicates, outliers, or trends, this tool applies formatting rules based on cell values. You can highlight top-performing sales, overdue payments, or cells that meet specific conditions—all without manually scanning the data.

6. Lookup FormulasModern Excel users should be familiar with XLOOKUP, which replaces older functions like VLOOKUP and HLOOKUP with greater flexibility. With XLOOKUP, you can search for a value in a column and return a matching result from another column—without worrying about data being sorted.

Click to enlargeIf you need to retrieve multiple matching results, the FILTER function is a game-changer. It dynamically extracts and displays all matching records, making it perfect for customer data, order history, and financial transactions.

7. Data ValidationEnsuring accurate data entry is essential in Excel. Data Validation allows you to restrict input values, ensuring consistency and reducing errors. One of the most common uses is creating drop-down lists based on existing data. This feature helps prevent typos and ensures uniformity in datasets.

8. Automating Data Cleanup with Power QueryPower Query is Excel’s built-in data automation tool that allows you to clean, transform, and merge data from multiple sources with just a few clicks. Instead of manually editing CSV exports each week, Power Query enables you to refresh data automatically and apply predefined transformation steps. This is a massive time-saver for anyone dealing with repetitive data cleanup tasks.

For more on Power Query, check out this tutorial: Power Query Overview: An Introduction to Excel’s Most Powerful Data Tool

9. Pivot TablesPivot Tables are one of the most powerful and flexible tools in Excel for summarizing large datasets. With drag-and-drop functionality, you can quickly group, filter, and analyze data to generate instant reports. Whether tracking sales trends, inventory levels, or financial performance, Pivot Tables allow you to gain insights without writing complex formulas.

10. Pivot Charts & Slicers for Interactive ReportsPivot Charts take Pivot Tables to the next level by visualizing summarized data in interactive charts. With Slicers, you can filter data dynamically, making it easier to explore key trends.

Combining multiple Pivot Tables, charts, and slicers can create interactive dashboards, offering a streamlined way to present data insights.

11. Power Pivot for Advanced Data ModelingFor advanced reporting, Power Pivot allows users to work with large datasets across multiple tables using data relationships. It eliminates the need for manual lookups by creating structured data models.

Power Pivot also introduces DAX (Data Analysis Expressions), a powerful formula language used to calculate KPIs, variances, and complex aggregations.

Learn more about how to use Power Pivot here: [LINK?]

12. LET Function for Efficient FormulasThe LET function improves formula efficiency by storing values as variables within a formula. Instead of repeating the same calculation multiple times, LET enables you to define intermediate results and reference them throughout the formula. This approach enhances readability, performance, and ease of troubleshooting, especially in large workbooks.

Walk through an example of the LET function here: Intro to Excel’s LET Function: Simplicity + Efficiency

13. LAMBDALAMBDA enables users to create custom Excel functions without VBA. Instead of copying a complex formula repeatedly, you can define it once and reuse it across your workbook. This feature allows users to build reusable, dynamic calculations, making formulas easier to maintain and share.

14. VBA + Office ScriptsAutomation in Excel is made possible through VBA (Visual Basic for Applications) and Office Scripts.

VBA allows users to write macros to automate repetitive tasks, such as generating reports, cleaning data, or formatting sheets.

Office Scripts, a newer alternative for Excel on the web, provides a cloud-based solution for automating workflows without traditional coding.

15. AI & Copilot in ExcelExcel’s AI-powered features are evolving rapidly, with Copilot leading the way. AI tools in Excel can generate formulas, analyze data, and even debug Python scripts.

While AI can significantly speed up your workflow, it’s important to double-check the results for accuracy.

Outside of Excel, AI-powered assistants like ChatGPT, Gemini, and Claude can help with Excel troubleshooting, VBA coding, and complex formula writing.

ConclusionMastering these 15 skills will elevate your Excel expertise and allow you to analyze data, automate workflows, and create dynamic reports with confidence. If you'd like to take the next step in become a Power User in Excel, try my free Modern Excel Blueprint Webinar.

Which features do you rely on most? Leave a comment below and share your favorite Excel tips!

Link to post: Unlock Your Excel Potential: The Power User Checklist

View Details

Bottom Line: Excel’s new dark mode is finally here! Learn how to enable it, its benefits, its limitations, and how it could be improved.

Skill Level: Beginner

Watch the TutorialWatch on YouTube & Subscribe to our Channel

How to Enable Dark Mode in ExcelExcel now has an official Dark Mode, allowing you to switch from the traditional light theme to a sleek, dark interface. You can toggle between these modes using the new Switch Modes button on the View tab—but there are a few settings you need to check first.

Where to Enable the Dark Mode ButtonTo access the Switch Modes button:

Go to File > Account.
Under Office Theme, select Use System Setting or Black.
If using Use System Setting, you must also enable Dark Mode in Windows settings under Personalization > Colors.

If Windows is in Light Mode, the Switch Modes button won’t appear in Excel.

Dark Mode in ActionSwitching to Dark Mode inverts most of Excel’s interface colors, changing the default white grid to black, with white text. Colored text and backgrounds may adjust to different hues to maintain contrast.

While Dark Mode works well in most cases, there are some limitations to keep in mind.

Limitations of Dark Mode Hidden Functionality – The Switch Modes button is difficult to access unless you adjust Office and Windows theme settings. It would be so much better if that toggle button were available regardless of what theme you're using. * Chart & Shape Issues – Text colors in charts, slicers, and shapes may not adapt properly, leading to poor visibility. * Keyboard Shortcut Could Be Improved – The shortcut to toggle Dark Mode is Alt + W + M1, which requires two keystrokes. A simpler Alt + W + D* would be more intuitive.

Quick AccessIf you switch frequently between Light and Dark Mode, you can:

Right-click the Switch Modes button and select Add to Quick Access Toolbar for faster toggling.

Dark Mode Is Still in BetaCurrently, Dark Mode is only available in Excel Beta, so if you don’t see it yet, that’s why. It may take some time before it reaches all Excel users.

ConclusionDark Mode in Excel is a great addition for users who work in low-light environments or simply prefer a sleek, modern look. However, hidden settings, limited functionality in charts and shapes, and the keyboard shortcut issue leave room for improvement.

Would you use Dark Mode in Excel? Leave a comment below and let us know!

Link to post: Excel’s Dark Mode: What Works and What Needs Improvement

View Details

Bottom Line: Choosing the right version of Excel depends on your needs. Learn about Beta, Microsoft 365, standalone versions, and the web version to find out which is best for you.

Skill Level: Beginner

Watch the VideoWatch on YouTube & Subscribe to our Channel

If you've ever wondered, “What’s the best version of Excel?”, you're not alone. With multiple versions available—including Microsoft 365, standalone editions, beta releases, and the web version—it can be confusing to know which one is right for you.

In this post, we’ll break down the differences between each version, their pros and cons, and how to stay updated on the latest Excel features.

The Excel Beta Version (Insider Program)The Beta version of Excel (also known as the Insider Program) is where Microsoft releases and tests new features before rolling them out to all users.

✔️Pros: First access to the latest features. * Free for Microsoft 365 subscribers. * Allows users to test and provide feedback* on upcoming changes.

❌Cons: Compatibility issues – If others you share files with don’t have Beta, some features may not work for them. * Potential bugs – Since it’s a testing version, Excel may be unstable or crash occasionally. * Not ideal for work computers*—Since bugs can potentially be an issue, I recommend Beta for personal use or a secondary device.

How to Get It: If you’re a Microsoft 365 subscriber, you can join the beta program for free. Click here to learn more about it. If you're not a 365 subscriber but want to be, start here.

Click to enlargeMicrosoft 365 (Subscription Version of Excel)Microsoft 365 is the most up-to-date standard version of Excel and includes:

Regular feature updates (though usually later than Beta).
Cloud integration with OneDrive, SharePoint, and Teams.
Access to Copilot AI features (for eligible plans).

This version is ideal for users who want the latest features without the instability of Beta.

📌 How to Check Your Update Channel:
Go to File > Account > About Excel to see whether you’re on the Beta Channel, the Current Channel (receives updates sooner), or Semi-Annual Enterprise Channel (gets updates less frequently).

Click to enlargeStandalone Versions (Excel 2021, 2019, and Earlier)Many users still rely on older, standalone versions of Excel (Excel 2019, Excel 2016, etc.). These versions:

Do not receive feature updates.
Are one-time purchases (no subscription required).
Are still widely used, meaning files created in these versions are often the most compatible when sharing with others.

Many users continue to use these older versions because they've already purchased them and don't want an ongoing subscription.

The Free Web Version of ExcelThe web version of Excel (accessible at excel.new) offers a browser-based alternative. It has some great advantages:

Universal feature access – No compatibility concerns.
Completely free – Available to everyone.
Great for collaboration – Works seamlessly with OneDrive and SharePoint.

However, it lacks some advanced features like Power Query, VBA, and complex formulas that power users rely on.

How to Stay Updated on Excel FeaturesTo track when new features roll out:

Check the Excel Blog – Microsoft posts monthly updates detailing new feature releases and rollout status.
Look at the Update Table – Found at the bottom of Microsoft's monthly Excel Blog post, it lists what features are available in different versions.

Click to enlargeWhich Version of Excel Is Best for You?It depends on your needs:

Want the latest features first? → Try the Beta version (but expect occasional bugs).
Need stability with regular updates? → Microsoft 365 is your best bet.
Prefer a one-time purchase? → Stick with Excel 2021 or 2019.
Looking for a free option? → The web version works great for essential tasks.

If you are consistently sharing files with vendors, customers, or other users, the best version of Excel is probably the version that is most compatible with others.

ConclusionNo single version of Excel is “best” for everyone—it all depends on your workflow and compatibility needs. Whether you’re using Beta for cutting-edge updates or sticking with a stable long-term version, knowing the differences can help you make the right choice.

Which version of Excel do you use? Leave a comment below and let us know!

Link to post: Which Version of Excel Is Best?

View Details

Bottom Line: Save time and effort cleaning up exported data in Excel with these tips for both manual adjustments and automated transformations using Power Query.

Skill Level: Intermediate

Watch the TutorialWatch on YouTube & Subscribe to our Channel

Download the Example FileFollow along with the same workbook used in this post.

Register.xlsxDownload Cleaning Up Exported DataExporting data from systems like QuickBooks can leave you with messy spreadsheets that require cleanup before analysis. In this post, we’ll cover step-by-step techniques for cleaning data manually and show how you can use Power Query to automate the process, saving you time and effort. First, let's look at cleaning up data manually.

Manual Data CleanupWhen cleaning up exported data manually, start by identifying common issues like merged cells, improper formatting, and inconsistencies in data types. Here are some practical steps:

1. Handling Merged Cells and Tabs Unmerge cells: Select the merged cell, then go to Home > Merge & Center to unmerge it. * Use Text to Columns: For text with tabs, colons, or other delimiters, use Data > Text to Columns* to split it into separate cells.

2. Converting Dates Stored as TextSome exported dates may look like dates but are stored as text. Convert them to date format using this trick:

Select the cells, press Ctrl + H for Find and Replace, and replace the date delimiter (e.g., “/”) with itself. This forces Excel to reevaluate the data in the cells and recognize the values as dates.

3. Fixing Numbers Stored as TextNumbers exported as text may cause issues with formulas or lookups. To convert them, select the column, click the Error Box at the top, and choose Convert to Number.

4. Formatting and Adjusting Column Heights Remove inconsistent row heights caused by wrapped text by selecting all cells, toggling Wrap Text* twice, and then autofitting column widths. * Adjust the font size and type for better readability (e.g., increase to size 11).

5. Splitting Sub-AccountsIf a column contains sub-accounts separated by colons (or other delimiters):

Use Data > Text to Columns, select the delimiter (e.g., “:”), and split the data into multiple columns for easier analysis.

Automating Data Cleanup with Power QueryManual cleanup works, but when dealing with frequent exports, automation is a game-changer. Power Query can automate repetitive steps, ensuring consistent results every time.

1. Load the Data into Power Query Starting with a blank workbook, go to Data > Get Data > From File > From Workbook*, select your exported file, and open it in Power Query. You'll see your data in the Power Query Editor, like this:

2. Transform the DataPerform the following transformations in Power Query:

Remove unnecessary rows: Use Remove Top Rows to delete unwanted headers or blank rows.
Promote headers: Bring the first row of data up as the column headers.
Convert data types: Ensure dates, numbers, and text columns are assigned the correct data types.
Split columns: Use the Split Column by Delimiter option to separate sub-accounts or combined values.

3. Save and Load Cleaned DataOnce your transformations are complete, choose Close & Load to output the cleaned data to a new sheet.

4. Automate with RefreshFor recurring tasks, save the Power Query steps:

Save the cleaned workbook.
Replace the exported file in the same folder with a file that contains new data.
Refresh the query by right-clicking the table and selecting Refresh (or use the shortcut Alt + F5).

If you could use some help with learning Power Query, Here are three resources:

Start with this tutorial: Power Query Overview: An Introduction to Excel’s Most Powerful Data Tool.
Watch my webinar on modern Excel “power” tools.
From there you can take our Power Query Pro Course for in-depth training.

ConclusionCleaning up exported data doesn’t have to be a time-consuming task. Whether you prefer manual adjustments or automated workflows with Power Query, these techniques ensure your data is ready for analysis in no time.

Which cleanup method works best for you? Leave a comment below and share your experience!

Link to post: Mastering Data Export Cleanup in Excel

View Details

Bottom Line: Master the 2-way XLOOKUP to streamline your Excel skills—covering everything from basic setup to advanced error handling, making data retrieval more efficient than ever.

Watch the VideoWatch on YouTube & Subscribe to our ChannelDownload the Excel FileYou can access the same file you see in the video by downloading it below.

2-Way XLOOKUP – Follow Along.xlsxDownload Learning Excel’s lookup formulas can be a game-changer for streamlining your data processes. Here, we’ll explore 2-way XLOOKUP to tackle double lookups with confidence, retrieving data based on both rows and columns.

Basic 2-Way XLOOKUP Setup1. Starting with Rows: Using the data set provided in the file above, begin by typing =XLOOKUP( and setting your team (row) as the lookup value. Select your row range, and press F4 to lock the range. 2. Adding Columns: Next, nest another XLOOKUP to handle the plan (column) lookup by selecting the plan header row and applying F4 to anchor it. 3. Final Return Array: For the return array, select the entire range of values in the source data to allow dynamic row/column results, providing an efficient way to cross-reference values.

Your formula should look something like this:

=XLOOKUP(J6,$B$6:$B$10,XLOOKUP(K6,$C$5:$F$5,$C$6:$F$10))

Advanced Error Handling with 2-Way XLOOKUPHandling errors effectively in a 2-way XLOOKUP setup requires managing both row and column lookups separately, allowing you to see exactly which part of the lookup is causing an issue. Here’s how you can achieve this with two different approaches:

Error Handling for Row Lookup (Team Not Found):
If the team name you’re trying to look up doesn’t exist in the data, Excel would return an error. By adding if_not_found to the first XLOOKUP (the row lookup), you can set up a custom error message that clearly indicates the issue is with the row.
- For example, append , "Team NF" to the end of the row lookup within the XLOOKUP function. Here, “Team NF” stands for “Team Not Found” and will appear any time the lookup value in the team row isn’t present.
Error Handling for Column Lookup (Plan Not Found):
Unlike the row lookup, if the plan you’re referencing isn’t available, the entire XLOOKUP will still attempt to complete. Since this component doesn’t use the if_not_found argument, wrap this part of the formula in an IFERROR statement. This way, if the XLOOKUP can’t find the specified column header, it will display a custom error for the plan.
- For example, **IFERROR(XLOOKUP(...), "Plan NF")** will ensure the formula returns “Plan NF” for a missing plan, giving you separate, specific messages for missing rows or columns.

This handling provides clear error messages that show where the data retrieval issue lies, making it easier to diagnose and resolve mismatches in your data.

Working with Excel TablesSwitching your data to Excel tables can automate formula management. Instead of fixed ranges, reference the header row and entire table to keep formulas dynamic as the table grows.

Using Excel Tables, our formula would look something like this:

=XLOOKUP([@Team],tblRate[Team],XLOOKUP([@Plan],tblRate[#Headers],tblRate))

Click to enlargeFor more information on how to get started using Excel Tables, check out this tutorial: Excel Tables Tutorial Video – Beginners Guide for Windows & Mac

Using XLOOKUP for Range Lookups (Closest Match)For lookups where you need to find a value within a range rather than an exact match, XLOOKUP’s Closest Match option offers a solution. This is particularly useful in tier-based data, where the exact lookup value may not exist in the dataset but should fall within a specific range.

Click to enlargeIn this scenario, set up your data to have tier minimums in a separate row to act as lookup references. For example, if you’re working with sales ranges, the tier minimums row might include values like 0, 20,000, 50,000, and so on.

Step-by-Step for Closest Match:
- Step 1: Begin with your primary XLOOKUP for the row (e.g., team) and select the tier minimums row as the array for the column XLOOKUP.
- Step 2: Instead of an exact match, use match_mode = -1 for Next Smaller Item. This tells Excel to return the closest, smaller tier when the exact match isn’t available. For example, if you’re looking up a sales value of 98,000, the formula will find the tier below it (50,000) if an exact match doesn’t exist.

This ensures the lookup references the appropriate range and calculates the corresponding value accurately. It’s especially helpful when ranges are large or data is not in strict order, as XLOOKUP can manage unordered lists and still retrieve the correct range.

Here's how the formula looks for our example:

=XLOOKUP(J7,$B$7:$B$11,XLOOKUP(K7,$C$4:$F$4,$C$7:$F$11,,-1))

Comparing XLOOKUP with INDEX MATCHWhile both 2-way XLOOKUP and INDEX MATCH offer similar results, XLOOKUP has:

Fewer functions, making it simpler to write and troubleshoot.
Superior error-handling capabilities, allowing specific error messages for rows and columns separately.

If you'd like to learn more about how INDEX MATCH works, watch this video: How to Use Index Match Instead of Vlookup

Practice ChallengeFinally, test your skills with this 2-way XLOOKUP practice challenge. Download the Excel file [EMBED] and try writing formulas to complete each example.

ConclusionUsing a 2-way XLOOKUP formula enables you to look up complex data efficiently. This tutorial covered basics to advanced applications, equipping you to handle real-world scenarios in Excel. Leave a comment and let us know how you’re applying this technique!

Black Friday Offer – Modern Formulas Ready to master Excel's most powerful new features? For a limited time, our updated Modern Formula course will be available at a special Black Friday price.

You'll learn how to harness game-changing innovations like XLOOKUP and Dynamic Array Formulas, helping you solve complex problems with elegant solutions that automatically adapt to your data. Whether you're tired of maintaining complex nested formulas or want to stay ahead of Excel's rapid evolution, this comprehensive 31-lesson course will transform how you work.

Click here to join the waitlist and get notified of our Black Friday Sale– and if you're already an Elevate Excel member, you can start learning immediately at no additional cost!

Link to post: 2-Way XLOOKUP in Excel: Beginner to Advanced

View Details

Bottom Line: With Excel's latest update, you can now create dynamic reports in multiple languages, making your spreadsheets more accessible and user-friendly for international audiences.

Skill Level: Intermediate to Advanced

Watch the TutorialWatch on YouTube & Subscribe to our ChannelDownload the Excel FileYou can follow along using the same Excel file that I used in the video.

TRANSLATE Function.xlsxDownload Getting Started with the Translate FunctionExcel’s TRANSLATE function is a new feature, allowing you to translate text in your reports with ease. Here's how:

In any cell, type =TRANSLATE.
Select the text you want to translate (for example, cell B4), choose the source language (for example, “en” for English), and the target language (such as “es” for Spanish).
Hit Enter and watch your text transform into the selected language.

This function taps into Microsoft’s translation service, and though it's currently in Beta, it's incredibly effective for multilingual reports.

Creating a Dynamic Report with Language SelectionA big part of the magic happens when you allow users to select their desired language via a dropdown menu. Here's how to create that dropdown.

Prepare Your Language List:
- Create a table or list of languages and their codes in one of your worksheets. You can copy this from Microsoft's Translation Service or from the Excel file at the top of this post.
Create the Dropdown:
- Select the cell where you want the user to choose the language.
- Go to the Data tab on the ribbon and click Data Validation.
- In the Data Validation window, select List from the dropdown.
- In the Source field, select the range of cells that contain the languages you prepared earlier (the entire language column).
- Click OK, and your dropdown list is now ready!
Link to the Translation Code:
- Use XLOOKUP to match the selected language from the dropdown to its corresponding code (e.g., “en” for English, “fr” for French). This code will drive the translation in your report.

Using the PIVOTBY Function for Summary ReportsThe new PIVOTBY function simplifies creating summary reports by automatically grouping your data. Here’s how to set it up:

Select your Row Fields (translated Month column).
Set your Column Fields (translated Source column).
Select your Values (Qty column).
Finish with a SUM to generate the summary.

This functionality allows you to present the same report in different languages without manually adjusting data.

For more about how to use PIVTOBY, check out this tutorial.

Note: If you want to create a chart from your data, be sure to translate the Chart Title using the TRANSLATE function as well. You can also wrap it in the PROPER function to ensure that all of the words translated are capitalized. See the video above for details on this technique.

Optimizing PerformanceOne challenge with translating large datasets is performance. To optimize your report:

Create a Translation Table with unique values instead of translating each cell individually. Follow the steps in the video to create this table using Power Query.

Click to enlarge2. Use XLOOKUP twice to reference this table in your Customer Data—first to find the word in the original language, and then to pull the corresponding translation based on the selected language. This method drastically reduces redundant translations and improves efficiency, as it eliminates the need to translate each cell individually. (See the video for a breakdown of the XLOOKUP arguments.)

For more details on how to use Power Query, check out this post: Power Query Overview: An Introduction to Excel’s Most Powerful Data Tool

Offline Translation with XLOOKUPThe TRANSLATE function requires an internet connection, but you can make your reports offline-ready by preloading translations into a lookup table. Simply use XLOOKUP to pull from this static table instead of relying on the translation service.

This technique ensures that your reports function even when offline, making them more versatile.

Bonus: DETECTLANGUAGE FunctionIf there is text in a language that you can't identify, you can use the DETECTLANGUAGE function to return the language code. Use your language list to match the code to the language so you can tell what language you are looking at.

ConclusionWith Excel's new translation features, creating interactive and dynamic reports in multiple languages is easy. Whether you’re managing data for international teams or optimizing reports for multilingual audiences, these new tools allow you to streamline your process and improve your workflow.

Do you see yourself using the translation functions? Leave a comment below and let us know what you think.

Link to post: Create Interactive Excel Reports in ANY LANGUAGE

View Details

Bottom Line: Learn how to run a macro on a protected sheet while maintaining security in Excel.

Skill Level: Intermediate

In Excel, protecting a sheet can prevent unauthorized changes, but it can also block macros from running. However, you can enable macros to function on protected sheets with a few simple tweaks.

Allow Macros to Run on a Protected SheetBy temporarily unprotecting the sheet within your macro, you can make changes and then protect it again automatically.

Steps:

Open the Visual Basic for Applications (VBA) editor by pressing Alt + F11.
Write your macro code to unprotect the sheet, perform the desired action, and re-protect the sheet:

**Sub MyMacro() Sheets("Sheet1").Unprotect Password:="yourpassword" ' Your macro code goes here Sheets("Sheet1").Protect Password:="yourpassword" End Sub**

Press **F5** to run the macro.

Note: If the sheet is unprotected, the macro will still work. The Unprotect command will simply be ignored, and the rest of the macro will run as usual. You don’t need separate versions of the macro for protected and unprotected sheets. However, keep in mind that this macro will always leave the sheet protected by the end, regardless of its starting state.

Alternative: Run Macros Without Unprotecting the SheetIf you want to allow your macro to make changes to a protected sheet without unprotecting it each time, you can use the UserInterfaceOnly option in your code. This setting allows macros to make changes to the sheet, but keeps the protection in place for the user.

This means:

Users cannot make changes to the protected sheet.
Macros can run freely without needing to unprotect or re-protect the sheet.

Use the following line of code in your macro:

**Sheets("Sheet1").Protect Password:="yourpassword", UserInterfaceOnly:=True**

This tells Excel to protect the sheet for users but allows the macro to edit the sheet.

Note: While this may seem like a simpler solution than unprotecting and re-protecting, there is a disadvantage to this method. This setting only works for the duration of your session (until you close the workbook). You would need to rerun this protection command each time the workbook is opened.

WorkaroundOne workaround for this limitation is to run the protection macro every time the workbook opens. This can be done automatically with the Workbook_Open event, ensuring that the UserInterfaceOnly setting is applied each time the workbook is launched.

Here’s how to set it up:

Open the Visual Basic for Applications (VBA) editor by pressing Alt + F11.
In the Project Explorer window, find your workbook, and double-click on ThisWorkbook.
Add the following code to the Workbook_Open event:

Private Sub Workbook_Open()
Sheets(“Sheet1″).Protect Password:=”yourpassword”, UserInterfaceOnly:=True
End Sub

This will automatically apply the protection with the UserInterfaceOnly setting every time the workbook is opened, allowing your macros to continue working without unprotecting the sheet. Users will still be restricted from making changes, maintaining the sheet’s security.

This method ensures you won’t have to remember to reapply the protection each time, streamlining your workflow while keeping your sheet protected.

ConclusionRunning a macro on a protected sheet is simple with the right approach and can be done without needing to manually unprotect or re-protect the sheet.

We'd love to hear your feedback or answer any questions you have. You can reach us by leaving a comment below!

Link to post: Run a Macro on a Protected Sheet in Excel

View Details

Bottom Line: Discover why you're seeing the error message “PivotTable Field Name is Not Valid” and learn how to resolve it quickly.

Skill Level: Beginner

When creating or refreshing a Pivot Table, you might encounter the frustrating error message: “The PivotTable field name is not valid…” This error can seem vague, but it almost always relates to issues with your source data.

Click to enlargeHere’s what the error message means and how to fix it.

Why the Error HappensThe error typically occurs because there’s a problem with the headers or the data range in the source data used for the Pivot Table. Here are a few common causes:

Empty Headers: Every column in your source data needs a header. If any of the columns have a blank cell in the header row, Excel will display this error.
Merged Cells: If your data range includes merged cells in the header or data, this can confuse Excel when trying to read the field names.
Empty Columns: An empty column or row within the data range can trigger the error.
Deleted Data Source: If the source data has been moved or deleted after creating the Pivot Table, refreshing it will produce this error.
Selecting the Entire Worksheet: If you mistakenly select the entire worksheet (including empty rows and columns) when setting the data range, this can also cause the error.

Steps to Fix the Error1. Check for Missing or Blank HeadersExcel needs a label for each column to create a Pivot Table. If one of the column headers is missing or blank, the Pivot Table will not work. check to ensure every column has a unique header and no blank cells in the header row.

One of the best ways to make sure that there are never missing headers is to always use Excel Tables as the data source of your Pivot Tables. I talk more about that here: 5 Reasons to Use an Excel Table as the Source of a Pivot Table

Unmerge Any CellsPivot Tables cannot handle merged cells, especially in the header row. To fix this:
Select the merged cells, go to the Home tab, and click Unmerge Cells.
After unmerging, ensure each column has its own unique header.
Delete Empty Columns or RowsAn empty column or row within the data range can confuse Excel and cause the error. Check for and delete any empty columns in the data.
Ensure the Data Source Still ExistsIf the source data has been deleted or moved, the Pivot Table cannot refresh. You can restore the data or redefine the data source to point to the correct range.
Avoid Selecting the Entire WorksheetMake sure you are only selecting the relevant data range when creating a Pivot Table. Selecting the entire worksheet may include empty rows or columns, leading to this error.

ConclusionThis error can be frustrating, but it’s usually an easy fix once you know what to look for. By ensuring every column has a header, checking for merged cells and empty columns, and selecting the correct data range, you can quickly resolve the issue.

Again, using Excel Tables as the source of your Pivot Tables is a great way to avoid these errors. If you're not familiar with Excel Tables, I can help you get started here: Excel Tables Tutorial Video – Beginner's Guide for Windows & Mac

Did this post help you spot the reason for your error message? I'd love to hear if it was useful to you. Let us know in the comments below!

Link to post: “Pivot Table Field Name Is Not Valid”– How to Fix This Common Excel Error

View Details

Bottom Line: Learn how to combine multiple cells in Excel into one for cleaner, more organized data.

Skill Level: Beginner

When working with data in Excel, there are times when you need to combine or merge information from multiple cells into one.

Whether you're working with names, addresses, employee numbers, or any other type of data, knowing how to efficiently combine cells can save you time and make your data more readable.

Let’s look at three simple ways to combine cells in Excel:

Using the & (Ampersand) OperatorThe easiest method to combine text from different cells is by using the ampersand symbol (&). This method allows you to merge content while keeping full control over formatting.

Steps:

Click on the cell where you want the combined result.
In the formula bar, type =A1&B1. Replace A1 and B1 with the cells you want to combine.
Press Enter.

If you want a space between the values, modify the formula to include quotation marks with a space:
=A1&" "&B1

This method is simple and works for combining text, numbers, and more.

Using the CONCATENATE FunctionCONCATENATE is another method for combining text, though it has been replaced by the more versatile TEXTJOIN function in later versions of Excel.

Steps:

Select the cell where you want the combined result.
Enter the formula =CONCATENATE(A1, " ", B1). Replace A1 and B1 with the cells you want to combine.
Press Enter.

Like the ampersand method, this one allows for specific formatting but requires more typing.

Using the TEXTJOIN FunctionIn Excel 2016 and later, the TEXTJOIN function makes it easy to combine values from multiple cells while specifying a delimiter (like a space or comma) and skipping empty cells.

Steps:

Select the cell where you want the result.
Enter the formula =TEXTJOIN(" ", TRUE, A1:B1). The first argument, " ", specifies a space as the delimiter. The second, TRUE, means empty cells are ignored. The third is the data range you want combined, so replace A1 and B1 with the relevant cell names for your purpose).
Press Enter.

This function is powerful because it can handle large ranges of cells at once, and it’s easier to use than repeatedly entering the ampersand or CONCATENATE function.

Bonus Tip: Merging CellsIf you need to combine multiple cells into one but don't want to keep their contents (such as merging cells for formatting), you can use Excel's Merge & Center tool.

However, be cautious with this method as it will keep only the upper-left cell value, deleting the contents of all others.

ConclusionNow that you know how to combine cells in Excel, you can organize your data with ease. Try each method and see which one works best for your specific needs!

Which method do you see yourself using more frequently? Leave a comment below to let us know!

Link to post: How to Combine Cells in Excel

View Details

Bottom Line: Learn how to customize 10 essential settings in Excel to improve your workflow, save time, and enhance productivity.

Skill Level: Intermediate

Watch the TutorialWatch on YouTube & Subscribe to our ChannelDownload the Excel FileYou can follow along using the same Excel file that I use in the video.

Excel Settings.xlsxDownload Excel Settings Worth KnowingExcel offers a wide range of customizable settings that can streamline your workflow and improve productivity. With over 200 options to choose from, it can be hard to know where to start. In this post, we’ll focus on 10 essential settings that will save you time, simplify your tasks, and enhance your overall Excel experience.

Create Custom Lists for AutoFillHave you ever used the Fill Handle to auto-complete months or days? You can customize this feature by creating your own custom lists. Here’s how to set it up:
Go to: File > Options > Advanced > Edit Custom Lists
Then create a new list by typing your entries or import a list from an existing sheet.

With this setting, you can easily auto-complete regions, product names, or any other list specific to your work.

Change the Gridline Color for Better VisibilitySometimes borders can blend in with the default gridlines, making them hard to see. You can change the color of gridlines for better contrast:
Go to: File > Options > Advanced > Gridline Color
Then choose a bright color to help differentiate the gridlines from your custom formatting.

This gridline setting applies only to your current worksheet, not the whole workbook.

Turn Off Annoying Page Break LinesWhen previewing or printing your Excel sheets, page breaks can appear as dotted lines, which may be distracting. Here’s how to turn them off:
Go to: File > Options > Advanced > Display Options for this Worksheet
Then uncheck “Show page breaks.”

This will remove those dotted lines and give you a cleaner workspace.

Bypass the Backstage View for Faster Saving and OpeningBy default, pressing Ctrl` + `S or Ctrl` + `O opens the Backstage view. You can skip this and open the standard File Explorer instead:
Go to: File > Options > Save
Then check “Don’t show the Backstage when opening or saving files with keyboard shortcuts.”

Now you can quickly save or open files without the extra step.

Turn Off Structured References in Excel TablesExcel tables use structured references by default, which can confuse users unfamiliar with them. You can switch to regular cell references:
Go to: File > Options > Formulas
Then uncheck “Use table names in formulas.”

This way, formulas will use standard cell references for a more familiar approach.

If you're interested in more details on this topic, check out this tutorial.

Set a Default Pivot Table LayoutIf you don’t like the default compact layout of PivotTables, you can change it to a tabular format:
Go to: File > Options > Data > Edit Default Layout
Set your preferred PivotTable format (e.g., tabular layout with blank rows).
If you'd like to change even more settings, you can do so by clicking the PivotTable Options button.

Every new PivotTable you create will automatically adopt your chosen layout.

Disable Automatic Grouping of Dates in PivotTablesBy default, dates in PivotTables are automatically grouped by years, quarters, and months. To disable this:
Go to: File > Options > Data
Then check “Disable automatic grouping of dates in PivotTables.”

This allows you to display individual dates without automatic grouping.

Ungroup Dates in AutoFiltersWhen using AutoFilters, dates are collapsed by default, making it harder to find individual dates. You can change this:
Go to: File > Options > Advanced
Then uncheck “Group dates in AutoFilter menu.”

Now all dates will appear expanded, making it easier to filter specific dates.

Customize the Quick Analysis ToolThe Quick Analysis tool provides an easy way to analyze data. If you find it appearing too often:
Go to: File > Options > General
Uncheck “Show Quick Analysis options on selection.”

You can still access the tool by pressing Ctrl` + `Q whenever you need it.

Change Excel’s Default Workbook SettingsAnd finally, if you want all new Excel workbooks to open with only one sheet, want to collapse the search box, or prefer to use a specific font size:
Go to: File > Options > General
Then adjust settings to your preference..

This makes it easier to create workbooks that match your preferred style without manual adjustments.

Bonus: Automatically Keep Leading Zeros in NumbersWorking with numbers that have leading zeros, like ZIP codes? Excel automatically removes these unless you store the data as text. But there’s a new option in Beta to make this easier:

Go to: File > Options > Data > Automatic Data Conversion
Then uncheck “Remove leading zeros and convert to number.”

Now Excel will keep leading zeros without the need for apostrophes or manual text formatting.

This post dives deeper into Data Conversion settings, if you are interested in learning more.

ConclusionThese 10 Excel settings can make a big difference in how efficiently you work with data. Whether it's customizing lists or formatting tables, these tips will save you time and simplify your workflow. I encourage you to explore Excel options and tweak the settings to suit your needs.

What Excel settings do you find most useful? Let us know in the comments!

Link to post: Hidden Excel Settings that Save Time and Frustration

View Details

Bottom Line: Quickly sort your data from A to Z or smallest to largest with this Excel shortcut.

Skill Level: Beginner

Sorting your data quickly can make a big difference in speeding up your workflow. Chances are you've used the Sort & Filter button on the Home tab.

Whether you're organizing a list of names, numbers, or dates, Excel also has a keyboard shortcut to make sorting a breeze. You can use it to instantly sort your data alphabetically (A to Z) or numerically (smallest to largest).

Shortcut to Sort Data from A to ZHere’s the shortcut you can use to sort your data alphabetically or numerically in Excel:

Select the column of data you want to sort.
Then, Press Alt + H, S, S on your keyboard.

That’s it! Excel will sort the column you’ve selected in ascending order.

If you need to sort from Z to A (or largest to smallest), just press Alt + H, S, O instead.

This shortcut can be used for text, numbers, and dates, making it an all-in-one sorting solution for your everyday tasks.

ConclusionSorting your data in Excel doesn’t have to be time-consuming. With just a few keystrokes, you can get your lists in order and move on to your next task.

So, the next time you're working on a project that requires sorting, try using this shortcut. It's especially handy when you're dealing with long lists, saving you time and effort!

What's your favorite Excel shortcut? Leave a comment below!

Link to post: Shortcut for Sorting Data from A to Z

View Details

Bottom Line: Understanding the percentage change formula can help you make better decisions, especially when analyzing data for conversions, sales, or performance.

Skill Level: Beginner

Watch the TutorialWatch on YouTube & Subscribe to our ChannelDownload the Excel FileYou can access the simple Excel file I use in the video here.

Percentage Change Formula.xlsxDownload Percentage ChangeCalculating percentage change in Excel is essential for anyone who works with data. Whether you're analyzing website conversions, sales, or even sports statistics, knowing the correct formula can help you avoid costly mistakes. In this post, we will walk through the steps to accurately calculate percentage change

What is the percentage change formula? The formula to calculate percentage change is:

Percentage Change = (Value 2 – Value 1) / Value 1

This formula compares one value to another value and expresses the difference as a percentage.

Example Scenario: Website Conversion RatesLet’s start with an example. Suppose we have two webpages with different conversion rates:

Page A has a conversion rate of 3%.
Page B has a conversion rate of 5%.

At first glance, it might seem like the difference between these two pages is only 2%. However, when we apply the percentage change formula, we see the real difference is 66.7%, not 2%.

Formula: Percentage Change = (5% – 3%) / 3% = 2 / 3 = 66.7%

This shows that Page B is performing 66.7% better than Page A.

Conversion Rates with Different Data SetsThe reason we calculate percentage change instead of just comparing the number of conversions is because the number of visitors to each page differs. Let’s say:

Page A has 6,000 visitors, yielding 180 buyers.
Page B has only 100 visitors, yielding 5 buyers.

In this case, you cannot directly compare 180 to 5, but you can still compare the conversion rates (3% vs. 5%) to calculate percentage change, which remains 66.7%.

Calculating Percentage Change in ExcelLet’s now see how you can apply this in Excel. The way I like to remember the formula is simply

New minus Old over Old:
In Excel, your formula would be something like this:

=(D5 – C5) / C5
where D5 is the new value (5%) and C5 is the old value (3%).

Another way to calclulate percentage change is:

New over Old minus 1:
This simplified version of the formula would look like this in Excel:

=D5 / C5 – 1

Both formulas give the same result, but the second requires fewer keystrokes and might be easier to use in spreadsheets.

I tend to use the first way when I'm doing the math in my head, and the second way when I'm calculating using a calculator or in Excel.

Order of Numbers MattersWhat happens if we reverse the values? If Page A has a conversion rate of 5% and Page B has a rate of 3%, the percentage change becomes -40%, indicating that Page B is underperforming by 40%.

A Wide Range of UsesYou can also use percentage change formulas in other scenarios, like:

Sales Discounts: Compare the sale price to the original price.
Sports Statistics: Compare performance rates between two players.
Survey Results: Measure improvement between response rates.
Lost Shipments: Find out if your modified procedures are preventing loss.
Opt-In Rates: Does page setup affect how many signups you get?

Final ThoughtsWhether you prefer the formula New – Old / Old or New / Old – 1, mastering percentage change will help you make better data-driven decisions. Don't forget to teach this formula to your team—it could save you a lot of money!

Comments or questions? Click here to tell us your thoughts.

Link to post: How to Write a Percentage Change Formula in Excel

View Details

Bottom Line: Master the IF function with three conditions to make your Excel data analysis more powerful.

Skill Level: Intermediate

Download the Excel FileYou can download the Excel file that has the examples from this post here:

If Function with 3 ConditionsDownloadThe IF Function with 3 ConditionsThe IF function in Excel allows you to test a condition and return one value if the condition is TRUE and another if it's FALSE. (See this tutorial for a detailed explanation of how to write an IF function). But what if you need to check multiple conditions?

In this post, we’ll walk through how to use the IF function with three conditions to help you handle more complex logic.

Let’s break down how you can use nested IF functions or the IFS function to achieve this.

Using Nested IF FunctionsYou can use nested IF functions to test multiple conditions in Excel. With this approach, you combine several IF statements to evaluate three conditions, one after the other.

Steps:

Start by typing your IF function in the desired cell.
The basic syntax is:
=IF(condition1, result1, IF(condition2, result2, IF(condition3, result3, result_if_all_false)))
Excel will evaluate each condition in sequence until it finds a TRUE result or returns the final FALSE result.

In the example below, the nested functions test multiple conditions to determine how to rate a test score.

If the test score is greater than 90, return “Excellent.”
If the test score is greater than 80, return “Good.”
If the test score is greater than 70, return “Average.”
If none of the above conditions are met, return “Poor.”

The formula reads =IF(A2>60, “Pass”, IF(B2>60, “Pass”, IF(C2>60, “Pass”, “Fail”)))

Using the IFS FunctionIn Excel 2016 and later versions, the IFS function simplifies checking multiple conditions. Instead of nesting IF statements, you can use IFS to test all conditions in one function.

Steps:

Select the cell where you want the result.
The basic syntax for the IFS function is:
=IFS(condition1, result1, condition2, result2, condition3, result3)
Excel will evaluate each condition in order and return the result of the first TRUE condition.

Below, you find the same test score example as above, but written using the IFS function.

=IFS(A2>90, “Excellent”, B2>80, “Good”, C2>70, “Average”, TRUE, “Poor”)

IFS checks multiple conditions in order of priority:

Bonus Tip: Combining with AND/ORYou can further enhance your IF function by combining it with AND or OR to test if multiple conditions are met at the same time or if any condition is true.

AND: All conditions must be TRUE.
OR: Only one condition needs to be TRUE.

For example, you can use:
=IF(AND(condition1, condition2, condition3), result_if_true, result_if_false)

In the example below, the formula checks if all three test scores (columns A, B, and C) are greater than 60 using the AND function. If they are, it returns “Pass”; if not, it returns “Fail.”

=IF(AND(A2>60, B2>60, C2>60), “Pass”, “Fail”)

ConclusionBy using nested IF, IFS, or combining with AND/OR, you can handle more complex logic in Excel. Mastering these functions will help you make smarter decisions based on your data!

Got questions or tips of your own on this topic? Leave a comment below!

Related Posts:

How To Write IF Functions

Link to post: IF Function with 3 Conditions

View Details

Bottom Line: Learn how to set up a “Show Details” checkbox using advanced Excel formulas and functions.

Skill Level: Advanced

In response to requests from our previous post on checkboxes, today we’ll dive into how to set up a “Show Details” checkbox in Excel. This is a fantastic opportunity to practice advanced formulas and work with helpful Excel functions like FILTER, INDEX MATCH, XMATCH, and EXPAND. So, let’s get into it!

Watch the Tutorialhttps://youtube.com/watch?v=oG37HjTV084Watch on YouTube & Subscribe to our ChannelDownload the Excel FileYou can follow along using the same Excel file that I used in the video.

Show Details Checkbox.xlsxDownload What is the “Show Details” Checkbox?What do I mean when I talk about a Show Details Checkbox? Simply that when you click a checkbox, it reveals itemized details pertaining to the row that has the checkbox.

Examples you'll find in the Excel file inlcude:

A checkbox that allows you to display detailed line items for an invoice when checked.
A checkbox showing phone numbers for customers.
A viewer-inspired checkbox showing expense details for projects.

Creating these checkboxes uses a combination of conditional formatting and a single formula to make everything work smoothly.

Setting Up the Show Details CheckboxHere’s a quick overview of what you’ll need for the invoice example:

Two Tables: One with all orders (unique order IDs) and one with the line items or details related to those orders.
Checkboxes: You’ll need to be on a version of Excel that supports the new checkboxes feature. See this Microsoft article for details.
Some Advanced Functions: You’ll be using a mix of Excel’s advanced functions to make this checkbox work effectively.

Step-by-Step Formula Walkthrough1. Start with the FILTER Function
Firstly, the FILTER function is key to returning all relevant details for the selected order. This formula looks at the selected order and pulls all related product names and quantities. You can modify it to return other columns as needed. 2. Integrate the Checkbox
To ensure that the checkbox drives this process, we modify the formula using INDEX and XMATCH. This ensures that only the line items for the checked order are displayed. XMATCH finds and returns the row number of the checked checkbox to INDEX for the FILTER criteria. 3. Align the Results with the Checkbox
Next, you’ll want the details to show up right beside the corresponding checkbox. This is where the VSTACK and EXPAND functions come into play, allowing the results to “spill” down the correct number of rows.

EXPAND allows us to return a range of blank rows above the details table.

The DROP function allows us to shift the range up to be in exact alignment with the checkbox row. 4. Improve formula efficiency.
Since XMATCH is being calculated twice within the formula, we can use LET to streamline it. LET allows us to specify variables that hold the results of a calculation and use the variables multiple times throughout the larger formula. 5. Remove unsightly error message.
If no checkboxes are checked, the formula will return a #N/A error. To replace this with a blank cell, we can wrap everything in the IFERROR function. 6. Add Conditional Formatting
Finally, applying conditional formatting gives the table a clean, professional look. When the checkbox is checked, the corresponding details are highlighted, making it easy to focus on the selected data.

Be sure to watch the video (above) to see the formula built out step by step.

You can see how the final formula looks in the image below, as well as how those details look when you've checked a checkbox:

Checking Multiple CheckboxesWhat happens when you check more than one checkbox? By default, the formula will return the results for the first checked box. However, you can modify this behavior to handle last-to-first matches.

XMATCH has a fourth argument called search_mode that changes the order of how the search is conducted. By going from last to first, only the lowest checked box in the list will display details.

Another alternative is to show an error message if more than one checkbox is checked.

ConclusionThe Show Details Checkbox provides a dynamic way to present detailed data in your Excel workbooks without clutter, making your spreadsheets much more interactive and user-friendly.

Are you planning to use this in your projects? Let us know in the comments!

Link to post: The Show Details Checkbox: Advanced Excel Formulas

View Details

Bottom Line: Master the Excel FILTER function, from basic lookups to advanced data analysis techniques, and elevate your data management skills.

Skill Level: Beginner

Watch the TutorialWatch on YouTube & Subscribe to our ChannelDownload the Excel FileYou can follow along using the same Excel file that I used in the video.

FILTER Function Explained.xlsxDownloadThe FILTER FunctionThe Excel FILTER function is a game changer for data analysis. Whether you're new to Excel or a seasoned pro, this function will transform how you manage and analyze your data. Unlike lookup functions such as VLOOKUP and XLOOKUP, FILTER can return multiple results, making it an incredibly powerful tool for dynamic data analysis.

In this post, we'll start with the basics of the FILTER function and then move on to more advanced techniques that will help you leverage its full potential.

Basic FILTER Function UsageLet's start with a simple example. Suppose you have a list of customer information, and you want to look up a customer name and return all matching contact names and phone numbers. Here's how you can do it with the FILTER function:

Step 1: Start by typing =FILTER( into the formula bar.
Step 2: For the first argument, select the columns with the contact names and phone numbers.
Step 3: For the second argument, select the customer name column and set it equal to the customer name you want to filter by.
Step 4: Hit Enter, and the FILTER function will return all matching results.

This basic use of FILTER mimics the manual filtering process in Excel, but with the added benefit of automation.

Handling Errors with the IF_EMPTY ArgumentWhat happens when no matching values are found? By default, the FILTER function returns a #CALC! error. However, you can use the **if\_empty** argument to display a custom message when no results are found. For example, you can set it to display “Not Found” instead of an error.

Returning Multiple ResultsOne of the key advantages of the FILTER function over other lookup functions like VLOOKUP and XLOOKUP is its ability to return multiple results. While VLOOKUP and XLOOKUP typically return only the first match, FILTER can return all matches, making it perfect for scenarios where you need a comprehensive view of your data.

Creating Interactive Drop-downs with FILTERYou can enhance your spreadsheet's interactivity by combining FILTER with a drop-down list. This allows users to select a customer name from a list, and the FILTER function will automatically update to show all associated contacts.

Using Excel Tables to Avoid Range MismatchesWhen using the FILTER function, it's important that the ranges you select are of the same length. To avoid errors, you can use Excel Tables, which automatically adjust as data is added or removed. This ensures that your FILTER function always references the correct data range.

Returning Non-Adjacent ColumnsSometimes, you may need to return non-adjacent columns with the FILTER function. You can achieve this by using the CHOOSECOLS function to specify which columns to include in your results. This allows you to customize the output to show only the data you need.

In te example below, I specify 1,2,4 in the column arguments to include Contact, Phone Number and State (the 1st, 2nd, and 4th cloumns) and exclude the 3rd column (City) from the results.

Click to enlargeCombining FILTER with Other Functions to Copy DownTo avoid problems with trying to copy down the FILTER formula, you can combine FILTER with other Excel functions like ARRAYTOTEXT or TEXTJOIN. These functions allow you to consolidate the results of your FILTER function into a single cell, or customize how the results are displayed.

If you don't like the idea of stuffing all of the data into one cell, I present a couple of alternatives in the video. One is to use the ROWS function to identify how many rows of results there are, and then the CHOOSEROWS function to look at the entries one at a time.

The other option is to create checkboxes that, when checked, will show the results for each entry.

You can learn how to create this in my tutorial: 19 Ways to Use Excel’s New Checkboxes Feature

Filtering with Multiple CriteriaThe true power of the FILTER function shines when you need to filter data based on multiple criteria. You can use logical operators like * for AND (multiplication) or + for OR (addition) to combine criteria** and refine your results. This is particularly useful for advanced reporting and analysis tasks.

Click to enlargeIn this example I want to filter for both customer name AND year. Both criteria can be specified in the include argument by wrapping each in parenthesis and multiplying them together.

(tblContacts2[Customer Name]=B5) * (tblContacts2[Year]>=C3)

Please see the video for a detailed explanation on how Excel evaluates the criteria into ones and zeros before filtering to return the results.

ConclusionThe Excel FILTER function is a versatile tool that can enhance your data analysis and reports. Whether you're filtering data based on single or multiple criteria, returning non-adjacent columns, or handling errors with ease, mastering this function will set you apart as an Excel pro.

If you have any questions or comments, feel free to leave them below. I'd love to hear how you're using the FILTER function in your work!

And if you’re interested in taking one of our online courses, check out the selection here.

Link to post: Excel FILTER Function: Beginner to Pro

View Details

Bottom Line: Excel's new checkboxes feature offers a versatile way to manage data, from simple checklists to interactive charts.

Skill Level: Beginner

Watch the TutorialWatch on YouTube & Subscribe to our ChannelDownload the Excel FileYou can follow along using the same Excel file that I used in the video.

19 Uses for New Checkboxes in Excel.xlsxDownload 1. Simple ChecklistCreate a basic checklist for tasks like packing for a vacation. Insert checkboxes next to your list, allowing you to check off items as you go. The checkboxes are interactive—just click to check or uncheck, or use the space bar for quick toggling.

Conditional Formatting for a ListEnhance your lists, such as a Christmas gift list, by applying conditional formatting to checked items. When an item is checked, the checkbox changes to a different color, like red, making it easier to see what's been completed.
IF FormulaUse the IF formula with checkboxes to automate calculations. For example, in an expense report, a checked checkbox can split an amount in half, simplifying calculations for users.
Highlight Entire RowMake comparisons easy by highlighting entire rows when checkboxes are checked. This is done using conditional formatting, making specific rows stand out for review.

Click to enlarge5. Filter to Compare RowsCompare multiple rows by filtering checkboxes. Filter your list to show only checked items, allowing you to focus on specific data points for comparison.

Format Checked ItemsAutomatically format other cells when a checkbox is checked. For instance, change the font color to gray for completed tasks, helping to differentiate them from the remaining items.
Highlight Next Item in ListGuide your progress by highlighting the next item in a list when the current one is checked. This method draws attention to what needs to be done next.
Show Details on a TableUse checkboxes to reveal details in a table. For example, checking a box could display all line items associated with an order, using functions like FILTER and XLOOKUP.
Show Details on a ChartSimilar to revealing details in a table, you can use checkboxes to show or hide data in a chart. This can help focus attention on specific metrics, like comparing revenue versus budget.
Counting CheckboxesEasily count the number of checked boxes with a COUNTIF formula. You can use this for attendance tracking, inventory management, and more.
Attendance TrackerStreamline attendance tracking by selecting multiple checkboxes at once. For example, check all present students with the space bar, and then uncheck any absentees with your mouse.
Progress ChartVisualize your progress with charts linked to checklists. As items are checked off, the progress is reflected in either a bar or donut chart, giving you a visual summary of what's completed.

Get more information about making progress charts with this tutorial: 8 Types of Progress Charts

Excel TablesCheckboxes are fully compatible with Excel tables. As you add new rows, the checkboxes automatically extend, making them a powerful tool for managing dynamic data sets.
Reveal List and Invisible RevealUse checkboxes to gradually reveal a list during a presentation, keeping the audience engaged.

Alternatively, hide the checkboxes themselves for a cleaner look while maintaining functionality.

Calendar ChartLink checkboxes to a calendar chart totrack deadlines or progress visually. Checking an item highlights the corresponding date, providing an intuitive view of your schedule.

Learn how to build this chart in this tutorial: Create a Calendar Chart

Interactive CalendarCreate an interactive calendar where entire weeks light up when all tasks are checked. This method is great for tracking weekly goals, with a reward or completion marker for fully checked weeks.

This post will teach you how to build it: Interactive Calendar Chart with Weekly Goals

Reset ButtonQuickly reset all checkboxes without using macros. Use a hyperlink to select the range of checkboxes, and then hit the space bar to clear them all at once.
Select All ButtonAdd a “Select All” button using a similar technique to the reset button, allowing users to check all boxes in a list simultaneously.
Toggle Chart SeriesControl which data series appear in a chart by toggling checkboxes. For example, you can hide or show the budget line in a revenue versus budget chart, giving you a focused view of the data.

Final Tips for Using Excel's CheckboxesHere are a few final tips to help you make the most of Excel's checkboxes:

Alignment Options: By default, checkboxes are center-aligned when inserted. However, you can easily adjust them to be left or right-aligned using the horizontal and vertical alignment options available in Excel.
Deleting Checkboxes: If you need to delete checkboxes, simply hit the Delete key on your keyboard. If any checkboxes are checked when you press Delete, they will be unchecked first. Press Delete again to remove them completely.
No More Ghosting: Early beta versions of the checkbox feature had a ghosting issue when deleting checkboxes, but this has been resolved in the production release.
Availability: As of July 31, 2024, checkboxes have been fully released to the production version of Excel for Microsoft 365 users on Windows and Mac. While web and mobile versions are still being rolled out, other channels will follow soon. Be aware that checkboxes are not backwards compatible, so users on older versions of Excel will see only TRUE/FALSE values instead of checkboxes. Here is a blog post from Microsoft that explains more about availability.

ConclusionI hope this blog post has inspired you to explore all the ways you can use Excel's new checkboxes feature to enhance your spreadsheets. Whether you're creating simple checklists, interactive charts, or anything in between, checkboxes offer a versatile tool to bring your data to life.

Which of these 19 ways can you see yourself using soon? Leave a comment below to let us know, or if you have any questions!

Link to post: 19 Ways to Use Excel’s New Checkboxes Feature

View Details

Bottom Line: This new update might make you consider switching from Windows to Mac for Excel.

Skill Level: Beginner

Watch the TutorialWatch on YouTube & Subscribe to our ChannelDownloadsGrab our free Shortcuts Guide PDF that contains over 270 Excel Shortcuts for the Windows, Mac, and Web versions of Excel.

Here's the link where you can download it! https://www.excelcampus.com/shortcuts/

KeyTips for MacMicrosoft has recently rolled out a new KeyTips feature for the Mac version of Excel, bringing it closer to its Windows counterpart. While Windows users have enjoyed this feature for years, Mac users can now unlock more than 259 new keyboard shortcuts.

This blog post will guide you through using this new feature, highlight some current limitations, and show you how to create custom shortcuts. We'll also discuss whether this feature makes the Mac version of Excel a viable option for dedicated Windows users.

Using the KeyTips FeatureTo activate KeyTips, press and release the Option key on your Mac keyboard (similar to the Alt key on Windows). This will display letters on the Ribbon, indicating the available shortcuts. For example, pressing Option then H reveals shortcuts for the Home tab, such as increasing or decreasing decimal places.

Example: Adjusting Decimal Places1. Select the column you want to adjust (use Ctrl + Space to select the entire column). 2. Press Option, then H, followed by 9 to decrease the decimal places. 3. To increase decimal places, use Option, then H, then 0.

Joining the Beta ChannelAt the time of this writing, the KeyTips feature is in Beta. To access it, go to Help > Check for Updates > Advanced and select the Beta Channel. Note that these features may not be immediately available to all Beta users.

A Popular Shortcut: Toggling GridlinesOne frequently used keyboard shortcut that's good to know is toggling gridlines:

Press Option, then W to open the View tab.
Press VG to toggle gridlines off or on.

A Limitation and a WorkaroundOne limitation of the KeyTips feature on Mac is the absence of underlined letters for some commands. For example, autofitting columns (Alt, H, O, I on Windows) lacks a direct Mac equivalent. You can still access the format menu (Option, H, O), use arrow keys to navigate, and press Enter to select autofit.

Comprehensive Shortcut ListIf the thought of memorizing one more shortcut causes you anxiety, we've created a list of keyboard shortcuts that you can print and keep handy. You can find it here:

Excel Keyboard Shortcuts List

Creating Custom Keyboard ShortcutsMicrosoft has announced a new keyboard shortcut for Paste Values (Ctrl + Shift + V), currently only available on Windows. However, Mac users can create custom shortcuts using KeyTips. Let's walk trhough an example.

Setting Up a Custom Shortcut for Paste Values1. Go to the Quick Access Toolbar, click the three-dot menu, and select More Commands. 2. Then choose All Commands and find Paste Values. 3. Add it to the Quick Access Toolbar and move it to the desired position.

To use the shortcut, press and release Option, followed by the number assigned to the Paste Values button.

Advanced Custom ShortcutsThe Mac version of Excel offers a Customize Keyboard option under the Tools menu, allowing you to create shortcuts for several commands. However, some commands like Paste Values are not included. If there's interest, a separate video on this feature can be made—leave a comment if you're interested.

Is the Mac Version Worth Switching To?As a longtime Windows user, the addition of KeyTips makes the Mac version of Excel much more appealing. However, there are still limitations, particularly with advanced features like Power Query, Power Pivot, and VBA user forms. If you don't require these features, this update is significant and long-awaited by many Mac users.

Final ThoughtsAre you excited about the new KeyTips feature for Mac? Will it make you consider switching from Windows? Share your thoughts in the comments below. Thanks for reading, and have a great day!

Link to post: Over 259 NEW Keyboard Shortcuts – Excel Update for Mac

View Details

Bottom Line: Avoid using a certain argument in XLOOKUP without proper investigation to prevent errors and ensure accurate data retrieval.

Skill Level: Intermediate

Watch the TutorialWatch on YouTube & Subscribe to our ChannelDownload the Excel FileYou can follow along using the same Excel file that I used in the video.

XLOOKUP Dangers.xlsxDownload XLOOKUP is a powerful function in Excel, but it has one feature that can cause problems if not used correctly. In this blog post, I'll highlight that feature and share some best practices and habits to help you avoid potential issues.

Writing the XLOOKUP FormulaTo demonstrate this common mistake with XLOOKUP, let's write an XLOOKUP formula.

In our example, we have some ticker symbols in a portfolio table, and we want to look them up in another data set to return the price. We'll use XLOOKUP for this.

Start typing =XLOOKUP, then tab into that.
Select your lookup value (the ticker symbol), then press Ctrl`+`Shift`+↓` to select all the way to the bottom. Hit F4 to make that an absolute reference.
Move on to the return array, which will be the price. Again, use `Ctrl`+`Shift`+↓andF4`` for the absolute reference.
For the **if\_not\_found** argument, we'll return a blank with two quotation marks, then hit Enter.
Double-click the fill handle to copy the formula down.

If writing this formula causes confusion for you, feel free to take a step back and check out these tutorials that explain XLOOKUP formulas in more detail:

The New XLOOKUP Function for Excel
Shortcuts for Writing XLOOKUP and VLOOKUP Formulas
How to Fix & Prevent the #VALUE Error with XLOOKUP

Identifying Common ErrorsYou'll notice some blank cells, which might be because not all ticker symbols exist in the other table. However, there could be other reasons.

A common mistake is specifying the **if\_not\_found** argument without investigating the cause of the error. This argument handles any errors, so if there's a mismatch or Excel can't find the value, it returns what we specify in **if\_not\_found**.

Many users get in the habit of specifying this without investigating.

Best Practice for Using if_not_foundA good habit is NOT to specify if_not_found first unless you're diligent about investigating blank cells. Since it's an optional argument, you can leave it out. After copying the formula down, errors will stand out more, prompting you to investigate.

Investigating ErrorsTo investigate, copy the ticker from the row that has the error (Ctrl`+`C), and open the Find window using Ctrl`+`F. Paste the value and search within the ticker column of the source data.

If the value is there but has an extra character like an asterisk or space, XLOOKUP won't find it due to the exact match requirement. Cleaning up these characters is crucial.

If you have tickers with extra spaces, use the TEXTBEFORE function to clean them up. The text to evaluate is the ticker symbol, and the delimiter is a space. If it doesn't find a space, it returns the ticker. Double-click the fill handle to copy the formula down, and your data is cleaned up.

If there's still an error, gut-check it with another find operation to ensure no matches exist. Ensure “Match entire cell contents” is unchecked in the Find Options for a more thorough search.

Adding Error Handling Back to XLOOKUPAfter investigation, you can add the error handling back to XLOOKUP. Specify the if_not_found argument, such as returning a blank or a custom message. This ensures errors are handled appropriately after thorough investigation.

XLOOKUP TriviaAn interesting piece of trivia: XLOOKUP was the first function to receive error handling with the **if\_not\_found** argument. Previously, we used the IFERROR function with VLOOKUP or INDEX MATCH for error handling. XLOOKUP's built-in error handling cleans up formulas and makes them easier to read. However, it's essential to use it correctly, with proper investigation to avoid issues.

ConclusionHopefully, this post helps you prevent errors with your XLOOKUP formulas. I'm curious to know if it's helpful for you or if you have other tips for preventing errors. Leave a comment below and let us know.

I mentioned in the video that you can subscribe to my free newsletter. Click here to do that.

And if you're wondering why I didn't use Excel tables, check out this video where I explain why. Thanks for reading. Have a great day!

Link to post: The Most Dangerous XLOOKUP Argument

View Details

Bottom Line: Discover the latest enhancements to Excel for the web.

Skill Level: Beginner

Watch the TutorialWatch on YouTube & Subscribe to our ChannelNew Updates for Everyone!The web version of Excel has received several updates recently. If you haven't used the web version in a while, you might be surprised at how similar it is to the desktop version of Excel.

Did you realize that Excel for the web is free to use? You don't need to buy a license. You can just go to excel.new in your web browser. If you don't already have a Microsoft account, you can sign up for a free one and then that will open Excel for the web.

This means that you do NOT have to wait for these features to come out of beta. They are available for everyone to use right now.

Now, let's explore seven new features in the web version of Excel.

Enhancements for Rows and ColumnsWhen you hover your mouse over the column header, a 6-dot icon appears.

You can click and hold this icon to move the column, and a vertical bar indicates where it will be inserted. This works similarly for rows.

Unlike the desktop version, there's no need to hold the Shift key to see the vertical bar. The Shift key performs a replace function, and the Ctrl key makes a duplicate copy.

Hovering above the column header reveals small dots that allow for quick insertion of columns and rows. This update simplifies the process compared to the traditional right-click and insert method.

Freeze PanesFreezing panes is now even easier with the modernized Excel grid. By dragging the blue line that appears when you hover above the row headers, you can freeze panes without using the Ribbon. Of course, this works similarly for column headers as well.
Autofill OptionsTyping a value and dragging the fill handle now presents an autofill options menu. This feature, which includes Fill Series and Copy Cells, is also available for numbers and dates. Although it may be a bit buggy currently, it's a welcome addition.
Paste OptionsAfter copying cells, right-clicking to paste now offers a paste special menu with various options such as values only, formulas, or formatting. These options are also accessible from the Home tab.
Links to Sheet ViewsSheet views allow you to create custom views for sorting or filtering data. You can now copy a link to a sheet view and share it via email or message. This link will open the file in the specified view.

You can also add the sheet view to your bookmarks bar for quick access.

Filters for CommentsNew filters have been added to the comments task pane, allowing you to filter active comments, resolved comments, or comments that mention you specifically. This helps manage busy comment sections more effectively.
Date PickerA date picker is now available for any cell containing a date. Double-clicking the cell or pressing F2 opens the date picker, which allows for easy date selection and navigation. It also appears when inserting today's date using the Ctrl + ; shortcut.

I'm curious to know which of these updates you're most excited about. Please leave a comment below and let us know.

If you liked this post, you might also enjoy 17 Amazing Excel Features Hiding in Plain Sight.

Thanks for reading, and have a great day!

Link to post: 7 New Excel Features & Updates

View Details

Bottom Line: Explore a comprehensive guide for those who have little to no experience using Microsoft Excel.

Watch the TutorialWatch on YouTube & Subscribe to our ChannelDownload the Excel FileYou can follow along with the video by using the same Excel file I use.

The Excel Starter Pack that I mention in the video is also included with the download. It contains guides for shortcuts, applications, and our popular Pro Tips eBook.

Excel Starter Pack – Excel Campus.zipDownloadThe zip file contains all of the files mentioned above.

Everything You Need to Know to BeginToday we're exploring Excel fundamentals to help anyone who's just starting with Excel or who has very limited exeprience using it. With over 15 years of experience teaching Excel, I'm excited to share essential tips, best practices, and common mistakes to avoid. We'll start with the very basics, so feel free to skip ahead to more advanced sections.

Table of Contents1. Getting Started with Excel * Default Start Window * Web vs. Desktop (Windows or Mac) * Insert and Open Workbooks * Application Guide 2. Navigating Excel * Workbooks vs. Worksheets * Adding and Renaming a Sheet * Understanding the Grid 3. Inputting and Editing Data * Entering Data * AutoComplete and Overwriting Data * Deleting and Resizing Data * Data Types 4. Formatting Data * Number Formatting * Dates and Text * Font, Fill, Borders, Alignment 5. Copying, Pasting, and Moving Data * Different Paste Options * Using Format Painter * Move Cells by Dragging * Insert and Delete Rows/Columns * Fill Handle 6. Writing Formulas and Functions * Basic Formulas * SUM and AVERAGE Functions * AutoSum and Absolute References * More on Formulas 7. Sorting and Filtering Data * Using Filters * Sorting Data 8. Creating Charts and Visualizing Data * Basic Charts * More on Charts

Getting Started with ExcelDefault Start WindowWhen you first open Excel, you'll be greeted with the start screen. Here, you can choose to start with a blank workbook, select a template, or open recent files. Start with a blank workbook by clicking the “Blank workbook” button.

Web vs. Desktop (Windows or Mac)Excel is available in both desktop and web versions. The desktop application is available on both Windows and Mac, while the web version can be accessed via your browser.

To open the web version, type **excel.new** in your browser and log in with your Microsoft account. The web version closely resembles the desktop version, but there are some differences in keyboard shortcuts and features.

Insert Blank WorkbookTo create a new workbook on the Desktop version, click “File” > “New” > “Blank Workbook”. You can also use keyboard shortcut `Ctrl +N`.

Open a Workbook To open an existing workbook, click “File” > “Open” and browse to the location of your file. You can also use keyboard shortcut Ctrl` + `O.

Application GuideExcel has different versions for Windows, Mac, and the web. Of course, each version has a slightly different layout and keyboard shortcuts. So use the application guide specific to your version to familiarize yourself with the interface.

Windows VersionClick to enlargeMac VersionClick to enlargeWeb VersionClick to enlargeAll of these application guides are contained in the Excel file at the top of this tutorial.

Navigating ExcelWorkbooks vs. WorksheetsA workbook is an Excel file containing one or more worksheets. Worksheets, often referred to as sheets or tabs, are the individual pages within a workbook. To navigate between sheets, click the sheet tabs at the bottom of the window.

Adding and Renaming a SheetTo add a new sheet in Excel, simply click the plus button (+) located next to the existing sheet tabs at the bottom of the window. This will insert a new blank sheet named “Sheet1,” “Sheet2,” and so on.

Renaming your sheets is a good practice to keep your workbook organized. To rename a sheet:

Double-click the sheet tab you want to rename. This will put the sheet name in edit mode.
Then type the new name for your sheet.
Lastly, press Enter to save the new name.

For example, you might rename a sheet to “Favorites List” to better describe the data it contains. This helps you quickly locate and reference specific sheets within your workbook.

Understanding the GridExcel's grid is made up of rows (numbered) and columns (lettered). Each rectangle in the grid is a cell, identified by its column letter and row number (e.g., A1). You can select a range of cells by clicking and dragging. This selected area is called a range.

Inputting and Editing DataEntering DataClick on a cell and start typing to enter data. Press Enter to move to the cell below, or Tab to move to the next cell on the right. For example, type names in column A and favorite numbers in column B. Use AutoComplete by typing the first few letters of a name that has already been entered, and Excel will suggest the complete name.

Overwriting and Deleting DataIf you want to edit a cell, double-click it or select it and start typing. To delete data, select the cell and press Delete. To overwrite data, simply type over the existing content.

The Undo button will undo your last entry, and the Esc key will allow you to abandon your current entry. This is especially handy if you accidentally start typing in a cell that contains data that you didn't intend to overwrite.

Resizing CellsTo resize columns or rows, drag the edges of the column or row headers. Double-clicking the edge will auto-fit the column or row to the content. You can also select multiple columns or rows to resize them simultaneously.

Data TypesThere are three main data types: numbers, text, and dates.

Any combination of these in a single cell is considered text.

Entering dates can be done with slashes (e.g., 12/31/2024) or dashes (e.g., 12-31-2024).

A fourth data type to quickly mention is a logical or Boolean data type, which can be used for writing formulas.

There are also other data types including rich data and images, but these are less important when you are getting started with Excel.

Formatting DataNumber FormattingTo format numbers, select the cell(s) and choose the appropriate format from the “Number” dropdown in the “Home” tab. Options include General, Currency, Accounting, and Percentage, among others. There are also buttons in the Ribbon that allow you to increase or decrease the number of decimal places.

Dates and TextExcel can automatically detect and format dates. To customize date formats, right-click the cell, select “Format Cells,” and choose the desired format.

Each date is assigned a whole number based on its relation to 1/1/1900. This is Excel's starting point for calendar dates. So if you ever change the formatting of a date to text, the number that you'll see is the number of whole days that have elapsed from that reference point.

See the video above for more details or checkout this post on How Dates Work in Excel – The Calendar System Explained + Video.

Font, Fill, Borders, AlignmentUse the “Home” tab to apply font styles, fill colors, borders, and alignment options. For example, select a header row and apply bold, a fill color, or center alignment.

Copying, Pasting, and Moving DataDifferent Paste OptionsCopying and pasting in Excel can be done with Ctrl` + `C and Ctrl` + `V, respectively. After pasting, use the “Paste Options” menu to choose different paste formats, such as “Values Only” to avoid copying formatting.

Here are 5 Keyboard Shortcuts to Paste Values in Excel, if you'd like to learn more on this topic.

Using Format PainterTo copy formatting from one cell to another, use the “Format Painter” in the “Home” tab. Select the cell with the desired format, click “Format Painter,” and then click the cell or range where you want to apply the format.

Move Cells by DraggingYou can left-click and hold and cell tro drag its contents, including the formatting, to another cell. You can also drag ranges of cells and insert them between others.

Insert and Delete Rows/ColumnsRight-click a row or column header to insert or delete rows or columns. Use the “Insert” option to add a new row above or a new column to the left of the selected row or column.

Fill HandleWhen you hover over the bottom right corner of a selected range, your cursor will turn into a plus symbol. This is called the fill handle, and it allows you to copy down or fill in a sequence based on the pattern it recognizes in the selected cells. Double-clicking on the fill handle will also fill the numbers down.

Writing Formulas and FunctionsFormulas in Excel start with an equals sign (=). For example, to add two cells, type =A1+B1 and press Enter.

SUM and AVERAGE FunctionsUse functions like SUM and AVERAGE to perform calculations on a range of cells. For example, =SUM(A1:A10) adds all values in cells A1 through A10.

AutoSum is a quick way to sum a column or row of numbers. Use the options in the AutoSum dropdowm on the “Home” tab to quickly add functions like SUM or AVERAGE.

The keyboard shortcut for AutoSum is Alt + =.

Absolute ReferencesUse absolute references (e.g., $A$1) to lock, or anchor, a cell reference when copying formulas. To change a reference to be absolute, use F4 for Windows ot Cmd + T for Mac.

More on FormulasWe've only seen the tip of the iceberg when it comes to formulas. Here are some additional tutorials on the topic:

5 Formula Editing Tips
How to Fix Excel Formulas that are Not Calculating or Updating
IF Formula Tutorial for Excel – Everything You Need To Know

Sorting and Filtering DataUsing FiltersTo filter data, select the range and click the “Filter” button in the “Data” tab. Use the dropdown arrows in the headers to sort or filter the data.

Sorting DataClick a column header to sort data in ascending or descending order. Use the “Sort & Filter” options in the “Home” or “Data” tab for more advanced sorting options.

Creating Charts and Visualizing DataBasic ChartsTo create a chart, select the data range and choose a chart type from the “Insert” tab. For example, create a column chart to visualize the number of coffee cups each person has.

Here are some more tutorials on charts:

8 Types of Progress Charts
When to Use Pie Charts – Best Practices
5 Bad Charts and Alternatives

ConclusionI hope you found this tutorial helpful. Excel takes time to learn, so don't hesitate to rewatch sections of the video. Practice will make it easier. To help you on your learning journey, we've put together a free Excel Starter Pack that includes some helpful guides to remember all these tips and use Excel like a pro. Of course, you can grab it for free here: XXXXXXXXXXXX.

I'd love to hear what's been most helpful for you, so please leave a comment below. For more advanced Excel skills, check out the hundreds of tips and turorials at Excel Campus, such as learning the VLOOKUP or XLOOKUP functions in this post.

Thanks for watching, and have a great day!

Link to post: Excel for Beginners – Complete Tutorial

View Details

Bottom Line: Learn what pivot tables are and how they can revolutionize your work.

Skill Level: Beginner

Watch the Video TutorialWatch on Youtube & Subscribe to our ChannelThe Magic of Pivot TablesPivot tables are essentially summary reports. They simplify your data analysis tasks, making them more efficient and effective. Despite their complex-sounding name, pivot tables are straightforward to use and can significantly enhance your reporting capabilities.

How to Create a Pivot TableTo start a pivot table, you'll need some data. This data can be anything relevant to your needs. For instance, let's consider sales data, where each row represents an order.

Suppose your boss wants to see total revenue by month and region. A pivot table can generate this report in just a few clicks.

No Need for FormulasOne of the best things about pivot tables is that they don't need formulas. Simply drag and drop the column names or fields into the report areas, and Excel handles the rest.

Pivot tables are highly flexible. If your boss decides they want the report by quarter instead of by month, for example, you can adjust it in seconds.

Interactive and Customizable ReportsPivot tables enable you to visualize data efficiently using pivot charts. These charts are directly connected to the pivot tables, so any changes you make to the table are instantly reflected in the charts. Adding slicers allows you to filter both pivot tables and pivot charts, making your reports interactive and user-friendly.

You can also create interactive dashboards by adding multiple pivot charts to a sheet. These dashboards are quick to set up and don't require complex formulas or extensive formatting.

Versatility of Pivot TablesPivot tables are versatile tools. They can compare multiple data sets, validate data, perform lookups, and much more. Every data analyst should have pivot tables in their toolkit.

Are Pivot Tables For You?Of course, maybe your data has nothing to do with sales orders and you're wondering if pivot tables can be useful for your data type. The answer is most likely yes! Pivot tables are incredibly flexible and capable.

They are also integral to more advanced business intelligence tools like Power Pivot, Power BI, and Tableau. The critical first step in using pivot tables is ensuring that you've correctly formatted your source data. If your data isn't ready, tools like Power Query can help transform it into the appropriate layout.

Free Resources to Get You StartedI've got a free three-part video series on pivot tables and dashboards to help you get started: Introduction to Pivot Tables, Charts, and Dashboards

Additionally, if you're interested in learning how to use pivot tables alongside other modern Excel tools like Power Query, Power Pivot, Power BI, and even macros and VBA, check out my free training webinar called the Modern Excel Blueprint.

I hope this post motivates you to get started unlocking the potential of pivot tables in Excel. If you have questions or comments, please leave a note below.

Link to post: Pivot Tables Explained in 3 Minutes

View Details

In a previous post, I showed you how to combine data from several sheets into one sheet using the VSTACK function. You can watch the tutorial for that here: VSTACK to Combine Multiple Sheets.

Video TutorialWatch on YouTube & Subscribe to our Channel

Downloads VSTACK Tutorials.xlsxDownload A common question that arises from the process is, “How do we add headers?”

Good question!

There are two quick ways to add headers to the stacked data.

The first way is to copy and paste the headers from the first sheet, but that's not very flexible.

The second way is to add the headers into the VSTACK formula.

To do that, edit your VSTACK formula so that the headers from your first tab come before the data range you've already specified. Separate this new data range from the existing range with a comma.

You are essentially adding another array and stacking the header line on top of the other stacked data.

Now, if your headers on the first tab ever change, the changes will automatically reflect in your combined VSTACK data.

Pretty simple, right?

If you have any questions, just ask in the comments and we'll get back to you as soon as we are able.

Link to post: Including Headers in VSTACK Data

View Details

I've been teaching Excel for over 15 years, and I'm always on the search for tips and features that will help save time. Here are 17 of my favorites that are hiding in plain sight. These tips will help you navigate and utilize Excel more efficiently.

Video TutorialWatch on YouTube & Subscribe to our Channel 17 Excel Features.xlsxDownload 1. Right-click Tab NavigationIf you have a workbook with a lot of sheets in it, it can take a lot of work to scroll back and forth using the sheet navigation arrows at the bottom left corner of the window. Instead right-click on the arrows . This action opens a list of all sheets, allowing you to quickly find and double-click to jump to the desired sheet.

Sheet Number in Status Bar Right-click on the status bar and click the option to add the sheet number to the sheets. This helps you and your coworkers know which sheet number you're on, making communication easier.

3. Status Bar Calculations The status bar is useful for quick calculations. Select a range, and you'll see stats about the cells in that selection. Right-click the status bar to see additional metrics and add them as needed.

Zoom to Fit Selection If you want to focus on a specific range in your spreadsheet, select that range, click on the zoom slider in the bottom right corner and double-click Fit Selection. To zoom back out, double-click 100%.

5. Expand Formula Bar For long formulas, click the expand button on the formula bar. If the entire formula is still not visible, drag the bar down to expand it further. Collapse it by clicking the button again or using the keyboard shortcut Ctrl+Shift+U.

Autofit Columns and Rows To autofit all columns or rows, click the Select All button in the top left corner, then double-click any column or row border to autofit all.
Name Box Range Selection Use the Name Box to select a range quickly. For example, to select from B4 to E500, first select B4, then type E500 in the name box, hold Shift, and press Enter.
Zoom Out to See Named Ranges Zoom out below 40% to see named ranges appear on the sheet. Undoubtedly, this will help with navigation and finding out where named ranges exist on the sheet.
Select Objects ToolWhen working with multiple shapes, use the Select Objects tool (found under Find & Select on the Home tab) to draw around and select shapes easily. Remember to press Esc to return to normal mode.
Lock Drawing Mode When drawing shapes, right-click and choose Lock Drawing Mode to keep adding the same shape without reselecting the tool each time. Press Esc to exit this mode.
Edit Mode and F2 Shortcut Use F2 to toggle between Edit and Enter modes. This allows you to use arrow keys to navigate within a formula or select cells when editing a formula.
Fill Handle Use the fill handle to copy formulas or data down a column. Place the cursor on the fill handle in the bottom right corner of a cell, and double-click to copy down the column.

The following post has more tips and tricks for the fill handle: Copy Dates & Fill Series of Numbers with the Fill Handle in Excel

Format Painter Lock Mode Double-click the Format Painter button to lock it. You can then click any cells to apply the same formatting. Press Esc to exit lock mode.
Tiny Ribbon Buttons The small buttons on the ribbon open detailed options. For example, clicking the button in the Font group opens the Format Cells window, where you can make more precise formatting changes.
Ctrl+Drag to Duplicate Sheets Hold the Ctrl key, then drag a sheet tab to the right to create a duplicate. This also works with multiple sheets selected.
Double-Click to Hide Ribbon Double-click any tab on the Ribbon to hide the Ribbon and gain more grid space. Click a tab to temporarily show the ribbon, then click back on the grid to hide it again. Double-click the tab again to bring it back permanently.
Keyboard Shortcuts in Screentips And lastly, hover over ribbon buttons to see their keyboard shortcuts in screentips. For example, the shortcut to toggle filters on and off is Ctrl+Shift+L.

These features can significantly enhance your Excel efficiency and productivity. Which one was your favorite? Let us know in the comments below. If you enjoyed this post, consider sharing it with your colleagues and friends. Thanks for reading!

Link to post: 17 Amazing Excel Features Hiding In Plain Sight

View Details

Bottom Line: Find out why I've stopped recommending Excel Tables (in some scenarios) due to the confusion they cause among users with varying Excel skills.

Skill Level: Beginner

Watch the VideoWatch on Youtube & Subscribe to our ChannelDownload the Excel FileYou can access the same Excel file I use in the video here:

Excel Tables Example.xlsxDownload Excel TablesSo, one of my favorite features of Excel is also one of my worst enemies. It's a feature that all Excel users can benefit from, but unfortunately, it's gotten me into a lot of trouble over the years. It's cost me hours of extra work, leading to frustration and confusion with my bosses and coworkers.

The feature I'm referring to is Excel Tables, and in most cases, I just don't recommend it anymore.

Don't get me wrong. I love Excel Tabes for many reasons.

The Benefits of Excel TablesIf you're familiar with Excel tables, you know their many benefits. They can take a range of data and turn it into a nicely formatted table with banded rows, automatic filters, and the ability to expand automatically when new rows or columns are added.

What's not to love here? Well, in reality, tables aren't that popular. Not many users use tables, and I believe it's for one main reason: they require you to learn a new formula language.

The Challenge of Structured ReferencesFor example, if we write a simple AVERAGE formula on a regular range, it looks like this:

=AVERAGE(G4:G18)

This is a regular range reference we're all familiar with. However, if we write that same formula against data in a table, it looks like this:

=AVERAGE(Table1[Amount])

This is called structured referencing. It introduces a whole new world for Excel users that can cause a lot of confusion. While this formula language might seem straightforward, it can be quite challenging when you get into it. It's like learning a new language.

In the video above, I explain how it reminds me of the show, The Amazing Race. Contestants on that show are dropped into a city around the world and have to ask for help from local citizens to complete challenges. The problems is that they don't always speak the local language, which can be frustrating.

Excel Tables are like this for a lot of users. They find it difficult to use an Excel file that contains a different formula language from the one they normally “speak”.

Real-World ExampleLet me share a real-world example of how tables have caused trouble for me. When I worked in the FP&A department of a company several years ago, we were in charge of the annual budgeting process. This involved creating and populating budget templates for departments worldwide. We had multiple sheets in these templates with tables, and we had to deal with 928 departments and about 800 people involved in the process.

Most of these users had varying levels of Excel skills. We had a few advanced users, several intermediate users, and many beginners. Because structured references and Excel tables are more of an intermediate to advanced skill, we couldn't expect all managers to understand them. This led to many phone calls and emails saying, “Your file's broken,” or “I don't understand these formulas.”

Workarounds and SolutionsOne potential solution I thought could save me was a setting in Excel. If you go into File > Options > Formulas, there's a setting to “Use table names in formulas.” Turning this off makes it so new formulas do not automatically use structured references.

However, this setting is an application-level setting. This means it stays with the user’s computer and doesn't travel with the workbook. This can create “bilingual workbooks” where different users see different types of references, leading to confusion.

Tips for Using Excel TablesI want to be clear: I'm not saying don't use tables. I love tables, but I don't always recommend them. It really depends on the scenario. Here are some tips to help you decide when to use tables:

Use tables when you know your users: If they're familiar with tables or if you think it's a good opportunity to teach them about tables, then use them.
Avoid tables when sending files to vendors or customers: If you're not sure who you're sharing the file with or who they might share it with, avoid tables.
Avoid tables with a large user base that has varying skills: As in my budgeting example, if you have users with different Excel skills, it's best to avoid tables.

Possible Solutions for the FutureI've thought of several potential solutions that Microsoft could implement to improve the experience with table formulas.

Ultimately, we want to remove the scenario where we have bilingual workbooks and allow users to author or edit formulas in their preferred style.

Tables have been around since Excel 2007, and they are an integral part of the data analysis process. It would be great if more users could use them. Until then, it's probably best to avoid tables unless you know your users will be able to use them.

Share Your ThoughtsI'm curious to know what you think. Leave a comment below and let us know your experience with Excel Tables and what changes you'd like to see to make them more popular.

If you're new to Excel Tables or would like a refresher, check out my Excel Tables Tutorial Video.

Thanks for reading, and have a great day!

Link to post: I’ve STOPPED Recommending This Excel Feature (After YEARS of Trying)

View Details

Bottom Line: Create a fully customizable KPI scorecard chart in Excel using icons, emojis, and images to make your performance reports engaging and dynamic.

Skill Level: Beginner

Watch the TutorialWatch on Youtube & Subscribe to our ChannelDownload the Excel FileYou can access the Excel file used in the video here.

Scorecard Chart – Excel Campus.xlsxDownload Creating a KPI scorecard chart in Excel is an excellent way to visualize key performance indicators (KPIs) and goals. Instead of sifting through pages of boring numbers, you can create an engaging and interactive chart that clearly displays your data from a high-level perspective. This tutorial will guide you through the steps to create a fully customizable KPI scorecard chart in Excel, using icons, emojis, and even pictures to enhance your reports.

Why Use a KPI Scorecard Chart?A KPI scorecard chart is ideal for presenting high-level data about your team's performance against set goals. It provides a quick snapshot of areas that need attention without overwhelming the viewer with too much detail. For instance, you might use it to track metrics like leads, appointments, sales, deliveries, and support.

Setting Up Your Source DataBefore creating the chart, you need to set up your source data.

Input Scores: Create a table with your KPI categories and input scores. For example, 1 = Incomplete, 2 = Pass, and 3 = Fail.
Conditional Formulas: Use an IF formula to mark each score. For example, =IF(L10=J6, 1, 0) where L10 is the actual score, and J6 is the score in your legend.

If you could use some practice or a refresher for the IF Function, here's a tutorial that will help: IF Formula Tutorial for Excel – Everything You Need To Know.

Building the Chart1. Select Data: Highlight your data, excluding the score column, and insert a clustered column chart. 2. Clean Up the Chart: Remove unnecessary elements like gridlines, legends, and vertical axis labels to keep your chart clean. 3. Format Data Series: Set the gap width to zero and the series overlap to 100% to fill in the columns.

At this point, your chart should look similar to this:

Adding Icons to Your ChartIcons can make your chart more intuitive. Follow these steps to add them:

Insert Icons: Go to the Insert tab, select Icons, and choose the desired icons (e.g., checkmark for pass, X for fail, dash for incomplete).
Format Icons: Color the icons according to your preference. I chose green for pass, red for fail, and gray for incomplete.
Copy Icons: Copy each icon and paste it into the corresponding column in your chart using the Format Data Series pane, select Picture or Text Fill, then Clipboard as the Picture Source.

Using Emojis for a Fun TwistIf you want to add a bit of fun to your reports, you can use emojis instead of icons.

Insert Text Box: Create a text box and insert an emoji using the Windows key + period shortcut.
Copy and Paste: Adjust the size of the text box and copy it. Then, paste it into your chart the same way you did with the icons.

Customizing with ImagesFor a personalized touch, you can use images, such as pictures of team members or managers.

Insert Pictures: Go to the Insert tab, choose Pictures, and select the image you want to use.
Resize and Format: Resize the image as needed and paste it using the Format Data Series pane, as before.

Because the chart elements are linked to your data source, these charts are dynamic, so the images or icons will automatically change as your data changes.

Final TouchesTo ensure your chart looks professional:

Adjust Chart Size: Resize the chart to ensure icons or images look proportional.
Remove Gaps: Reduce any white space by adjusting the vertical axis maximum bound.
Final Formatting: Turn off the primary vertical axis for a cleaner look.

ConclusionCreating a customizable KPI scorecard chart in Excel is an engaging way to present performance data. Whether you use icons, emojis, or images, you can tailor the chart to suit your needs and make your reports stand out.

We'd love to hear how you plan to use this chart in your own work. Leave a comment below and share your ideas!

For more tutorials on different types of progress charts, check out our video series.

8 Types of Progress Charts
Creating Gauge Charts in Excel
Creating a Steps Chart in Excel
Create a Calendar Chart – Part 1
Interactive Calendar Chart with Weekly Goals – Part 2

Link to post: KPI Scorecard Chart in Excel – Fully Customizable

View Details

Bottom Line: Learn how to effectively use Excel's new REGEX functions to validate, extract, and replace data patterns, enhancing data accuracy and consistency with the help of AI tools.

Skill Level: Intermediate

Watch the TutorialWatch on Youtube & Subscribe to our ChannelDownload the Excel FileThe workbook that I use in the video can be accessed here:

REGEX Examples.xlsxDownload New REGEX FunctionsMicrosoft recently introduced three new REGEX functions in Excel that simplify finding and extracting complex patterns in your data. This tutorial will walk you through using these functions to prevent data entry errors with data validation and conditional formatting. You'll also learn how to leverage AI tools like Copilot or ChatGPT to make writing formulas easier.

Understanding REGEX FunctionsREGEX TestThe REGEXTEST function allows you to check if a specific pattern exists in your data. For example, to determine if account codes contain any numbers:

Insert REGEX Test Function
- Type =REGEXTEST(B6,"[0-9]") in the cell where you want to test.This pattern [0-9] checks for any numeric values.
Drag to Apply
- Then drag the formula down to apply it to the rest of the column. You’ll see TRUE for cells containing numbers and FALSE otherwise.
For Complex Patterns
- For more complex patterns, such as detecting phone numbers in scraped data, use a more detailed REGEX pattern.

Fortunately, we can use AI tools to help write these complex patterns for us. More on that in a minute.

REGEX ExtractThe REGEXTEXTRACT function is used to pull data matching a pattern from a text string.

Insert REGEX Extract Function
- Type =REGEXEXTRACT(B6, "your_phone_number_pattern") to extract phone numbers.
Return All Matches
- Modify the return to get all matches by using =REGEXEXTRACT(B6, "your_phone_number_pattern", 1). This will spill the results into adjacent cells.
Transpose Results
- To avoid spill errors, use TRANSPOSE(REGEXEXTRACT(B6, "your_phone_number_pattern", 1)).

REGEX ReplaceThe REGEXREPLACE function lets you find a pattern and replace it with specified text or another pattern.

Insert REGEX Replace Function

Use =REGEXREPLACE(B6, "your_phone_number_pattern", "replacement_format") to standardize phone number formats.

Apply to Dates

Similarly, format dates consistently using REGEXREPLACE.

Enhancing Data Validation with REGEXA great way to put REGEX to use is with data validation. You can ensure data integrity by incorporating REGEX into your data validation rules.

Copy REGEX Test Formula
- Copy your REGEX.TEST formula, e.g., =REGEXTEST(C7, "your_sku_pattern").
Apply Data Validation
- Then select the cells where you want the data validation applied, go to the Data tab, click Data Validation, choose Custom, and paste the formula. Adjust cell references as necessary.

Using Conditional Formatting with REGEXA great way to make noncompliant entries stand out is to use conditional formatting with REGEX. You can highlight cells that do not match the specified pattern.

Copy Formula
- Copy the REGEXTEST formula.
Apply Conditional Formatting
- Select the cells, go to the Home tab, click Conditional Formatting, choose New Rule, use a formula, paste the formula, and format the cells to highlight mismatches.

Once you hit OK, the entries that are out of compliance with your pattern will be highlighted.

Leveraging AI Tools for Complex PatternsCreating complex REGEX patterns can be challenging. Use AI tools like ChatGPT or Copilot to assist you. For example, request help from ChatGPT by providing the pattern requirements with a prompt such as, “Please help create a REGEX expression for the following pattern…”

ChatGPT not only gives you the expression, but breaks down its components for explanation. Copy the generated pattern into your Excel formula.

Another website that you can use is regex101.com, where you can test your patterns and get help learning the language for them.

ConclusionThese new REGEX functions in Excel enhance your ability to manage and validate data efficiently. Share how you might use these functions in the comments below. Of course, any questions are welcome there as well.

Thanks for taking the time to read this post!

Link to post: How to Use the New REGEX Functions in Excel

View Details

Bottom Line: Learn 5 Excel techniques that will help you grow as an Excel user.

Skill Level: Beginner

Watch the TutorialWatch on YouTube & Subscribe to our ChannelDownload the Excel FilesYou can download the Excel file that you see in the video. I've included both the before and after versions of the file below:

5 Excel Secrets.xlsxDownload 5 Excel Secrets I Wish I'd Known SoonerWith decades of Excel experience, there are things I know now that I wish I'd learned sooner in order to avoid spending too much time on tasks that can be done better, faster, and smarter. To save you from headaches, here are five Excel techniques that will improve the way you work with spreadsheets.

These tips will not only save you valuable time but also help you avoid common pitfalls and mistakes.

Streamline Data Selection with Keyboard ShortcutsNavigating large datasets can be a cumbersome task, especially when manually selecting data with the mouse. By mastering keyboard shortcuts like Ctrl + Shift + ↓, you can swiftly navigate to the end of your dataset with precision and efficiency. Say goodbye to tedious scrolling and hello to lightning-fast data selection!

Step-by-Step:

Open your Excel spreadsheet containing a large dataset.
Click on the cell from which you want to start your selection.
Press `Ctrl + Shift +↓` to quickly select all contiguous data in the column.
If your column has blank cells, a better shortcut might be Ctrl + Shift + End.
This will also select the contiguous columns to the right, but if you keep Shift pressed, you can reduce your column selection by clicking ←.

That's it! Your data is selected in seconds, saving you valuable time and effort.

Interested in more keyboard shortcuts? Try these:

7 Keyboard Shortcuts for Selecting Cells and Ranges in Excel
Excel Keyboard Shortcuts List
Best Keyboards for Excel Keyboard Shortcuts
Harness the Power of Lookup FormulasIf you're tired of scouring through rows of data to find a matching value, try using lookup formulas, your new best friend in Excel. With functions like XLOOKUP, you can instantly find and retrieve data based on specified criteria. No more manual searching.

Step-by-Step:

Open your Excel spreadsheet containing your dataset and the value you want to look up.
Enter the XLOOKUP formula in a cell where you want the result: =XLOOKUP(lookup_value, lookup_array, return_array)
Replace **lookup\_value** with the value you want to find, **lookup\_array** with the range where you want to search, and return_array with the range from which you want to return a corresponding result.
Press Enter, and your lookup formula will instantly return the desired result.

And of course, after writing your first formula, you can use the fill handle to copy the formula down to subsequent rows.

Here are some additional posts that give you more guidance in using lookup formulas:

The New XLOOKUP Function for Excel + Video Tutorial
Shortcuts for Writing XLOOKUP and VLOOKUP Formulas
When to use VLOOKUP instead of XLOOKUP
7 Essential Habits of VLOOKUP Pros
Automate Data Consolidation with Power QueryGone are the days of manual data consolidation! With Power Query, you can automate the process of combining and cleaning data from multiple files.

You will NOT miss the tedious copy-pasting once you master this unique tool for streamlined data consolidation. Basically, you can let Power Query do the heavy lifting while you focus on analyzing insights.

Click to enlargeStep-by-Step:

Open Excel and go to the Data tab.
Click on Get Data and select From File > From Folder.
Choose the folder containing your files and click OK.
Power Query will display a preview of the folder contents. Click Combine > Combine & Load to consolidate the data into a single table.
Customize your data transformation steps as needed and click Close & Load to load the consolidated data into Excel.

Here are more posts about how and when to use Power Query:

Power Query Overview: An Introduction to Excel’s Most Powerful Data Tool
Excel Clean Up with Power Query: Say Goodbye to Messy Data Forever
The 3 Benefits of Power Query
Create Dynamic Summary Reports with Pivot TablesWriting complex formulas for summary reports can be a daunting task. But Pivot Tables offer a powerful solution for creating dynamic and insightful reports in Excel. With just a few clicks, you can summarize and analyze large datasets without breaking a sweat.

Step-by-Step:

Select any cell within your dataset.
Go to the Insert tab and click on PivotTable.
Then choose where you want to place your pivot table and click OK.
Drag and drop fields from your dataset into the Rows and Values areas to create your summary report.
Finally, customize your Pivot Table layout and formatting as needed to meet your reporting requirements.

For more help with Pivot Tables, check out these posts:

How Do Pivot Tables Work?
Introduction to Pivot Tables and Dashboards
Pivot Table Checklist
Supercharge Your Workflow with MacrosDo you find yourself performing the same tasks repeatedly in Excel? It's time to unleash the power of macros! With macros, you can automate common Excel tasks, such as formatting headers and cleaning data. For increased productivity, let macros do the work for you while you focus on more important tasks.

Step-by-Step:

Open your Excel spreadsheet and go to the View tab.
Click on Macros and select Record Macro.
Name your macro, choose where to store it, and click OK to start recording.
Perform the desired actions, such as formatting headers or cleaning data.
Click on Stop Recording when you're done.
Lastly, to run the macro, go to the View tab, click on Macros, select your macro, and click Run.

Learn more about macros with these tutorials:

5 Uses for VBA Macros in Excel with Your Job
How to Create Macro Buttons in Excel Worksheets
How to Add Macro Buttons to the Excel Ribbon or Quick Access Toolbar

I also mentioned in the video that if you can't see the Developer Tab in your Ribbon, I have a video that shows how to make it appear: How to Enable the Developer Tab in Excel for Windows.

ConclusionThere you have it—five helpful Excel techniques to supercharge your productivity and accuracy. By mastering these techniques, you'll not only save time but also prevent common errors in your Excel projects. So what are you waiting for? Start implementing these techniques today and take your Excel skills to the next level!

Don't forget to leave a comment letting us know which technique you find most helpful.

Link to post: 5 Excel Secrets You’ll Be Embarrassed You Didn’t Know

View Details

Bottom Line: Learn to master VLOOKUP habits in Excel that are essential for error-free formula writing.

Skill Level: Intermediate

Watch the TutorialWatch on YouTube & Subscribe to our ChannelDownload the Excel FileYou can follow along using the same Excel workbook I use in the video. Download it here:

7 Essentials of VLOOKUP Pros.xlsxDownload 7 Helpful Habits When Using VLOOKUPIf you've encountered errors while using VLOOKUP in Excel, you're in good company. It's a tough formula to master, so it's good to develop go-to procedures that will eliminate such errors in the future.

Mastering VLOOKUP can significantly boost your productivity and accuracy. That's why I want to share with you 7 essential habits that will help you write VLOOKUP formulas flawlessly and efficiently.

1. Always Use Absolute References for a Table ArrayIn other words, be sure to drop the anchor.

One common mistake when writing VLOOKUP formulas is forgetting to make the table array an absolute reference. This can lead to errors when copying the formula down.

To avoid this, use the shortcut F4 (or Command + T on Mac) after selecting the table array in your formula to make it an absolute reference.

2. Don't Forget the Range Lookup ArgumentThe range_lookup argument in VLOOKUP is optional, but specifying it ensures an exact match. Always include FALSE or 0 for an exact match to prevent errors. You can use the shortcut Down Arrow ↓ followed by **Tab** to quickly select FALSE.

3. Trim Extra Spaces for Accurate MatchesInvisible errors caused by trailing spaces can lead to mismatched values in VLOOKUP. Use the TRIM function to remove extra spaces from text strings before performing the lookup. This ensures accurate matches and prevents errors.

4. Start with Naked VLOOKUPsBefore applying error handling techniques like IFERROR, evaluate your VLOOKUP formulas without them. This allows you to identify and fix errors in the lookup process before implementing error handling.

5. Understand When to Use VLOOKUP, INDEX MATCH, or XLOOKUPKnowing when to use each function is crucial for efficient data retrieval. While VLOOKUP is straightforward and quick to write, INDEX MATCH and XLOOKUP offer more flexibility and robustness, especially when dealing with changing data structures.

I think these tutorials will help you understand what I mean:

How to Use Index Match Instead of Vlookup
When to use VLOOKUP instead of XLOOKUP

Check out the video above to learn what two of the world's top Excel pros think about using VLOOKUP versus XLOOKUP.

6. Use Shortcuts for Faster Formula WritingSpeed up your formula writing process by utilizing shortcuts. For instance, use Ctrl + Shift + End to select the entire table array quickly. This reduces the need for manual selection and saves time.

Check out these helpful shortcuts for your lookup functions: Shortcuts for Writing XLOOKUP and VLOOKUP Formulas – Excel Campus

7. Avoid Formula Stuffing for Better DebuggingInstead of cramming multiple VLOOKUP formulas into one cell, separate them into individual cells or helper columns. This makes debugging easier and allows you to identify and fix errors more effectively.

Click to enlargePrintable GuideWe've created this one-page guide to help you remember and develop these 7 habits. Hope it's helpful to you!

7 Essential Habits of VLOOKUP.pdfDownloadConclusionBy adopting these seven habits, you'll streamline your VLOOKUP workflow, minimize errors, and become more proficient in Excel data management. Whether you're analyzing financial data or organizing inventory information, mastering VLOOKUP will make your tasks smoother and more efficient.

Leave a comment with your thoughts on other VLOOKUP habits or tips. Thanks for reading!

Link to post: 7 Essential Habits of VLOOKUP Pros

View Details

Do you prefer using the button or the line?

There are two quick and easy ways to apply borders to cells in Excel, and I'm curious to know what your go-to method is.

Video TutorialWatch on YouTube & Subscribe to our Channel

Downloads Borders Buttons or LinesDownload Cell BordersWhile the Border dropdownon the Home tab offers a standard set of options, sometimes you may need more customization.

That's when we can go to the Format Cells window by clicking on More Borders. (You can also access this window by pressing Ctrl + 1.)

On the Border tab of the Format Cells window, we have two options for adding and removing borders.

Technique 1: Using the Border ButtonsOne way to apply cell borders is by using the buttons provided. Clicking these buttons and then hitting OK allows you to apply borders to selected cells immediately.

This method is straightforward and convenient for quickly adding common border styles.

Technique 2: Using the Preview DiagramAnother approach is to utilize the Preview Diagram within the Format Cells window. Instead of relying solely on buttons, you can interact directly with the preview diagram to toggle borders on and off. Clicking on the lines in the diagram enables or disables specific borders, providing more detailed control over the appearance of cell borders.

This method offers greater customization, ideal for intricate border designs tailored to your needs.

ConclusionBoth techniques for adding cell borders in Excel have their advantages. While buttons offer simplicity and ease of use, the lines in the preview diagram provide better customization options. Ultimately, the choice between these methods depends on your preference.

I'm curious to know which technique you use: the buttons or the lines? Leave a comment and let us know.

Related Posts* Why Cell Borders Disappear When Hiding Rows & Columns + Video * Keyboard Shortcuts to Change Font & Fill Color or Cell Style * Find and Replace Formatting in Excel

Link to post: Adding Cell Borders in Excel

View Details

Bottom Line: Learn five issues and workarounds for the new PIVOTBY function recently introduced by Microsoft.

Skill Level: Intermediate

Video Tutorialhttps://youtu.be/AFrzpEfdWlAWatch on YouTube & Subscribe to our ChannelDownload the Excel FileYou can access the Excel file used in the video for free by clicking below.

5 Problems with PIVOTBY.xlsxDownload Excel's New PIVOTBY FunctionMicrosoft recently introduced two new functions for Excel: GROUPBY and PIVOTBY. These functions allow users to create summary reports using formulas. While these new features offer exciting possibilities, they come with their own set of challenges. In this blog post, we'll look into five problems with Excel's new PIVOTBY function and explore potential workarounds.

First, let's take a quick look at how they work.

GROUPBYThe GROUPBY function allows you to create a summary of the data you choose to see. In the example below, I've chosen to sum the quantities from rows that have the same color.

PIVOTBYPIVOTBY is similar, but it adds an additional option for column fields. In this example, I've added Years as my column fields, so that the quantities by color are broken out into the various years they were sold.

These two new functions are awesome, especially because any changes you make to the data are immediately reflected in the tables. With Pivot Tables, you must refresh the table whenever you make data changes. If you are not familiar with Pivot Tables, you can start learning about them with this tutorial: How Do Pivot Tables Work?

Although the automatic refresh is a pretty sweet benefit of GROUPBY and PIVOTBY, these functions present some problems when compared to Pivot Tables, in my opinion. Here are 5 of those problems and their workarounds.

Formatting ChallengesWhen comparing PIVOTBY to traditional Pivot Tables, one noticeable difference is in formatting options. Pivot Tables offer a variety of styles and layout options through the Design tab, making it easy to customize the appearance of the report. However, PIVOTBY lacks these formatting options, requiring manual formatting of headers and totals. Furthermore, formatting in PIVOTBY is not dynamic, meaning changes in data may not reflect in the formatting automatically.

WorkaroundConditional formatting can be used to dynamically format PIVOTBY reports. By setting conditional formatting rules based on specific criteria, such as the presence of Total rows, we can ensure consistent formatting even as data changes.

If you don't have much experience with conditional formatting, check out this tutorial on How to Apply Conditional Formatting to Rows Based on Cell Value.

Limited FlexibilityTraditional Pivot Tables are renowned for their flexibility, allowing users to quickly pivot or rearrange the report structure with simple drag-and-drop actions. In contrast, PIVOTBY lacks this flexibility, making it challenging to modify report layouts quickly.

WorkaroundA workaround involves creating a PIVOTBY Builder tool, which offers a more user-friendly interface for selecting row and column fields. While it may not replicate the drag-and-drop functionality of Pivot Tables, it provides a more structured approach to modifying PIVOTBY reports.

Since the formulas for this Builder can get pretty complicated, I recommend copying it from the Excel file included at the top of this tutorial rather than building it yourself.

Absence of Pivot Charts and SlicersAnother drawback of PIVOTBY is the absence of built-in support for pivot charts and slicers. Pivot Tables allow users to easily create visualizations and add interactivity through charts and slicers, like the ones pictured below, enhancing the presentation and usability of reports.

Click to enlargeWorkaroundOne workaround is to use a Dynamic Grid Slicer, which leverages checkboxes and formulas to mimic slicer functionality in PIVOTBY reports. While not as seamless as built-in slicers, Dynamic Grid Slicers offer a formula-based alternative for filtering data.

Here's a tutorial that explains the Dynamic Grid Slicer in more detail: New Excel Slicer for PIVOTBY.

Challenges with Totals and SubtotalsControlling totals and subtotals in PIVOTBY can be challenging, as the options for customizing these calculations are limited compared to traditional Pivot Tables. Additionally, subtotal columns may not be clearly identifiable, making it difficult to distinguish them from regular data.

You can see the difference by comparing the two images below (PIVOTBY shown on top and Pivot Table on bottom).

Workaround Conditional formatting can once again come to the rescue by applying formatting rules to subtotal columns, making them visually distinct from other data. By setting conditional formatting based on specific criteria, such as the presence of a blank cell in the subtotal columns, we can add the word “Total” to the subtotal and fill those columns with gray color to mimic what we are accustomed to seeing with Pivot Tables.

Here's what our PIVOTBY table looks like with the conditional formatting.

Limited Options for % of TotalsWhile PIVOTBY offers the option to calculate percentages of column totals, it lacks the flexibility of traditional Pivot Tables, which also allow for percent of row total calculations. This limitation may impact the types of analysis users can perform directly within PIVOTBY reports.

WorkaroundTo calculate percent of row totals in PIVOTBY, we can switch the row and column fields and then apply the percent of column total calculation. By manipulating the report structure and then using the TRANSPOSE function, we can achieve similar results to traditional pivot tables.

ConclusionIn conclusion, while Excel's new PIVOTBY function introduces exciting capabilities for creating formula-based reports, it also comes with its share of limitations. From formatting challenges to flexibility issues, users may find themselves grappling with various aspects of PIVOTBY compared to traditional Pivot Tables.

However, with creative workarounds, we can mitigate these challenges and leverage PIVOTBY more effectively.

Do you see yourself using PIVOTBY and GROUPBY in place of Pivot Tables? Leave a comment and let us know.

Link to post: 5 Problems with Excel’s New PIVOTBY Function

View Details

Bottom Line: Learn to create a date field that automatically updates weekly.

Skill Level: Beginner

Video TutorialWatch on YouTube & Subscribe to our Channel

Downloads Date for Same Day in Current Week.xlsxDownload I received an inquiry from Rhonda asking how to automate the regular update of a date field weekly.

The solution lies in leveraging Excel's built-in functions, TODAY and WEEKDAY, to dynamically calculate the date for each week.

If you're like Rhonda and would like to know how to avoid manually updating a date field each week, let me walk you through writing a formula to make it automatic, saving you time and effort.

How to Create a Date Field That Automatically Updates WeeklyLet's break down the process step by step:

TODAY Function: First, we begin by utilizing the TODAY function, which always returns the current date. This function is essential as it ensures that our date field updates every time the spreadsheet is opened or recalculated.
WEEKDAY Function: Next, we incorporate the WEEKDAY function. This assigns a number from one to seven to each day of the week. By specifying TODAY as the serial_number argument and 2 for the return_type to represent Monday, we obtain the numeric value corresponding to the current day of the week.
Mathematical Calculation: Now comes the math part. We subtract the numeric value obtained from the WEEKDAY function (representing the current day of the week) from the current date. Adding 1 to the result ensures that we obtain the date for Monday of the current week. For instance, if today is Friday (resulting in a numeric value of 5 for WEEKDAY), subtracting 5 from the current date and adding 1 would yield Monday's date for the current week.

Combine Into a FormulaTo streamline the process further, all these steps can be combined into a single formula. Basically, this formula dynamically calculates the date for the desired day of the current week.

Here is the formula:

=TODAY()-WEEKDAY(TODAY(),2)+1 If you want the formula to return a day other than Monday, just change the 2 in the WEEKDAY formula to the appropriate day designation. So, whether it's Monday, Wednesday, or Friday that you need, Excel has you covered.

ConclusionAutomating the update of date fields in Excel not only saves a little time but also minimizes the risk of errors associated with manual entry. As usual, by tapping into the power of Excel's functions, you can create efficient workflows that enhance productivity.

Questions or comments? We'd love to hear them. You can write them here.

Link to post: Automate Weekly Date Updates in Excel

View Details

The football squares pool betting game is a great way to add some fun to your Super Bowl party. Especially for people who aren't fans of the game or teams. It also adds excitement to boring games.

You can click the links below to download the printable or digital versions.

DownloadsPrintable PDFFootball Squares Pool.pdfDownloadTip: Print double sided if you want to use the signup sheet (details below).

Excel FileFootball Squares Pool – Updated.xlsxDownloadNote: File updated on 2/11/24 to fix an error in the formula that displays the winner in row 23.

If you've already filled out the previous Excel file, you can copy the formulas from F23:L23 on the Digital Pool sheet of the Updated file and paste them in your file. Or, you can determine the winners manually and type them in the sheet.

Google SheetsFootball Squares Pool – Go to File > Make a copy to save the file to your Google account and then share it with friends.

Video TutorialWatch on YouTube & Subscribe to our ChannelSquares Pool SheetsSquares pool sheets are a popular and entertaining way to keep everyone's interest in the game (in addition to hilarious commercials, buffalo wings, and smack talk, that is.)

Whether you prefer the tangible feel of paper or the convenience of digital, Excel Campus has you covered.

Printable VersionClick to EnlargeFor you classic pen-and-paper types, here's a printable version of the squares pool sheet. This version comes with a signup roster sheet, making it easy for the organizer to manage the pool.

Football Squares Pool.pdfDownloadDigital VersionsClick to enlargeAnd for those who prefer an online pool, these versions allow you to fill out your predictions digitally. Whether you're watching the game with friends remotely or in different locations, this online-friendly option ensures everyone can participate. Here's the version for Excel:

Football Squares Pool.xlsxDownloadAnd here's another sharable version made for Google Sheets:

Football Squares Pool – Go to File > Make a copy to save the file to your Google account and then share it with friends.

How to Use the Squares Pool SheetFirst, print the Squares Pool Sheet. Then follow the instructions below.

Bonus tip: If using the signup sheet, print double-sided so you only have to keep track of one piece of paper.

Set the Cost per Square and write it in the box along with the Total Pool amount.
See notes below for additional tips on determining the cost per square.
Have each player fill out the sheet by following these instructions:
1. Determine the number of squares you want to buy.
2. Pay for the squares.
3. Write your initials in any blank square. Repeat for the number of squares you purchased.
AFTER all squares have been filled, it’s time to fill in the numbers.
Write the numbers 0 to 9 in each box across the top and down the left side (gray squares) of the pool.

It’s best to write the numbers in random order. You can write the numbers on small pieces of paper and have someone choose the numbers out of a hat/bowl. Or ask your digital assistant (Siri, Google, ChatGPT) for random numbers. 4. Write the team names along the top and left side. This can also be random order.

Your grid is now complete, and you can watch the game.

If you start late, just get the grid filled in by the end of the first quarter.

ScoringAt the end of each quarter, write the score for each team in the box below the pool.

The winner is the person with the last digit of the score for each team.

Example: The score at the end of the first quarter is 14 – 7 (team 1 – team 2). The winning numbers are 4 for team 1 and 7 for team 2.

Find the square in the pool that matches these coordinates. That is your winner for the first quarter.

Repeat this for each quarter.

If the game goes into overtime, then use the final score instead of the fourth quarter score. Or do a new game for overtime.

PayoutThe payout per quarter is completely up to you.

Typically, the final score will be worth more than the other quarters. To do this you can multiply the cost per square by 20-20-20-40 for each quarter.

If you want to keep it even for each quarter, then multiply the cost per square by 25.

Tips on setting the Cost per SquareDetermining the cost per square will depend on the number of people who want to play and how much they want to bet. There are 100 squares total. If you have 10 people and they are each comfortable with betting around $10 to $20, then you might want to make the cost per square $1.

Total Pool = $100 / (10 people x $10 bet) = $1 per square

The more people you have, the higher the cost per square can be. If you have 25 people and they are each comfortable with betting $10 to $20, then you might want to make the cost per square $5.

Total Pool = $500 / (25 people x $20 bet) = $5 per square

Max Squares Per PersonThis is optional. It prevents someone from buying a lot of squares and not leaving enough for everyone else. If you have leftover squares at the end, you can raise the max limit.

Using the Signup SheetThe signup sheet is optional but might be a good way for the organizer to keep track of all the bets and the number of squares left. When having each player fill out the pool in step 3, you can also have them fill out the signup sheet.

The Name and Initials columns are nice because often you will have people with the same initials. Especially if it’s a family party.

Players will be able to scan the list to see if their initials are already used, and then add a middle initial or something that makes it unique. Could even be a letter and number. (John the 3rd is J3)

Running Total ColumnThe Running Total column tracks the number of squares that have been purchased. To calculate this, take the total number of squares in the same row and add it to the Running Total number in the row ABOVE.

This gives you the running total, so you know how many squares you have left for purchase. That will be 100 – running total.

The Bet $ is the total amount owed per player. # of Squares x Cost per Square $.

The Paid column allows you to check off if the player paid yet. Handle IOUs at your own risk. 😉

Bonus tip: If you have more than 30 players then print an additional signup sheet.

Using the Digital VersionThe digital version works the same basic way as the print version.

The major difference is picking squares. There is a dropdown list in each square where the player can pick their initials. The dropdown list is based on the initials column in the Signup Table. This just makes it easy so players don’t have to type initials in each selected square.

Bonus tip: you can copy paste in squares instead of picking from the list.

The running total is automatically calculated in the total row of the table (cell R34).

The table can be extended to add more players.

The Squares Picked column automatically calculates the number of squares that the player has picked. This makes it easy to see any discrepancies between squares purchased and picked.

The Bet $ is also automatically calculated. Paid can be selected from the Yes/No dropdown.

Share the FunIt might feel like there are a lot of rules, but the football squares pool game is easy once you get going. It’s definitely a crowd favorite that can keep boring games interesting. And people that aren’t football fans will also be excited about the game.

Spread the word about these free sheets by telling your friends to visit excelcampus.com/pool to access their copies.

Free Excel Pro Tips NewsletterIf you love Excel, or are forced to use it for work, then it's always a good idea to improve your Excel skills. Join our Free Excel Pro Tips Weekly Newsletter to get Excel shortcuts and tips straight to your inbox.

ConclusionAs the anticipation for the big game grows, Excel Campus invites you to elevate your experience with squares pool sheets. What other elements are must-haves for your Super Bowl parties? Drop a comment and let us know how you'll be celebrating.

Link to post: Football Squares Pool – Free Downloads for PDF, Excel, Google Sheets

View Details

Bottom Line: Learn to create a new type of slicer or filter that you can use to make interactive reports and dashboards in Excel.

Skill Level: Intermediate

Watch the Video TutorialWatch on YouTube & Subscribe to our Channel Download the Excel FilesYou can follow along using the following Excel files.

Dynamic Grid Slicer for PIVOTBY.xlsxDownload In today's tutorial, we'll explore a new approach to slicers for creating interactive reports and dashboards in Excel. Microsoft recently introduced GROUPBY and PIVOTBY functions, alternatives to pivot tables for data analysis and summary reports. However, these new functions lack slicers for interactivity.

To address this, we'll build a Dynamic Grid Slicer as a workaround.

Let's dive into the details.

Understanding the SetupTo set the stage, we have a summary report generated by the new PIVOTBY function. This function is pulling from source data containing order details. The report shows the number of units sold by color and with a year-over-year comparison.

From this table, I've created a simple bar chart to display the data visually.

With a normal pivot table, we can easily add slicers, which allow us to filter data down, but that's not the case with a PIVOTBY table. So we're going to replicate the interactivity of regular pivot tables and slicers using a combination of formulas and checkboxes.

Dynamic Grid Slicer FeaturesOur Dynamic Grid Slicer mimics the functionality of regular slicers. It includes a dropdown button for selecting columns, checkboxes for applying filters, and automatic recalculation when source data changes.

The term “dynamic” in the slicer's name comes from its ability to adapt to changes in the source data. Unlike traditional slicers, the Dynamic Grid Slicer offers automatic refresh and recalculation, ensuring real-time updates without manual intervention.

Creating the Dynamic Grid SlicerLet's look into the process of creating the Dynamic Grid Slicer. While using a regular pivot table and slicer is easier, this tutorial accomodates those who prefer a formula-based approach. Even if you don't plan to use Dynamic Grid Slicers, you'll learn valuable techniques for dynamic array formulas.

I'll address the challenges with GROUPBY and PIVOTBY in an upcoming video, so be on the lookout for that in the future.

Setup and FormulasLet's explore the setup for the Dynamic Grid Slicer. We need a list of unique values that will be used to create the filter list. The UNIQUE function, combined with the INDIRECT function for dynamic referencing, helps create a list of unique values for user filters.

Additionally, a hidden column with the FILTER function generates a list of values where the checkbox has been checked. This list will be used for the Filter argument in PIVOTBY. The overflowing spill range ensures dynamic updates as the list changes.

Applying Filters in PIVOTBYTo apply filters in PIVOTBY, we use the filter_array argument. A complex formula using ISNUMBER and XMATCH facilitates the filtering process, ensuring that the results dynamically adjust to the selected values in the Dynamic Grid Slicer.

Click to enlargeFor a more detailed explanation of each part of this formula, watch the video at the top of this tutorial.

The checkboxes are added using the Checkboxes in Cells feature, found on the Insert tab of the Ribbon.

Watch this tutorial for more information about how to add checkboxes: New Checkboxes in Excel.

You can use conditional formatting to make invisible the entries that aren't being displayed for each filter list. You can see the various rules that I use listed under Conditional Formatting in the Excel file I've provided above.

Bonus Features In an attempt to recreate the Clear-All button on a regular slicer, I've added a button at the top of the Dynamic Grid Slicer that allows us to reset all filters.

This button is actually just an image of a checkbox, which is linked to a named range that includes all of the checkboxes. So by clicking the spacebar, you can check or uncheck all of the checkboxes in the list.

Moreover, we can limit dropdown columns using another Dynamic Grid Slicer. Using the slicer below, I simply check or uncheck the column names that I want to be available for filtering in the dropdown list of my first slicer.

Feel free to experiment with multiple grid slicers on a sheet. Again, the file used in this tutorial is available for free download above.

NoteBoth PIVOTBY and the new checkboxes are on the Beta channel for Microsoft 365 at this time. Hopefully, we will see wider availability in a future release soon.

ConclusionThanks for reading this tutorial on creating interactive and dynamic reports with the new PIVOTBY function. Please share your thoughts and questions in the comments below, and stay tuned for our upcoming video comparing PIVOTBY and GROUPBY with regular pivot tables.

Have a great day!

Link to post: New Excel Slicer for PIVOTBY

View Details

Bottom Line: Learn how Power Query can be used to quickly clean up messy data.

Skill Level: Beginner

Whether you're dealing with messy spreadsheets or CSV files, the process of cleaning up data can be daunting.

Fortunately, Excel provides powerful tools like Power Query to streamline this task. In this guide, we'll walk you through the process of data cleanup using Excel, focusing on the invaluable Power Query feature.

Video TutorialWatch on YouTube & Subscribe to our Channel

DownloadsYou can download the example files from our full Power Query guide linked here.

Why Data Cleanup MattersBefore diving into the details, let's understand why data cleanup is essential.

Messy data can lead to inaccuracies, hinder analysis, and make it challenging to derive meaningful insights. Whether you're working with task lists, sales data, or any other dataset, a clean and organized structure is the foundation for effective data analysis.

Using Power Query for Data CleanupOne of Excel's hidden gems for data cleanup is Power Query. In the provided example, we have a daily task list in CSV format that needs refinement. Here's a step-by-step guide:

1. Importing DataBegin by navigating to the “Data” tab and selecting “From Text/CSV.”

Locate your file and hit “Import.” Once imported, click “Transform Data” to open the Power Query editor.

2. Removing Unnecessary ColumnsIdentify columns that are surplus to your requirements. In this example, the shipping address information is unnecessary. Select the relevant columns, right-click, and choose “Remove Columns.”

3. Eliminating Blank RowsCleanse your dataset further by removing any blank rows. A clutter-free dataset ensures accurate analysis and visualization.

Check out our tutorial on other ways to eliminate blank rows here.

4. Sorting DataOrganize your data according to your needs. In our example, the boss wants the data sorted by customer name. Utilize the sorting options in the Power Query editor to achieve this.

5. Closing and Loading the DataOnce your data is refined, close the Power Query editor and load the results into a new sheet in the workbook. This ensures a neat and organized presentation of your cleaned-up data.

Automating the Process with Power QueryOne of the standout features of Power Query is its ability to automate the cleanup process.

Simply refreshing the query reruns all the defined steps, making it a breeze to update your data regularly.

This is particularly handy for tasks like daily updates or handling dynamic datasets.

ConclusionMastering data cleanup with Excel, especially with the help of Power Query, empowers you to efficiently manage and analyze your data.

Don't forget to check out our full guide and download the example files here.

Will you be using Power Query to clean up your data? Let us know in the comments below.

Link to post: Excel Clean Up with Power Query: Say Goodbye to Messy Data Forever

View Details

Bottom Line: Learn how Power Query can be used to merge Excel tables.

Skill Level: Beginner

Historically, VLOOKUP and its successor XLOOKUP have been the go-to functions for Excel users to retrieve data based on a key.

However, these functions come with limitations, such as one-dimensional searches and potential errors with non-exact matches. So, what is the alternative? Enter Power Query!

Video TutorialWatch on YouTube & Subscribe to our Channel

Power Query vs Traditional Lookup FormulasPower Query, a robust tool integrated into Excel, offers a transformative approach to data manipulation.

Instead of relying on conventional lookup functions, Power Query provides a more dynamic and versatile way to merge tables, transform data, and perform intricate tasks with ease.

Leveraging Power Query for Efficient Data RetrievalSetting the Stage: Preparing Your DataBefore diving into Power Query, make sure your data is structured correctly. Consistency in column formats, clear headers, and distinct identifiers are crucial for accurate results.

A Step-by-Step Guide to Power Query's Merge Feature1. Accessing Power Query: Navigate to the ‘Data' tab on the Excel ribbon and select ‘From Table or Range'.

Configuring the Merge: Choose the primary table and column (e.g., ‘rep ID') you wish to match. Subsequently, select the secondary table and its corresponding identifier column.
Finalizing the Merge: Adjust any additional settings as required and confirm the merge operation.

Expanding and Finalizing the DataOnce the merge operation is complete, use the ‘Expand' option to select columns from the secondary table.

This action ensures only relevant data is incorporated into your primary dataset. The merged data is now ready for analysis or further processing.

Enhancing Your Excel Experience with ShortcutsKeyboard ShortcutsShortcuts are not just about speeding up tasks; they also enhance precision and reduce the risk of errors. Familiarizing yourself with keyboard shortcuts can save considerable time and effort, transforming your Excel experience.

Check out our 15 Power Query Shortcuts guide here.

The Art of EfficiencyPractice makes perfect. Dedicate time to learn and internalize essential Excel shortcuts. From simple navigation commands to complex data manipulation actions, each shortcut contributes to a seamless and efficient workflow.

Full Tutorial and ResourcesIf you're excited about Power Query and want to learn more, we have a full tutorial on 5 ways Power Query can replace other Excel functions and help automate routine tasks.

ConclusionBy embracing the capabilities of tools like Power Query and harnessing the efficiency of Excel shortcuts, you can elevate your proficiency and achieve remarkable results.

Will you be using Power Query instead of XLOOKUP? Let us know in the comments below.

Link to post: Power Query Replaces XLOOKUP: Excel Data Merging Made Simple

View Details

As we dive into the new year, updating dates in Excel sheets for 2024 is on everyone's agenda. The task may seem daunting, especially if you're dealing with numerous sheets or cells.

In this blog post, we'll walk you through a handy Excel shortcut that will simplify the task of updating headers and values across multiple sheets.

Video TutorialWatch on YouTube & Subscribe to our Channel

The 2024 Header Date UpdateOne common task when updating Excel sheets for the new year is changing the headers to reflect the current year. Manually updating each cell can be tedious and time-consuming. Fortunately, there's an Excel shortcut that can help automate this process.

The Ctrl + H ShortcutTo update all the headers for 2024, follow these simple steps:

Select All Cells: Begin by selecting all the cells containing the headers you wish to update.
Open Find and Replace: Press Ctrl + H on your keyboard to open the Find and Replace dialog box.
Enter Values: In the “Find what” field, type 2023. In the “Replace with” field, type 2024.
Execute Replace: Press Alt + A to execute the “Replace All” command. This will replace all instances of 2023 with 2024 in the selected cells.

Updating Dates Across Multiple Excel SheetsWhat if you have multiple sheets in your workbook that need updating? The process is similar but requires an additional step to ensure accuracy.

Workbook vs. SheetWhen updating across multiple sheets, you'll need to specify whether you want to update the entire workbook or just the current sheet. Here's how:

Go to Options: Navigate to the “Options” menu in Excel.
Choose Workbook: Select “Workbook” instead of “Sheet” in the Find and Replace dialog box.
Be Cautious: Before executing the replace command, ensure that there are no other values in the workbook that match the criteria. This will prevent unintended changes to your data.

Using Forward Slash for PrecisionTo avoid replacing values that contain the target year within other data, you can use a forward slash as a prefix. This ensures that only dates with the specified year are updated, leaving other values intact.

The Power of Excel ShortcutsExcel shortcuts are invaluable tools for anyone looking to optimize their workflow. They allow you to perform tasks quickly and efficiently, reducing the time spent on manual operations. Whether you're a seasoned Excel user or just getting started, mastering shortcuts can significantly boost your productivity.

You can find our full list of Excel shortcuts here.

ConclusionUpdating Excel sheets for the new year doesn't have to be time-consuming. By leveraging the power of Excel shortcuts, you can streamline the process and ensure accuracy across multiple sheets.

The Ctrl + H shortcut can save you time and effort, allowing you to focus on more critical tasks.

Link to post: Excel New Year’s Shortcut: Update Dates

View Details

If your selection is at the top of a column, and you want to quickly select the entire column, you can use the keyboard shortcut Ctrl + Shift + ↓. However, this ONLY works if there are no blank cells in the column.

Columns that have blank cells require you to repeatedly hit the down arrow to make it all the way down to the end.

So, is there a workaround?

There are two things you could do to achieve the same result and potentially save you some time.

Video TutorialWatch on YouTube & Subscribe to our Channel

Downloads Select Column with Blanks Example.xlsxDownload Option 1The first is to hit Crtl + Shift + End. This will take you all the way to the bottom right cell in your data range.

From there, you can hold Shift down while you hit the left arrow until only your desired column is selected.

Option 2Another option is available if you have a column with no blank cells directly adjacent to your desired column selection. If so. You can start from the top of that non-blank column and hit Ctrl + Shift + ↓.

Then Shift + →.

Then hit Tab to put the active cell at the top of your desired column.

And finally, Shift + → to deselect the original column.

Excel TablesIf you are using an Excel Table, the process is much easier. With any cell in the desired column selected, all you have to do is hit Ctrl + Space. That will select the entire column.

Do you use any of these methods, or do you find yourself just hitting the down arrow multiple times? Or maybe you tend to use the mouse instead? I'm curious to know your preference. Tell me in the comments, and feel free to leave any questions there as well.

Link to post: Selecting Entire Columns That Have Blank Cells

View Details

Are you looking for gift ideas for a fellow Excel fan, co-worker, or data geek this holiday season?

In this post, I share some of my favorite holiday picks and exclusive offers for our Excel Campus community.Happy holidays!

Featured Gift: Elevate ExcelYou can now give the gift of our comprehensive Excel training program, Elevate Excel! You can purchase an entire year for $490. There will be an option to check a box labeled “Is this a gift?” at check-out.

What is Elevate Excel?

Elevate is our subscription-based, beginner to advanced-level Excel training course with access to our entire training library. With our 30-day money-back guarantee you can purchase the training for someone else 100% risk-free.

Please use your name and information at checkout. You will have the option to include your recipient's information after check-out.

Give the Gift of Excel Training

Price Range $20 – $40First up, here is a list of products your favorite Excel/data geek might like for under $40.

TB4A.com ($20 – $30+) Use coupon code EXCELCAMPUS for 10% off your purchase. Shop the Big4Accountant “Excel Humor” category for Microsoft-based merchandise, or explore their selection of hats, candles, onesies, and shirts custom-made for the accountant in your life.

Their products range in the $20-$30 range, and with our 10% discount (EXCELCAMPUS) you can find an affordable gift for the data geek in your life.

SHOP NOWFunction 101 ($30-$50)Use Coupon Code EXCELCAMPUSJON for 10% off your purchaseFor the Mac users in your life, Function101 creates products with a minimalistic design that complements a whole range of Apple hardware.

SHOP NOWBaronfig Desk Organizers ($20+)Baronfig designs tools for “thinkers.” Their selection of planners, notepads, stickies, and other office tools is perfect for the minimalists on your gift list who prioritize utility in the workplace.

I especially like their neutral colors and clean lines. These would make excellent “Secret Santa” gifts for coworkers or friends who are difficult to shop for.

SHOP NOWThe 40 Greatest Excel Tips of All Time ($25)This book contains Excel tips, shortcuts, jokes, and even cocktail recipes. The content was crowdsourced from over 300 Excel users (myself included).

It's a great book to keep on your desk or coffee table. Every time you pick it up you will learn something new and have a good laugh.

SHOP NOWThe Excel Campus Shortcuts Mousepad ($29.95)Our very own shortcuts mousepad! With tons of everyday shortcuts plus a direct link to our free downloadable PDF list of 200+ shortcuts. Color: White Size: 8.7″x7.1″

SHOP NOWNew Microsoft Number Pad ($29.99)Sleek and slim, with a premium finish, work with numbers faster and more naturally while making your Windows 11 Home/Pro and Windows 10 PC or compact keyboard more productive.

BUY NOW

Price Range $40 – $100Next, here are my top picks for budgets between $40 and $100.

Microsoft Gift Card ($50)Give the gift of apps, games, and more to use at the Microsoft Store online, on Windows, and on Xbox. You'll receive an email with a digital code that your gift recipient can use to buy what they want.

SHOP NOW

Price Range $100+And finally, here are our top picks for budgets of $100 or more.

Insta360 Link ($299)Another cool item for those who work at home is the Insta360 Link Webcam.

It's a webcam that follows you around the room. So it's great if you do a lot of virtual meetings, but are sick of sitting behind the desk or want to present in front of a whiteboard.

My friend Justin has a video review of it with more detail.

SHOP NOWSurface Pro 8 ($600-$1000)I switched to digital notetaking about two years ago, and absolutely love it. One thing I really like about the Surface pen is that it has an eraser. Even though it's digital, you can flip the pen and erase anything you've written. This is something the Apple Pencil doesn't have.

I use Microsoft OneNote to store all my notes. The nice part is that you can type or write in OneNote and EVERYTHING is searchable, including your handwriting.

SHOP NOWHappy HolidaysI hope this list helps you find that perfect gift or spark some ideas.

Please leave a comment with any questions or gift ideas you have this year. Thanks and Happy Holidays! 🙂

Link to post: The 2023 Holiday Gift Guide for Data Diehards

Insta360 Link - PTZ 4K Webcam with 1/2" Sensor, AI Tracking, Gesture Control, HDR, Noise-Canceling Microphones, Specialized Modes, Webcam for Laptop, Video Camera for Video Calls, Live Streaming

View Details

Bottom Line: Check out the new Microsoft Office theme, see how it's different from the old one, and learn how to migrate to the new theme or revert back. Skill Level: Beginner Watch the Tutorial Microsoft's New Office Theme Microsoft recently rolled out a brand-new theme for its Office products, and—woah!—purple has entered the building! […]

Link to post: New Microsoft Office Theme 2023 – How to Migrate or Revert Back

View Details

Happy Tuesday! 🌏 Today I'm sharing some of my favorite Excel-related content you should know from the last week, as well as trending Excel content you should know. Microsoft Excel Tips & Updates: Watch on YouTube & Subscribe to our Channel Creating Gauge Charts in Excel: First up, we introduce the world of Microsoft Excel speedometer charts! […]

Link to post: The Excel Digest (Excel You Should Know): September 12th 2023

View Details

Bottom Line: Learn to create a chart that looks like a gauge you might see on a car's dashboard. Skill Level: Intermediate Excel Files Download the before and after Excel files to follow along as we build the chart. Watch the Tutorial The step-by-step instructions can be viewed here. A Surprising Favorite Recently, I created […]

Link to post: Creating Gauge Charts in Excel

View Details

In a recent post, we looked at how to pull data from several sheets and combine it into vertically stacked data using the VSTACK function. Here's the tutorial: VSTACK to Combine Multiple Sheets. Video Tutorial Watch on YouTube & Subscribe to our Channel Downloads You might have noticed that when we stacked the data, we […]

Link to post: Exclude Blank Rows from VSTACK Data

View Details

Happy Tuesday! 🌏 Today I'm sharing some of my favorite Excel-related content from the last week, as well as trending Excel content you should know. Microsoft Excel Tips & Updates: Watch on YouTube & Subscribe to our Channel View this post on Instagram A post shared by Jon Acampora (@theexcelcampus) Learn How to Lock Down Excel Dashboards […]

Link to post: The Excel Digest (Excel You Should Know): August 29th 2023

View Details

Bottom line: Learn why the cell borders disappear when hiding or collapsing rows and columns, and how to fix it. Plus video tutorial. Skill level: Beginner Video Tutorial on Disappearing Cell Borders Watch on YouTube & Subscribe to our Channel Downloads Why Do The Cell Borders Disappear? It all depends on what cells the borders were […]

Link to post: Why Cell Borders Disappear When Hiding Rows & Columns + Video

View Details

Link to post: The Excel Digest (Excel You Should Know): August 15th 2023

View Details

What do you do if you want to combine the data from multiple sheets into one sheet, stacking the data from each sheet? There are many ways to stack data in Excel, but I do not recommend using Copy & Paste to do so because when data changes, you have to repeat the process all […]

Link to post: VSTACK to Combine Multiple Sheets

View Details

Happy Tuesday! 🌏 Today I'm sharing some of my favorite Excel-related content from the last week. Do you have any questions or Excel discoveries to share? Post them in the comments section below the post. Microsoft Excel Tips & Updates: Watch on YouTube & Subscribe to our Channel First up: a vibrant change or controversial choice? In […]

Link to post: The Excel Digest: August 8th 2023

View Details

Save a step by naming your new sheets right when you create them. Video Tutorial Watch on YouTube & Subscribe to our Channel Downloads You're probably familiar with the plus-shaped button + at the bottom of your workbook that creates a new sheet. One click, and instantly a new sheet appears. However, if you double-click […]

Link to post: Double Click New Sheet Button

View Details

There are two different ways to calculate percentage change in Excel, and people can be pretty adamant about which way is better. Let me show you both. Video Tutorial Downloads First, what do I mean by “percentage change”? I'm just talking about the difference between an old value and a new value expressed as a […]

Link to post: 2 Ways to Calculate Percentage Change in Excel

View Details

If you were to ask me which Excel shortcut I use most often, I would definitely say Ctrl + Arrow Keys (↑ ↓ → ←). I use these all the time to navigate and select cells. Video Tutorial Downloads Let's look at how these work. In the example below, when I use Ctrl + ↓, […]

Link to post: Navigating Cells and Ranges

View Details

There are a lot of different chart types we can use to visualize progress toward a goal. In this post, I share eight progress charts you can use in your reports and dashboards. Most progress charts or graphs display the percentage complete out of 100%. However, I've also included the Steps, Scorecard, and Calendar charts. […]

Link to post: 8 Types of Progress Charts

View Details

A great time-saving trick is to use a prefix when naming your tables. Video Tutorial Downloads I use “tbl”, but some people use “t_” as the prefix. You can use whatever you want, but the key is to be consistent. Why is this a good habit to get into? Because it makes writing formulas easier. […]

Link to post: Use a Prefix for Table Names – Excel Best Practice

View Details

Excel Bad Habit #14: Filling Cells with White Instead of Turning Off Gridlines Sometimes, you may want to remove the gridlines from your Excel sheet to have a simple white background. Video Tutorial Downloads Often, people will accomplish this by selecting all and then formatting the cells with a white fill. Why is this the […]

Link to post: Turn Gridlines ON or OFF

View Details

One of the things I like best about Excel Tables is ALSO one of the things I like the least.

I'm talking about table formulas.

Video TutorialWatch on YouTube & Subscribe to our ChannelDownloads Table Formulas.xlsxDownload For example, when I create a simple formula in the Excel Table below, multiplying cell C2 by cell D2, Excel writes those as [@Qty] and [@Price] when I select them. Using these column header names—or structured references—instead of cell designations can cause the formulas to look longer or different from what many people are used to.

While some people find structured references easier to read, to others they make the formula appear more convoluted. This is especially true if the formulas are long or the column headers are not well labeled. This use of structured references might cause some confusion for your boss, coworkers, or other users who are trying to understand your formulas.

Turning Off Structured ReferencesYou can actually turn off the use of structured references if it's helpful for you. To do so,

Go to File.
Click Options.
Select Formulas.
Uncheck the box labeled Use table names in formulas.

Now, when we type a formula in the Excel Table, it will use cell names instead of column header references.

Just know that turning off the table names is part of the Excel settings for your computer. It's an application-level setting. That means you'll need to change the settings for each computer you use.

If you turn the setting off on your computer and send a file to another user, the setting will NOT be turned off on their computer.

How do you feel about structured references? Do the header names make the formulas more or less confusing to read and write for you? Leave a comment and let me know your thoughts.

Link to post: My Love/Hate for Table Formulas

View Details

Autofilling dates saves time and reduces manual input errors. Let me share 5 tips to help you autofill dates like an Excel boss!

Video TutorialWatch on YouTube & Subscribe to our ChannelDownloads Auto Fill Hacks.xlsxDownload 1. Drag Down the Fill HandleWith your date cell selected, hover over the bottom right corner until the cursor becomes a plus symbol. This is called the fill handle. If you left-click the handle and drag it down, Excel will autofill dates in sequential order all the way down to where you stop dragging.

Double-Click the Fill HandleThis only works if there is corresponding data in the column to the left of your date. If you double-click on the fill handle, Excel will autofill all the way down to the end of the contiguous data set. In other words, it will stop when there is a blank cell in the column to the left.
Autofill with Copied Values Instead of Sequential If you are looking to copy the same date all the way down instead of autofilling with sequential dates, you can start by doing either of the two options above and then choosing Copy Cells in the Autofill Options menu found in the little box that appears at the bottom right corner of the filled cells.

The result looks like this:

You'll notice that the Autofill Options menu has some other cool items to choose from like filling only weekdays or filling sequential months or years. Here's how it looks when I choose Fill Months.

Ctrl + Drag to Copy DownAnother way to copy the cells down is to drag the fill handle down, as we did in the first tip, but press Ctrl before letting go.
Drag to the Right to Fill HorizontallyYou can also drag the fill handle to the right instead of down if you want to autofill horizontally.

Bonus Tip: If you drag the fill handle to the left or upwards, the dates will autofill in REVERSE sequential order.

Do you use autofill often? It's a great habit to get into.

Feel free to leave a comment about this post by clicking here.

Link to post: How to Autofill Dates in Excel

View Details

When your workbook only has a few sheets, you can easily navigate to them using the tabs at the bottom of Excel. But for workbooks that have lots of sheets, I recommend adding a Table of Contents as your first sheet.

Video TutorialWatch on YouTube & Subscribe to our ChannelDownloads Table of Contents Macros.xlsxDownload List-Style Table of ContentsOne format for your Table of Contents is a simple list, where each item in the list is a link that pulls you over to the corresponding sheet.

I've created a macro so that you can add this Table of Contents to any workbook in an instant. You can copy the macro from this tutorial:

Excel Table of Contents that Automatically Updates

Gallery-Style Table of ContentsA visually appealing alternative to the list-style format is a Table of Contents Gallery. This format shows screenshots from each of your worksheets. Each of the images links to its corresponding sheet so that you can jump to the sheet you want with one click.

For instructions about how to insert this type of Table of Contents into your file, check out this tutorial:

How to Add a Table of Contents Image Gallery Sheet to Your Excel Files

Help with MacrosIf you don't have much experience working with macros, check out my free 3-part video series on getting started with macros & VBA.

I hope these free tools are helpful for you and impress your boss and coworkers! If you have questions or feedback, leave a comment below.

Link to post: Table of Contents Macros

View Details

A friend called me the other day with an Excel question. He came across a formula in one of his reports that didn't look the way he would have written it.

Video Tutorial Watch on YouTube & Subscribe to our ChannelDownloads IF Statement Logic – Greater Than Zero-IMAGES.xlsxDownload It's a simple IF formula to calculate the difference between budget numbers and actuals.

The way it was written was =IF(D5, D5-D4,””), where D5 would be the budget number and D4 would be the actual number.

Because the first D5 in the formula was all by itself and didn't have any logical statements attached to it, he was thrown off. My friend would have written the formula =IF(D5>0, D5-D4,””). Here are both formulas so you can compare them:

In my friend's version, the formula is saying: “If the value in D5 is greater than zero, subtract the budgeted number from the actual number, and if not, return a blank.”

The first version would be translated more like this: “If there is a value in cell D5, subtract the budgeted number from the actual number, and if not, return a blank.”

Which is better?So is the first formula better because it's shorter?

Not necessarily. An important factor in building your formulas is using logical statements that make sense to you and/or your audience. If a formula is confusing, it may be best to go another route.

One thing to note is that in the example above, the report is dealing with values that are always positive numbers (like Sales, for example). But if we were dealing with projected numbers that might be negative (Revenue/Expenses, for example), the first formula might be a better choice because it would show values for negative months.

On the other side of the coin, however, if my friend were dealing with negative numbers or zeros for his actuals, he could alter his formula to read: =IF(ISBLANK(D5),””,D5-D4). In other words: If the Actual is blank, then return a blank cell, otherwise, subtract the Budget from the Actual. This would have the same effect but might be more logical to his and many people's way of thinking.

For either formula, when dealing with negative numbers, I would recommend adding some number formatting to make the font red (as above) or conditional formatting to change the fill color when a number is negative. This would better highlight that the variance is a negative thing instead of a positive thing.

I explain conditional formatting in these tutorials:

How to Apply Conditional Formatting to Rows Based on Cell
Conditional Formatting for List of Partial Matches
Compare Two Sheets for Duplicates with Conditional Formatting
Enhance Your Checkboxes with Conditional Formatting in Excel

ConclusionEven with negative numbers, the point remains the same: write your formulas in a way that makes the most sense to you and your audience.

Thoughts, comments, or questions? I'd love to hear them. Leave a comment by clicking here.

Link to post: IF Statement Logic Comparison

View Details

I've got two shortcuts for you if you'd like to filter data faster.

Video TutorialWatch on YouTube & Subscribe to our ChannelDownloads Filter By Cell Value.xlsxDownload Shortcut #1: Open Filter Drop-Down Using Alt + ↓With a cell in the header row selected (as long as you have filters applied), when you use Alt + ↓ , it will open up the filter drop-down menu.

To navigate the menu using the keyboard instead of the mouse, you can use the up and down arrows or type the underlined letter to select the option you wish to use. For example, typing the letter O in the menu below will sort the column in reverse alphabetical order.

Shortcut #2: Jump to Search Using EYou can also speed up your filtering by using the search box in the drop-down menu. The keyboard shortcut to jump to the search box is the letter E.

From there, you can type whatever you are searching for and hit Enter to apply your search.

Bonus Tip: Open the Filter Drop-Down from within an Excel Table Using Shift +Alt + ↓I mentioned above that to use the Alt + ↓ shortcut, you need to start with your header cell selected. However, if you are using Excel Tables, you can actually open the filter drop-down menu from within the table by using Shift +Alt + ↓. Your filtering will apply to the column you have selected within the table.

Check out these posts for more information about Excel filters and Excel Tables:

Excel Filters Video Training Series
Excel Tables Tutorial Video
Filter Mate Add-in & Filters 101 Course

Share your filtering tips or ask a question in the comments below!

Link to post: Filter by Cell Value

View Details

If you aren't changing most of your data tables into Excel Tables, you're missing out on some major benefits. Converting data to Excel Tables is not only easy and quick to implement, but it will save you tons of time. Let me give you five reasons why.

Video TutorialWatch on YouTube & Subscribe to our ChannelDownloads Reasons to Use Excel Tables.xlsxDownloadReasons to Use Excel Tables – AFTER.xlsxDownload But first, how do you make an Excel Table? Starting with any cell in your data set selected, just click the Table button on the Insert tab.

Or instead, use the keyboard shortcut Ctrl + T.

Now, here are 5 reasons that Excel Tables rock:

1. Better Formatting The rows are banded with alternating colors making it easier for the eye to track along each row.

You can change the format colors and table style with the click of a button from the library of preset formats that Excel offers. These options are on the Table Design tab.

2. Filter Drop-Down Menus Are AppliedWhen you create a table, each column automatically gets filter drop-down menus. This allows you to quickly filter and sort your data.

I've got some great keyboard shortcuts that you can use for drop-down menus. Check out this tutorial for seven of them:

7 Keyboard Shortcuts for the Filter Drop Down Menus in Excel (excelcampus.com)

3. Automatic FormulasIf you add a formula to the right of the table, Excel will automatically extend the table to the right to include it. In addition, it will copy that formula all the way down the new column, applying the formula to the other rows in the table.

There may be times when you don't want Excel Tables to auto-fill formulas. If that's the case, you can learn how to disable that feature here: How to Prevent or Disable Auto Fill in Table Formulas.

You can also turn off structured references as well: How to Turn OFF Structured References in Excel Table Formulas.

4. Total RowFor every Excel Table, you have the option of turning on the Total Row feature. This option is found on the Table Design tab. The keyboard shortcut for adding the Total Row is `Ctrl` + `Shift` + `T`.

This will add a row that gives you a total for the rightmost column in the table. There are several options to choose from in the drop-down menu for different metrics.

It's good to know how the total is calculated in the Total Row. It uses the SUBTOTAL function, which I explain here: The SUBTOTAL Function for Excel Tables Total Row Explained.

5. Table Automatically ExtendsFinally, if you add data to the bottom of the table, Excel will automatically extend the table to include the new rows.

Excel Tables are the best! Want to learn more about them? I've got everything you need to get started in this tutorial:

Excel Tables Tutorial Video – Beginner's Guide for Windows & Mac

I hope these tips were helpful! Leave a question or comment below with the reasons you love Excel Tables!

Link to post: 5 Reasons to Use Excel Tables

View Details

If you have a table of data that you want to sort by certain criteria, but you know that you'll want to revert back to the original order after you've sorted it, I have a really easy tip for you.

Video TutorialWatch on YouTube & Subscribe to our ChannelIn the example above, let's say that I want to sort these entries by date, and then go back to how it looks now.

The way to do this is simple. Before I sort, I just add another column and fill it with sequential numbers. I like to label the column Index, but you can name it whatever you like.

Important Note: The index column should be hardcoded values in the cells. The numbers should NOT be created with something like the ROW function because the values would change when the data is sorted. We don't want the values to change.

Now if I sort by any of the other columns, such as Date, for example…

…all I have to do is sort the Index column from smallest to largest…

…and I return to my original sort order.

Straightforward yet effective–and super easy!

Do you have other simple tricks that you use all the time in Excel? I'd love to hear about them in the comments.

Hope this helps!

Link to post: Retain the Original Sort Order in Excel

View Details

You can write a lookup formula that returns multiple values using tools like VLOOKUP or XLOOKUP, but the FILTER function in Excel is a faster and easier option.

Video TutorialFor example, I want to look for a specific company name in a range of data and return any phone numbers that are associated with that name.

The FILTER FunctionLet's walk through the steps to write this function.

Start by typing the equal sign = and the word FILTER, then hit tab.
There are three arguments in this formula. The first is array, which is the range of cells that that we want to return values from. In this case, the phone numbers.
Next is the include argument, which is our filter criteria. For our example, we will take the list of companies and set it equal to cell B2.
Then, for the [if empty] argument, you can type whatever you want in quotation marks, such as “Not found.”

Finally, with all of our arguments written, we can hit Enter, and you will see that all of the phone numbers associated with the specified company spill into the cells below our formula.

ConclusionHope this quick overview of the FILTER function helps you to give it a try in one of your spreadsheets. Leave a comment if you have questions or feedback!

Link to post: Return Multiple Values with the FILTER Function

View Details

Excel Bad Habit #19: Removing Indentation One Level at a Time Instead of All at OnceSome reports come stacked with several levels of indentation, which can help the user read and understand categories and line items.

Video TutorialWatch on YouTube & Subscribe to our ChannelDownloads Remove Indentation Multiple Levels.xlsxDownload But when you want to remove indentation, it doesn't work to highlight all of the levels and click the Decrease Indent button.

So some people get into the bad habit of going level by level and removing indentation using Decrease Indent. This can take too long, especially if it's a large report.

Instead, remove all of the indentations simultaneously. Here's how.

Start by selecting the range of cells you want to change. Then use the right-click menu to select Format Cells. The keyboard shortcut for Format Cells is Ctrl + 1.

On the Alignment tab, change the Indent level to 0, and then hit OK.

Voilà! You've just removed all of the indentation in one stroke and kicked another Excel bad habit to the curb. Congratulations!

Questions? Leave a comment below! And be sure to let us know if this tip was helpful for you.

Link to post: Remove Multiple Levels of Indenting

View Details

If you have a list of items and want to remove any duplicate entries in the list, here are three great ways to go about it.

Watch on YouTube & Subscribe to our Channel1. The Remove Duplicates Button on the Data TabFirstly, if you go to the Data tab on the Ribbon, there's a button called Remove Duplicates.

With any cell selected in the column that you want to remove duplicates from, just hit the button. Then you'll get a Remove Duplicates popup window to verify which columns you want to change. Verify your selection and hit OK.

If you want to keep the original list, make a copy before removing the duplicates.

The UNIQUE FunctionThe UNIQUE function has options to de-duplicate columns OR rows. Also, it has the ability to return values that only appear once in the list.

You can even use the UNIQUE Formula for Non-Adjacent Columns.

Power QueryPower Query has the ability to remove duplicates in one or more columns.

If you're not familiar with Power Query, it's a powerful data automation tool, and it's great to use for importing data into Excel and cleaning it up. You can learn more about Power Query at this tutorial: Power Query Overview: An Introduction to Excel’s Most Powerful Data Tool

Other Methods?So, how do you de-dupe? Leave a comment if there are other techniques that I missed.

Hope this was helpful! See you again next time.

Link to post: 3 Ways to Remove Duplicates

View Details

I want to share with you three things that you may not know about the Function ScreenTip, which is the box that appears just below the function you're editing or writing.

Watch on YouTube & Subscribe to our Channel1. Clicking the argument name will highlight the corresponding text in the function.Each of the sections in a function corresponds with an argument name in the Function ScreenTip. If you click anywhere in the function, it will bold the argument name in the ScreenTip. On the other hand, clicking any of the arguments in the Screentip will highlight the corresponding text in the formula.

This can be helpful for editing specific portions of the function.

Clicking on the function name in the Function ScreenTip will open up the Help menu for that function. This menu gives all sorts of practical information about the function, including what it's used for, what each of the arguments does, and examples of how to use the function.
The ScreenTip can be moved.Finally, many people don't realize that you can relocate the Function ScreenTip if you find that it's getting in your way. Just hover over the border of the ScreenTip, left-click, and then drag it to wherever you'd like it to be.

Also, if you want to turn off the ScreenTip completely, you can do so in Excel Options.

If you have any questions about the Function ScreenTip, just leave a comment below. We're happy to help!

Link to post: 3 Tips for the Function ScreenTip in Excel

View Details

Excel Bad Habit #7: Using Formulas When Pivot Tables Are a Better OptionLet's say your boss gives you a big table of data and asks you to create a summary report of the revenue per region.

Watch on YouTube & Subscribe to our ChannelYou might be tempted to create a list of unique values, write a complex SUMIF formula, and then format the results into a legible report.

Instead, you should use pivot tables! They're fast and easy.

How do you create a pivot table?

Start by selecting any cell in the data range and go to the Insert tab. Click on the Pivot Table button.

Double-check that the range is correct. Then press OK.

Drag the Region field into the Rows area and the Revenue field into the Values area.

That's it! With just a few mouse clicks, you've created the entire report requested by your boss.

Pivot tables don't require you to write any formulas, and they do all of the formatting work for you. You can also use them to create interactive reports, charts, and dashboards.

For a more in-depth look at pivot tables, check out this tutorial:

How Do Pivot Tables Work?

Be sure to leave questions or feedback in the comments section below. I hope this quick example of pivot tables helps you create summary reports that your boss will appreciate!

Link to post: Use Pivot Tables to Create Summary Reports and Dashboards

View Details

A Look at the Advantages and Disadvantages of Using AI Tools for Excel TasksBottom Line: Gain insight into how AI tools like ChaptGPT can help with Excel. Also learn some of their limitations and drawbacks.

Skill Level: Beginner

Watch the VideoWatch on YouTube & Subscribe to our ChannelArtificial Intelligence Is HereIt's amazing how, seemingly overnight, AI tools have found their way into all sorts of professions and industries. These include journalism, customer service, computer programming, and blogging. AI (Artificial Intelligence) is now part of the Excel world as well, so let's take a look at how AI can help or possibly hinder your Excel tasks.

I find it interesting how these tools output high-quality text faster than a person can type, but about as fast as we can read. I believe the output is actually one of the reasons we are so fascinated with them, but that might just be my unpopular opinion. 😉

One of the most popular tools to use right now is ChatGPT by OpenAI.

I can ask ChatGPT, for example, to write a macro that creates a Table of Contents sheet. In seconds I receive something like this:

It's astounding how far technology has come. Let's take a look at some of the ways AI can be used with Excel.

Uses for AI Tools in ExcelTools like ChatGPT can be used in several ways to help with Excel.

ResearchMaybe you're working in a file created by someone else and you come across a formula you don't understand. You can copy the formula, paste it into ChatGPT, and tell ChatGPT to explain it.

Here's an example where I asked the AI tool to explain an XLOOKUP formula that I copied from a spreadsheet.

It's important to note that every time you ask it a question, it will give you a different answer. Sometimes the answers are longer and more detailed; sometimes they are short. If you're not happy with the answer you're given, you can hit the Regenerate Response button and the AI will try again.

The example above is relatively simple, but I've asked ChatGPT to explain more complex and nested formulas, and it does a pretty good job of breaking down the various components as well. Again, if it doesn't give you the level of clarity you're looking for, you can regenerate the response or even ask the tool to explain it with more detail.

Important to NoteIt's imperative you understand that the answers given by ChatGPT and other AI tools are not always 100% correct. The tool is pulling from information all over the internet to create its answer and sometimes the info it gathers can be outdated or false.

For example, one of the answers the AI gave me about the XLOOKUP formula above told me that the lookup column needs to be the first column in the table and that the table needs to be sorted in ascending order.

This simply isn't true and was probably retrieved from an internet source written about VLOOKUP before XLOOKUP ever existed.

OpenAI, the creator of ChatGPT, does warn that the tools can produce incorrect results in its list of limitations.

Solve ProblemsChatGPT can also give instructions to tell you how to do things you don't know how to do. For example, I asked it how to combine worksheets, which is a common question among Excel users, and it gave me guidance for how to go about it in three different ways.

Again, the answers may not always be completely accurate, but depending on what you need or already know, they can still be helpful in getting you where you want to go.

Other Ways to Use ChatGPTHere are a few other ways to put AI tools to use.

a. Explain a Macro. You can copy and paste a VBA macro into the tool and ask it for an explanation of what it does.

b. Be Creative. You can ask the tool to write something in a specific tone to customize its response. Tell the tool you want it explained in technical terms or as if you were a small child, or ask it to insert some humor into the response and see what happens. I asked ChatGPT to explain VLOOKUP like an angry drunken sailor, and it rose to the occasion with a fun response.

c. Create DataYou can ask the tool to come up with a fake set of data in order to practice some of your Excel techniques. Here's an example of a data set it created for theoretical coffee sales.

d. Analyze DataCopy and paste some data into the tool and ask it to analyze the numbers. It will probably give you some feedback about the highs, lows, averages, etc.

e. Write MacrosAs mentioned above, you can ask ChaptGPT to write a macro for you. The response can range from simple to more complex, and most of the time these answers will work. However, I did get a response where some of the macro code was written outside of the box, while the rest was inside:

Obviously, something like that can be confusing, especially if you are relatively new to macros. But overall, the responses can give you a pretty good structure to start from.

Assistant, not ReplacementSo will a robot be taking over our jobs anytime soon?

It's important to know that these Artificial Intelligence tools are here to stay and that they are only going to improve with time. The errors and mistakes were are seeing today will gradually get better, so I definitely encourage you to get familiar with using these tools.

However, at least for the foreseeable future, AI is more like an assistant than a replacement. AI tools can automate time-consuming tasks to improve your overall productivity. They also provide a great launching point that you can use your human brain to improve and enhance.

You don't want to blindly use these tools and trust the answers without checking, fixing, or improving them. Relying solely on AI tools to provide formulas, macros, or answers can cause you embarrassment when mistakes come to light.

So, as helpful as these tools are, you still need to be familiar with Excel and all of its features and functions—at least until the robots get smarter than us (and/or take over the world😉).

ConclusionI hope this post has whet your appetite for seeing what AI tools can do for you and that you spend some time playing around with ChatGPT to get familiar with its abilities and limitations. Let us know in the comments if you've already started using AI to help with Excel, and if so, what you are using it for. We'd love to hear about it.

Link to post: AI vs. Excel

View Details

Excel Bad Habit #13: Failing to Format Excel Sheets for PrintingWatch on YouTube & Subscribe to our ChannelAny time you send someone a worksheet, it's a good idea to send it printer-ready. Here are three quick tips for making sure your sheets don't print wonky!

Use a preview to see how the sheet will print. You can use either the Page Break Preview or Page Layout. Both are found on the View tab.

Both of these view modes will allow you to see how the pages will print. You can also find these buttons in the bottom right corner of the application window.

Change Page Width to 1 Page.If you want your report to print so that it is only one page wide, you can change this setting on the Page Layout tab.
Adjust the Break Lines ManuallyIf your page breaks land in weird places, you can manually move them in Page Break Preview mode. Just click and drag the dotted lines to where you want them to be for printing.

Helpful? Let us know!

What printing tips do YOU have to share? Tell us in the comments.

Link to post: 3 Tips for Printing Excel Sheets

View Details

Excel Bad Habit #10: Formatting Individual Cells Instead of Several at OnceIf you want to change all cells that have the same formatting quickly, you can use the Find and Replace feature in Excel to do it all at once instead of one cell at a time.

Video TutorialWatch on YouTube & Subscribe to our ChannelThat's right, many people don't realize that Find and Replace isn't just for values. You can replace formatting as well.

To make the process as quick as possible, start by changing just one cell to appear the way you want it. That way you can point to it as an example when you're replacing formats.

Type Ctrl + H to bring up the Find and Replace window. Select the Options button.

You'll see Format buttons for both the Find and Replace fields. For the Find Format, select the option that says Choose Format From Cell.

Then select any cell that has the format you want replaced.

Repeat the process for the Replace format, choosing a cell with your desired formatting.

When you hit the Replace All button, all of the cells will be reformatted simultaneously to match the format you selected, including fill color, font size and style, bold, italics, borders, and alignment.

If you want to replace the formatting for the entire workbook instead of just the selected worksheet, choose Workbook from the Within dropdown.

I hope this helps you save tons of time! Questions? Leave a comment.

Link to post: Find and Replace Formatting in Excel

View Details

Bottom line: Check out our top performing content of 2022, and what that means for you.

Below we recap our top performing YouTube videos and shorts in 2022. Our audience gravitated most towards content on Pivot Tables, VLOOKUP, and Index Match.

This could mean that employers are looking for these skills, and that mastering these three topics could help you secure your next promotion in the new year. If you want to learn more about these Excel “Power Tools”, then join us for new (free) webinar dates in 2023.

We also want to thank you for your support in 2022. Excel Campus would not be possible without your, and we appreciate you stopping by to read our posts!

Video RecapWatch on YouTube & Subscribe to our ChannelIntroduction to Pivot TablesOne of our top performing videos of 2022 was our Introduction to Pivot Tables. This video tells the story of “Andy,” a character who is tasked with creating reports for his boss.

This is a time consuming job for Andy, and in our video, he learns how to use Pivot Tables to create interactive dashboards and reports (which ultimately saves him TONS of time).

Pivot Tables are an essential Excel tool that can help you communicate data analytics more clearly and engage your audience more effectively.

These are just a few of many reasons why Pivot Tables continue to be so popular, and if you're not familiar with Pivot Tables, we encourage you to follow along with Andy in our 3-part series.

VLOOKUP Tutorial for ExcelVLOOKUP is an essential, foundational Excel skill that every Excel user should have — one reason why this video proved so popular in 2022.

In this video, we use a Starbucks menu analogy to explain VLOOKUP. It helps simplify a complex process, and allows us to cover common errors in VLOOKUP and how to solve for them without overcomplicating it.

If you haven't mastered VLOOKUP yet, make sure to check out our video.

Index MatchOur third most viewed video was on Index Match, an alternative to VLOOKUP.

It's important to master this after you familiarize yourself with VLOOKUP, so you can differentiate between the two tools and when each one is best used in which case.

What about XLOOKUP?And of course, let's not forget about the new kid on the block, XLOOKUP. Microsoft introduced this new lookup function in 2019 as a replacement for VLOOKUP and INDEX MATCH.

Here is a video that explains more about XLOOKUP and when NOT to use it.

Formatting Numbers into Thousands, MillionsOur most popular YouTube short in 2022 was on formatting numbers into thousands and millions. We started creating short form content late into 2022, so it was great to see so much support on this video.

If you're looking for any tips or tricks in the new year, make sure to leave your requests on the comments section of our videos.

Thanks again and cheers to an Excel(lent) 2023!

Link to post: 2022 Recap: Our Top Excel Tutorials

View Details

Wasting Paper by Not Using WRAPCOLS When Printing ListsPrinting long lists that are only one column wide can waste a lot of paper, which of course is bad for business as well as the environment.

Video TutorialWatch on YouTube & Subscribe to our ChannelConsider using the WRAPCOLS function to reduce your print page count.

WRAPCOLS takes a long list that is in a single column and breaks it up into several columns side-by-side.

Writing the WRAPCOLS function is easy.

Select the range that contains your list to be the formula's first argument, vector.
For wrap_count, tell Excel how many rows you want your new column to have. Excel defaults to 47, but you may want to adjust that number if you've changed the row heights.
Finally, for [pad_with], enter two quotation marks. This tells Excel that you want to see blanks for any leftover cells in your final column.

Teach this tip to a coworker to reduce paper waste even further, and be sure to let the boss know you're saving the company money.

Have an Excel bad habit that you need help breaking? Tell us about it in the comments below, where you can leave questions and feedback as well.

Link to post: Excel Bad Habit #11

View Details

Not Using Ctrl + Click to Insert Cell References in FormulasVideo TutorialWatch on YouTube & Subscribe to our ChannelIf you've been typing commas between cell references whenever you write a formula in Excel, please know that there's a faster way to do it!

By simply holding the Ctrl key down while clicking on the cells you want to include, the comma will automatically appear between the references in your formula.

This trick also works while inserting ranges. Just click and drag your mouse to select multiple cells while holding down Ctrl. The range references will automatically be separated by a comma.

Like this tip? Have questions? Want to share another Excel bad habit? Leave a comment below!

Link to post: Excel Bad Habit #12

View Details

Bottom line: Check out our new video from the Power Query Pro course on the benefits of learning Power Query. Join our Power Query Pro Course waitlist to preregister for our Black Friday offer. Skill level: Beginner Video Tutorial The Power Query Data Machine When I was thinking of a real life analogy to use […]

Link to post: The 3 Benefits of Power Query

View Details

The holiday season is upon us! Are you looking for gift ideas for a fellow Excel fan, co-worker, or data geek? In this post I share some of my favorite picks and exclusive offers for our readers. Note: For the 2022 season, our affiliate link commissions from the Holiday Gift Guide will be donated to […]

Link to post: The 2022 Holiday Gift Guide for Excel Lovers

View Details

Happy Halloween! Spin the wheel below for your chance to win Excel Tricks and Treats, including a free year of our comprehensive Excel training program Elevate Excel. I can't Reese-ist a good Halloween pun Looking for more scarily good Excel content? Check out our new reel on shortcuts below! View this post on Instagram A […]

Link to post: The Excel Campus Wheel of Fortune

View Details

Bottom Line: Learn the pros and cons of embedding Excel workbooks in PowerPoint files. Skill Level: Beginner Watch the Tutorial Download the Files Access the Excel workbook and PowerPoint file that I use in the video: Using Excel with PowerPoint You'll notice in the tutorial video above that I mention previous and upcoming videos. That's […]

Link to post: Embed Excel Workbooks in PowerPoint

View Details

Bottom Line: Learn how to lock rows and columns when scrolling with the freeze panes feature. Skill Level: Beginner Watch the Tutorial Download the Excel Files You can access both the BEFORE and AFTER Excel files to follow along: Making Budgets Better Welcome to the fourth episode in our Budget Makeover video series. Earlier in […]

Link to post: Budget Makeover – Ep. 4 – Using Freeze Panes in Excel

View Details

Bottom Line: Learn how to create a dropdown list in Excel. This wil make it easier to categorize transactions for a budget. Skill Level: Beginner Watch the Tutorial Download the Excel Files I've included both the BEFORE and AFTER files from the video. Budget Makeover This post is the third in a series that improves […]

Link to post: Budget Makeover – Ep. 3 – Creating Dropdown Lists

View Details

Bottom Line: Learn how to remove duplicates to create a list of unique values that can be used for dropdown lists, lookup tables, reporting, and more. Skill Level: Beginner Watch the Tutorial Download the Excel Files You can download both the BEFORE and AFTER versions of the Excel files that I use in the video. […]

Link to post: Budget Makeover – Ep. 2 – How to Remove Duplicates in Excel

View Details

Bottom Line: Learn how to lock cells for editing to prevent users from making unwanted changes to formulas. Skill Level: Beginner Watch the Tutorial Download the Excel Files I've included both the BEFORE and AFTER files for you to download here: Budgets – From Simple to Stupendous If budgeting has ever been frustrating for you, […]

Link to post: Budget Makeover – Ep. 1 – How to Lock Cells for Editing in Excel

View Details

Bottom Line: View 5 different charts that can be difficult to read, cause confusion, and prevent good decision-making. Then learn some alternatives that do the opposite. Skill Level: Beginner Watch the Tutorial Download the Excel File This Excel file contains all of the charts that we look at in the video. Choose the Best Charts […]

Link to post: 5 Bad Charts and Alternatives

View Details

Bottom Line: Learn what whole column references are, as well as the pros and cons of using them in formulas. Skill Level: Beginner Watch the Tutorial Download the Excel File Here's the workbook I use in the video. Are Whole Column References a Good Option? I received a couple of great questions on my tutorial […]

Link to post: Whole Column References can get you in TROUBLE with Excel

View Details

Bottom Line: Learn about new data conversion settings that will prevent errors with leading zeros, long numbers, and scientific notation. Skill Level: Beginner Watch the Tutorial Download the Excel File The file I use in the tutorial can be downloaded by clicking below. A New Feature for Excel Have you ever had someone try to […]

Link to post: New Excel Feature: Automatic Data Conversion for Numbers

View Details

Bottom Line: Take the challenge to write a formula to determine when a vehicle can enter the national park, according to park rules. Skill Level: Advanced Watch the Video Download the Excel File You can use this worksheet to write your formula in. It contains sample license plate numbers for you to work with, as […]

Link to post: Excel Formula Challenge: Even or Odd License Plates

View Details

Bottom Line: Learn the most common error with XLOOKUP and how to fix it. Skill Level: Beginner Watch the Tutorial Download the Excel File #VALUE! Error When working with XLOOKUP functions, there are two main errors that I've encountered, and both can be avoided using the same solution. Let's look first at the #VALUE! error. […]

Link to post: How to Fix & Prevent the #VALUE Error with XLOOKUP

View Details

Bottom Line: Learn several shortcuts to save time when writing lookup formulas. Skill Level: Beginner Watch the Tutorial Download the Excel Files I've included both the Begin and Final files so you can follow along and try the shortcuts for yourself. Five Shortcuts for Lookups Below are some shortcuts that will help you to write […]

Link to post: Shortcuts for Writing XLOOKUP and VLOOKUP Formulas

View Details

Bottom Line: Find out why it's still important to have a healthy knowledge of VLOOKUP, even though XLOOKUP has replaced it. Skill Level: Beginner Watch the Tutorial Download the Excel File The workbook that I use in the video can be found here: XLOOKUP Replaces VLOOKUP XLOOKUP was released in 2019 as the successor to […]

Link to post: When to use VLOOKUP instead of XLOOKUP

View Details

Bottom Line: Learn time-saving shortcuts for working with folders and files. Skill Level: Beginner Watch the Tutorial 12 Keyboard Shortcuts Below are 12 of my favorite keyboard shortcuts to use when working with Windows File Explorer. These will work for both Windows 10 and Windows 11. 1. Opening File Explorer To open the File Explorer […]

Link to post: 12 Keyboard Shortcuts for Windows File Explorer

View Details

Bottom Line: Take this Excel Challenge to create a solution that calculates the points and the winner for a guessing game. Skill Level: Intermediate Watch the Video Solution Video I went live on YouTube to walk through the solution and show some great submissions from our community members (that's you). The recording of the session […]

Link to post: Excel Challenge: Baby Shower Guessing Game

View Details

Bottom Line: Learn about the new Power Query Editor that Microsoft just released for the Mac version of Excel. Skill Level: Beginner Watch the Tutorial The Power Query editor for Mac is finally here! Let’s take a look at how Power Query for Mac works, what some of the current limitations are, and if this […]

Link to post: Excel Update: Power Query Editor for Mac

Excel Campus: Recent Episodes

None

1 Text Parsing: Extract items from messy listsOne of the first things I tried was parsing messy lists. The COPILOT Function in Excel is excellent at extracting items separated by inconsistent characters.

2 Return Multiple Columns: Shape output into columnsThe COPILOT Function in Excel can also return multiple columns. You just adjust the prompt to ask for separate columns.

3 Potluck Planner: Balance practical suggestionsPotlucks are a fun test case. They reveal how AI can balance categories and fill gaps. Use the COPILOT Function in Excel to categorize signups and recommend missing dishes.

4 Meal Planner: Cook with what you haveUse the COPILOT Function in Excel to convert an ingredients list into seven meal suggestions. The prompt includes quantities and a note about pantry essentials.

5 Splitting Names: Tackle difficult name formatsSplitting full names into title, first, middle, and last is a classic headache. The COPILOT Function in Excel handles diverse formats well.

6 Trip Planner: Build an itinerary from stops and preferencesPlanning family trips becomes easier. Pass a list of stops, days, dietary needs, and interests to COPILOT.

7 Categorize Bank Statements: Automate expense mappingBank statements often have inconsistent descriptions. Use COPILOT to assign categories for uncategorized items. Pair it with XLOOKUP and FILTER to create an efficient pipeline.

8 Sentiment Analysis: Score product reviewsThe COPILOT Function in Excel can analyze text sentiment. It returns structured outputs like a 1–5 rating and a positive/negative flag.

9 Seating Planner: Assign tables by interestsAssigning seats for events is a complex optimization problem. COPILOT handles it by grouping attendees with similar interests.

10 Standardize Dates: Normalize Multiple Date FormatsDates arrive in many shapes. COPILOT can standardize them to any format you choose.

11 Standardize Company Names: Clean Brand VariationsCompany names often appear in multiple variations. COPILOT can propose a canonical name for each unique variant.

12 Extract Order Details: Parse complex order stringsOrders often contain product, color, size, and quantity in one cell. COPILOT can parse those into separate columns.

13 Categorize Support Issues: Map tickets to topicsCustomer support tickets are messy. Use the COPILOT Function in Excel to assign each ticket to one of your predefined categories.

14 Semantic Duplicates: Harmonize Product VariantsSemantic duplicates are variations that mean the same thing. For example, “iPhone 15 Pro” vs “iPhone 15 – Pro”. COPILOT can identify and standardize them.

15 Shorten Job TitlesHR lists include long job titles with seniority and contract notes. Use COPILOT to extract core roles so you can group and count employees.

16 Address Cleanup: Standardize to USPS or local styleAddresses are a classic data mess. COPILOT can standardize them to USPS style or to your preferred local format.

17 Customized Emails: Recommend products and draft outreachUse the COPILOT Function in Excel to generate personalized email copy. Base suggestions on historical purchase combinations.

1. Better Formatting The rows are banded with alternating colors making it easier for the eye to track along each row.

2. Filter Drop-Down Menus Are AppliedWhen you create a table, each column automatically gets filter drop-down menus. This allows you to quickly filter and sort your data.

3. Automatic FormulasIf you add a formula to the right of the table, Excel will automatically extend the table to the right to include it. In addition, it will copy that formula all the way down the new column, applying the formula to the other rows in the table.

4. Total RowFor every Excel Table, you have the option of turning on the Total Row feature. This option is found on the Table Design tab. The keyboard shortcut for adding the Total Row is `Ctrl` + `Shift` + `T`.

5. Table Automatically ExtendsFinally, if you add data to the bottom of the table, Excel will automatically extend the table to include the new rows.

Excel Campus: Recent Episodes

None

1 Text Parsing: Extract items from messy listsOne of the first things I tried was parsing messy lists. The COPILOT Function in Excel is excellent at extracting items separated by inconsistent characters.

2 Return Multiple Columns: Shape output into columnsThe COPILOT Function in Excel can also return multiple columns. You just adjust the prompt to ask for separate columns.

3 Potluck Planner: Balance practical suggestionsPotlucks are a fun test case. They reveal how AI can balance categories and fill gaps. Use the COPILOT Function in Excel to categorize signups and recommend missing dishes.

4 Meal Planner: Cook with what you haveUse the COPILOT Function in Excel to convert an ingredients list into seven meal suggestions. The prompt includes quantities and a note about pantry essentials.

5 Splitting Names: Tackle difficult name formatsSplitting full names into title, first, middle, and last is a classic headache. The COPILOT Function in Excel handles diverse formats well.

6 Trip Planner: Build an itinerary from stops and preferencesPlanning family trips becomes easier. Pass a list of stops, days, dietary needs, and interests to COPILOT.

7 Categorize Bank Statements: Automate expense mappingBank statements often have inconsistent descriptions. Use COPILOT to assign categories for uncategorized items. Pair it with XLOOKUP and FILTER to create an efficient pipeline.

8 Sentiment Analysis: Score product reviewsThe COPILOT Function in Excel can analyze text sentiment. It returns structured outputs like a 1–5 rating and a positive/negative flag.

9 Seating Planner: Assign tables by interestsAssigning seats for events is a complex optimization problem. COPILOT handles it by grouping attendees with similar interests.

10 Standardize Dates: Normalize Multiple Date FormatsDates arrive in many shapes. COPILOT can standardize them to any format you choose.

11 Standardize Company Names: Clean Brand VariationsCompany names often appear in multiple variations. COPILOT can propose a canonical name for each unique variant.

12 Extract Order Details: Parse complex order stringsOrders often contain product, color, size, and quantity in one cell. COPILOT can parse those into separate columns.

13 Categorize Support Issues: Map tickets to topicsCustomer support tickets are messy. Use the COPILOT Function in Excel to assign each ticket to one of your predefined categories.

14 Semantic Duplicates: Harmonize Product VariantsSemantic duplicates are variations that mean the same thing. For example, “iPhone 15 Pro” vs “iPhone 15 – Pro”. COPILOT can identify and standardize them.

15 Shorten Job TitlesHR lists include long job titles with seniority and contract notes. Use COPILOT to extract core roles so you can group and count employees.

16 Address Cleanup: Standardize to USPS or local styleAddresses are a classic data mess. COPILOT can standardize them to USPS style or to your preferred local format.

17 Customized Emails: Recommend products and draft outreachUse the COPILOT Function in Excel to generate personalized email copy. Base suggestions on historical purchase combinations.

1. Better Formatting The rows are banded with alternating colors making it easier for the eye to track along each row.

2. Filter Drop-Down Menus Are AppliedWhen you create a table, each column automatically gets filter drop-down menus. This allows you to quickly filter and sort your data.

3. Automatic FormulasIf you add a formula to the right of the table, Excel will automatically extend the table to the right to include it. In addition, it will copy that formula all the way down the new column, applying the formula to the other rows in the table.

4. Total RowFor every Excel Table, you have the option of turning on the Total Row feature. This option is found on the Table Design tab. The keyboard shortcut for adding the Total Row is Ctrl + Shift + T.

5. Table Automatically ExtendsFinally, if you add data to the bottom of the table, Excel will automatically extend the table to include the new rows.

4. Total RowFor every Excel Table, you have the option of turning on the Total Row feature. This option is found on the Table Design tab. The keyboard shortcut for adding the Total Row is `Ctrl` + `Shift` + `T`.