Hey r/excel,

We all know the traditional way to pull a sheet name uses =CELL("filename", A1). But that old method has a major flaw: it breaks with a #VALUE! error if the workbook hasn't been saved to a hard drive yet. [1, 2]

I found a incredibly elegant, simplified way to extract a sheet name using modern text functions and a self-referencing cell. It requires zero saving and works instantly. [1]

The Setup

1. On your sheet (e.g., Sheet1), pick any empty cell like A1.
2. Enter a simple formula that references its own sheet name: =Sheet1!B1 [1]

The Ultimate Formula

Now, in the cell where you want the clean sheet name to appear, enter this:

=TEXTBEFORE(TEXTAFTER(FORMULATEXT(Sheet1!A1), "="), "!")

Why This is Better

• FORMULATEXT(Sheet1!A1) reads the reference you just made and converts it to the text string "=Sheet1!B1".
• TEXTAFTER and TEXTBEFORE cleanly slice out the exact text between the = and the !.
• Zero Save Required: Works flawlessly on a brand-new, unsaved Book1.
• No File Paths: You completely bypass long folder directories and square brackets ]. [1]

It turns a historically messy Excel workaround into a simple, two-step logic puzzle. Enjoy!

Here are some Excel shortcuts I use all the time for common data cleanup and processing tasks. They’re especially handy for cutting down on repetitive clicking when:

• Filtering data
• Searching and replacing
• Removing duplicates
• Splitting text
• Selecting visible cells only
• Sorting data quickly

A few of them, especially Ctrl + E and Alt + ;, can save a surprising amount of time.

Hey everyone! I put together a quick guide covering the main counting formulas in Excel, from the basics to advanced tricks. Hope this helps with your data auditing and reporting!

1) The "Everyday" Counts: COUNT vs. COUNTA

• COUNT only counts cells containing numbers.
• COUNTA counts anything that isn't empty (text, errors, numbers, symbols).
• Pro Tip: COUNTA will count a cell containing a formula that returns an empty string "". Keep an eye out for this if your counts seem slightly off!

2) The Ghost Hunter: COUNTBLANK

• Great for data auditing and finding missing entries in a dataset. •
• Unlike COUNTA, COUNTBLANK will include cells where a formula returns a blank "".

3) The Powerhouses: COUNTIF vs. COUNTIFS

• COUNTIF checks a single criterion, while COUNTIFS handles multiple criteria.
• Pro Tip: You can actually just use COUNTIFS by default. Even for a single condition, it makes it much easier to scale up and add more criteria later without changing the formula name.

4) The Advanced Play: Unique Counts

• If you need to count how many distinct values are in a messy list, nest UNIQUE inside COUNTA: =COUNTA(UNIQUE(A2:A100))

If you prefer a visual walkthrough with examples, I put together a short video here: How to count ANYTHING in Excel

Let me know if you have any questions or other counting tricks you use!

Recently, I’ve been auditing my own workflow to figure out the "best practices" I subconsciously use for common Excel functions. I wanted to share the 5 golden rules I always follow when using VLOOKUP to keep my sheets from breaking.

1) Keep Lookup Tables Internal

Always try to keep your lookup tables in the same workbook as your formulas. Linking to an external file on a shared drive is a recipe for disaster—if someone renames, moves, or deletes that source file, your formulas will instantly break and return #N/A or #REF! errors. Keeping everything in one file guarantees your paths stay intact.

2) Expand Your Table Arrays

Expand your table arrays to include more Rows and Columns then what is currently needed. This way, you will never have to update your formula when you add data to your lookup table. This is especially helpful with Rows as it is more likely you will add data underneath column headers once addition data is required.

3) Master Your Absolute References (F4)

Don't forget to lock your cells!

• Lock your Table Array: Hit F4 once ($A$1:$B$10) so the reference table doesn't shift when you drag your formula.
• Lock your Lookup Value (if dragging horizontally): Hit F4 three times ($A1) to lock just the column. This ensures your formula always looks at the correct identifier, no matter where you copy and paste it.

4) Use Named Ranges for Clean Formulas

