Excel 2020 Best Tips & Tricks!
This is a recap of the best Excel tips and tricks released for most users in 2020.
You'll have all these features if you use the subscription-based version of Microsoft 365 Excel
and have updated to the latest release. If you are on a slow release channel you may
still have to wait a few more months for some of these though. Let's take a look. #1 XLOOKUP.
XLOOKUP is a great new function that was added technically in the latter part of
2019 but became available for most people in 2020. It works like this. If you do XLOOKUP
of the name John in this list of names and you want it to return the Jan total
it finds 17,498 which is the total for January for John. The nice thing about XLOOKUP is not
only does it do vertical lookups like the VLOOKUP command but we can look up things
horizontally. So let's say we want to find the month of Feb in this list
and return the value for Karen. It finds 17,834 which is Karen's total for February.
Now another thing we can do with XLOOKUP is use some of the additional parameters.
For example, let's find the Discount in this list of discounts and we'll return the Region.
Well it doesn't find that value because 46% is not in this list. So instead of
N/A let's put in the next parameter which is - if it's not found let's have it insert zero
and if you go to the parameter after that it gives you an option to do an exact match or next smaller
or next larger item. So let's find the next smaller item and that would be 45 so it returns
Region 3. If you want to see more details on how XLOOKUP works take a look at my XLOOKUP video
and I'll put a link in the description below this one. #2 LET. The LET function was added mid-year
2020 and it allows you to assign a variable to a portion of a formula to reduce its complexity
and potentially speed up the calculation. As an example let's say we have a column of
three numbers A, B, and C and we want to do this formula A3*B3/C3*25%. Now let's use that formula
in an IF statement so that we can assign 10, 20, 30, or 40 based on the results of that formula.
Well you can see in here that I'm repeating that same formula multiple times to do that comparison.
We can substitute a variable for that. First thing you need to do is put in the LET function
and it asks for a name. We're going to call this var for a variable
and the name value is going to be the portion of the formula that we're going to substitute
which is A3*B3/C3*25%. Now it wants the calculation. Well that's the actual formula
but now we can take our variable and put that in place of where the formula
is.
And at the end let's make sure to put an ) for the LET function and it still calculates the
same results. We'll copy that down and as you can see it's a much smaller formula and much
easier to understand. That is the LET function. #3 Dynamic Array Functions. Microsoft added 6 new
dynamic array functions to Excel. They are Unique, Filter, Sort, Sortby, Sequence, and Randarray.
What's interesting about dynamic array functions is that when there are multiple results
it spills the data over into the adjacent cells. For example, let's take the UNIQUE
of this range of names. What it does is it determines all the unique names in that list
and it spills those values over into all the cells. The first cell contains the formula
and the additional cells have a shaded version of that formula.
You can now reference each one of these individual cells and the value contained
in it. This is why it's considered dynamic. Taking a look at FILTER let's do a filter on this range
and let's select where this set of data equals East.
It selects all of that data for only the East region. Each one of these dynamic
array functions serves a different purpose. If you want to look at them in more detail take a
look at my dynamic array formulas video and I'll put a link in the description below this video.
#4 Linked Data Types. Microsoft added Data Types to Excel. They're available under the Data tab
in this category for Data Types and when you hit the down-arrow you can see a number of them are in
preview and there are some existing ones already implemented. The way Data Types works is you
can get a set of data highlighted and then select the data type associated, in this case Geography.
It adds a show card symbol and it also creates a symbol for Geography next to
the data that you've selected. When you hit this button it brings up data elements that
you can add to your spreadsheet. For example, let's put in the population
and it provides the population for all of those states. You can also reference these data types
in a cell. For example, let's take a look at Oregon's largest city and you see that it had
a period in the field name. Hit Enter and now you have Portland is the largest city in Oregon.
I can now reference that one and see what the population is in Portland.
All of these data types reference information from the web and if you go down to the bottom
of the data set you'll see that the data is coming from these various locations. So you're leveraging
data elements from the web into your spreadsheet without having to create a detailed list of data
elements yourself. If you want to see more detailed information about Linked Data Types
make sure to check out my data types video. I'll put a link in the description. #5 Analyze Data.
Off the Home tab there's an option here called Analyze Data. If you click inside of a table
of data and then select Analyze Data from the menu it brings up this window where you can select from
predefined tables and graphs of your data. Analyze Data isn't new. It used to be called Ideas but one
thing that is new for 2020 is the natural query language portion of this Analyze Data. You can
put in here text of what you want to ask questions about your data. For example, let's show the total
sales by product and it provides a graph of that data. You can also do things like what is the
average rating for product socks and it calculates the average rating based on your text.
The natural query language is a very interesting way of analyzing your data just by asking simple
questions. Excel does a good job of analyzing the questions that you're asking and breaking
it down into different components and providing tables and graphs of that information. If you
want a little bit more detail about the natural query language portion of the analyze data take
a look at my video on Natural Query Language. I'll put a link in the description. #6 Premium
Content. If you go to the Insert tab-Pictures there's an option here for Stock Images.
Here you'll find images, icons, cutout people, stickers, and illustrations. This
premium content is made available by Microsoft for you to use anywhere in your spreadsheet.
Here you can choose a different category as you're looking for different images or you
can type something in the search such as SUNRISE. When you find the image that you like, select it
and you can insert that into your spreadsheet. Here you can resize it, remove the background,
do corrections, and other edits just like any other picture. Microsoft provides all of this
premium content as royalty free for use anywhere you want and it's a great addition for 2020.
#7 LAMBDA. LAMBDA is a function that helps you create your own functions. It's technically in
beta at the end of 2020 so this is more like a preview of what's coming soon. I have a formula
right here to calculate the volume of a cylinder. It's PI times the radius squared times the height
and I created a table with some radius and height values. I've calculated the volume
by putting in the formula here but let's say we want to create a function of our own that
calculates the volume of that cylinder. that's where lambda comes in. if you go to the formulas
tab, click on name manager, and we're going to create a new name. let's call this cylindervolume
and we're going to put in =lambda. the function lambda has multiple parameter values that you
put in. the first thing you want to do is start with the variables that you're going
to use in the formula so we're using the radius and the height so I'm going to use r
and h. The next parameter is the actual formula and now we're going to use r and h in that formula
so our volume formula is pi times r squared times h so I'm going to take pi*r^2*h. End parentheses
and that is everything you need to do to pass the radius and height into this lambda function and
calculate that formula based on those values. Hit OK. It creates a new name called CylinderVolume
and I can close that. Now I'm going to type in CylinderVolume. That's our new function
and we're passing two variables in the radius and the height and there you go.
This new function we created can now be used anywhere in an Excel spreadsheet and it saves
with your spreadsheet so that it's available the next time you open it. I can now copy it
around just like I do any other formula. I'm sure I'll cover this in more detail in a future video.
That's Excel 2020 Best Tips and Tricks. Thanks for watching. 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!