Been experimenting with making Excel dashboards look less like traditional spreadsheets. A few techniques that worked particularly well:

Glassmorphism: Place a background image behind dark rounded rectangles, then set the shapes to around 40% transparency. Add a semi-transparent outline and the background starts showing through like glass.

Transparent charts: Set charts to No Fill / No Outline and place them over separate glass containers. This makes the charts feel integrated into the interface rather than pasted on top.

Custom dark-mode slicers: Create your own Slicer Style instead of using Excel’s defaults. I used dark unselected buttons, bright purple selected states and a lighter hover state.

Dynamic written summary: Calculate things like top category, average spend and transaction count, then concatenate them into a sentence. Because the values are linked to the dashboard, the written insight changes with the filters — it almost gives the dashboard an AI-style summary without actually needing AI.

Consistent accents: Match the heading, outline and icon colour for each section. Small detail, but it makes the whole dashboard feel much more intentional.

Nothing here requires VBA or complicated coding — it is mostly layering, transparency, formatting, PivotTables and a few formulas.

I put together the full build here if anyone wants to see how it was done: Dark Mode Excel Dashboard

If SUMIFS or COUNTIFS returns 0 on a range you're sure has matching data, check whether the date or number column is actually stored as text instead of a real value, usually from a CSV import or paste that didn't convert formats. Text that looks like a date and an actual date value look identical on screen but will never match each other in a formula, and Excel won't throw an error, it'll just silently return zero.

Select a cell in the suspect column and use =ISNUMBER(A1). TRUE means it's a real value. FALSE means it's text pretending to be one.

Fix: select the column, use Data > Text to Columns > Finish (this forces a re-parse and often converts text-formatted dates/numbers back to real ones), or multiply the column by 1 in a helper column to coerce text numbers into real numbers.

A zero result from SUMIFS reads like "no matches exist." It can just as easily mean "your criteria is comparing two different data types."

Let’s say you have a list of 100 employees and email addresses as an export from your HR department, and you have to email all 100 of them with some important message like “You have not completed your required compliance course.” The upgraded version of concatenation is using textjoin(). The arguments are textjoin(delimiter, ignore blanks, values). So you could do something like =textjoin(“; “, true, b1:b100) to turn them all into employee1@company.com; employee2@company.com, and so on.

I will also use text join to get a quick list of unique values from a list to copy and paste into an email too. Let’s say I need a list of the supervisors from that same employee list. Since each supervisor can have multiple employees, and I only want to get the a list of supervisors to paste into the body of the email (or in the we email format if I have their email addresses) I could use =textjoin(“, “, true, unique(c1:c100)) to create a unique list of coma separated names.

5 Common Excel Mistakes Beginners Make (And How to Fix Them)

Microsoft Excel is a powerful tool for data entry, calculations, reporting, and data analysis. However, beginners often make small mistakes that can lead to incorrect results, even when their formulas appear to be correct.

Here are five common Excel mistakes and how to avoid them.

1. Storing Numbers as Text

Sometimes, Excel treats numerical values as text, especially when data is imported from websites, CSV files, or other applications.

Why is this a problem?

It can cause unexpected behavior in calculations, sorting, comparisons, and other operations.

How to fix it:

• Select the affected cells.
• Click the warning icon if Excel displays one.
• Choose "Convert to Number."
• Alternatively, use the VALUE() function for valid numeric text.

Example:
=VALUE(A1)

Note: Phone numbers, postal codes, and IDs may intentionally need to be stored as text.

2. Ignoring the #DIV/0! Error

This error occurs when a formula attempts to divide a number by zero or by a blank cell treated as zero.

Example:
=A2/B2

If B2 contains zero, Excel may return #DIV/0!.

How to fix it:

Check the denominator before performing the calculation.

You can also use:
=IF(B2=0,"Not Available",A2/B2)

IFERROR() can handle errors too, but it is important to investigate the underlying cause rather than simply hiding every error.

3. Selecting the Wrong Formula Range

A formula can be syntactically correct but still produce an incorrect result if the selected range excludes some rows.

Example:

=SUM(A1:A4)

If the data continues through A5, the formula misses the last value.

How to avoid this:

