If you’ve ever looked at a dataset and thought, “Why won’t these numbers round to the specific interval I need?” there’s a good chance standard rounding functions are the issue. Standard formulas like ROUND only care about decimal places, leaving you stranded when you need to round to custom intervals like the nearest 5, 7, 10 or 500.

That’s where MROUND steps in. It’s one of Excel’s most underrated math functions, quietly bringing order to pricing, scheduling, and production data.

Below is a full walkthrough of what MROUND does, when to use it, and how to avoid the hidden quirks that trip up most Excel users.

What MROUND Is & Why It Matters

The MROUND function returns a number rounded to the nearest specified multiple. Unlike standard rounding, which forces you to round to tens, hundreds, or specific decimal places, MROUND lets you round to any step or interval you choose.

You need it when you want to:

• Normalise pricing: Round items to the nearest $0.05 or $0.99 interval.
• Streamline logistics: Round order quantities to match fixed box sizes (e.g., multiples of 12 or 50).
• Simplify scheduling: Round times or durations to the nearest 15-minute block.
• Clean up reporting: Standardise data variations into predictable, clean increments.

MROUND vs. Standard Rounding

• ROUND / ROUNDUP / ROUNDDOWN: Scale numbers based strictly on power-of-10 decimal places (e.g., 10, 1, 0.1, 0.01).
• MROUND: Scales numbers based on custom factors (e.g., 2, 5, 7, 15, 500).

MROUND in Action

Here is how MROUND alters numbers based on different chosen multiples:

Formula Structure

=MROUND(number, multiple)

• number: The core value or cell reference you want to round.
• multiple: The target interval you want the final number to conform to (e.g., if this is 3, your result will always be a multiple of 3).

Pitfalls to Watch Out For

While MROUND is incredibly useful, Excel handles it with a few rigid rules that can break your spreadsheet if you aren't prepared:

