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.
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.
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.
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.
@@YouGotPropofol 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.
@@YouGotPropofol 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.
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 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.
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.
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.
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.
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 (:).
@@stickinthemud23Found 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 😂
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).
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.
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!
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
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
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.
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
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 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.
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)
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!
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);"")
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.
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" .
*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* 💪👍
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
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
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.
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.
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++ ???
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.
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...
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'.
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.
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).
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() )
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!
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
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
I remove empty rows by removing duplicates (which can be put into the quickbar) and then just remove 1 empty line. I know it's not the most precise but I like it because removing duplicates is quite a staple function for me.
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.
conditional formatting... missed one key item. if doing a formula, the cells go off of the top right, so if selecting cells in column C, the formula would need to reference c1, and the formula would be changing when it goes to c2, the details would be c2. if you entered d1, it would always be referencing the column that far over unless you use the $ to lock the column or row.
Some of my co-workers like data bars and some don't. I think they are great for a glance. For those who don't like it, I select all of the cells that contain them, create a new rule (specific cell) = 0 and leave the formatting and all blank. You can go back to the Manage Rules and click "Stop If True" and assign it to "(specific cell)l=0". When there is a 0 in that cell, the data bars will go away. You can delete it, or change it and the data bars will reappear. I normally write some VBA to manipulate it and put on 2 square shapes. They label them "Graphics On" and "Graphics Off". Custom format the cell with ;;; and no one will ever see it.
Bro I'm already the most advanced possible in Excel, but let's see what you got Edit: Watch window & camera are pretty cool. Most of the others I know or are not relevant, but good video!
This is great information, as usual. Thank you. I was hoping that you'd cover something that has frustrated me in Excel: the absence of the "Font" group, as exists in Word. In Word, you can go to the Font group and set the text in a cell or a line or a section (or in the whole document, if you want) to always be in caps. I have not found a way to do this in Excel. This is annoying because there are a few douments that multiple people can edit, but I have to maintain. The text is meant to be in all caps, but some people insist on writing in lower case. So I have to spot each instance of that and change it. If only I could set the entire document to all caps.
If the spreadsheet is one that people are editing with the web version of Office (through their browser), that might not work, but if you have access to the actual file, you could make a macro that you run yourself to do the same thing. Sorry for the reply spam. I wanted to keep the code bits separated. It's weird that Excel doesn't just have all caps and a font option, but that's Microsoft for you.
@@lordelliott42 - Other people are editing the file in SharePoint. Thanks for the suggestion about the macro, but I don't think I have the chops to do what you suggest. Anyway, I'm glad that I am not the only one who finds Excel's lack of a Font group strange.
I haven't checked but I think there is an all caps checkbox in the font dialog box. CTRL +1 to open cell format and click the Font tab to see if it's there.
@@overyourheadtampa - Thanks for the suggestion. But in that group there are the options only for Strikethrough, Superscript, and Subscript. Lacking are the other options that appear in Word, the most important one being All Caps.
@@FerdinandCesarano you can add another column with =upper(a1) as a formula where a1 can be replaced with the cell that people enter the data into. Any cells that need to reference the upper case data can instead reference the formula cell. You can also limit the character input using data validation. Selecting the input cell, e.g. A1 Click the Data ribbon (Alt+A) and select Data Validation Select Custom from the drop-down Type this in the formula source =Exact(A1,Upper(A1)) On the Error tab, enter the message you want to appear when the entry is not all caps. Click ok. Test it out. This will not allow the user to enter lowercase characters into the cell otherwise it would produce an error, although copy and paste may be able to bypass it.
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.
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.
@@YouGotPropofol
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.
@@YouGotPropofol 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.
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.
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.
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.
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.
@@stickinthemud23Found 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?
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.
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!
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
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
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.
I think I’m a heavy Excel user and still I didn’t know many of the features presented in this video. Great stuff!
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
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).
One underrated feature is recognizing and parsing date values. I can't get enough of this feature ngl.
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.
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.
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)
the first one of these “hidden/advanced” excel features that’s impressed me!
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!
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);"")
I was waiting for this one. Thanks for making it! Next is PowerPoint, I'd love to see it.
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.
Powerpoint next? Also, a video about lesser-known miscrsoft office programs wold be awesome!
The INDIRECT modifier will come in handy! Thanks for the intel!
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
Very useful video! Hopefully you will continue this with PowerPoint next.
iterative calculation is great!! thanks Joe
Excel is getting more powerful features every day, thanks for the video and sharing!
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" .
I learnt cool features about Excel from this video,thanks
3D references.... nice! Learned something new today! 🙂 Well done!
*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* 💪👍
"Indirect" function and circular reference features look super useful. These are new to me.
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 don't use Excel much, still saving this for using in the future
I love teaching my students Excel a lot
Very informative video. Thanks
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
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.
Most of these where new to me and I will use several of them, great video.
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++ ???
It would be nice to have one video like this for Google Sheets and Docs :D
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.
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...
I didn't know about inserting a tab or adding the range name by typing it into the location box. Thank you for sharing!
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'.
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.
Perfect time for this vid, since i was just looking for this stuff
Thank you. Very useful tips
I knew about a few of the options but it is always a good idea to have a refresher :)
This was really useful. Nice video.
good tips.VERY GOOD.
Thank you. Excellent information!
Everytime really useful tips and hints. I like this channel👍
Nice job Thio! This was very useful
Next do PowerPoint
This video was indeed very helpful!
Awesome video great job 👍
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).
Excellent, there's always something really useful in the videos - The INDIRECT one's going to be useful.
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() )
You might have given me back several hours of my life just now 😊😊
That is cool tipps. I like the camera and watchdog feature.
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!
Good as always congrats
I have done some brilliant things with "Indirect"... it is GREAT!
Thank you!🙏
Thanks
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
Sparklines was new. Thanks!
Can we import Amazon data like this? 1:57
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.
Very good! Thanks for that marvellous information.
The camera and formula watch are useful
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
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
This is awesome, thank you!!!
Very useful. Thank you!
I remove empty rows by removing duplicates (which can be put into the quickbar) and then just remove 1 empty line. I know it's not the most precise but I like it because removing duplicates is quite a staple function for me.
That is amazingly awesome! I wish I would have watched this like... 10 years ago, but hey, it's better late than never!
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.
conditional formatting... missed one key item.
if doing a formula, the cells go off of the top right, so if selecting cells in column C, the formula would need to reference c1, and the formula would be changing when it goes to c2, the details would be c2. if you entered d1, it would always be referencing the column that far over unless you use the $ to lock the column or row.
Some of my co-workers like data bars and some don't. I think they are great for a glance.
For those who don't like it, I select all of the cells that contain them, create a new rule (specific cell) = 0 and leave the formatting and all blank. You can go back to the Manage Rules and click "Stop If True" and assign it to "(specific cell)l=0". When there is a 0 in that cell, the data bars will go away. You can delete it, or change it and the data bars will reappear. I normally write some VBA to manipulate it and put on 2 square shapes. They label them "Graphics On" and "Graphics Off". Custom format the cell with ;;; and no one will ever see it.
Definitely amazing ❤
Thw word one was more interesting, this video contains very common knowledge for any intermediate excel user
Thanks for making me understand me i was already an advanced excel user. 🎉
Outstanding!
Cool and helpful video! :D
Bro I'm already the most advanced possible in Excel, but let's see what you got
Edit: Watch window & camera are pretty cool. Most of the others I know or are not relevant, but good video!
Let me know 🧐
Now I accidentally learnt your upload schedule!
Fantasttic stuff. Thanks!
This is great information, as usual. Thank you.
I was hoping that you'd cover something that has frustrated me in Excel: the absence of the "Font" group, as exists in Word.
In Word, you can go to the Font group and set the text in a cell or a line or a section (or in the whole document, if you want) to always be in caps. I have not found a way to do this in Excel.
This is annoying because there are a few douments that multiple people can edit, but I have to maintain. The text is meant to be in all caps, but some people insist on writing in lower case. So I have to spot each instance of that and change it. If only I could set the entire document to all caps.
If the spreadsheet is one that people are editing with the web version of Office (through their browser), that might not work, but if you have access to the actual file, you could make a macro that you run yourself to do the same thing. Sorry for the reply spam. I wanted to keep the code bits separated. It's weird that Excel doesn't just have all caps and a font option, but that's Microsoft for you.
@@lordelliott42 - Other people are editing the file in SharePoint.
Thanks for the suggestion about the macro, but I don't think I have the chops to do what you suggest.
Anyway, I'm glad that I am not the only one who finds Excel's lack of a Font group strange.
I haven't checked but I think there is an all caps checkbox in the font dialog box. CTRL +1 to open cell format and click the Font tab to see if it's there.
@@overyourheadtampa - Thanks for the suggestion. But in that group there are the options only for Strikethrough, Superscript, and Subscript. Lacking are the other options that appear in Word, the most important one being All Caps.
@@FerdinandCesarano you can add another column with =upper(a1) as a formula where a1 can be replaced with the cell that people enter the data into. Any cells that need to reference the upper case data can instead reference the formula cell.
You can also limit the character input using data validation.
Selecting the input cell, e.g. A1
Click the Data ribbon (Alt+A) and select Data Validation
Select Custom from the drop-down
Type this in the formula source =Exact(A1,Upper(A1))
On the Error tab, enter the message you want to appear when the entry is not all caps. Click ok.
Test it out.
This will not allow the user to enter lowercase characters into the cell otherwise it would produce an error, although copy and paste may be able to bypass it.
Don’t forget the PowerShell module ImportExcel!
already knew about most of these and how to achieve them in google sheets, which is my CSV editor of choice
the conditional formatting i used for downtime, but i will defo use the other features with my other spreadsheets