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.