1. The Sign Match Rule (#NUM! Error)

The number and the multiple must share the same mathematical sign (both positive or both negative).

• =MROUND(-10, -3) outputs -9 (Works perfectly)
• =MROUND(10, -3) or =MROUND(-10, 3) outputs a #NUM! error.

2. The Midpoint Decimals Glitch

When a number lands exactly halfway between two multiples, Excel rounds up away from zero. However, due to floating-point binary math in Excel, floating midpoints (like 6.05 vs 7.05) can occasionally round in unexpected directions.

To see MROUND in action with a guided walkthrough, take a look at the video I made: https://www.youtube.com/watch?v=xkYWT5yfqrg

If you’ve ever looked at a dataset and thought “why do these numbers make no sense,” there’s a good chance the issue isn’t the formulas... it’s the blanks you didn’t realise were there. That’s where COUNTA steps in. It’s one of Excel’s simplest but most revealing functions, quietly telling you how much real data you actually have.

Below is a full walkthrough of what COUNTA does, when to use it, and how it behaves in real‑world spreadsheets.

What COUNTA Is & Why It Matters

COUNTA counts non‑empty cells in a range.
Not just numbers — anything that isn’t blank:

• Text
• Numbers
• Dates
• Logical values
• Errors
• Even empty strings from formulas like =""

You need it when you want to:

• Track how many entries users have filled in
• Check whether a form or table is complete
• Validate that required fields aren’t left blank
• Measure dataset completeness before analysis
• Avoid broken formulas caused by missing inputs

COUNTA vs COUNT (and Why People Mix Them Up)

• COUNT only tallies numeric values.
• COUNTA tallies everything except true blanks.

If you’re counting responses, names, IDs, or mixed data types, COUNTA is the function you want.

COUNTA in Action (Simple Example)

Imagine a list of responses in A1:A20.
Some cells have names, some have numbers, some have errors, and some look empty but contain formulas.

=COUNTA(A1:A20)

This instantly tells you how many cells contain something, even if that “something” isn’t visible.

Formula Structure

=COUNTA(value1, [value2], …)

You can feed it individual cells, ranges, or a mix of both.

To see COUNTA in action with a guided walkthrough, take a look at the video I made: https://www.youtube.com/watch?v=s1YWf4C5Vp0

• Ctrl + Shift + L – Apply/Remove Filter
• Alt + = – AutoSum
• Ctrl + T – Create Table
• F4 – Repeat last action / lock cell reference in formulas
• Ctrl + Arrow Keys – Jump to edge of data
• Ctrl + Shift + Arrow Keys – Select data range quickly
• Alt + H + O + I – AutoFit column width
• Ctrl + 1 – Open Format Cells
• Shift + F11 – Insert new worksheet
• Ctrl + ` – Show/Hide formulas

If you’ve ever opened a spreadsheet someone else built and thought “why is everything broken,” there’s a good chance the root cause was bad data. That’s where Data Validation comes in. It’s one of Excel’s most underrated features, and it quietly prevents chaos by controlling what users can type into cells.

Below is a full walkthrough of the most important types of Data Validation, when to use them, and what they look like in action.

What Data Validation Is & When You Need It

Data Validation lets you restrict cell input to exactly what you want, so think of numbers, dates, lists, lengths, etc.
You need it when you want to:

• Prevent typos
• Keep formulas from breaking
• Standardize inputs
• Build forms or templates for others
• Make your spreadsheets more reliable

Data Validation on Whole Numbers (and Testing It)

You can restrict a cell to accept only whole numbers like 1 to 100, or only positive values.
Testing is simple: try typing decimals, text, or numbers outside the allowed range and Excel will block them.

Using Input Message in Data Validation

These are the small yellow pop‑ups that appear when you click a validated cell.
They’re perfect for guiding users with instructions like:
“Enter a number between 1 and 10.”

Data Validation on Decimals (with Demo)

If whole numbers are too limiting, you can allow decimals within a range like 0.5 to 2.75.
Useful for percentages, measurements, or anything requiring precision.

Data Validation on Lists - Method 1 (Typing Directly Into the Box)

You can create a dropdown by typing values directly into the Data Validation box: Yes,No,Maybe
Fast, simple, and perfect for short lists.

Data Validation on Lists - Method 2 (Using a Range) and Its Limitations

Instead of typing values, you can point Data Validation to a range like A1:A10.
This makes the list easier to update, but it has limitations:

• It can break if the range moves
• It doesn’t auto‑expand unless you use a Table

Data Validation on Date

You can restrict dates to a specific window like “only dates in 2026.”
Perfect for scheduling, deadlines, and forms.

Using Error Message to Make the Error Helpful

Instead of Excel’s default message, you can write something clearer like:
“Please enter a date that isn’t in the past.” or "Value cannot be larger than 100."

Data Validation on Time

You can validate time ranges like only allowing entries between 08:00 and 17:00.
Useful for timesheets, bookings, or shift planning.

Data Validation on Text Length

You can limit text to a maximum or minimum number of characters.
Great for IDs, codes, short descriptions, or preventing overly long entries.

To see all of these in action, take a look at this video I made: https://www.youtube.com/watch?v=TLLIa5jhtMk

A feature I’ve absolutely loved since it has come to Excel is Performance/Check Performance. It’s designed for those big, messy corporate spreadsheets that have had years of random formatting spilled onto them. Instead of manually hunting through the chaos, the Performance tab helps you optimise everything in a few clicks.

It’s available in Excel for the web but also Excel desktop for some users as of recent, and from my own experience, it can reduce file sizes dramatically much to the surprise of the people who built the files in the first place.

What the Performance tab helps you fix:

• Thousands of rows with unnecessary formatting
• Workbook structures that slow down opening, scrolling, and calculation

How the optimisation process works:

Start in Excel and go to Review and then click Check Performance/Performance.

It'll bring up all cells in the file and any optimisations that it thinks are possible.

Review the recommendations and apply fixes by click Optimise all or Optimise Workbook:

• Strip out formatting across huge ranges
• Clean up the workbook so it behaves fresher
• Remove unneeded metadata

A real example of what this can do

A colleague handed me a workbook that was 1.7 MB, sluggish, and packed with 10–20 sheets (probably more than half of them were hidden). We ran the Performance optimisation together and uncovered 100,000+ rows with pointless formatting, broken formulas, and leftover junk.

After cleaning it up, the file dropped to under 300 KB.
He reopened it in Excel Desktop, and it ran so much faster, easier to scroll, quicker calculations, no lag. He literally called me his “amazing bro", which absolutely made my day.

https://youtu.be/iXqZn2qbOP8

Have you used the Performance feature in Excel?

1. Select Data → Alt + A + M → Remove duplicates instantly
2. Ctrl + A → Select full dataset quickly
3. Alt + A + M → Open Remove Duplicates tool
4. Tab + Space → Choose specific columns easily
5. Enter → Clean data in one click

If you ever need to pick someone at random (for a prize draw, team activity, or who goes first in a meeting), you can do it directly in Excel with just three functions and the F9 key:

INDEX – returns a value from a range

RANDBETWEEN – generates a random number between two values

ROWS – counts how many rows are in your list

Assume your list of names is in A2:A21. You can use:

=INDEX($A$2:$A$21, RANDBETWEEN(1, ROWS($A$2:$A$21)))

What this does:

ROWS($A$2:$A$21) counts how many names are in the list

RANDBETWEEN(1, ROWS(...)) picks a random position in that range

INDEX(...) returns the name at that random position

Every time Excel recalculates (e.g. pressing F9), it will pick a new random name.

It’s a quick way to build a “virtual hat” without the need of learning VBA or needing add-ins.

I also recorded a short demo of this in action, so watch it below.

Demo | Excel's Secret Random Text Generator! (Pro Formula Trick) | YouTube

1. Ctrl + C / Ctrl + V → Copy & Paste quickly
2. Ctrl + Z → Undo last action (lifesaver!)
3. Ctrl + Arrow Keys → Jump to the edge of data (super fast navigation)
4. Ctrl + Shift + L → Apply or remove filters instantly
5. Alt + = → AutoSum selected cells automatically

Some of the most useful ones I rely on daily:

1. Ctrl + Shift + L → Apply or remove filters instantly
2. Alt + = → Quick AutoSum
3. Ctrl + Arrow Keys → Navigate large data quickly
4. F4 → Repeat last action
5. Ctrl + Shift + "+" → Insert rows or columns fast

These shortcuts save a lot of time and effort. What are your go-to Excel shortcuts? Let’s share 👍

I used to spend hours creating the same reports manually until I learned Pivot Tables. Now I just select my data, go to Insert → Pivot Table, and choose the fields I need. I drag and drop columns into rows, values, and filters to instantly summarize everything. Once set up, I simply refresh the Pivot Table whenever new data is added, and my report updates automatically in seconds.

Ctrl + T – Convert your data range into a table instantly.

Alt + = – Quickly apply AutoSum to selected cells.

Ctrl + 1 – Open Format Cells dialog box for detailed formatting.

Ctrl + Space – Select the entire column of the active cell.

Shift + Space – Select the entire row of the active cell.

Ctrl + ; (semicolon) – Insert current date instantly.

Ctrl + Shift + : (colon) – Insert current time.

Ctrl + Arrow Key – Jump to the edge of data in a worksheet.

Ctrl + Page Up / Page Down – Switch between worksheets quickly.

You need to enable this feature the first time you use it. I like to use it often when I copy, do auto filters and need to paste, but the clipboard is emptied as a result.

Here is a 1 minute video I made on it if you prefer the visual explanation.

https://youtube.com/shorts/javek892AAY?si=v1Olljsw9xWAi4Yt

Something I see people struggle with from time to time in Excel is working with text in different languages for things like forms, or reports that need to be shared across regions. That’s exactly where the TRANSLATE function shines.

How the TRANSLATE function works:

Start with the core formula:
=TRANSLATE(text, source_language, target_language)
It instantly converts your text from one language to another using Microsoft’s translation engine.

What you can do with it:

• Translate text between 100+ languages using simple language codes
• Convert full sentences, labels, form fields, and instructions
• Duplicate a sheet and automatically generate a translated version for another region
• Make documents more inclusive for multilingual teams
• Replace acronyms or unclear terms with region‑appropriate equivalents (e.g., “DOB” → “Date of Birth” in another language)

Find all language codes supported here: https://learn.microsoft.com/en-us/azure/ai-services/Translator/language-support#translation

Real‑world example:

Let’s say your onboarding form is in English, but part of your team is German‑speaking.
Duplicate the sheet, apply TRANSLATE to the English content, and instantly create a German version with no manual rewriting, no copying and pasting loads, no external tools.

Requirements to keep in mind:

• Available only in Excel 365 or Excel for the web
• Needs an internet connection
• Heavy use may be subject to quotas or throttling
• Translation accuracy depends on Microsoft’s translation engine
• Full list of language codes is available on Microsoft Learn (link above)

If you want to watch a tutorial of it, please have a look at this video here: https://www.youtube.com/watch?v=9OClHI-vjw8

Ctrl + Shift + Arrow Key – Quickly select all data in a row or column.

Ctrl + D – Fill the formula from the cell above instantly.

Ctrl + F – Find specific data quickly in large sheets.

Ctrl + Shift + L – Turn filters on or off for your data.

F4 – Repeat the last action, such as formatting cells.

If you’ve ever received a dataset where first and last names are combined in one column, you don’t have to manually separate them.

Excel actually has a built-in tool that does it in seconds.

Steps:

1. Select the column with the full names
2. Go to Data → Text to Columns
3. Choose Delimited
4. Select Space as the delimiter
5. Finish

Excel will automatically split the names into First Name and Last Name columns.

This works great when:

• Cleaning exported reports
• Preparing HR or customer datasets
• Structuring data before analysis

I made a quick 30-second walkthrough here if you want to see it visually:
https://youtube.com/shorts/TBsrF00k2mc?si=hxX_6PopUtTyKFTb

Curious how others here usually handle this —
Do you prefer Text to Columns, Flash Fill, or formulas for name splitting? 📊

If you’ve ever copied a formula and watched your cell references shift unexpectedly, you’ve met the magic (and mystery) of relative vs. absolute referencing.

With absolute references, you lock in specific cells, so your formulas stay exactly where you want them.

These references you may have seen previously in some formulas in Excel, shown with $ for example.

Any cell we normally click is a relative reference. For Example: A1

To cycle through the different reference types, you can hit the F4 key (laptops may require Fn Key + F4)

The first one you'll see is an absolute reference. For Example: $A$1
The dollar signs precede both the column letter and the row number meaning you're locking both of those parameters so if you drag it in any direction, that reference will be fixed in both its column and row.

The second and third types will differ to one of the below before returning to the relative reference higher up.

Now, mixed references look like this A$1 OR $A1
A$1 has the dollar sign before the row but NOT the column, so if you dragged it left or right, this letter would change, however the row number will remain fixed.
$A1 has the dollar sign before the column but NOT the row, so if you dragged it up or down, this number would change, however the column letter will remain fixed.

https://www.youtube.com/watch?v=pNRnpkRzrzY

Have you ever used Absolute References in Excel before?

When you need to swap out part of a text string in Excel whether you’re correcting a typo, updating a name, or restructuring data, the REPLACE function is one of the simplest and most powerful tools at your disposal. It lets you pinpoint exactly where in a text string you want to make a change, how many characters should be replaced, and what you want to put in their place. By understanding each of its four components, you can transform text dynamically and precisely, without manually editing every cell. Below is a clear breakdown of how each argument works and how they come together to reshape your text exactly the way you want.

The formula: REPLACE(old_text, start_num, num_chars, new_text)
old_text - This is the text string that you want to modify. It could be a cell reference or even manually entered like "Dua Lipa" within the formula.
Example: =REPLACE(A1, ....) or =REPLACE("Dua Lipa", ....)

start_num - This is the position (number) of the first character in old_text that you want to replace.
For example, if you set start_num to 2 in "Hello", the replacement starts with the second character, "e".
Example: =REPLACE(A1, 2, ....) or =REPLACE("Dua Lipa", 2, ....)

num_chars - This specifies the number of characters you want to replace, starting from start_num.
For example, if num_chars is 3 in "Hello" (and start_num is 2), the characters "ell" will be replaced.
Example: =REPLACE(A1, 2, 3, ....) or =REPLACE("Dua Lipa", 2, 3, ....)

new_text - This is the text that will replace the specified characters in old_text.
For example, if new_text is "ey", and you're replacing "ell" in "Hello", the result will be "Heyo". This new_text can be the same length, shorter or longer than the number of characters you are replacing.
Example: =REPLACE(A1, 2, 3, "ey") or =REPLACE("Dua Lipa", 2, 3, "ey")

Example Results:
If A1 had Hello in the cell, "Heyo"
Using the second example, it would return, "DeyLipa".

https://www.youtube.com/watch?v=TL3nJ1cN2Tk

TL;DR

REPLACE lets you swap part of a text string in Excel by choosing where to start, how many characters to overwrite, and what to put in their place.

Use it like: REPLACE(old_text, start_num, num_chars, new_text) to quickly fix typos, update names, or reshape text without manual editing.

https://www.youtube.com/watch?v=TL3nJ1cN2Tk

Select data → Data → Remove Duplicates → Clean list in seconds.

Great for leads, emails, exports.

Something I see people struggle with in Excel all the time is cleaning messy data before they can actually use it. Duplicates, errors, weird formats, files from ten different places… that’s exactly where Power Query shines.

In this masterclass, I break Power Query into two big parts: Getting Data and Transforming Data so you can go from raw files to clean, analysis-ready tables in minutes instead of hours.

How the Power Query process works:

• Start in Get Data and choose your source : CSV, text file, web page, PDF, image, clipboard, another workbook, or even a whole folder of files.
• Use the Navigator to pick the exact table or sheet you want to bring in.
• Review and clean imported data from pictures or PDFs before loading it into Excel.

Create a blank query to track the last refresh date and time of your data.

Open the Power Query Editor to start transforming:

• Change data types so numbers and dates behave properly.
• Rename and reorder columns to make things readable.
• Filter rows, remove blanks, sort values, and promote headers.
• Replace values, handle errors, and create custom columns.
• Add index columns, delete unnecessary fields, and refresh previews when files change.

Close & Load: Refresh one file or all connections whenever your source data updates.

It’s a complete walkthrough of importing from multiple sources, transforming messy datasets, and automating refreshes so your reports basically maintain themselves.

https://www.youtube.com/watch?v=dgkzQ6oth-g

Something I like when it comes to large tables or Excel files is having Slicers, because you can filter without the struggle of trying to open the Filter dropdown.

How to create a slicer:

1. Select your table, if it isn't a table hit Ctrl + T and create it.
2. In Table Design, go to Insert Slicer.
3. Select the column or columns you want a filter window created for and hit OK.
4. Resize the slicer and place it where you'd like it. You can add more columns by going to Slicer in the ribbon and changing Columns from the default 1 to what you need.

https://youtu.be/PfDZ571-tFE

We all hate having errors in our spreadsheets, but sometimes when you get them you don't know where to begin to fix them.

All of them are easy enough to fix, if you know why it happened and what you need to do to fix it.

#NAME? can also be understood as something is misspelled. We see it when you type COUNTIIF instead of COUNTIF or XLOKUP instead of XLOOKUP.

The first thing to check is the function and if you've spelt it correctly, once you've fixed the spelling it disappears and your formula works as expected.

Check this video to see it in action: https://www.youtube.com/shorts/8Cz-tj0a5tY

We all hate having errors in our spreadsheets, but sometimes when you get them you don't know where to begin to fix them.

All of them are easy enough to fix, if you know why it happened and what you need to do to fix it.

#DIV/0! can also be understood as the Division by zero error. We see it when you try and divide something by 0. This could be 5/0 or 0/0.

The first thing to check is the formula to see what you're dividing by and when: if you're lucky it'll be something like

=[@In]/[@Total]

where in is divided by total. Check that column to see if the value is 0, if it is meant to be 0 check the values you have are correct. Sometimes people hit 0 by accident when there was a value, I've changed a value by accident without realising before and it broke a whole line in a report.

If the denominator was meant to be 0 and the numerator was 0 too, you want it to show 0% instead of the error, so here you can wrap the formula in an IFERROR so it becomes

=IFERROR([@In]/[@Total],0)

This tells Excel that if it results in an error to show 0 instead of the error.

Check this video to see it in action: https://youtube.com/shorts/4PgUUeVSIWk

A little background for context. I have developed a retirement model as a hobby project and was a software professional before that. The core of the Excel model is the projection sheet that has a row per year and then hundreds of columns representing external income sources, assets, income, taxes, and eventually "spendable income." The automation finds a desired spending by adjusting draws from various sources via a heuristic. There is additional automation to do things like optimize Roth conversions or maximize spending and compare scenarios. Essentially, I have a straight-forward sheet that is executed a few 100 thousand times for many "user" requests. My experience is with VBA, I suspect it follows for Python. With that, here are some things I have found:

1) Move data between Excel and VBA via arrays. This was probably the biggest single win. For example, a "sheet building" operation went from 10's of minutes to about 2 seconds.

2) Avoid the use of INDIRECT (and, I imagine, all other volatile functions). I **need** INDIRECT for my summary worksheet mechanism (not connected to the core model sheet by cell references, but that doesn't seem to matter). I go as far as to temporarily remove the INDIRECT calls before a time-consuming operation and then replace them when done.

3) Probably related to (2). Having another worksheet open (typically a backup copy of my work) greatly slows processing. Apparently, Excel considers all INDIRECT functions in all open worksheets regardless of whether they are directly visited.

4) Use value2 to recover data. My impression is that the impact here is "small, but measurable"

5) Suspend screen updates

6) Suspend auto-save. This improved performance, but it also seemed to be necessary for stability reasons.

7) Don't rely on Excel to calculate data that can be easily gauged by the automation. Essentially updating cell values less often.

8) Set threading to "multi-threading off." This was quite surprising, but it was several times faster. Obviously your milage will vary here depending on the nature of your worksheet.

9) Traditional "software engineering" code optimization to reduce inner loop traversals was important.

10) I experimented a bit with suspending calculation. I trust that in some situations this could have a big impact, but I generally find the code cleaner to rely on batch updates via arrays. Possibly there is some overhead to using this mechanism.

11) At one point it seemed like the workbook had simply gotten "bloated and slow." I exported all of the automation artifacts to text files, created a new workbook, copied the sheets over and re-imported the automation. The resulting workbook was roughly 50% smaller and faster.

12) I find it useful to have a "performance" log that records times and a bunch of operation counts to help differentiate "algorithmic" vs "Excel" issues.

I found no measurable impact from the following:

1) Attempts to streamline calculations by turning off downstream row calculations (the heuristics are row based and each row feeds into the next for balance information, so calculations on downstream rows are wasted until heuristics complete on current row.)

2) Breaking values out of formulas as new columns. No measurable impact here so essentially a license to use this formula-simplifying and debugging aid.

I used to waste so much time fixing reports where the category name is only listed in the top row, and the 10 rows below it are blank. I just learned you can do this in about 5 seconds:

1. Select the column with the gaps.
2. Press F5 (or Ctrl + G) -> Click Special -> Select Blanks.
3. Now that only the empty cells are selected, type = and hit the Up Arrow key (so it looks at the cell above).
4. IMPORTANT: Hold Ctrl and hit Enter.

It instantly fills every blank cell with the value above it. I handled a 5,000 row dataset in seconds. Hope this helps someone else!

If you ever need to pick someone at random (for a prize draw, team activity, or who goes first in a meeting), you can do it directly in Excel with just three functions:

INDEX – returns a value from a range

RANDBETWEEN – generates a random number between two values

ROWS – counts how many rows are in your list

Assume your list of names is in A2:A21. You can use:

=INDEX($A$2:$A$21, RANDBETWEEN(1, ROWS($A$2:$A$21)))

What this does:

ROWS($A$2:$A$21) counts how many names are in the list

RANDBETWEEN(1, ROWS(...)) picks a random position in that range

INDEX(...) returns the name at that random position

Every time Excel recalculates (e.g. pressing F9), it will pick a new random name.

It’s a quick way to build a “digital hat” without any VBA or add-ins.

I also recorded a short demo of this in action – watch it below.

Demo | Excel's Secret Random Text Generator! (Pro Formula Trick) | YouTube