10 Hidden Features in Microsoft Excel (You’ll Wish You Knew Sooner)

▼ Time Stamps: ▼
0:00 - Intro
0:25 - Camera
1:27 - Importing Web Data
2:32 - 3D References
3:14 - Go To Special
3:49 - A Very Excellent Thing
5:36 - Circular References
6:17 - Autosave Interval
6:32 - Custom Lists
7:03 - Developer Tab
7:32 - Advanced File Properties
7:53 - INDIRECT Formula
8:49 - Cell Dropdown Lists
9:26 - Part 2: Features Hidden in Plain Sight
9:34 - Named Ranges
10:11 - Quick Analysis
10:30 - Forecast
10:53 - Watch Window
11:12 - Formula Auditing
12:39 - Conditional Formatting
13:46 - Sparklines
14:29 - Change Multiple Sheets
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬

[ThioJoe]

How many of these did you know about? 👀 And did I miss any really good ones?

[NigelMelanisticSmith]

Power Query is a seriously underrated tool in Excel. If you ever have to work with SharePoint, CSVs, JSON, SQL, or lots others, it is the strongest tool to use within the Office ecosystem.

For JSON specifically, I even prefer Power Query to most text editors, since Power Query makes it easy to turn JSON data from hierarchical to tabular.

Additionally, once you move from Excel to Power BI and the Power Platform, all your Power Query knowledge transfers. Your Power Queries can even be replicated by others, since Power Query is a front-end for "M" Code.

[lordelliott]

9:12 Here's a tip. You can use the INDIRECT formula as the source of your data validation list. This is really helpful if you make the list of things a table, because when you add anything to the table, it will show up in the data validation dropdown.

For example, you have a cells that you need to type names of employees in. Create a table that list all their names, and when you hire or fire people, all you have to do is update the table and the data validation list updates automatically. So, if you have a table called "Names" with a column called "Employees", put the formula =INDIRECT("Names[Employees]") as the data validation list source. If the table is running a power query, even better. If the table is running a power query to pull from an pre-existing company database, even better.

[dragons_advocate]

re: 0:26 Apart from the "camera" feature, which is a bit confusing, you can copy cells and paste them as "paste special" - "linked graphic", which does the same and is much quicker.

[RobBulmahn]

One that really is a game-changer: ListObject tables (and subsequently, structured references). This is when you use Insert > Table on your data (or Ctrl+T or Ctrl+L). Once you turn your data into a table, you can start using structured references. Instead of needing to remember the precise row/col address of a cell (particularly annoying when you have huge tables), it will instead be addressable by column name in the header (first row). Additionally, tables can be named, so it becomes really simple to do lookups and whatnot across tables, since you can just use everything's name (e.g. "Sales[Unit Cost]"). Also great, it makes it much more difficult to screw up formulas. When you're adding/removing stuff, it's too easy to have that address get off by a row/column, and suddenly everything's messed up. If you use structured references, it automatically assumes you're referring to the same row that you're currently on* (minus aggregate functions like SUM), and also auto-applies this formula down the entire column.

[exoticlol]

Excel is a really powerful tool. I have been experimenting with it lately and it offers a lot of niche features. I have found myself attempting to code something complicated and finding out there's an Excel feature which does what I'm looking for.

[xntumrfoivrnwf]

Circular references / iterative calculation: useful if you have a complicated financial model, e.g. merger model with full 3 statements, etc, etc. However remember to turn it off when adding data tables

[Tyranitar]

Yes I NEEDED the named ranges for my school science project. Very useful to just name whatever values and reference it instead of loosing track. Also make one for Powerpoint too!

[Roobitz]

One trick I learned: when you get that error message that you have numbers stored as text in a cell, you can use "Paste: Multiply" to apply a multiplication of one to all of the cells to instantly convert them all into being stored as numbers instead of text. Just select the value 1 in another cell, copy it, then select the range of cells you want it to apply to and Paste as Multiply. Way faster than fixing each cell one at a time.

[VincentHardwick]

Just a note of caution - keep any use of the INDIRECT function to an absolute minimum, and avoid it if possible.

INDIRECT is a 'volatile' function - its output is recalculated every time a change is made anywhere else that causes Excel to recalculate, whether any of its input arguments have changed or not.

This can cause severe drag on the performance in a workbook if it's not used sparingly and judiciously.

There is usually an alternative to using INDIRECT involving defining a name in the Name Manager to represent a dynamic range, using COUNTIF or COUNTA in conjunction with INDEX and the range operator (:).

[Robertganca]

Very useful video! Hopefully you will continue this with PowerPoint next.

[marxmaiale]

Indirect is normally matched with ADDRESS, to be able to use a calculation to determine the source of the data to be displayed. It should be used sparingly as it is a 'volatile' function that recalculates on every change (the more it is used the worse the workbook performs similar to the other lookup functions).

[Cossieuk]

F12 for File Save As
CTRL+` will shows any formulas as text rather than the result of the formula, great for finding formulas in large sheets
If you make a drop down list it can be useful to hide column with the data so it doesnt get deleted accidentally, or if the cells are protected make the text white so it doesnt show up

[exoticlol]

I was waiting for this one. Thanks for making it! Next is PowerPoint, I'd love to see it.

[Sembazuru]

A simple pair of keyboard shortcuts that I seem to constantly use are "<ctrl>+;" and "<ctrl>+:". The control with the semi-colon inserts the current date and the control with the colon (which on US keyboards is actually "<ctrl>+<shift>+;") inserts the current time. I at times that this was a more universal keyboard shortcut, but it does also work in LibreOffice Calc, and partially works in Google Sheets. In Google Sheets it only seems to work for direct cell entry, not in the cell text entry box. I.E., if you have a cell in Sheets selected the keyboard shortcut works, but if you click into the formula bar for a cell it doesn't work (unlike in Excel and Calc where the shortcut also works in the formula bar).

[wayneyadams]

There is a very handy hidden formula in Excel that does not show in the list of formulas. It is "Datedif. It is formatted like this.
=DATEDIF(cell1, cell2, "X")
cell1 = start date
cell2 = end date
X = ""y" Number of full years between dates.
X = "m" Number of full months between dates.
X = "d" Number of days between dates.
X = "ym" gives the number of months left in the final partial year, for instance 6 for 3 years 6 months.
X = "yd" gives the number of days left in the final partial year, for instance 180 for 3 years, 180 days.
X = "md" Gives the number of days left in the final partial month

Here is an example from one of my spreadsheets.
C6 = 06/01/1960
F6 = 12/07/1974
=DATEDIF(C6, F6, "y")&" years, "&DATEDIF(C6, F6, "ym")&" months, "&DATEDIF(C6, F6, "md")& " days"
Output: 14 years, 6 months, 6 days

[ChrisMelville]

ThioJoe - I am an advanced VBA developer. But you still taught me a few nifty Excel tricks I did not previously know. Thank you :)

[dragon]

Conditional form, atting is also one of my favorites in clarifying the sheet data as well as making it more presentable. And interactive as well. My god it's such a beautiful tool. The dropdown list I also knew but forgot how to so this was a nice reminder. The indirect formula is new to me and imma use the hell out of it in a few sheets

[edutenorio]

I think I’m a heavy Excel user and still I didn’t know many of the features presented in this video. Great stuff!

[andrewmoncada]

the first one of these “hidden/advanced” excel features that’s impressed me!