50 Ultimate Excel Tips & Tricks.
Updated for 2020! This video is the ultimate compilation of Excel tips and
tricks to enhance your productivity using Excel. I've combined
long-established tips and tricks featured in previous videos with
prominent new ones Microsoft added in 2019 and 2020. You'll find solutions to
the most common challenges you face in Excel. For the complete list of contents
please refer to the description of this video. Let's get started. #1 Move
Data. Here's the quickest method to move data around in Excel. Just highlight the
cells and grab the border anywhere around the outside and drag it and drop
it where you want it to go. You can do an entire column or you can do an entire
row. No need to copy/paste and then delete the original. #2 XLOOKUP().
XLOOKUP() is a powerful new function added to excel in late 2019 and designed
to replace VLOOKUP and HLOOKUP. Let's say we want to look up the name "John" in this
table and return a value from the February column. You can use XLOOKUP
and the first parameter is the lookup value which is going to be John. The
array that we're going to look up John's name in is this area right here and we
want to return values from the February column. And that's all you need to do. It
returned 17,232 which is the intersection of John and February. Now
one of the benefits of XLOOKUP is it does horizontal lookups as well so let's
say we want to look up February in this list but we're going to return the value
from John's row and it found the same number 17,232 in
the February column next to John. So it does both vertical and horizontal
lookups. Be sure to check out my XLOOKUP dedicated video if you want more details and some of the other powerful features in XLOOKUP. #3 Filter List. Want an
easy way to manage a large table of data. Click anywhere inside the table, go to
the Data tab and click on this Filter icon and it creates a filter at the top
of each column in your table. Now you can select one and choose from the list the
values that you want to see. It filters that data out. You can use multiple
columns and filter out just the data you want to see. It's a great way to manage
large lists of data. #4 Ideas. If you're looking for suggestions on how
best to display your Excel data use Ideas for inspiration. Just click
anywhere inside of a table, go to the Home tab, and choose Ideas. This brings up a list of graphs and charts based on that data. You can hit + to add them to
your spreadsheet. You can even choose which fields interest you the most and
in this example, let's change Sum to an Average and update those charts. I hit + here on the Units by Region, it creates a new tab, and adds the data from
that chart into your spreadsheet reflecting the average of the unit's.
Ideas is great at suggesting the data that matters the most.
#5 Remove Blanks. If you want to remove a bunch of empty cells in a set
of data just highlight that whole list, go to the Home tab, and choose Find and
Select from the menu. Choose Go to Special, and check this box for blanks.
Hit OK. It's now highlighted all the blank cells in my data set. Just
right-click on any one of them, choose delete, and choose Shift Cells
Left. Hit OK. It just cleaned up that list. #6 Quick Analysis Tool. When you highlight a table in your spreadsheet in the bottom-right corner there's an icon. This is the Quick Analysis Tool. When you click on
that icon it brings up a wealth of information that you can select from. You
can pick Sums of the columns, Sums of the rows, get a Running Total, or Percentage.
You can select Charts and insert them into your spreadsheet. You can set the
formatting so that you have data bars that represent the value or you can
change the color scheme for the value sets. Have visual representation of your
data as an icon, mark the top 10%. You can add sparklines. That shows you a mini
line graph of your data. There's so much available here without having to go
through the menus to manually create each one of these items, so look for that
icon in the bottom-right corner of any of your tables. #7 AutoFit Column
Width. This is by far the quickest way to adjust the width of your columns. Just go
to the space in between any columns until your cursor changes and
double-click. It'll automatically readjust the size of the column to fit
the widest point of the data anywhere in that entire column. You can also
highlight multiple columns, double-click on any one of them, and it'll do multiple
columns at the same time. #8 Absolute Cell Reference. Excel uses two
types of referencing - Relative and Absolute, or a mixture of the two. As you
can see in this cell B4 X C1 is the cost times the discount. These are both
relative references because they are just referring to a certain location to
the left or up three rows and if we were to copy this formula down you would get
errors because each one is still referring to the one to the left and the
one three above. In this case it gets an error
because it's trying to use Row 3 so what we want to use instead is an absolute
reference. We want C1 to be fixed to this location for all the rows. The way to do
that is to highlight that and hit F4 to toggle between the different relative
and absolute cell referencing. Now you can lock the column the row or both and,
in this case, we're going to keep this locked to C1. Now when I copy it down it
does the correct formulas for each one always referencing the absolute
reference of C1. Anytime you plan to copy formulas make sure you set the relative
or absolute referencing appropriately. #9 Paste Special Values. Sometimes
you want to eliminate formulas and copy just the data from cells. In this example,
I have a first and last name combined using a formula. I want to highlight this,
right-click, choose Copy, and paste it but I want to paste just the values not the
formulas, so if I right-click, choose Paste Special, check this box for Values
and hit OK. Now you can see it just has the value and not the original formula.
You'd be surprised at how often you need to use this type of function because you
want just data and not formulas, so make sure to use Paste Special Values. #10 Drop-Down Lists. Let's say you have a region North, South, East, and West and you want them to be selectable anywhere in this list. Just highlight the rows that
you want, selectable, go to the Data tab, choose Data Validation from this list,
change this to List, make sure this checkbox for In-cell Drop-down is
checked, and for the source you want to choose your options available in the
list, which is going to be these four. Hit OK and now in each one of these rows you
have an arrow that you can choose from that list. You can still type in data but if you try to
enter it, it gives you an error because the value doesn't match the data
validation list that you created. You can go back to the list, you can change the
input message prompting you to enter the data, and you can also edit and create an
error alert whenever you type data that doesn't validate. So if you want to make
it easy to select data from a list and validate it, use the Drop-Down List.
#11 FILTER(). FILTER() is a new dynamic array function added to Excel in
2020. One of the challenges with XLOOKUP and other lookup functions is that they can't return multiple values from a list but FILTER() does. This is how it works. Let's
say we wanted to find all the East region sales results from this list. You
can use the FILTER() function by providing it an array, which is going to be this
entire list and what we want to include is anything in the region that equals
"East" and it's that simple. It returned all the region East Reps, Products and
Units. The FILTER() function spills the results over into multiple rows and
columns based on what it finds and if you look at any individual one you can
see that the formula is the same in all of them but it originates from the upper-left corner. You can even get more complex with the filter by taking the
region and multiplying it by the rep equaling your selected rep and now we
have the combination of both where the region and the rep match. FILTER() is a
powerful new dynamic array function that solves many issues with previous lookup
functions. Look for it in the 2020 or later versions of Excel. #12 Remove
Duplicates. If you have a list of data in Excel and you want to remove all the
duplicates sets of information just highlight it, go to the Data tab,
select Remove Duplicates from the menu, and in this case, it defaults to all
three headings - First Name, Last Name, and Amount.
Where all three match I'm going to remove one duplicate and it took that
row from the list. Let's try this again but this time let's do all of the
duplicates where the first and last name match and it removed two duplicates. This
is a quick method to remove those duplicates and it works on large lists
of data. #13 Difference Between Lists. Sometimes you want to see the difference between two lists of data. This could be a monthly budget
report or other similar list. If you want to see the difference between two lists
highlight the first one, hold down the Ctrl key, and highlight the second one.
The second one can be on a different sheet if you want to, just hold the
Ctrl key down when you click on it, then go to the Home tab, select
Conditional Formatting, Highlight Cells Rules, and Duplicate Values. This shows
the duplicates between the two lists. But we can make it the Unique. Hit OK and
now you've highlighted the differences between those two lists. #14 Flash Fill. Flash Fill is an automatic entry built into Excel to make you more
efficient. For example, I've got a first name and a last name and I want to
combine them into a full name in column C. I can start typing and it
automatically detects the pattern that I'm using with first and last name and
wants to fill it in for the remaining rows on that column. Just hit Enter and
it Flash Fills the data. Flash Fill is enabled by going into File, Options,
Advanced, and under editing options make sure this check mark next to
Automatically Flash Fill is turned on. If it doesn't appear to be working
automatically then hit Ctrl-E to trigger it and Excel does a great job of
determining those patterns to do the Flash Fill for you.
#15 AutoFill. Excel can complete patterns for you with
AutoFill. So if I were to enter a 1 and a 2, I highlight those and drag them down, it's going to fill in the remainder of
those numbers in sequence. I can do the same thing with dates. You can use
combination of words and numbers and it goes horizontal as well as vertical. AutoFill can use dates, times, weekdays,
months, or any kind of sequential numbering scheme that you come up with
and is great at saving you time in data entry. #16 Data Types. Data Types is
a new feature added in 2020. It's available by going to Data and into this
category for Data Types. Currently there are Stocks and Geography,
but over time Microsoft will be adding additional data types to the list. I've
created a list of states and if I highlight those and select Geography, it
creates an icon showing the link to the data type and gives you an insert option
to add additional data from this list. I can pick other variables of data like
the Area, the Largest City, Population, and a variety of other data elements. You can
reference Data Types as well. I'm going to create a formula for this state of
Oregon and I'm going to select a data element from here, hit enter and I've
just referred to the largest city in that state. You can use formulas anywhere
in your spreadsheet once you've created these data type links. Data Types is a
great way to access information without having to create your own data sets and
over time those new additional data types will come up that'll make this
even more powerful. #17 Transpose. Transpose allows you to switch the
columns and rows on a table. I highlight this table, right-click,
Copy, select the new location where I want to paste the reverse, right-click
again, Paste Special, and choose Transpose. Hit OK. It's now reversed the columns and
rows. #18 Freeze Panes. Freeze Panes allows you to lock certain portions of
the screen. In this example, I have a large set of data and when I scroll down
I lose the top row header information and when I scroll to the right I lose
the left column header information. You can solve this problem by clicking in
the upper-left corner of your data, going to the View tab, click on Freeze Panes,
and select Freeze Panes from your menu. Now when I scroll down the header row
information stays and when I scroll right the left column header information
stays. To turn it off go back to the same menu and choose Unfreeze Panes. So if you have a large set of data and you need to lock those columns and header rows use
Freeze Panes. #19 Text to Columns. When using Excel you'll often find
yourself copying data from a web page or other application. I have a set of data
and if I copy it and paste it into Excel it comes in as one line instead of
separated out into columns like I want. There is a way to solve that problem. Go
to Data, pick Text to Columns. In this case, the data is all separated with
commas, so I'm going to change it from a tab to a comma delimited and in the data
preview you see that it has figured out where to separate the data and I hit
Finish and it spreads it among the different columns. It's as simple as that.
The key is you need to have some delimiter in your data or if there's
even spacing you can do it based on a fixed width. Either way it can convert it
into columns and insert it into your spreadsheet. #20 Recommended Pivot
Table. Excel has made pivot table creation much easier by recommending
them for you. Click anywhere inside of a data table, go to the Insert tab, and choose Recommended Pivot Tables from the menu. Excel uses artificial intelligence to make the best recommendation of the data
that you have in your table. You can choose from multiple different choices
here and insert them into your spreadsheet on a new sheet, then you can
edit the settings just like any normal pivot table. So if you're not comfortable
with pivot tables let Excel recommend one for you. #21 Slicers. You've
seen how to filter a list but there's another method to filtering your data.
Take your data table, highlight it, go to Insert, Table and make sure this checkbox
next to My Table has Headers is turned on. Hit OK. Now go over to Insert, Slicer and select the different filtering options
you want and it creates a menu for each one of these slicers. Now just select the
ones that you want and it filters them out based on that data. If you hold the
Ctrl key down you can select multiples and filter just on that data. Slicers is
like the cool version of the Filter List. #22 Conditional Formatting. You saw
a sneak peak of Conditional Formatting with the Quick Analysis Tool but there
are more options available from the menu. Let's say, on this table, we want to show
all the entries with units greater than 15,000. Just highlight the units, go to
Conditional Formatting from the Home tab, Highlight Cells Rules, choose Greater Than, and enter 15,000 and it highlights everything over 15,000. To clear the
rules go back to Conditional Formatting and Clear the Rules from the Selected
Cells. You have many more options available not just highlighting cells
that are Greater Than, but you can do In Between numbers, Equal to certain numbers, anything that's a Duplicate Value. You can pick the Top 10%, the Bottom 10%, how
many are Above Average. You can set Data Bars with different
color codes based on the values, Color Scaling, Icon Sets and you can even
create your own new rules based on all these different rule types. You can even
use formulas. Just make sure you get your absolute referencing correct. So give yourself a better representation
of your data using Conditional Formatting.
#23 IF(). It's very common to use the IF() function to evaluate data in your
spreadsheet. For example, let's create an IF() statement that determines when the
units is greater than 15,000. That is the logical test. If the value is true then
we want to print the word BIG and if the value is false we want to print the word
SMALL. That's the format for an IF() function. Hit Enter and we have our
results. Just remember that the logical test can be as complex as you want it to
be and you can use a variety of different things for the value of true
or false. There's a lot of flexibility with the IF() function. #24 3D References. If you're tracking monthly data in Excel, it's common to create a
different sheet for each month and in this case I have a January, February, and
a March sheet. They're identical with the exception of the units. Let's say I want
to make a total of those three months, so the first thing I'm going to do is I'm
going to hold down the Ctrl key and drag March over to a new sheet, then rename
that Total and put a heading on it called Total. Now let's use 3D Referencing to
add the quantities from each of the three tabs into this one cell, so click
on the first cell. I'm gonna choose AutoSum. Now I'm going to go to the first tab,
click on the first cell, and hold the Shift key while I highlight all three of
these sheets, and hit Enter. That created a formula totalling January through
March. Then I copy that down and now I have the total from all three sheets.
That's how you use 3D Referencing. What if we wanted to add the Year to all of
the sheets. I'm gonna hold down the Shift key, and highlight all these again, and
right-click and Insert a new row. We'll Merge and Center, and call it 2020,
and you can see that it added it to all the sheets. 3D Referencing is as simple
as selecting multiple sheets when you do entries into formulas or type data into
your sheet. #25 Forecast Sheet. It's really easy to create a forecast of data
that you have in a table. Just highlight your table, go to the Data tab, click on
Forecast Sheet, and it automatically calculates a trend based on your data.
You can change the forecasted end date and extend it if you like, and change
other options such as the confidence level of your data, change the range of
your data. Hit Create. It creates a new sheet with your data in a table,
including the forecast results and a graph representing your data. Hover over
any spot on the data and you can see values at different periods of time. So
if you want a quick representation of a trend line and your data use Forecast
Sheet. Congratulations! You've made it through half of the tips and tricks list.
The other half will continue in 15 seconds but please take a moment right
now to subscribe to Sele Training and click the Bell icon to get notified when
new videos are released. Don't forget the entire 50 tips and
tricks list is in the description of this video with hyperlinks to each. Now
let's get back to it. #26 SUMIFS(). The SUMIFS() function allows you to do
conditional summing of data. In this example, I have a set of data for months
and items and the amount of each and I want to create a sum of January. So I can
use the SUMIFS() function. The first parameter it wants is the range and
that's going to be this amount right here. Then it wants to know the criteria
range so we want the month where it equals January and that gives us a 128
total, which is the total of these three entries. Now for this SUMIFS() we want to
add just March for Item 2. So we're going to use the SUMIFS() function again and we're going to sum the same range of amounts, but the criteria is still
going to be the month equaling March with the second criteria where the item
equals Item 2, and that gives you the sum 245, which is the total of these
three March Item 2's. #27 IFERROR(). The IFERROR() function can be used to clean up bad data. For example, I have a formula here that calculates B divided by C so
that it takes the Total divided by the number of Quantity and gives me the
price Each. This formula works fine except when I get down here to the
quantity zero and I end up with a divided by zero error. You can clean this
up with the IFERROR() function. Just add the IFERROR() function in front of this
formula and if there is an error let's just say zero instead. Now when I copy
that down it corrects our divide by zero error. So if you want to remove those
errors from your spreadsheet use the IFERROR() function. #28 Ctrl-Arrow Keys. Don't forget to hold down the Ctrl key when you're moving around a
set of data. If you hold the Ctrl key down and hit the right-arrow it goes to
the very end of the row, down-arrow goes to the bottom, left-arrow to the left, and
up-arrow to the top. This is a quick method to use to scroll around a table
of data. #29 Filled Maps. You can use Filled Maps to make a
geographic chart of your data. Make sure you have location specific information
here. It can be a state, a city, a zip code, GPS coordinates, or any other location
related data elements. Just highlight your data in a table, go to Insert, Maps,
and choose Filled Map. It inserts the chart on to your page where you can
customize it using a variety of different settings. You can change the
data labels, pick a different chart style, change the
layout, and even change the color scheme. #30 PMT(). The payment function is
useful to calculate a payment on a loan amount. In this example we've entered
.045 and formatted the annual rate as a percentage. We're using
sixty months of payments on a loan amount of $20,000. In order to calculate
the monthly payment for this amount we're going to enter PMT(), pick the rate
as the first parameter, and the key here is you need to divide this by 12 to get
it into monthly periods instead of a total annual rate. Then the number of
periods is going to be 60 on a total amount of 20,000. There are other
parameters like Future Value for more complex calculations, but we won't use
those at this time. End-parentheses on that and Enter and we get a monthly
payment of $372.86. Now by default it makes this a negative number,
but you can put a minus in here to reverse this to a positive if that's how
you want to use the data. So if you intend to do any loan payments you're
probably going to use the PMT function. #31 Show Formulas. When working
with formulas in Excel it's sometimes helpful to see the formulas in an
underlying cell. You can do that by double-clicking on a cell with a formula
in it. It brings up the formula and it shows color codes for the cells that it's
referring to. Another option is to go to File, Options, Advanced and scroll down to
Display Options for this Worksheet. Check this box to Show Formulas in Cells and
hit OK. That brings up all the formulas for all the cells in your sheet or you
can use the Ctrl-~ shortcut to turn it on and off. These options make it
easy to see formulas at a glance. #32 Advanced Select. Here's a trick to making changes to multiple items all at once. Come up to
Find and Select, choose Find, type in something you want to search for, and
select Find All, hit Ctrl-A to select everything in this list, and it
highlights all of those items on the list. Now you can close the search and it
still leaves them selected and at this point you can make changes. Anything you
do will change all of those entries the same way. That's just a shortcut to make
changes to multiple items. #33 Named Range Shortcut. You may be aware that you can highlight a range of data in your spreadsheet and come up to this
name box in the upper left and give it a name. That gives you the ability to refer
to it by that name anywhere in a formula on your spreadsheet, but what you may not
be aware of is if you have a wide area of named ranges you can select the
entire list all at once, go to Formulas, choose Create from Selection, and check
this box for the Top Row. Hit OK and it names them all at once. You can see them in the Name Manager and there they are. Now you can refer to any one of these months by name. It's just a quick trick
to name multiple ranges. #34 Hide Cells. There's a slick trick to hiding
cells. In this example, I have 4 times 5 equals 20 in a formula, but let's say we
wanted to hide this cell from view. If you right-click on it, go to Format Cells,
click on Custom, and enter 3 semicolons, hit Enter. That hides the cell
from view, but it still works both in the formula and its contained in that cell
even though it's not visible. To turn this off, go back to the same location
again and remove the three semicolons. A quick trick if you just want to hide a
cell. #35 COUNTBLANK(). The COUNTBLANK() function is useful to count the number
of blanks in a range of cells. Just type in COUNTBLANK() and put in the range that
you want to count. It shows 6 empty cells. You can also use COUNTA() in the
same range to count the number of cells that are not empty.
#36 Natural Language Query. We've seen previously how you can click on a
chart and go to the Ideas tab to see recommended charts of the data, but there
is a Natural Query area here as well. You can type in things like Total Sales by
Category and it creates a chart based on that query. Try Average Sales by Year. How about Top 3 Ratings by Product, or how about Which is the Most Common
Product, or Products that Start with 'S'. Add to it the Total Sales of those
products. Natural Query Language uses artificial intelligence to analyze your
question and provide the data in the best format possible. You can even see
it's question analyzed and the answer that it determines from your question.
It's very powerful. #37 Goal Seek. Goal Seek is an
advanced function in Excel that is part of the what-if analysis tools. In this
example, say we have several items for sale. We know how many quantity there are of the first two items so we can calculate their totals but we don't know
how many of the third item we need to sell in order for this number to be
$6,000. That's our target. Yyou could guess the numbers by punching in things
randomly until you reach your total of 6,000 but Goal
Seek will calculate this number for us. To start a Goal Seek you need to click
on the target number. This is the number that we want to change until we reach
this target of 6000, then go to Data What-if Analysis, and choose Goal Seek. It
set our target as the Set Cell. The value we want it to go to is going to be our
6000 and what we want to change in order to reach that target is this cell right
here - the quantity. Hit OK and it does the goal seek and determines that 196.9
is the quantity. And that's just one example of Goal Seek but be aware you
can use your imagination to come up with all kinds of scenarios for the what-if
analysis using Goal Seek. #38 Insert Screenshot. Here's a tip to adding images from other applications. Go to Insert, select Screenshot and it shows
the open windows that you have maximized on your computer. Select one and it
inserts that image of that application in Excel. You can then resize and move it
around however you want, and do the same with multiple images. You can also insert
just a portion of your screen by using the Screen Clipping. So if you want to
add an image from another application rather than doing copy and paste you can
insert it directly. #39 Power Pivot. Power Pivot is a free feature that's
installed as an add-in to Excel but it's not installed by default. To enable it go
to File, Options, Add-ins. Come down here to the bottom and select COM Add-ins
from the list and hit Go. Make sure to check this box for Microsoft Power Pivot
for Excel. Hit OK and it adds this Power Pivot option to your menu. When you open
that there's a whole new set of features available from the menu. Power Pivot
allows you to connect to Oracle, SQL, and other external data sources,
and use it for large sets of data. In fact, it expands the 1 million row limit
of Excel to virtually unlimited numbers. It turns Excel into a Business
Intelligence Analysis tool. I won't show you the details in this video but take a
look at Power Pivot if you deal with large sets of data from a lot of
different sources. #40 3D Maps. You can create stunning three-dimensional
maps of your data. In this example, I have a table of countries with various
amounts over multiple years. Click anywhere inside that table, go to Insert,
and choose 3D Map from the menu. This brings up a world globe from Bing Maps.
The first thing I'm going to do is add a Location so for that I pick the Country,
and for the Height I'm gonna pick the Amount, and it shows a 3D representation
of the Height on the globe. I'm also going to pick a Timeline for the year, and
it immediately creates a timeline on the chart, and I can adjust this and it
reflects the data over that time-frame. Let's position this to a different
location. Now I can choose different themes but we'll stick with the default. You can
also add labels which shows the country names, I can flatten the map or leave it
as a globe, you can also find a location and it takes you to that location on
your map. For the layer options let's change the thickness so it widens out
the data elements on the screen and we'll make the height a little bit
larger. All the changes I make are recorded to this tour and if I click on
the settings I can give it a name, change the duration, or the amount of time
between each transition. I'm also going to set this one to rotate the globe. Now
let's play the tour and see what it looks like. It moves while it's updating
the data elements. You can even choose a different location to begin your tour. You can create a video from this and
choose from a variety of different resolutions, or you can capture screen and
then paste it back into your chart. It also creates a note here that there is a
3D Map tour associated with this workbook. You can delete this but that's
the only way you'll know there's a 3D map linked and your indication that you
can come back in here, click on 3D Maps, and you can see your tour that you
created. You can create multiple tours or you can select one that you are already
created in the past and edit it however you like. 3D Maps is a great way to make
visually stunning 3D images of your data. #41 ISBLANK(). Earlier we looked at how you can count blank cells but another useful
tool is the ISBLANK() function. ISBLANK() returns true if a cell is empty and
false if it contains any data. So if we check column C to see if it's blank, it's
true for this row. You can combine it with the IF() statement to make it even
more powerful. You can use the ISBLANK() function anywhere there's a formula with
a true/false value. #42 Analysis ToolPak. Excel has an advanced hidden
menu option available called Analysis Toolpak. You can access it by
going to File, Options, Add-ins, and under the Excel add-ins hit Go, and check this
box for Analysis Toolpak. Hit OK and it creates a new menu under your Data tab
called Data Analysis. When you click that it brings up a menu with a number of
complex data analysis tools like Correlation, Exponential Smoothing, Moving Averages, and a variety of other options. When you select one it prompts you for
the input variables and output range for the calculation. Enter the pertinent
information and hit OK and you get your data analyzed and the results
output to your spreadsheet. I won't go into details but just be aware that the
Data Analysis ToolPak is available by turning it on from the add-ins menu.
#43 CONVERT(). The CONVERT() function is useful to convert data from one
measurement into another. For example, let's type in CONVERT() and we're going to
choose this original data value. It brings up a list of the options we have
for conversion so let's take Days, hit Comma and then it gives me only the
choices of values that I can convert days into, and let's pick Hours. And
that's how easy it is to convert data from one measurement into another.
#44 Get Data from Web. Did you know you can pull live data from a web page into
Excel? For example, let's go to a web page called x-rates. This website provides
live exchange rates for various currencies. I'm going to take a look at
the US dollar conversion rates in the rate tables. Now that I have this table
information available on the screen I'm gonna copy this URL, go back into Excel,
go to Data, and Get Data from the Web, and I paste this URL, and hit OK. That goes to
that web page, connects to the data, and brings up the different tables of
information available. Now you can look through these tables to find one that
looks like what you want and we'll take this table 1, which has a variety of
different currencies against the US dollar. I'm going to load that table. It
establishes a query and connection to that table on the webpage and it pulls
my data into the spreadsheet. I can now reference this information just like any
other table in Excel. Now that we have this data let's go up here to the
Refresh menu and go to Connection Properties. I can set this to Refresh
Every 60 Minutes or whatever time I like, and I can Refresh the Data
Whenever Opening the File. This ensures an automatic update of the data.
Hit OK and now I have a permanent link to data from the web. #45 People Graph.
People Graph is a quick method to graph data from a simple two column table. Just
go to Insert and select this icon for People Graph. That brings up a default
graph. The first thing you need to do is Select the Data and choose a two-column
table. The second column needs to have values that you can display in your
graph. Click Create and you can also come in here and change the title. And that
displays a graph of your data. You have multiple options available from the
Settings. You can change the Type of the graph, the Theme, and even the Shape of
the items that show up on the graph. Over the right-hand corner you can mark this
as a Saved Image and now it's locked so that you can't make changes anymore. You
can now resize and move it. Click again in the upper-right corner and you can
turn that back off. A very simple way to create a quick graph. #46 SORT(). SORT()
is a new dynamic array function added in 2020. It allows you to sort a list such
as this list of states. Let's enter the function and we'll choose the entire
table. The sort_index is either the first column or the second column. We're going to go ahead and sort based on state so we'll pick the first column, and it
defaults to ascending order but you can put a -1 in here if you want to
reverse it, and the final option is true or false whether you want to sort by
column or sort by row. It defaults to row but column would be useful if you had a
horizontal list. We'll choose the default and hit Enter and it sorts all those
states alphabetically. We can go back and change the sort_index to 2
and then it sorts by the amount in the second column, and you can see it's
sorted in the second column now. Dynamic Arrays will spill over into multiple
columns and rows as it needs to fill the data, so even though this formula is in
the upper-left corner, you'll see that it's grayed out but spills over into the
other columns and rows. Just be aware these dynamic array functions are only
available in the 2020 version or newer and will not function if you open a
spreadsheet in an older version so you want to use them carefully. #47 Status Bar Info. The Status Bar is this row at the very bottom of your
spreadsheet and it contains information that's useful while you're working. For
example, if we highlight this list of numbers, down at the status bar it shows
the average, the count, the sum, of that data. Anything that you select will show information down here. You can right-click and turn on and off different values on the status bar like the
maximum and minimum values, the numerical count, you can turn off certain information,
and even turn off other indicators like your zoom slider and your status. So if
you want information in a quick glance take a look at the Status Bar Information. #48 Insert Multiple Rows. You may be aware you can right-click on a row, select Insert, and it inserts a row above that line but
there's a quick method to insert multiple rows. Just select as many as you
want, right-click anywhere in that group, click Insert, and it inserts all of those
rows. You can do the same thing with columns. Select multiple columns, right-click, and Insert, and it inserts multiple columns.
#49 CHOOSE(). The CHOOSE() function returns a value from a list using a
given position or index. For example, if we wanted to choose the second item from
the list of Blue, Red, and Green it returns Red, which is the second item
in the list. Instead of explicitly listing the values you can select them
from a cell location. So let's pick the second item from this, this, or this value
and it returns the second value, which is Washington. With the CHOOSE() function you can have up to 254 values in that list. It does allow you to select from a range
but watch what happens when we do that. Let's choose the second value from this
range. Yyou might think it's going to return Washington but it just gives you
an error and the reason why is because it treats the range all as one value. If
we pick the first value from that list it returns the entire list of names.
Let's add a second value which is this range. So we can return the first value
which is that first range or we can return the second value which is the
second range. So it will work with ranges but it treats them all as one value. You
can make the position or index number a variable as well. We'll put in 1 up
here and then we choose the position number 1 from these choices and it
uses that cell to trigger which index number to select. So if you're looking
for a quick function to pull values from a list use the CHOOSE() function. #50 UNIQUE(). The UNIQUE() function is a new dynamic array function that came out in
2020. It returns a unique set of values from a list. For example, if we do a
UNIQUE() of this list right here, it returns the four unique values from that
list and ignores the duplicates. The UNIQUE() function has a couple of
additional parameters. The first one is by column and you would use true here if
you intend to do a horizontal list but it defaults to false, and the second parameter is exactly_once. If this is false it returns
every distinct item from the list which is the default. If it's true
it returns items that appear exactly one time and, in this example, Pear is the
only item that shows up exactly once in the list. As with any dynamic array
function it will spill the data over into multiple rows and columns as needed.
The UNIQUE() function is available in the very latest Office 365 Excel version so
remember this if you have users with old versions of Excel because it won't
function correctly. Hey, if you want to see more videos like this one please
subscribe, and if you've enjoyed this video be sure to click the thumbs-up and
leave a comment. I really do appreciate your support!
