10 Hidden Features in Microsoft Excel (You’ll Wish You Knew Sooner)
HTML-код
- Опубликовано: 26 июн 2024
- Thanks to Mine for Sponsoring: Find out which companies have your data and reclaim it 😤 by visiting ⇨ bit.ly/thiojoe-say-mine
▼ 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
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
Merch ⇨ teespring.com/stores/thiojoe
⇨ / thiojoe
⇨ / thiojoe
⇨ / thiojoetv
My Gear & Equipment ⇨ kit.co/ThioJoe
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬ 
Наука
![NOSFERATU - Official Teaser Trailer [HD] - Only In Theaters December 25](http://i.ytimg.com/vi/b59rxDB_JRg/mqdefault.jpg)
How many of these did you know about? 👀 And did I miss any really good ones?
I actually took Microsoft's Official Excel Expert certification a while back, and this still taught me some new tricks. Same for the last episode, great series so far.
would you do google sheets?
All
you should link the microsoft word video you mentioned in the description, so we can find it quickly
never have I used excel in my life
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.
Well! Going to have to learn that one. Thanks. :)
Anyone know how to read-in and format syslogs with Excel?
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.
This is great. A dirtier solution might be to reference a custom Named Range. But you need to highlight more cells than you currently have if you expect your list to grow, so your way is more elegant.
@@stryp yea, the lack of dynamic growth in Data Validation is one downside, hoping it will support named ranges and tables along with dynamic selections in a future release.
My only concern about INDIRECT is that it's a volatile formula so, for larger workbooks, it could slow the performance down.
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
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.
Yup. I thought the same thing. Probably why they hid the photo option deep in the settings. One thing I don’t like about excel is that it’s got too many menus and a lot of them are either duplicate or identical choices from other menus. They really need to clean up this monstrosity.
@@DeputyBurbank
I agree, they are way too messy. You can customise the menus in excel if it really bothers you.
They probably keep duplicate options around because different people use the different ones and if you were to pick just one and remove the rest you would just be asking for complaints. Not everyone who uses excel is actually tech savvy enough to figure stuff out, they were likely shown how to do a specific thing with specific menus and if the menus or options change they will be f...ked. Again, not a problem when it is a regular Joe Soap but a massive problem with it is a C-level exec from a big company that suddenly can't make those pretty graphs for the shareholders.
Unfortunately there are probably some massive legacy issues around removing a feature entirely. For regular users not a big problem if you remove a feature or replace it with something else or move some things around, but some companies could have vital business processes dependent on their excel workbooks working correctly and giving specific predictable outputs. I imagine they would get quite annoyed when all of their automated systems start spewing out garbage data because excel changed or removed something they used. So Microsoft keeps it there but just hides it from the menus. Legacy systems keep working, new users only see the new option, sort of a decent solution but at the cost of added bloat with every update.
@@DeputyBurbank I disagree that they should clean up multiple routes of access to features. I've complained on the Excel help forms years ago that the function (and help) wasn't in the place i expected, so I had to search the Web to find where the heck they'd placed it. Much better the way they have it now with different ways for how different people think.
Hmmm, put the camera just on the QA menu. Select the cells to copy, click the camera, then immediately go to your paste area and click. Extremely easy and quick. Unless I am missing something in your comment.
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.
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.
Didn't know about that one. Neat! The alternative I've used is selecting the whole column and then going to Data > Text To Columns. Select delimited and click finished. It won't actually do anything except update all the cells of the column as if you went into each one an hit enter.
Your way is better as long as you can get 1 into the clipboard easily. Text to Columns forces you to do it to a whole column.
Very useful! Also you can copy a blank cell; Paste Special/Add to the target cells, as Excel will interpret a blank cell as 0 in Maths.
Another option is using hot keys for excel. ALT+H+V loads the paste menu, select which option to use by pressing the corresponding letter shown for the option.
Alt+H+V+v pastes just text without formatting and has the pasted range autoselected, then press Ctrl+1 (not numpad) to open the cell format menu. Select Number and remove the .00 if those are not desired. This way you don't have unnecessary calculations which can take a performance hit when dealing with sheets containing 300,000 rows across 20+ columns with other formulas or filters applied. Probably 75% of my daily excel usage doesn't utilize the mouse, which if did, would take 4 times longer.
Other hot keys that will help for selecting large ranges
CTRL+A +A (If in a table, pressing A a third time selects the whole sheet)
Or try CTRL+SHIFT+DOWN ARROW (also works with up, left and right arrows)
F2 allows to edit the selected cell even as formula.
Is that better than left clicking a range, pick format cel- number, or hitting the format as number button in the ribbon? You can do that for a range or column(s)
@@GeeThevenin
Unfortunately, changing the format of the cells won't update the data to change the numbers stored as text into numbers stored as numbers, if that makes sense.
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.
I'm surprised how many people don't know what an Excel _table_ actually is. Structured references are the best!
The only real downside to tables is the performance hit when they get well into 100k rows over dozens of columns.
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
Circular references also come in handy in certain self-referential equations in engineering.
Circular references can also be used to do a col/row return based on dynamic criteria. I cant think of a specific use case off top of my head but its another way to cross reference data in other sheets. Say you want to know what row(s) or columns has the same information on other spreadsheets in the same workbook or even partial information. A circular reference can be used to quickly return all row or column numbers that contain the information. With this you can use mid with find/search and also indirect and a lookup formula to return the data. I used this when some of the data had a punctuation or space which would not be found in a duplicate check.
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!
One underrated feature is recognizing and parsing date values. I can't get enough of this feature ngl.
I was never, in my life, excited about Excel until I opened a business and now.... excel is like cocaine because I can't get enough
Excel is getting more powerful features every day, thanks for the video and sharing!
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
Powerpoint next? Also, a video about lesser-known miscrsoft office programs wold be awesome!
The most useful thing I ever learned in Excel: You can press F2 to edit cells
Some more super-useful ones:
Ctrl+Enter to complete input, but not jump down a row.
Ctrl+D to copy down what is in the cell above.
Ctrl+R to copy right what is in the cell to the left.
@@RobBulmahn Didn't know about those! Thank you!
You can use F2 to edit in windows explorer too. Really great for changing file names. I think you can also press tab in edit mode and it will let you edit the next file name in the list. Can save a lot of time over the more finicky alternatives.
F2 works in a lot of applications to edit the current selection. Even non Microsoft apps use it sometimes.
One handy feature with range names is pressing F3 to get a list that you can then click and entry and insert it. Saves trying to type them and making spelling mistakes.
the first one of these “hidden/advanced” excel features that’s impressed me!
I was waiting for this one. Thanks for making it! Next is PowerPoint, I'd love to see it.
ThioJoe - I am an advanced VBA developer. But you still taught me a few nifty Excel tricks I did not previously know. Thank you :)
I'm self taught with VBA.
I love using that language.
I was able to tie batch files in it.
I think I’m a heavy Excel user and still I didn’t know many of the features presented in this video. Great stuff!
actually circular references is incredibly helpful in a dcf analysis - and that formula that uses tab columns is useless it's too prone to user error of moving another tab inbetween
I knew about a few of the options but it is always a good idea to have a refresher :)
3D references.... nice! Learned something new today! 🙂 Well done!
5:36 - Circular References
This example will put the date of today in the cell it's written in, A1, when something is written in the adjacent cell, B1. But only if _Iterative calc._ is enabled. Quite useful.
=IF(B1"",IF(A1="";NOW();A1);"")
Very useful video! Hopefully you will continue this with PowerPoint next.
Thank you. Excellent information!
This was awesome! I have a very specific problem that I'm trying to find an answer for, and the 3-D referencing tip, almost got me there! Can't wait to see more!
iterative calculation is great!! thanks Joe
Perfect time for this vid, since i was just looking for this stuff
Nice job Thio! This was very useful
Most of these where new to me and I will use several of them, great video.
Thank you. Very useful tips
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).
Interesting
It takes a *lot* of INDIRECT functions to actually notice lag on a modern computer. Most people won't use enough to notice, but it is good to know it could be an issue. I try to avoid them mainly because volatile functions are a pain to troubleshoot.
I would consider myself an "advanced" user (subjectively) but there were a lot of tips here that i did NOT know about. You're precise, to the point and clear = OUTSTANDING video! Thank you taking the time to make this tutorial.
Next do PowerPoint
I learnt cool features about Excel from this video,thanks
The INDIRECT modifier will come in handy! Thanks for the intel!
The change for all selected at once really surprised me. Will definitely use it in my job because I always have multiple sheets with similar data that need to have the same changes applied to at once. You have earned a new subscriber, I really like your simple and straightforward way of describing things. Keep it up!
Thanks Thio, Circular Refference is used in Finance (company validation). One other tip: select a part of a formula and press F9. for example select SEQUENCe(3;6) and then F9, to see what is happening " under the hood" .
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
Excellent, there's always something really useful in the videos - The INDIRECT one's going to be useful.
Very informative video. Thanks
This was really useful. Nice video.
thank you.
I have been searching for this INDIRECT function for quite some times without knowing it's name, but never find it until now.
thank you very much
Thank you for this video. You covered a lot of ground with very good stuff. Didn't know about the camera one, I always used formulas to point to cells on other sheets that I need to have visible. :)
A word of warning on INDIRECT and Conditional Format as these are volatile formulas which might (if your sheet is big and complex enough) slow your calculations down.
Also a word of warning on changing multiple sheets at once - there is a bug that consistently crashes Excel if you are doing formatting options (like cell styles or conditional formatting).
Some i already knew and used often, but others i never had though in my wildest dreams. I found very interesting the: web data import, indirect formula, dropdown list, named ranges, forecast and sparklines!
Next "secrets" video about Notepad++ ???
This video was indeed very helpful!
A simple pair of keyboard shortcuts that I seem to constantly use are "+;" and "+:". The control with the semi-colon inserts the current date and the control with the colon (which on US keyboards is actually "++;") 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).
I didn't know about inserting a tab or adding the range name by typing it into the location box. Thank you for sharing!
Very good! Thanks for that marvellous information.
This is awesome, thank you!!!
Everytime really useful tips and hints. I like this channel👍
That is cool tipps. I like the camera and watchdog feature.
Good stuff, thanks!
Very useful. Thank you!
It would be nice to have one video like this for Google Sheets and Docs :D
good tips.VERY GOOD.
I love teaching my students Excel a lot
"Indirect" function and circular reference features look super useful. These are new to me.
Circular references are useful if you are calculating interest in a cash flow model based on the average cash balance. The year end cash is affected by the interest paid. Since the formula uses ½ of the year end cash balance (as it is an average of two numbers), the result will resolve with iterations.
A further tier useful feature in conditional formatting I learnt of recently and really love: there is a formula you can use to format alternate rows. Very useful if you have a multi user file that keeps receiving pasted data but needs improved row visualisation. (Can't remember formula but it involves MOD() )
Awesome video great job 👍
Sparklines was new. Thanks!
This is fantastic stuff!
Circular references are used quite a bit in Investment Banking in creating pro-forma finacial statements when the acquiring company has an established line of credit referred to as a 'revolver'.
Conditional formatting is something I regularly use, but most of the others were new to me. One tip in conditional formatting - if you want to highlight a cell only when data is present, use the formula “greater than” and set value as 0 (zero)
That is amazingly awesome! I wish I would have watched this like... 10 years ago, but hey, it's better late than never!
Good as always congrats
Outstanding!
Thank you!🙏
Crazy ! Love it
Cool and helpful video! :D
Definitely amazing ❤
Naming a cell is HUGE if you're adding lines to a sheet. That way your formula can always find a cell even after it's row and column have changed because you added rows and columns later.
useful af, thanx
I learned a few things. But some warnings should have been included:
*3D References* - If someone re-orders the sheets after this formula is made (moving them into or out of the "range of sheets"), the results of the formula will be affected.
*Go To Special* - This might be a BAD way of removing the blank rows. If there are blank cells that are in not-fully-blank rows, those cells will also be selected, and then be deleted. So, for example, if the extra blanks were in column C, if you: a) "Shift cells up", you could end up shifting some column C data up to rows with non-matching column B data(!); if you: b) delete "Entire row", you would delete some non-blank data!
Thanks! Advanced properties were a thing i thought was removed from the new version
nice, thank you bro
I don't use Excel much, still saving this for using in the future
You might have given me back several hours of my life just now 😊😊
I have done some brilliant things with "Indirect"... it is GREAT!
Awesome !!!
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 (:).
I hate defining names. Makes it impossible to know, within the formula, what I'm referencing. But I hate INDIRECT() even more.
@@stickoutofthemudFound out recently (while working on a sheet I didn't create) that you can define dynamic ranges in the name manager that won't show up on the sheet even if you type their name into the name bar (left of the formula bar).
That was a nice little run around that wasn't a complete piss off at all 😂
@@ragnarok7976
Was that a case of the name being scoped to a different sheet?
*Some people spend 60 dollar son strawberries. We get imported ones from japan here and they can cost more than that. nice video, BTW I dont even use Excel now, but I just like your channel lol* 💪👍
3D references are useful for making (say) a monthly summary from four weekly sheets. Just copy one 3D reference around the summary sheet. Just be sure W1,W2,W3 and W4 are adjacent tabs though...
the conditional formatting i used for downtime, but i will defo use the other features with my other spreadsheets
Thanks
You missed to talk about the power of macros. I found out you can bind them to shapes inside your sheet. Eg you can inserted a rectangle etc, add text "do stuff" and bind it to a macro. Now I can execute it by using this self-made button.
I din't know you can create your own lists for autocomplete, that's really cool!
Making a good VBA Program on the back end is far more than a 'Macro', you can interface with the rest of MS Office, Active Directory, Windows, Databases, and much more. I cringe when I have to fix a 'Macro' that is full of relative references that break workbooks such as 'Selected', 'ActiveSheet', or are full of scrolling. When building functionality, don't take shortcuts in the code someone will have to edit it in the future.
You can also create keyboard shortcuts linked to your macros. I made a ctrl+shift+v to paste values as I liked that functionality in Google sheets better than how it was implemented in excel.
@@marxmaiale9981You can use a macro to help set up your VBA program.
Nice ones. Still for me the biggest surprise was when I realized that you can actually set up shortcuts to commands with the "alt" + number keys.
You should grab all the comments on this video and make a tips and tricks sheet from them. These comments are as good as the video, everyone telling their favorite featyres that few people kniw about
Thanks for making me understand me i was already an advanced excel user. 🎉
Good features :)
The camera and formula watch are useful
I just use Google sheets cause I don't wanna pay for Excel 😂
This is really GREAT information!! You just NEED to SLOW DOWN!! Show everything you are clicking on, and say that you are clicking on it!! Not everyone can follow you when you are clicking and talking so fast!
I've been using excel for quite some time now and I just realised I could put a chart in a single column 😂 from this video. Thanks
I have to say you explain this better than most actual courses.
ps I used to be an excel expert butthen they introduced the ribbon and ~I was cast back into the abyss.
Hi, can you please make a video also talking about libreoffice?
3:00 You can accomplish the same task by doing a data sort and that will remove all the blank lines.