10 Hidden Features in Microsoft Excel (You’ll Wish You Knew Sooner)
10 Hidden Features in Microsoft Excel (You’ll Wish You Knew Sooner)
Thanks to Mine for Sponsoring: Find out which companies have your data and reclaim it 😤 by visiting ⇨ https://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 ⇨ https://teespring.com/stores/thiojoe
⇨ / thiojoe
⇨ / thiojoe
⇨ / thiojoetv
My Gear \u0026 Equipment ⇨ https://kit.co/ThioJoe
▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬
Content
0 -> Get ready to update your resume that
you're advanced in Microsoft Excel,
4.02 -> because after this video, you will be. Last
time I did hidden features in Microsoft Word,
8.1 -> and this time I've got 10 hidden features that are
really useful in Microsoft Excel. And also I've
13.74 -> got eight more features that aren't really secret,
but more hidden in plain sight, you might say,
18.12 -> that are really useful, but not a lot of people
seem to know about them or know what they do.
22.32 -> So if you end up enjoying the video, be sure
to give it a like, and let's just jump in.
25.8 -> Starting off, we have the camera tool, which is
truly hidden. It's not in the ribbon by default,
30.12 -> and I'll show you how to add it to that in
a second. But here's an example of how it
33.54 -> works. I have this demonstration Excel file
with a few sheets for some imaginary stores,
38.04 -> and say I wanted to monitor the totals
of each store from one summary page.
43.56 -> Well, I can use the camera tool to take a snapshot
of whatever cells I want to keep track of,
48.96 -> and this way it acts more like an image. So
it's a little bit different than if I just
53.58 -> made a cell that referenced the other ones
that way. So that could come in handy. To
57.66 -> get this feature to show, just right click on
the ribbon and then hit customize the ribbon.
62.1 -> Then on the right hand side, click "new
tab," and it'll make a new custom tab,
65.76 -> but you can rename it, and there will
also be a custom group beneath it
69.6 -> that you'll want to select. Then when
selected, look on the left hand side,
72.54 -> and in the drop down, select "commands not
in the ribbon," and then look for Camera.
76.98 -> And then hit the button to add it to that custom
tab. Or if you can't find it for some reason,
81.42 -> it should also be under All Commands.
And now you have your own custom group
84.84 -> you can add other stuff to, and
it'll show up in there. All right,
87.6 -> next up we have the ability to import data
and tables from a website using Power Query.
92.34 -> Say I want to copy the information from a
table, like on this website that has daily
96.78 -> interest rates. I could just highlight and
copy it all, but what if I want to keep it
100.98 -> up to date and not copy it every single day?
What I can do is go to data, and then "From
105.72 -> Web," it'll ask me for the URL that I put in,
and then hit connect, and then wait a second.
110.4 -> Now I should see a list of all the data tables
on that site, and I can even hit Web View to
116.16 -> see which table is which, though the label might
be a bit cut off like it is here. Interestingly,
120.78 -> it seems to have even found some hidden columns
that didn't even show on the website. But anyway,
125.04 -> I can select the table and hit load,
and it will bring it into a sheet.
129.9 -> And this table in the Excel sheet is now connected
to the table on that website. And you can see this
135.24 -> if you go to Queries and Connections,
and there is an option to hit refresh,
139.98 -> or you can just hit the Refresh All button
in the data tab. And that'll keep it up to
144.72 -> date whenever you want. There's also plenty
of other ways to get data using Power Query.
148.32 -> I'm not going to get into all those, that would
take all day, but maybe you can look those up
151.86 -> yourself. All right, the next hidden feature
is 3D References. And this is if you want to
156.3 -> reference the same cell coordinate across multiple
sheets. Like maybe I want to sum all the totals
162.12 -> from each of these three sheets, and they all
happen to be in the K13 cell on those sheets.
167.82 -> To do this, I would use the sum formula. And
in this case, I want to do a 3D reference of
172.92 -> a range of sheets. And I can do a 3D
reference of that range by doing the
177.66 -> following syntax. You would do the first
sheet, which is computer, and then a colon,
182.22 -> and then the last sheet name, which is science.
And then I would do an exclamation mark,
186.36 -> and then the coordinate of the cell I
want to reference from all those sheets.
189.84 -> And now it will use those three K13
[cells] from those sheets. Moving on,
195.18 -> we have "Go To Special", which is useful
for a lot of stuff, but here's just one
198.9 -> example. Say I have a sheet with a bunch
of empty rows scattered in between those,
204.12 -> and I just want to get rid of all those
empty rows at the same time. What you
208.08 -> can do is go to the Home tab, and then under
Find and Select, you select "Go To Special."
213 -> Or you can just hit F5 and then go to Special.
Then one of the options is to select blank
218.58 -> cells. And once you do that, they'll all be
selected. And then you can simply right-click
222.72 -> and hit Delete Row, and that'll get rid of
all of them. And you can see that using this,
226.62 -> there were other ways you could
select cells in an advanced manner.
229.98 -> Now speaking of cool stuff, let me
tell you about today's sponsor, Mine,
233.58 -> which is a really cool service. Mine lets
you find out which companies have your data,
238.32 -> and lets you control where you do or don't want
to keep it. You start out by going to SayMine.com,
243.96 -> and just sign in with your email account you want
to analyze, and give it a minute to do its thing.
248.82 -> Mine will only analyze the subject
line and sender of the email,
252.06 -> the first line preview snippet, plus
some metadata, to figure out which
256.44 -> companies you've interacted with. But as their
privacy policy states, they don't collect the
260.82 -> content of your email for analysis. Then
when it's done, it shows the results.
264.42 -> For me, there are over 260
companies that have data on me,
268.5 -> which is probably not great. On the My Footprint
page, I can see a selection of these companies,
273.18 -> but I got up the courage to look at
the tab showing all the companies,
276.78 -> and it is eye-opening. By clicking on a
company, it shows me that for example EA
281.1 -> stores info like my financial, and identity
data, and even online behavior data and more.
286.26 -> And I can see that it has a pretty
elevated exposure risk. Here's another
290.04 -> company that I barely remember signing
up for, but never ended up using and I
294.3 -> don't really want them to have my
data. So I can just click Reclaim,
298.08 -> and it will compose an email that will be
sent directly from my inbox to the company.
303.12 -> And this point is important because companies
generally only honor requests coming directly
307.92 -> from the person. Then, after you allow it to send
the email, you can track and review your requests,
312.9 -> and cancel it within an hour if you
change your mind. And since privacy
316.44 -> is important for businesses too, Mine has
created a solution for companies to help
320.7 -> manage their own privacy operations, including
automation of handling requests from customers.
326.04 -> So if you want to start reclaiming your own data,
be sure to visit SayMine.com and sign up. And I'll
331.74 -> also put that link in the description. And with
all that being said, let's continue. Next up,
336.9 -> we have the ability to enable circular references.
Normally in Excel, if you use a formula that
342.6 -> references itself, or a value that feeds into
itself, it'll give you a warning and block it.
347.46 -> But you can actually enable it if you
go into the settings, and then formulas,
351.24 -> and then check "Enable Iterative Calculations."
And you can also change the max number here if
356.28 -> you want. Now any circular references will work,
and you can iterate another time by pressing F9,
361.92 -> or it also seems like if you edit
any cell, it'll again iterate.
366.24 -> And while I can't exactly think of a good example
for why to use this, it still might come in handy
370.62 -> for certain people. However, I would keep
it disabled until you actually need it,
374.28 -> because most of the time you probably
don't want circular references. Okay,
377.76 -> this next one could be a lifesaver, and it
allows you to have Excel autosave more often.
382.8 -> To do this, go to the Options menu, and then under
Save, look for "Save Auto-recovery information
387.84 -> every," and then the default is 10 minutes,
but you might want to lower that. Next up,
393.06 -> we have Custom Lists. Now you probably already
know that Excel will auto-complete common lists
398.58 -> like months of a year. But what if you have
your own list of things that you use frequently,
403.02 -> and would like that to be able to
auto-complete whenever you need it?
405.6 -> What you can do is go to Options, and
then Advanced, and then under General,
409.14 -> look for "Edit Custom Lists." And this will let
you add a list of whatever you want. It does
414.54 -> have to be text, it doesn't seem to work with
numbers, but now it will auto-complete if it
419.7 -> detects that you're trying to expand
that list of things you added.
423.6 -> Moving on, we have the developer tab, which
is not enabled by default, and you can enable
427.98 -> it by right clicking on the ribbon, hitting
customize the ribbon, and then simply check the
432 -> box next to developer. This adds several tools
that are pretty advanced, and usually have to
437.1 -> do with macros or forms, but it still might be
nice to have access to and know they're there.
441.54 -> One example is being able to add check
boxes, though there are two types of these,
445.68 -> and you'll probably need to look up a
separate tutorial on how to use these
449.34 -> because they aren't very intuitive, but
you might want to know about them. The
452.76 -> next hidden feature is Advanced Properties,
and you can get to this by going to File,
456.78 -> and then Info, Properties, and
then click Advanced Properties.
460.5 -> And here it lets you easily set a bunch of
different metadata for your Excel file that
465.42 -> you usually wouldn't be able to, like author,
title, company, whatever else. And there's a
470.94 -> couple other tabs if you want to mess around
this kind of stuff too. And next we have the
475.02 -> indirect formula, and I wanted to mention this
formula specifically because it's kind of unique.
479.16 -> It's more like a meta formula, you might say.
You probably know that if you reference a cell,
484.02 -> like doing equals E5, like here, it will
just copy the value of that cell. But if
490.26 -> you were to do equals indirect E5,
watch what happens. It actually used
494.88 -> the value within E5 as the reference
instead of the value in E5 itself.
500.46 -> This lets you do some interesting stuff,
especially when using it with named ranges,
504.24 -> which I'll explain in a second, and also
with referencing other sheets. Here's an
508.38 -> example where maybe I want to easily get
the total from any of my store sheets,
513.36 -> where each total is in cell
[K13] on the corresponding sheet.
516.84 -> So I have a formula using indirect
that references the [K13] cell of
521.28 -> whatever sheet's name gets put in the
cell above, D4. So if I put in science,
526.44 -> it gets the [K13] cell of the science sheet.
But going off of that one, what if I wanted
530.82 -> to make this a drop-down list? So I kind of
have an extra hidden bonus feature for that.
534.96 -> First, I want to have a list of
the possible drop-down options,
538.32 -> and you could put that anywhere.
Then I'll go to the Data tab,
541.08 -> and then Data Validation. In the Allow box, I'll
select List, but you can also select something
546.18 -> else to restrict what kind of data can be
entered into that box for other purposes.
551.7 -> Then make sure that "In-cell dropdown" is checked,
554.58 -> and for source, select that list of cells you
made for the options, and then click OK. And
559.8 -> now you can simply select the options as a
drop-down instead of having to type it out,
564.24 -> and the indirect command makes it auto-update.
All right, so those are all the features that are
568.32 -> more hidden, but now we can get to the part
of features that are hidden in plain sight.
572.76 -> So they might be right in front of you, but
you never used them. And we can start off
575.7 -> with named ranges. This might be more common,
but you really need to know about. To do this,
579.96 -> just select any range of cells, and
then look at the box at the top [left],
584.46 -> and you could actually type in a
name here to reference that range.
588.06 -> So for example, maybe I want to select
this whole column of totals on this sheet,
593.22 -> and name it "ComputerTotals". This way,
I can simply do equals sum ComputerTotals
598.98 -> instead of having to select it.
When you reference a named range,
602.16 -> it doesn't appear to be case sensitive, and also
if you forget what all the named ranges are,
606.9 -> you can click the little drop-down in
that box and it should show all of them.
611.04 -> Next up, we have the Quick Analysis box, which
shows up every time you select a range of cells,
616.32 -> but you probably maybe never
looked at it. When you click it,
618.96 -> it gives you quick access to a bunch of common
actions, such as conditional formatting,
623.52 -> creating a chart for that range, and
creating a new row with various totals.
628.74 -> So this might come in handy. Next up, we have the
forecast sheet. You can get to this by going to
633.96 -> data, forecast, and then Forecast Sheet. This
does some basic forecasting based on the data,
639.54 -> and shows you even upper and lower ranges as
well. It's like a prediction. You can also
643.98 -> expand the forecast length, and you can even
expand the options for lots of more settings.
648.72 -> And if you want, you can hit create to create a
chart and table with that forecast data. Moving
654.3 -> on, we have the Watch Window. To get to this,
go to formulas, and then formula auditing. Here,
659.22 -> you can add multiple cells that you may
want to keep track of, such as maybe you
663.6 -> want to monitor the grand total value in the
summary sheet while updating another sheet.
668.22 -> And that'll show the live up-to-date value when
you're somewhere else, for example. Next though,
673.08 -> I want to talk about more of these
formula auditing features as a whole.
677.16 -> And that's also located under formulas,
formula auditing. One really cool one is
681.06 -> Trace Dependents or Trace Prededents. If you
select a cell that has a calculated value,
685.56 -> it will show you where that value came from
in terms of like the different calculations,
690.3 -> and it'll show you with arrows on those various
cells, or what cells it depends on going forward.
697.44 -> Another really powerful option
is Evaluate Formula. To use this,
701.16 -> say we have this example with a table of
expenses, and you see one of the totals is
706.38 -> wrong. No way you spent $60 on strawberries.
But that cell uses a formula based on other
712.44 -> stuff. And just imagine that this is a way more
complicated spreadsheet with a ton of steps,
717.06 -> and you want to figure out what is causing
this high total, where things are going wrong.
721.62 -> So you can select that cell and hit evaluate
formula. And I'm just going to give you the
726.3 -> short version of what this does, but you
should probably just go watch a dedicated
729.54 -> video for how to actually use it. But basically
it lets you step into variables in the formula,
735.36 -> which basically means digging down
to trace what happens for each step.
739.56 -> You can also Step Out and go up a level and
the Evaluate button lets you calculate values
744.66 -> during each step. Eventually, I could see that
the price is set at $8 for each strawberry,
750.06 -> which surely must be a mistake. Again, it might
not be very useful in this example, it's easy to
755.1 -> trace yourself, but in a much more complicated
situation, it could really come in handy.
758.94 -> All right, moving on, we have one of
my favorites, which is Conditional
761.64 -> Formatting. And you can get to this by going
to Home and then Styles. And this lets you
766.08 -> change the visuals of cells based on their
values. You can just hover over and see some
771.54 -> of the presets for what they do. Maybe you
want high values to be in red or whatever.
775.86 -> Most of these are fully automatic
presets, but for others, it might ask
779.34 -> you for additional info. Like if you want
to highlight values that are greater than
783.66 -> some value you select. But you can set really
complicated rules if you want. Like you can go
788.4 -> to "format cells based on value,"" and then
you can customize the scale for what ranges.
793.44 -> You can also choose what colors each thing
shows up as. One of my favorites is "format
798.54 -> only cells that contain." And then you can
do stuff like Specific Text, or even have a
803.58 -> few options in there. Or you can select specific
cell values or ranges. A useful example might be
809.16 -> changing the cell color of something, depending on
whether it says True or False text, for example.
814.08 -> Or you can even set a whole custom formula
to determine which cells to format. Really
819.54 -> powerful. And then of course, for all of
these, you can change what the cell will
822.9 -> look like based on the rules, and you can
actually set multiple rules too. All right,
826.86 -> moving on, we have sparklines, which is
pretty cool. So say we have a bunch of data,
831.06 -> like inches of rain per month for a whole
bunch of cities, that we want to visualize,
835.44 -> but we don't want to make one huge messy graph,
but rather we want like a mini graph for each one.
840.84 -> What I can do is go to the Insert tab,
then Sparklines, and then Column. Then
845.58 -> I can select the data range, and then select
the cells to put the graphs in. And then you
850.14 -> can see that each row now has its own mini bar
graph. Now, obviously it's not as much data as
855.18 -> you would see with a full graph, because
it doesn't have the scales or anything,
858.06 -> it's all relative. But still might come in
handy. And if you don't want to do bar graphs,
861.6 -> there's also option for a line graph. And
there's also one for a win loss graph,
865.98 -> like for showing if values are simply
positive or negative. The next feature
870.24 -> is the ability to apply the same
change to multiple sheets at once.
874.74 -> You can select the multiple sheets
a few different ways. For example,
877.5 -> you could right click a sheet and then hit
Select All Sheets, or you can hold Control
882 -> to select multiple sheets individually, or
you can hold Shift and then select a range
886.92 -> of sheets. And now if you do something
like resizing the columns of one sheet,
891 -> it'll do it to all of them, which is really
useful if you want it to be consistent,
895.02 -> so stuff shows up in the same place when
switching through tabs. Just remember
899.22 -> to unselect the tabs after you're done,
because you don't want to accidentally
902.58 -> change a bunch of data on more sheets than
just the one you're working on. So hopefully
907.14 -> you found these helpful. Let me know down in the
comments if you already knew about some of these,
910.74 -> and maybe which ones you learned about that
you're definitely going to start using.
913.86 -> Thanks again to Mine for sponsoring
this video. Again, be sure to visit
917.4 -> SayMine.com so you can start reclaiming your
own data too. And that link is also in the
922.32 -> description. If you enjoyed the video,
again be sure to give it a big thumbs up
924.9 -> for my YouTube algorithm overlords. Also if
you liked the video, consider subscribing.
928.62 -> I try to make videos about twice a week,
930.18 -> usually Wednesday and Saturday. And if you want
to keep watching, the next video I'd recommend
933.66 -> is where I talked about hidden Microsoft
Word features. Same idea as this video,
937.62 -> but for Word. You can just click on that right
there. You'll probably find it pretty cool.
941.46 -> So thanks so much for watching,
and I'll see you in the next one.
Source: https://www.youtube.com/watch?v=QvXvfc6oEqI