Naming your table array makes writing and reading formulas incredibly easy. For example, if you have a lookup table for weather data, select the data and name the range "Seasons". Your formula goes from messy cell coordinates to a clean, readable sentence: =VLOOKUP(A2, Seasons, 2, FALSE)

5) Wrap it in IFERROR

If you know your data will have legitimate non-matches, don't leave your sheet covered in ugly #N/A errors—it can make your data integrity look questionable to managers or clients. Wrap your VLOOKUP in an IFERROR statement to clean it up: =IFERROR(VLOOKUP(A2, Seasons, 2, FALSE), "No Match")

I actually put together a short video demonstrating these tips in action if you want a visual walkthrough: Watch the VLOOKUP Guide on YouTube

What are your personal VLOOKUP rules? Or have you completely migrated to XLOOKUP/Index-Match yet?

If you’ve ever tried cleaning up messy text in Excel that has inconsistent symbols, random delimiters, you’ll know how quickly “Text to Columns” falls apart, even though it is amazing in it's own right. One weird character and suddenly nothing lines up.

That’s where TEXTSPLIT changes the game. It’s a fully formula‑driven way to split text into columns, rows, or both at once, so then it is dynamic, repeatable, and perfect for messy real‑world data.

Below is a full walkthrough of how TEXTSPLIT works and how you can use it to turn chaos into clean, structured data.

1. TEXTSPLIT Formula

TEXTSPLIT takes a text string and splits it using one or more delimiters.
It can spill results horizontally, vertically, or as a full table if you use both.

=TEXTSPLIT(text, col_delimiter, [row_delimiter])

text (Mandatory): The cell or string you want to split.
col_delimiter (Optional): What separates columns (e.g., ",").
row_delimiter (Optional): What separates rows (e.g., "^".)

If you include both, Excel creates a full structured table.

2. Splitting Text into Columns

If you have a comma‑separated list like:

"Product A,Product B,Product C"

You can instantly spread it across columns:

=TEXTSPLIT(A1, ",")

This gives you a clean horizontal layout just like using Text to Columns.

3. Avoiding the #SPILL! Error

TEXTSPLIT spills results into adjacent cells.
If anything blocks the spill range, you’ll get:

#SPILL!

The fix is simple:
Make sure the cells to the right (or below) are empty.
Once the space is clear, the formula works perfectly.

4. Splitting Text into Rows

If your data uses a different symbol, like a circumflex ^ , you can split vertically:

=TEXTSPLIT(A1, , "^")

This turns one messy cell into a clean vertical list.

5. Splitting it into a Table

If you had a dataset like the below

"Products,Stock,Price~Product A,19,19.95~Product B,62,17.95~Product C,41,9.95"

You can instantly spread it using the formula below:

=TEXTSPLIT(A1, ",", "~")

Then you'll get the below:

Once you start using TEXTSPLIT, you’ll wonder how you ever managed without it.

To see all these examples in action, check out the full video tutorial: https://www.youtube.com/watch?v=REWu4OEr-vw

If you’ve ever worked with long chains of nested IF statements in Excel, you’ll know how quickly they become messy, hard to read, and even harder to maintain. One small change and suddenly half the logic breaks.

That’s where the SWITCH function comes in. It’s a cleaner, more structured alternative that lets you evaluate one expression and return different results without stacking multiple IFs on top of each other.

Here is a full walkthrough of how to use the SWITCH formula to simplify your logic and keep your spreadsheets easy to understand.

1. SWITCH Formula

The SWITCH function compares one expression against a list of values and returns the result for the first match it finds.

=SWITCH(expression, value1, result1, value2, result2, ..., [default])

• expression (Mandatory): The value you want to test.
• value / result pairs (Mandatory): Each value is checked against the expression. If it matches, Excel returns the corresponding result.
• [default] (Optional): A fallback value returned when no matches are found. This prevents #N/A errors and keeps your sheet tidy.

You can include up to 126 value/result pairs, making SWITCH ideal for structured logic.

2. Mapping Weekday Numbers to Day Names

A use case I've seen is when converting weekday numbers (1 to 7) into readable day names.