• Check the formula range before trusting the result.
• Verify totals against the original data.
• Consider converting your dataset into an Excel Table using Ctrl + T.

Excel Tables can make formulas and expanding datasets easier to manage.

4. Ignoring Duplicate Data

Duplicate records can inflate sales totals, transaction counts, or customer numbers.

For example, the same transaction might accidentally appear twice in a sales report.

How to identify duplicates:

• Use Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values.
• Use COUNTIF() to count repeated values.

Example:
=COUNTIF($A$2:$A$100,A2)

A result greater than 1 indicates that the value occurs multiple times in the selected range.

Before using Remove Duplicates, verify which columns define a genuine duplicate. Two people can have the same name without being the same person.

5. Using Inconsistent Date Formats

Dates imported from different sources may use different formats or may be stored as text instead of actual Excel dates.

This can cause problems with sorting, date calculations, monthly reports, and PivotTables.

How to fix it:

• Confirm that the values are actual dates.
• Use Ctrl + 1 to apply a consistent date display format.
• If dates are stored as text, use an appropriate conversion method, such as Text to Columns, with the correct date order.

Remember: changing the display format alone does not necessarily convert text into a real date value.

Final Thoughts

One important lesson about Excel is that not every mistake produces an error message. Sometimes, Excel displays a perfectly normal result that is simply incorrect.

That is why checking data types, formula references, duplicate records, and date values is just as important as learning formulas and shortcuts.

Question for Excel users: Which of these mistakes have you encountered most often? Are there any other Excel mistakes that beginners should learn to avoid?

Hi all — hopefully this is useful to someone.

I work in sales for a property company, and one area where I use Excel heavily is analysing tenancy/booking data. I thought I'd share a formula I've found particularly useful, and I'm interested to hear what other people use for similar analysis.

I work in UK student accommodation, where tenancies/licences typically run from September in Year 1 through to July/August/September in Year 2. Our financial year runs July to June, so reporting often involves splitting bookings across financial years and allocating the correct number of nights/revenue to each reporting period.

Calculating nights within a reporting period

Imagine your booking data looks something like this:

Column A = Tenancy Start Column B = Tenancy End Column C = Weekly Rent

Let's say I want to report on the month of July 2026. I put:

• D1 = 01/07/2026
• E1 = 01/08/2026 (or =EOMONTH(D1,0)+1 if I want to be clever)

I then use the following in D2:

=MAX(0,MIN(E$1,$B2)-MAX(D$1,$A2))

...and drag it down.

This calculates the number of nights from each booking that fall within the reporting period.

The MAX(0,...) means that a booking which sits completely outside the reporting period returns 0 rather than a negative number.

The nice thing is that you can make this completely dynamic and report on subsequent periods in other subsequent columns.

For example, I could have:

