Get ready to update your resume that you're advanced in Microsoft Excel,
because after this video, you will be. Last time I did hidden features in Microsoft Word,
and this time I've got 10 hidden features that are really useful in Microsoft Excel. And also I've
got eight more features that aren't really secret, but more hidden in plain sight, you might say,
that are really useful, but not a lot of people seem to know about them or know what they do.
So if you end up enjoying the video, be sure to give it a like, and let's just jump in.
Starting off, we have the camera tool, which is truly hidden. It's not in the ribbon by default,
and I'll show you how to add it to that in a second. But here's an example of how it
works. I have this demonstration Excel file with a few sheets for some imaginary stores,
and say I wanted to monitor the totals of each store from one summary page.
Well, I can use the camera tool to take a snapshot of whatever cells I want to keep track of,
and this way it acts more like an image. So it's a little bit different than if I just
made a cell that referenced the other ones that way. So that could come in handy. To
get this feature to show, just right click on the ribbon and then hit customize the ribbon.
Then on the right hand side, click "new tab," and it'll make a new custom tab,
but you can rename it, and there will also be a custom group beneath it
that you'll want to select. Then when selected, look on the left hand side,
and in the drop down, select "commands not in the ribbon," and then look for Camera.
And then hit the button to add it to that custom tab. Or if you can't find it for some reason,
it should also be under All Commands. And now you have your own custom group
you can add other stuff to, and it'll show up in there. All right,
next up we have the ability to import data and tables from a website using Power Query.
Say I want to copy the information from a table, like on this website that has daily
interest rates. I could just highlight and copy it all, but what if I want to keep it
up to date and not copy it every single day? What I can do is go to data, and then "From
Web," it'll ask me for the URL that I put in, and then hit connect, and then wait a second.
Now I should see a list of all the data tables on that site, and I can even hit Web View to
see which table is which, though the label might be a bit cut off like it is here. Interestingly,
it seems to have even found some hidden columns that didn't even show on the website. But anyway,
I can select the table and hit load, and it will bring it into a sheet.
And this table in the Excel sheet is now connected to the table on that website. And you can see this
if you go to Queries and Connections, and there is an option to hit refresh,
or you can just hit the Refresh All button in the data tab. And that'll keep it up to
date whenever you want. There's also plenty of other ways to get data using Power Query.
I'm not going to get into all those, that would take all day, but maybe you can look those up
yourself. All right, the next hidden feature is 3D References. And this is if you want to
reference the same cell coordinate across multiple sheets. Like maybe I want to sum all the totals
from each of these three sheets, and they all happen to be in the K13 cell on those sheets.
To do this, I would use the sum formula. And in this case, I want to do a 3D reference of
a range of sheets. And I can do a 3D reference of that range by doing the
following syntax. You would do the first sheet, which is computer, and then a colon,
and then the last sheet name, which is science. And then I would do an exclamation mark,
and then the coordinate of the cell I want to reference from all those sheets.
And now it will use those three K13 [cells] from those sheets. Moving on,
we have "Go To Special", which is useful for a lot of stuff, but here's just one
example. Say I have a sheet with a bunch of empty rows scattered in between those,
and I just want to get rid of all those empty rows at the same time. What you
can do is go to the Home tab, and then under Find and Select, you select "Go To Special."
Or you can just hit F5 and then go to Special. Then one of the options is to select blank
cells. And once you do that, they'll all be selected. And then you can simply right-click
and hit Delete Row, and that'll get rid of all of them. And you can see that using this,
there were other ways you could select cells in an advanced manner.
Now speaking of cool stuff, let me tell you about today's sponsor, Mine,
which is a really cool service. Mine lets you find out which companies have your data,
and lets you control where you do or don't want to keep it. You start out by going to SayMine.com,
and just sign in with your email account you want to analyze, and give it a minute to do its thing.
Mine will only analyze the subject line and sender of the email,
the first line preview snippet, plus some metadata, to figure out which
companies you've interacted with. But as their privacy policy states, they don't collect the
content of your email for analysis. Then when it's done, it shows the results.
For me, there are over 260 companies that have data on me,
which is probably not great. On the My Footprint page, I can see a selection of these companies,
but I got up the courage to look at the tab showing all the companies,
and it is eye-opening. By clicking on a company, it shows me that for example EA
stores info like my financial, and identity data, and even online behavior data and more.
And I can see that it has a pretty elevated exposure risk. Here's another
company that I barely remember signing up for, but never ended up using and I
don't really want them to have my data. So I can just click Reclaim,
and it will compose an email that will be sent directly from my inbox to the company.
And this point is important because companies generally only honor requests coming directly
from the person. Then, after you allow it to send the email, you can track and review your requests,
and cancel it within an hour if you change your mind. And since privacy
is important for businesses too, Mine has created a solution for companies to help
manage their own privacy operations, including automation of handling requests from customers.
So if you want to start reclaiming your own data, be sure to visit SayMine.com and sign up. And I'll
also put that link in the description. And with all that being said, let's continue. Next up,
we have the ability to enable circular references. Normally in Excel, if you use a formula that
references itself, or a value that feeds into itself, it'll give you a warning and block it.
But you can actually enable it if you go into the settings, and then formulas,
and then check "Enable Iterative Calculations." And you can also change the max number here if
you want. Now any circular references will work, and you can iterate another time by pressing F9,
or it also seems like if you edit any cell, it'll again iterate.
And while I can't exactly think of a good example for why to use this, it still might come in handy
for certain people. However, I would keep it disabled until you actually need it,
because most of the time you probably don't want circular references. Okay,
this next one could be a lifesaver, and it allows you to have Excel autosave more often.
To do this, go to the Options menu, and then under Save, look for "Save Auto-recovery information
every," and then the default is 10 minutes, but you might want to lower that. Next up,
we have Custom Lists. Now you probably already know that Excel will auto-complete common lists
like months of a year. But what if you have your own list of things that you use frequently,
and would like that to be able to auto-complete whenever you need it?
What you can do is go to Options, and then Advanced, and then under General,
look for "Edit Custom Lists." And this will let you add a list of whatever you want. It does
have to be text, it doesn't seem to work with numbers, but now it will auto-complete if it
detects that you're trying to expand that list of things you added.
Moving on, we have the developer tab, which is not enabled by default, and you can enable
it by right clicking on the ribbon, hitting customize the ribbon, and then simply check the
box next to developer. This adds several tools that are pretty advanced, and usually have to
do with macros or forms, but it still might be nice to have access to and know they're there.
One example is being able to add check boxes, though there are two types of these,
and you'll probably need to look up a separate tutorial on how to use these
because they aren't very intuitive, but you might want to know about them. The
next hidden feature is Advanced Properties, and you can get to this by going to File,
and then Info, Properties, and then click Advanced Properties.
And here it lets you easily set a bunch of different metadata for your Excel file that
you usually wouldn't be able to, like author, title, company, whatever else. And there's a
couple other tabs if you want to mess around this kind of stuff too. And next we have the
indirect formula, and I wanted to mention this formula specifically because it's kind of unique.
It's more like a meta formula, you might say. You probably know that if you reference a cell,
like doing equals E5, like here, it will just copy the value of that cell. But if
you were to do equals indirect E5, watch what happens. It actually used
the value within E5 as the reference instead of the value in E5 itself.
This lets you do some interesting stuff, especially when using it with named ranges,
which I'll explain in a second, and also with referencing other sheets. Here's an
example where maybe I want to easily get the total from any of my store sheets,
where each total is in cell [K13] on the corresponding sheet.
So I have a formula using indirect that references the [K13] cell of
whatever sheet's name gets put in the cell above, D4. So if I put in science,
it gets the [K13] cell of the science sheet. But going off of that one, what if I wanted
to make this a drop-down list? So I kind of have an extra hidden bonus feature for that.
First, I want to have a list of the possible drop-down options,
and you could put that anywhere. Then I'll go to the Data tab,
and then Data Validation. In the Allow box, I'll select List, but you can also select something
else to restrict what kind of data can be entered into that box for other purposes.
Then make sure that "In-cell dropdown" is checked,
and for source, select that list of cells you made for the options, and then click OK. And
now you can simply select the options as a drop-down instead of having to type it out,
and the indirect command makes it auto-update. All right, so those are all the features that are
more hidden, but now we can get to the part of features that are hidden in plain sight.
So they might be right in front of you, but you never used them. And we can start off
with named ranges. This might be more common, but you really need to know about. To do this,
just select any range of cells, and then look at the box at the top [left],
and you could actually type in a name here to reference that range.
So for example, maybe I want to select this whole column of totals on this sheet,
and name it "ComputerTotals". This way, I can simply do equals sum ComputerTotals
instead of having to select it. When you reference a named range,
it doesn't appear to be case sensitive, and also if you forget what all the named ranges are,
you can click the little drop-down in that box and it should show all of them.
Next up, we have the Quick Analysis box, which shows up every time you select a range of cells,
but you probably maybe never looked at it. When you click it,
it gives you quick access to a bunch of common actions, such as conditional formatting,
creating a chart for that range, and creating a new row with various totals.
So this might come in handy. Next up, we have the forecast sheet. You can get to this by going to
data, forecast, and then Forecast Sheet. This does some basic forecasting based on the data,
and shows you even upper and lower ranges as well. It's like a prediction. You can also
expand the forecast length, and you can even expand the options for lots of more settings.
And if you want, you can hit create to create a chart and table with that forecast data. Moving
on, we have the Watch Window. To get to this, go to formulas, and then formula auditing. Here,
you can add multiple cells that you may want to keep track of, such as maybe you
want to monitor the grand total value in the summary sheet while updating another sheet.
And that'll show the live up-to-date value when you're somewhere else, for example. Next though,
I want to talk about more of these formula auditing features as a whole.
And that's also located under formulas, formula auditing. One really cool one is
Trace Dependents or Trace Prededents. If you select a cell that has a calculated value,
it will show you where that value came from in terms of like the different calculations,
and it'll show you with arrows on those various cells, or what cells it depends on going forward.
Another really powerful option is Evaluate Formula. To use this,
say we have this example with a table of expenses, and you see one of the totals is
wrong. No way you spent $60 on strawberries. But that cell uses a formula based on other
stuff. And just imagine that this is a way more complicated spreadsheet with a ton of steps,
and you want to figure out what is causing this high total, where things are going wrong.
So you can select that cell and hit evaluate formula. And I'm just going to give you the
short version of what this does, but you should probably just go watch a dedicated
video for how to actually use it. But basically it lets you step into variables in the formula,
which basically means digging down to trace what happens for each step.
You can also Step Out and go up a level and the Evaluate button lets you calculate values
during each step. Eventually, I could see that the price is set at $8 for each strawberry,
which surely must be a mistake. Again, it might not be very useful in this example, it's easy to
trace yourself, but in a much more complicated situation, it could really come in handy.
All right, moving on, we have one of my favorites, which is Conditional
Formatting. And you can get to this by going to Home and then Styles. And this lets you
change the visuals of cells based on their values. You can just hover over and see some
of the presets for what they do. Maybe you want high values to be in red or whatever.
Most of these are fully automatic presets, but for others, it might ask
you for additional info. Like if you want to highlight values that are greater than
some value you select. But you can set really complicated rules if you want. Like you can go
to "format cells based on value,"" and then you can customize the scale for what ranges.
You can also choose what colors each thing shows up as. One of my favorites is "format
only cells that contain." And then you can do stuff like Specific Text, or even have a
few options in there. Or you can select specific cell values or ranges. A useful example might be
changing the cell color of something, depending on whether it says True or False text, for example.
Or you can even set a whole custom formula to determine which cells to format. Really
powerful. And then of course, for all of these, you can change what the cell will
look like based on the rules, and you can actually set multiple rules too. All right,
moving on, we have sparklines, which is pretty cool. So say we have a bunch of data,
like inches of rain per month for a whole bunch of cities, that we want to visualize,
but we don't want to make one huge messy graph, but rather we want like a mini graph for each one.
What I can do is go to the Insert tab, then Sparklines, and then Column. Then
I can select the data range, and then select the cells to put the graphs in. And then you
can see that each row now has its own mini bar graph. Now, obviously it's not as much data as
you would see with a full graph, because it doesn't have the scales or anything,
it's all relative. But still might come in handy. And if you don't want to do bar graphs,
there's also option for a line graph. And there's also one for a win loss graph,
like for showing if values are simply positive or negative. The next feature
is the ability to apply the same change to multiple sheets at once.
You can select the multiple sheets a few different ways. For example,
you could right click a sheet and then hit Select All Sheets, or you can hold Control
to select multiple sheets individually, or you can hold Shift and then select a range
of sheets. And now if you do something like resizing the columns of one sheet,
it'll do it to all of them, which is really useful if you want it to be consistent,
so stuff shows up in the same place when switching through tabs. Just remember
to unselect the tabs after you're done, because you don't want to accidentally
change a bunch of data on more sheets than just the one you're working on. So hopefully
you found these helpful. Let me know down in the comments if you already knew about some of these,
and maybe which ones you learned about that you're definitely going to start using.
Thanks again to Mine for sponsoring this video. Again, be sure to visit
SayMine.com so you can start reclaiming your own data too. And that link is also in the
description. If you enjoyed the video, again be sure to give it a big thumbs up
for my YouTube algorithm overlords. Also if you liked the video, consider subscribing.
I try to make videos about twice a week,
usually Wednesday and Saturday. And if you want to keep watching, the next video I'd recommend
is where I talked about hidden Microsoft Word features. Same idea as this video,
but for Word. You can just click on that right there. You'll probably find it pretty cool.
So thanks so much for watching, and I'll see you in the next one.