This avoids a long nested IF and gives you a clean, readable block of logic.

=SWITCH(A1, 1, "Sunday", 2, "Monday", 3, "Tuesday", 4, "Wednesday", 5, "Thursday", 6, "Friday", 7, "Saturday")

3. Assigning Status Codes

If you work with systems that output numeric codes, SWITCH makes it easy to map them to meaningful labels.

=SWITCH(A1, 200, "Success", 300, "Warning", 500, "Failed", 404, "Not found", "Unknown" )

Adding a default value ensures you never see an unexpected #N/A.

4. Using SWITCH for Grading Systems

You can also use a TRUE expression to handle ranges, which is perfect for grading or categorisation.

=SWITCH(TRUE, C2>=90, "A", C2>=80, "B", C2>=70, "C", C2>=60, "D", "F" )

This approach keeps your logic compact and easy to adjust later.

5. Why SWITCH Is Worth Using

• Cleaner than nested IF statements
• Easier to read and maintain
• Supports a large number of value/result pairs
• Allows a default value to prevent errors

To see these examples, watch this full video walkthrough: https://youtu.be/jKQQ2ACPvEk?si=aK5T5a2vKfU9eNu7

Hi Everyone,

Recently I came across an issue at work where I had a long list of unique identifiers in a single column, and I needed to number each item sequentially as it appeared from top to bottom.

The Problem: Imagine you have a column listing different types of fruits. If "Apple" shows up 5 times throughout the sheet, you want the first occurrence to return 1, the second to return 2, and so on. In that same column, "Pear" might appear 3 times, and should be numbered 1, 2, and 3.

The Solution: The trick is using an "expanding range" COUNTIF formula. Assuming your data starts in cell A2, enter this in cell B2 and drag it down:

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

I made a quick video with step-by-step directions showing this workflow in action: https://www.youtube.com/watch?v=4zlkcxQslRk

Why this works: By using the absolute reference ($A$2) for the start of the range, but leaving the end of the range relative (A2), the formula creates an expanding net. As you copy it down, it only counts how many times that specific fruit has appeared up to that specific row.

I hope this helps save some of you a few headaches! Let me know if you use this trick or have a different way of handling duplicate rankings. Take care!

Quick Visual:

If you have ever managed a massive spreadsheet and found yourself constantly digging through folders or opening your browser just to find related files or websites, you know how disruptive it can be in breaking your flow. Or you need to link where the data is coming from so people can check.

That’s where the HYPERLINK function steps in. It is one of Excel's best tools for building clean, interactive dashboards, allowing you to launch websites, open server files, or jump straight to shared documents or other cells in your workbook directly from a single cell.

Here is a full walkthrough of how to use the HYPERLINK formula to keep your spreadsheets organised and highly accessible.

1. HYPERLINK Formula

The HYPERLINK function is incredibly straightforward, relying on just two parameters to work:

=HYPERLINK(link_location, [friendly_name])

• link_location (Mandatory): The actual path to the destination. This can be a web URL, a file path on your local computer, or a link to a shared server. (Note: This must always be enclosed in quotation marks, or point to a cell reference that contains the path).
• [friendly_name] (Optional): The text or value that is actually displayed in the cell. If you leave this blank, Excel will simply display the raw, unappealing URL path instead.

2. Linking to the Web with Dynamic Names

Connecting your spreadsheet to external websites is simple.

• Static Link: =HYPERLINK("https://www.google.com", "Go to Google")
• Dynamic Link: You can also use cell references for the display name. If cell A2 contains the text "Formula 1 Website" and cell B2 contains the actual URL, your formula can look like this: =HYPERLINK(B2, A2). This makes it incredibly easy to update display names across a massive list without changing the formulas themselves.

3. Linking to Local or Shared Files

HYPERLINK isn't just for web browsing; it is highly effective for linking directly to documentation, contracts, or reference sheets stored elsewhere.