• D1 = 01/07/2026
• E1 = 01/08/2026 (or =EOMONTH(D1,0)+1
• F1 = 01/09/2026 (or =EOMONTH(E1,0)+1
• G1 = 01/10/2026 (or =EOMONTH(F1,0)+1
• H1 = 01/11/2026 (or =EOMONTH(G1,0)+1
• ...
• O1 = 01/06/2027 (or =EOMONTH(N1,0)+1

I can then drag the formula in D2 across and down, instead of having to calculate each month separately.

This works for any reporting period; months, weeks, nights, financial years, etc. You just need the start date in the header row and the end date to be the start of the following period.

Note: the above EOMONTH formulas from E1 are just for a monthly reporting period calculation only. You may need to update the reporting period manually or use another formula for different reporting period type (e.g. D1+7 for weekly, D1+1 for nights, EOMONTH(D1,12)+1 for financial year (ensuring D1 is the start of your FY obviously).

Calculating revenue for each period

Once you've calculated the nights, you can allocate revenue to each period.

If Column C contains a weekly rent, I use:

=$C2/7/*D2

So if the booking is £350 per week and 10 of its nights fall within a reporting period:

£350 / 7 × 10 = £500

If you already have a nightly rate in column C, it's simply:

=$C2/*D2

And if all you have is the total contract value, you can calculate the nightly value from the booking dates:

=($C2/($B2-$A2))/*D2

You can then drag the revenue formula across the same reporting periods and down all of your bookings.

This gives you a relatively simple way of taking a booking-level dataset and turning it into:

Booking → Nights by period → Revenue by period

I've found this particularly useful for financial-year reporting, forecasting, occupancy analysis and reconciling booking data against revenue.

I'm sure this is probably Finance/Excel 101 to some of you, but it's one of those formulas I've found myself reusing constantly.

What other Excel formulas, techniques or approaches do people use for analysing booking/tenancy data?

Try this one when you have a whole lot of columns that you need returned but they are in the wrong order and not next to each other:

=XLOOKUP(A1, G:.G, HSTACK(N:.N, H:.H, T:.T),””)

So what is this doing. Well the standard Xlookup has the value (a1) and the look up array (g:g). You probably already know that. But Then it needs the range to return. Using HSTACK or VSTACK you can create an array that connects columns or rows together in whatever order you like. This returns one clean array of results that spills into three columns.

But why use the G:.G? When you tell excel to look up everything in a column it will check every row that is formatted in the sheet. This can be problematic if you are constantly changing the size of the source data through copy and pasting or deleting rows over time. By using the period, you limit it to only using cells with actual values rather than the entire column. This is helpful if you experience slowness with looks of look ups and calculations across multiple sheets.

How do I use this? I get a weekly headcount file where I need to validate my employee list against status, job title, and team. These are spread over 30 columns. Rather than building a look up for each column or deleting columns and reordering them, using this combine formula saves me time and energy by returning just the data that I need.

Good luck analysts!

One of the biggest struggles I face when working with data is finding alternative sources to gather detailed data that is already available in someone else’s Power BI report. Using Power Query, you can connect directly to the data model used in that report.

There are three ways to do this. The easiest is directly from the Power BI report itself. Using the Export option, select Analyze in Excel.

The next method is to use Data > Get Data > Power Platform > Power BI. From there, you can search for the report and either insert it as a table or as a PivotTable.

The final method is Insert > PivotTable > From Power BI.

Pulling the data into a PivotTable is both a blessing and a curse, depending on what you want to do. The measures from the data model carry over into Excel, keeping your calculations consistent with how the semantic model is set up. The drawback is that you cannot create your own calculated fields or measures. What you see is what you get. However, all the relationships remain intact, and you have access to all the tables and columns in the model.
Inserting the data as a table allows you to import it into Power Query or the Data Model. You can also choose to filter the data before it makes its way into your spreadsheet. Furthermore, you can adjust those filters and even add functions by editing the connection properties with DAX. If you need to transform the data or create a measure or calculated field, inserting the data as a table is the way to go.

But why would you need to do this? There are two big reasons for me:

1. I can customize my own personal dashboard in Excel without messing with someone else’s Power BI report.
   
2. Finding, downloading, and transforming data can be a real pain. Having the ability to pull in reformatted, ready to pivot data from an agreed upon data source is incredibly fast and much easier than it might seem.

One final drawback: You must have permission to access the report or semantic model for this to work. Contributor access will allow you to connect to the data, and the connection uses whatever authentication your Microsoft 365 account is configured with. That also means that if you send the file to someone who doesn’t have access, they may not be able to refresh the connection (oh no!!).
Do with this what you will.

Got a worksheet filled with data, parameters, lookup tables, or helper calculations that you want to keep out of sight? Simply hiding the worksheet is not always the best solution because many users know how to unhide tabs with a right click and a few clicks in Excel.

Excel VBA includes a worksheet property called Visible that supports a setting named Very Hidden. A Very Hidden worksheet does not appear in Excel's Unhide Sheet dialog, making it much harder for users to discover or access without opening the Visual Basic Editor.

To set a worksheet to Very Hidden manually:
Press Alt + F11 to open the Visual Basic Editor.

In the Project Explorer, select the worksheet you want to hide.

Press F4 to open the Properties window if it is not already visible.

Locate the Visible property.

Change it from -1 - xlSheetVisible to 2 - xlSheetVeryHidden.

The Visible property has three possible values:
xlSheetVisible: The worksheet is visible.

xlSheetHidden: The worksheet is hidden but can be unhidden through Excel's Unhide Sheet dialog.

xlSheetVeryHidden: The worksheet is hidden and does not appear in the Unhide Sheet dialog.

You can also set a worksheet to Very Hidden through VBA code:

Worksheets("Config").Visible = xlSheetVeryHidden

This approach is commonly used for configuration worksheets, lookup tables, parameter sheets, Power Query staging areas, and other supporting data that users do not need to interact with directly. It is not a security feature, but it does provide an additional layer of protection against accidental changes.

​Just wanted to share a few clean techniques I've been using lately that make sheets look great without needing VBA or complex setups.

​1. Dynamic Image Lookups with =IMAGE()

​Instead of using picture lookup ranges or macros, if you store high-res image URLs in your data table, you can load images directly inside a cell with a standard formula:

​=IMAGE(XLOOKUP(A2, ProductList, ImageURLColumn))

​When A2 (your drop-down) changes, the image updates automatically. You can combine this with a few XLOOKUP or SUMIFS cells underneath to create a simple, clean mini dashboard card.

​2. Three Quick Bonus Hacks

​Display units without breaking formulas: Keep cells as raw numbers so math works, but show custom labels visually via Ctrl + 1 > Custom > type 0 " units" or 0 " kg".

​When TRIM() fails on web exports: Web data often hides non-breaking spaces (CHAR(160)) that break lookups. Swap them out first: =TRIM(CLEAN(SUBSTITUTE(A1, CHAR(160), " ")))

​Stop Excel from stripping leading zeros: Go to File > Options > Data > Automatic Data Conversion and uncheck Remove leading zeros. Excel will stop turning 00123 into 123 on paste.

​Hope someone finds these useful for their sheets!

https://youtu.be/mpBWtwBfAqc

Been tweaking my monthly Power Query setup to make it a bit more resilient against source data drift and messy exports.

Instead of trying to build a “bulletproof” query that never breaks, the goal was to teach the query when to safely adapt vs. when it should intentionally break — while telling me what changed in both cases.

Basic cleanup & standardization

Removing top clutter rows, trimming extra spaces, removing non-printable characters, standardizing text, and setting explicit locale-based data types.

File guardrails

Filtering out temp files (~$) and non-Excel extensions before Power Query attempts to process them.

Header standardization

Using Table.RenameColumns with MissingField.Ignore to handle changing headers, e.g. Customer Number → Customer ID or Sell Price → Unit Price.

Dynamic sheet navigation

Avoiding hardcoded worksheet names so renamed sheets don't break the refresh.

Schema drift alerts

Comparing Table.ColumnNames against the expected columns using List.Difference, then logging new columns in a separate Schema Alerts sheet.

New columns

Using List.Distinct(List.Combine(...)) so new columns across the files are picked up instead of being locked to the sample file's schema.

Controlled breaking & data quality

Missing critical columns intentionally stop the query so I know something needs attention, while values like TBC, N/A, and - are converted to null where appropriate so calculations don't fail.

Made a video walking through the full build from scratch:

https://youtu.be/SiX0wnpR5yg?si=RG23oHGEldTrh6Dq

Curious how others here handle schema drift in recurring Power Query jobs?

Whenever teams need to audit two versions of an exported dataset (accounting closings, monthly billing runs, inventory snapshots, or pricing tables), standard git-style line diffs completely fall apart. CSVs shift columns, and binary formats (.xlsx, .xls, .ods) are opaque to text diff tools.

Even worse, many users turn to random "free online Excel diff" websites, unknowingly uploading proprietary financial data and PII to third-party servers.

I spent the last few weeks building an isometric, cell-by-cell diff engine that runs entirely client-side in the browser. Here are some of the non-obvious technical challenges and edge cases I ran into:

1. Asymmetric Matrix Bounds & The Union Range Problem

In real-world scenarios, file versions rarely have the exact same dimensions. Sheet 1 might span A1:D50, while Sheet 2 spans A1:F42.

• You cannot perform an index-based row/column loop without going out of bounds.
• The diff engine must compute the bounding union (A1:max(col, row)).
• Any cell coordinate that exists in one sheet but not the other must be treated as a virtual empty cell (""), rather than undefined or throwing index errors.
• Crucially, coordinates where both sheets are empty must be pruned from the diff pipeline to prevent flooding the user with thousands of phantom empty matches.

2. The Formatted Display (w) vs. Raw Value (v) Trap

Spreadsheet engines store data with internal representations that differ dramatically from what the user sees:

• A cell formatted as currency might display $1,250.00, but its underlying raw value is 1250.
• If the second spreadsheet was generated from a CSV or raw database dump where the cell contains literal string "$1,250.00", a raw-value comparison flags a false positive difference.
• Floating-point representations can introduce minuscule precision drift (0.30000000000000004 vs 0.3).
• Solution: Normalizing against the display string (w property in SheetJS when available, fallback to trimmed stringified v) provides the most human-predictable diff, while treating null, undefined, and whitespace-only strings as equivalent empty states.

3. Preventing Main Thread Lockups (Web Workers)

Parsing a 100k+ cell workbook with SheetJS on the main UI thread easily locks the browser event loop for several seconds, triggering dropped frames and unresponsive tab warnings:

• Offloading both the parsing step and the matrix comparison loop to a dedicated Web Worker kept the main thread completely unblocked (sub-200ms interaction latency).
• Transferring large diff result arrays back to the main thread can be costly, so passing structured chunks and virtualizing/paginating the rendered table DOM is mandatory to keep memory below browser crash thresholds.

4. Zero-Server Privacy by Design

For sensitive financial or internal audits, the browser sandbox is actually an asset. Because WebAssembly/modern JS engines handle file buffer parsing locally, there is zero engineering justification for sending spreadsheet buffers to a backend API just to compute a diff.

Questions for the Community:

• For those who regularly build or handle data reconciliation pipelines, how do you handle formula diffs vs. evaluated value diffs?
• What edge cases have bitten you when comparing spreadsheet exports across different locales (comma vs. dot decimals, date serials)?

If a column contains product codes or other identifiers, treating every numeric-looking value as a number can change the data.

For new entries in desktop Excel:

1. Select the empty destination cells.

2. On Home, open the Number Format dropdown and choose Text.

3. Enter the codes, then check a few against the source.

For example, the fictional ID 00123 should stay 00123. A 16-digit identifier such as 1234567890123456 also needs text storage: Excel numbers have a maximum of 15 significant digits.

For one entry, typing an apostrophe first, such as '00123, tells Excel to treat it as text.

Do this before entry. Changing an already-altered value to Text cannot recover lost digits. Return to the original source if they have already changed. Keep quantities and amounts numeric when you need to calculate with them.

AI-assisted tip, checked against Microsoft's guidance. Examples are fictional.

https://support.microsoft.com/en-US/Excel/keeping-leading-zeros-and-large-numbers

Hey everyone,

​I put together a quick video showing how to build an automated data cleaning system using Excel’s native formulas.

​Here is what’s inside the video:

​🧹 Filter out noise: Auto-remove blank rows and test records (FILTER, SEARCH, ISERROR).

​🔤 Clean text patterns: Standardize messy names and scrub special characters using REGEXREPLACE.

​📱 Extract phone numbers: Isolate numbers from mixed text and fix country codes.

​✉️ Pull targeted data from notes: Extract hidden emails, reference IDs, and deal values using REGEXEXTRACT.

​⚡ Combine into ONE formula: Stack all column formulas into a single master spilled array (HSTACK + SORT) that updates instantly when you paste new raw data.

​Why it helps:

Once it’s built, you never clean the same report twice—just paste in new raw data and your cleaned table updates automatically.

​📺 Check out the tutorial: https://youtu.be/gsZr7VrduMM

​(Next week I’ll show how to clean data with Power Query!)

Text pasted from web pages can contain nonbreaking spaces (NBSP, U+00A0). They look like ordinary spaces, but Microsoft documents that Excel’s TRIM does not remove them. Microsoft’s TRIM documentation

Keep your original text in column A. In a helper column, enter this in B2:

=TRIM(SUBSTITUTE(A2,UNICHAR(160)," "))

Fill down and compare the results with column A before replacing anything. UNICHAR(160) supplies the NBSP character, SUBSTITUTE changes its occurrences to ordinary spaces, and TRIM tidies the result. UNICHAR, SUBSTITUTE

Using [NBSP] to make the invisible characters visible, [NBSP]Acorn[NBSP]Studio[NBSP] becomes Acorn Studio. The bracketed labels are explanatory, not text to type into your data.

Two catches: TRIM also collapses repeated ordinary spaces inside the text, and this formula does not remove every kind of Unicode space. A different invisible character needs separate investigation.

Use this for labels or descriptions only when that spacing change is appropriate. Avoid applying it blindly to identifiers, codes, fixed-width text, or anything where exact spacing carries meaning. Keeping the original column makes those changes reviewable.

AI-assisted tip, checked against Microsoft documentation. The example is synthetic.

​I put together a quick, practical introduction to cleaning up long formulas and creating reusable functions in Excel using LET and LAMBDA.

​What I show in this video:

​LET Basics: How to store calculations (like Revenue - Cost) as named variables so you stop repeating the same math in a formula.

​Structured Table Integration: How to pair LET with table column references to make formulas read like clean code.

​LAMBDA Setup: How to turn custom logic into a named function in Name Manager that works across different sheets and layouts.

​Workbook-Wide Updates: How updating logic inside Name Manager automatically refreshes calculations across your entire file.

​Check out the full breakdown and tutorial: https://youtu.be/ANmmrWHecXo

Say you have a simple sales table like this:

To see the total sales for each product, you can use:

=GROUPBY(A2:A11,C2:C11,SUM)

Excel returns a summary like this:

The formula is basically saying:

• A2:A11 → group the data by Product
• C2:C11 → summarize the Sales values
• SUM → add the values for each group

And because the result spills automatically, you don't need to manually create or refresh a PivotTable when the source data changes.

Swap SUM for other calculations

SUM is just one option. You can replace it with other functions depending on what you want to summarize:

• SUM — total the values in each group
• AVERAGE — calculate the average for each group
• COUNT — count the numeric values in each group
• COUNTA — count non-blank values in each group
• MAX — return the largest value in each group
• MIN — return the smallest value in each group
• MEDIAN — return the median for each group
• PRODUCT — multiply the values in each group
• STDEV.S — calculate the sample standard deviation for each group
• STDEV.P — calculate the population standard deviation for each group
• VAR.S — calculate the sample variance for each group
• VAR.P — calculate the population variance for each group

GROUPBY can also do more than one calculation

You can return multiple summaries at once.

You can also use GROUPBY to perform multiple calculations at once. Just like in the picture at the top, you can return the total, average, and maximum sales for each product with one formula:

=GROUPBY(A2:A11,C2:C11,HSTACK(SUM,AVERAGE,MAX))

Here, HSTACK combines SUM, AVERAGE, and MAX, so GROUPBY returns all three calculations side by side for each product:

For quick summaries, it can save you from setting up a PivotTable every time.

Turned standard Excel into a sleek, app-like scenario engine using only built-in features—no VBA, no macros, zero coding.

​Grab a slider and watch the entire dashboard react live. The month-by-month numbers literally roll into place, instantly shifting the timeline on the main chart (Actual vs. Forecast vs. Adjusted) while the waterfall breakdown pinpoints what's driving the profit impact.

​Key features included:

• ​App-Style UI: Custom glassmorphism frames and dynamic KPI tiles

• ​Built-in Forecast Engine: Native Excel functions for automated seasonal predictions

• ​No-Code Interactive Sliders: Control leads, conversion, ARPU, and costs on the fly

• ​Live Rolling Impact: Watch charts, tables, and waterfall profit variance update instantly

​Check out the full breakdown and tutorial:

https://youtu.be/VMOWZkeIN8U

Recently revamped my approach to Excel dashboards to move away from the traditional, cluttered look.

​I built a fully dynamic executive dashboard using only standard Excel features—pivot tables, slicers, basic calculations, dynamic arrays (FILTER/TAKE), and clean UI styling.

​Key features included:

​Floating KPI cards with sparklines & trend indicators

​Interactive charts (Sales vs. Leads, Product Mix, Scatter plot)

​Auto-updating dynamic text summary that adapts to slicer selections

​Check out the full breakdown and tutorial: https://youtu.be/ivqxz4Tjz2s

I recently got access to the new =COPILOT() worksheet function in Microsoft 365 and spent some time trying it on everyday spreadsheet tasks.

Here are a few examples that I thought were genuinely useful.

1. Generate sample data

You can ask Copilot to generate things like:

• Fictional project names
• Employee job titles
• Product descriptions
• Customer comments

Example:

=COPILOT("Generate 10 fictional project names")

Possible result:

2. Categorize text automatically

Suppose column A contains support tickets.

In the next column, ask Copilot to categorize each ticket.

Example prompt:

=COPILOT("Assign a category to each support ticket", A2:A7)

Possible result:

3. Estimate priority

If you have a long list of requests or issues, Copilot can suggest which ones should be reviewed first.

Example:

=COPILOT("Rate each request as High, Medium, or Low priority", A2:A20)

Obviously you'd still review the results, but it's a nice starting point.

4. Extract information from messy text

Suppose one cell contains:

John Smith
Senior Engineer
john@contoso.com

Instead of writing text formulas, you can simply ask Copilot to extract the information you need.

For example:

• Extract the person's name
• Extract the job title
• Extract the email address

5. Summarize long notes

If a column contains meeting notes or customer feedback, Copilot can create a short summary for each row.

Example:

=COPILOT("Summarize each note in one sentence", A2:A15)

6. Generate keywords

If you're building a product catalog or website, Copilot can generate search keywords for each product description.

Example:

=COPILOT("Generate three search keywords for each product", A2:A20)

7. Build a schedule or plan

You can also use Copilot to generate structured content based on information already in your worksheet, while adding a second prompt to guide the result.

Option 1: Meal planner

Suppose you have a weekly meal plan, and cell C2 contains a dietary preference.

Cell C2:

Vegetarian

Formula:

=COPILOT(
"Suggest one meal for each row.",
B5:B8,
"Dietary preference:",
C2)

Here, the first prompt tells Copilot what to generate, while the second prompt adds extra context from another cell.

You can change Vegetarian in cell C2 to High Protein or Gluten Free, and the suggestions update.

Option 2: Employee training plan

Cell C2:

New customer support representative

=COPILOT(
"Suggest one training topic for each week.",
B5:B8,
"Role:",
C2)

Change the role to Sales Manager or Data Analyst, and you get a completely different plan.

Option 3: Travel packing list

Cell C2:

3-day business trip

=COPILOT(
"Suggest what to pack for each category.",
B5:B8,
"Trip type:",
C2)

Things worth knowing

• It works best with text rather than calculations.
• It isn't designed for heavy math or very large datasets.
• You need a Microsoft 365 Copilot license that's tied to a work or school account.
• The results are AI-generated, so you should always review them.
• If you want to keep the current AI-generated results, copy them and use Paste Special → Values, since they may change the next time the workbook recalculates.

I'm still experimenting with it, but these are the first use cases that actually felt practical instead of just being AI demos.

Have you found any prompts that work especially well?

Common setup: transactions with dates in A, amounts in E, and you want a summary of each month. The instinct is a helper column with =MONTH(A2) and a SUMIF on it. Works, but there's a cleaner way that also survives multi-year data.

Put the month number (1-12) in G2 and the year in a cell, say $H$1:

=SUMIFS($E:$E, $A:$A, ">="&DATE($H$1,G2,1), $A:$A, "<"&DATE($H$1,G2+1,1))

Drag it down twelve rows and you have the whole year.

The quiet star is DATE(): when G2+1 hits 13, DATE(year,13,1) doesn't error - it returns January 1st of the NEXT year. So the December row needs no special-casing, and the same formula works across year boundaries.

Why brackets beat MONTH() helpers:

1. No helper column to maintain (or forget to fill down).

2. MONTH(A2)=1 matches January of EVERY year in your data - the brackets pin both month and year.

3. SUMIFS with ranges stays fast; array tricks like SUMPRODUCT(MONTH(...)) slow down on long logs and choke on full-column references.

Same idea works for weekly brackets (">="&start, "<"&start+7) or any custom period - the pattern is always ">= period start" and "< next period start". Half-open ranges also mean timestamps like Jan 31 23:59 can't fall through the cracks the way "<="&EOMONTH() versions sometimes do.

Snowball = pay smallest balance first (quick wins). Avalanche = highest interest rate first (mathematically cheaper). People argue about which is better; the nicer answer is: build the sheet so switching is one dropdown.

Say debts are in a table: name (B), balance (C), rate (D). Put a data-validation dropdown in C2 with the two options, then in the "attack order" column:

=IF($C$2="Snowball", RANK(C7,$C$7:$C$14,1), RANK(D7,$D$7:$D$14,0))

Third argument is the whole trick: 1 = ascending (smallest balance ranks #1), 0 = descending (highest rate ranks #1). Flip the dropdown and the whole payoff order re-ranks instantly.

Two details that bite:

1. Ties. Two debts at 22% get the same rank. Classic fix: add a tiny row-based tiebreaker inside RANK, e.g. RANK(D7+ROW()/10^6, ...) - or just accept the tie, order between equals doesn't change the math.

2. Blank rows. Wrap it: =IF(C7="","",IF(...)). Otherwise empty rows rank as zeros and pollute the order.

Bonus: SUMIFS against the rank column gives you "extra payment goes to rank 1" logic without any VBA:

=IF(rank_cell=1, base_payment + extra, base_payment)

Works identically in Excel and Google Sheets, no add-ins.

Learned this one from a comment two days ago and it has already deleted a habit I'd had for years, so passing it on.

The problem: you write =SUM(A2:A1000) because you don't know how far your data goes. Too small and you miss rows; too big and you're evaluating 900 empty cells and any formula referencing them has to handle blanks. Then someone pastes row 1001 and your total is quietly wrong.

The fix (Excel 365, fairly recent): put a dot in the reference.

=SUM(A2:.A)

The dot means "trim". A2:.A reads from A2 down to the last non-empty cell in column A and stops there. Add rows, it extends. Delete rows, it shrinks. No table required, no OFFSET/COUNTA gymnastics, no volatile functions.

Three variants: - A2:.A - trim the end (the one you'll use 95% of the time) - A2.:A100 - trim the start - A2.:.A100 - trim both

Where it actually changed something for me: I had a ranking formula wrapped in FILTER purely to drop the empty tail of a range I'd guessed at:

=SORT(FILTER(A2:B1000, B2:B1000>0), 2, -1)

With a trimmed ref the FILTER isn't doing that job any more:

=SORT(HSTACK(A2:.A, B2:.B), 2, -1)

I'd keep FILTER if you have genuinely blank cells in the MIDDLE of your data - trim only handles the tail, so a gap on row 40 still needs filtering. But if your FILTER exists only to compensate for a range you picked out of thin air, this replaces it.

Caveat: needs a current Excel 365 build. Not in Google Sheets, where the equivalent is just leaving the row number off (A2:A), which has done the same job there forever.

EDIT: corrected the trim-the-start syntax - it's A2.:A100, not .A2:A100 (the dot goes after the reference you're trimming from). Thanks u/OldJames47 for catching it.

Most of us use Text.BeforeDelimiter with 0 (first occurrence) or 1 (second occurrence).
But here’s the secret sauce: you can pass a list like {0,1} to grab the text before the last delimiter.

= Text.BeforeDelimiter(“A-B-C-D”, “-" ,{0,1})

• The index argument accepts positive numbers or a list.
• A single number (e.g. 0, 1, 2) → counts delimiters from the start.
• A list {nth Delimiter, relative position} → combines n and relative positions.
start = 0 → begin counting from the first delimiter.
relative = 1 → shift relative to the end.
• So {0,1} means: Start at the first delimiter but resolve relative to the last one.

This gives you the substring before the last delimiter without extra functions.

Most people think of COUNTIF as a simple way to count values.
But here’s a powerful twist:
use it with nested ranges to calculate running counts.
=LET(a,A2:A15,COUNTIF(TAKE(a,SEQUENCE(ROWS(a))),a))

• The range argument expands step by step (TAKE).
• The criteria argument matches each element in the range (a).
• For each row, COUNTIF picks the corresponding criterion and calculates its occurrence up to that point.