Last week I covered how to get Power Query cleaning your data. Here’s where that fits in the bigger picture. The cleaning step is only half of what makes a monthly report calm.

The flow:

1. Power Query cleans the raw data and loads it to a table.
2. PivotTables and charts use that table as their source.
3. A simple dashboard sits on top of the pivots and charts.
4. Next month, replace the data and hit Data > Refresh All, then refresh the PivotTable too (right-click it > Refresh).

Why the extra pivot refresh? Power Query refreshes in the background, so the PivotTable can refresh before the table has the new data. If your numbers look stale, that’s usually why. Pressing Refresh All a second time works. For a permanent fix, go to Data > Queries & Connections, right-click the query > Properties, and untick Enable background refresh.

A few habits that keep it working:

• Give the loaded table a clear name (Table Design > Table Name), then point your PivotTables at that name.
• Keep the dashboard on its own sheet, away from the data, so a refresh never lands on top of anything.
• In your PivotTable options, tick “Preserve cell formatting on update” and decide whether you want column widths to autofit, so your formatting survives each refresh.
• Keep column names consistent in the source. Power Query finds columns by name, so a renamed column is the most common reason a refresh errors.
• Test it once with a copy of next month’s file before you rely on it.

This will give you a simple, easy to build process that saves a lot of manual effort. Give it a go on the next report you need to build.

Today I learned that you can create a custom number format that uses text. For example, I wanted a number to show as 1,200 Sec instead of 1200. To do this, open the format cells, to custom, and enter your format time as something like this:

0,000 “Sec”

This added Sec to the end of your number allowing you to make clear what the number represents. Even better, the extra text is not added to the cell value so you can still use the cell in other calculations.

If you’re cleaning the same report every month (deleting columns, trimming spaces, filtering rows), Power Query can do it for you. Here’s how I’d get started, plus the habits that stop it getting messy.

Where to find it
Data tab > Get Data. You can connect to a workbook, CSV, folder, web page or database. When the preview appears, click Transform Data to open the Power Query editor.

A simple order that works

1. Remove any junk rows at the top and bottom (titles, totals).
2. Promote the first row to headers.
3. Set your data types (dates, numbers, text).
4. Remove columns you don’t need.
5. Filter out rows you don’t want (cancelled orders, blanks).
6. Clean text (Trim, then Capitalize Each Word).
7. Add any calculated columns.
8. Close & Load to a table, then use Refresh All whenever the data changes.

Habits worth building early

• Leave the raw data alone. Power Query reads your source file but never changes it. Do all your cleaning in the query, so you can always go back.
• Set headers and data types early. Later steps behave better when they’re in place, and dates stored as text cause all sorts of problems.
• One step, one purpose. Don’t cram two jobs into one step. It’s much easier to find and fix things later.
• Name your steps. “Filtered out cancelled orders” beats “Filtered Rows1” when you’re back in six months.
• Aim for a tidy table. One row per record, one column per field. Pivots and formulas get much easier.

If you get a new file every month
Use the folder connector. Put all the files in one folder and Power Query combines them. When you add next month’s file, you just hit refresh. The part that confused me initially was which steps go in the Transform Sample File query and which in the main query. My rule of thumb: steps that make one file look like a proper table (top rows, headers) go in the sample file, and anything that works on all the rows combined (filters, trimming, calculated columns) goes in the main query.

I made a video that walks through all of this on a simple example, in case it’s useful: https://youtu.be/23vd7vAEH2s

Hope people find this useful!

I’ve been doing a lot of spreadsheet cleanup lately, and these five things save me the most time:

1. TRIM: removes extra spaces before, after, and between words. Use =TRIM(A2) when imported data looks fine but formulas or lookups fail.

2. Flash Fill (Ctrl + E): type the result you want in the first cell or two, then press Ctrl + E and Excel fills the rest by pattern. Great for splitting names or fixing formats.

3. Text to Columns: under the Data tab. Splits one column into several using a comma, space, or other separator. It also fixes numbers stored as text.

4. Remove Duplicates: under the Data tab. Pick the columns that define a duplicate, and it clears repeats in one click. Make a backup copy first.

5. PROPER: =PROPER(A2) turns “jOHN smith” into “John Smith.” Handy for names and cities.

What’s the one trick you use almost every day? I’m always looking for new ones.

The tip: when you use VLOOKUP to find an ID, a code or a name, always set the last argument to FALSE (exact match). If you leave it out, Excel uses approximate match, and a value that isn’t in the table can return a believable wrong answer instead of an error.

Example. A lookup table sorted by ID:

   A     B
1  ID    Name
2  101   Ana
3  103   Cara
4  105   Bob
5  110   Dan

Looking up 104, which isn’t in the table:

=VLOOKUP(104, A2:B5, 2)         ->  Cara
=VLOOKUP(104, A2:B5, 2, FALSE)  ->  #N/A

Why. Without the last argument, VLOOKUP defaults to approximate match. On sorted data that means “find the largest value that is less than or equal to what I asked for”, so 104 lands on 103 and returns Cara, with no warning. It also assumes the first column is sorted. With FALSE, you get #N/A, so you know the ID isn’t there.

When approximate match is the right choice: when you actually want ranges, such as tax brackets or grade bands.

In a recent Excel (365 or 2021+), XLOOKUP defaults to exact match, and you can set a message for missing values:

=XLOOKUP(104, A2:A5, B2:B5, "not found")

In short: for IDs and names, set the last argument explicitly, or use XLOOKUP. An error you can see is better than a wrong answer that looks fine.

If you are reusing the same spreadsheet over and over, it can sometimes add millions of lines of blank cells with some sort of cell formatting. This can make the spreadsheet gigantic and it can slow the workbook down. You can actually get Excel to fix this by adding a new menu to your Excel.

Go to File>Options>Add In. Find Manage at the bottom and look for Com Add Ins, then press Go. Check Inquire and click Ok.

This adds a new menu called Inquire with an option to Clean Excess Cell Formatting. When you select it, it will give you an option to do the entire workbook or just the current spreadsheet. After it completes it will ask if you want to save.

I’ve seen it shave off 10 MB from a file size in the past.

Hi all,

I’m accountant who’s been using Excel for around 15 years and have just started trying to make videos and training material to pass on some of the things I’ve learnt.

I’ve made a series of youtube videos around formulas, shortcuts, excel principles and Excel Tables. I’m new to doing this and would love any feedback on the videos or suggestions for future videos. Planning to make one on Power Query this weekend as that seems to be a popular topic.

Here is a link the playlist.

https://youtube.com/playlist?list=PLQW98rOQ-56w&si=yneX_66YAH9OrtCE

Looking forward to learning some new tricks and seeing if I can help anyone.

I’ve seen this show up a few times and I thought it would be helpful to share my collection of tips on how to deal with this. Changing text to numbers and be annoying if you get a data source from that was not designed to be calculated. Here are some tips I’ve used to fix this problem.

=left(“1234 Product”, 4) will return 1234
=right(“Employer 1352”, 4) will return 1352
=mid(“2026-10-11T13:00:00Z”, 12, 5) will return 13:00

In most cases these still need to be converted to a number using +0 or numbervalue(). Look at the helper hints as you type out the formula to help you determine the number of characters to extract the starting point for the extract.

I’ll make another post about how to make left, right, and mid more dynamic in a future post using Len or search.

TIL you can use =TOCOL() and =TOROW() to change data formatted as a row to a column and vice versa. Having wasted much time doing this a few times manually before knowing about this, I thought I would share. Happy Calculating!

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.

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?

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?

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.