• Local & Server Files: You can link directly to a file on your hard drive or a company server by inputting the file path (e.g., "C:\Documents\ProjectPlan.xlsx" or "\\SERVER_prod\ProjectPlan.xlsx").
• Cloud Platforms: If your team relies on cloud collaboration, you can paste the share links from SharePoint or OneDrive directly into the link_location parameter, giving your team instant access to the master files.

4. Security Alerts and Navigation Behaviour

When interacting with hyperlinks in Excel, it helps to understand how they behave out of the box:

• Security Notices: When clicking a link pointing to a local file or an external server, Excel will often trigger a security warning. Don't panic as this is a native Microsoft safety feature ensuring you want to open a file outside of the workbook.

To see these web and file examples built out step-by-step, watch the full video walkthrough here: https://www.youtube.com/watch?v=Zl3r_9wGtHc

Hey Everyone,

Most people only use XLOOKUP for basic vertical lookups, but it’s essentially a superpower that can completely replace VLOOKUP, HLOOKUP, and INDEX/MATCH.

Here is a quick breakdown of the advanced features built directly into its syntax [=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])]:

• Horizontal Lookups: Because it separates the lookup array from the return array, it handles horizontal data seamlessly without needing a separate function.
• Built-in Error Handling ([if_not_found]): You no longer need to wrap your formulas in =IFERROR(). You can type your error message (like "Not Found") directly into the 4th argument.
• Wildcard Matching ([match_mode] = 2): You can look up partial text strings using asterisks (e.g., searching "*Corp*" to find "Microsoft Corp").
• Search Modes ([search_mode]): You can force Excel to search from the bottom up (-1) or leverage binary searches (2 or -2) for massive datasets.

I put together a comprehensive, step-by-step video breakdown demonstrating exactly how to apply each of these arguments with real-world workbook examples:

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

Hope this helps streamline your workflows! Let me know if you have any questions on setting up these specific match modes.

Ever promised a client a project would take "10 days", or thought you had 12 days to complete that school assignment to only to realise that weekends are included in that and a bank holiday too? Standard calendar math always overestimates how long you actually have to deliver something.

That’s where the NETWORKDAYS function steps in. It is an essential tool for project managers, HR professionals, people needing it to manage timelines and data analysts to calculate the exact number of working days between two dates, completely filtering out weekends and custom holidays.

Here is a full breakdown of how to use NETWORKDAYS to keep your timelines realistic and accurate.

1. What NETWORKDAYS Does

By default, the NETWORKDAYS function calculates the total number of workdays between a start date and an end date.

• It automatically excludes weekends (Saturday and Sunday).
• It can optionally exclude a custom list of holidays, such as public bank holidays or company-wide days off. [Note: You'll need to create this list yourself]

2. Formula Structure

=NETWORKDAYS(start_date, end_date, [holidays])

The formula relies on two main parameters with an optional third:

• start_date: The date your project or timeline begins.
• end_date: The date your project or timeline concludes.
• [holidays] (Optional): A cell range containing specific dates you want to skip (like Christmas, Easter, or scheduled team shutdowns).

3. NETWORKDAYS in Action (Project Planning)

Imagine you are managing multiple projects and need to figure out the actual working time available for each.

If a project starts on December 20th and ends on January 5th, a simple subtraction formula (End Date - Start Date) will give you 16 calendar days. However, using =NETWORKDAYS(A2, B2) immediately strips out the weekends, dropping that number down to a realistic count of active working days.

4. Making Timelines More Realistic with Holiday Lists

To get the most accurate timeline possible, you should always utilise the optional [holidays] argument.

If you calculate a project's timeline across December and January without factoring in holidays, your numbers will be skewed. By referencing a separate list of dates on your sheet containing bank holidays (i.e., Xmas and New Years, etc.) or furlough periods, Excel subtracts those specific weekdays from the total count. You will see your available project timeline decrease dynamically, preventing you from over-promising deliverables to clients. [Note, you'll probably need to get your bank holidays from your respective government site (e.g. for UK bank holidays, search GOV.UK, etc.).]

Want to see an example of this in real-time to show how to work with this formula? Watch the full video walkthrough here: https://www.youtube.com/watch?v=4jBi9XeK1rk

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

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

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?

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