September 19, 2020

50 Ultimate Excel Tips and Tricks for 2020

50 Ultimate Excel Tips & Tricks.

Updated for 2020! This video is the ultimate compilation of Excel tips andtricks to enhance your productivity using Excel.

I've combinedlong-established tips and tricks featured in previous videos withprominent new ones Microsoft added in 2019 and 2020.

You'll find solutions tothe most common challenges you face in Excel.

For the complete list of contentsplease refer to the description of this video.

Let's get started.

#1 MoveData.

Here's the quickest method to move data around in Excel.

Just highlight thecells and grab the border anywhere around the outside and drag it and dropit where you want it to go.

You can do an entire column or you can do an entirerow.

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 designedto replace VLOOKUP and HLOOKUP.

Let's say we want to look up the name “John” in thistable and return a value from the February column.

You can use XLOOKUPand the first parameter is the lookup value which is going to be John.

Thearray that we're going to look up John's name in is this area right here and wewant to return values from the February column.

And that's all you need to do.

Itreturned 17, 232 which is the intersection of John and February.

Nowone of the benefits of XLOOKUP is it does horizontal lookups as well so let'ssay we want to look up February in this list but we're going to return the valuefrom John's row and it found the same number 17, 232 inthe February column next to John.

So it does both vertical and horizontallookups.

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 aneasy way to manage a large table of data.

Click anywhere inside the table, go tothe Data tab and click on this Filter icon and it creates a filter at the topof each column in your table.

Now you can select one and choose from the list thevalues that you want to see.

It filters that data out.

You can use multiplecolumns and filter out just the data you want to see.

It's a great way to managelarge lists of data.

#4 Ideas.

If you're looking for suggestions on howbest to display your Excel data use Ideas for inspiration.

Just clickanywhere 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 toyour spreadsheet.

You can even choose which fields interest you the most andin 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 fromthat 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 setof data just highlight that whole list, go to the Home tab, and choose Find andSelect 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.

Justright-click on any one of them, choose delete, and choose Shift CellsLeft.

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 onthat icon it brings up a wealth of information that you can select from.

Youcan 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 theformatting so that you have data bars that represent the value or you canchange the color scheme for the value sets.

Have visual representation of yourdata as an icon, mark the top 10%.

You can add sparklines.

That shows you a miniline graph of your data.

There's so much available here without having to gothrough the menus to manually create each one of these items, so look for thaticon in the bottom-right corner of any of your tables.

#7 AutoFit ColumnWidth.

This is by far the quickest way to adjust the width of your columns.

Just goto the space in between any columns until your cursor changes anddouble-click.

It'll automatically readjust the size of the column to fitthe widest point of the data anywhere in that entire column.

You can alsohighlight multiple columns, double-click on any one of them, and it'll do multiplecolumns at the same time.

#8 Absolute Cell Reference.

Excel uses twotypes of referencing – Relative and Absolute, or a mixture of the two.

As youcan see in this cell B4 X C1 is the cost times the discount.

These are bothrelative references because they are just referring to a certain location tothe left or up three rows and if we were to copy this formula down you would geterrors because each one is still referring to the one to the left and theone three above.

In this case it gets an errorbecause it's trying to use Row 3 so what we want to use instead is an absolutereference.

We want C1 to be fixed to this location for all the rows.

The way to dothat is to highlight that and hit F4 to toggle between the different relativeand 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 itdoes the correct formulas for each one always referencing the absolutereference of C1.

Anytime you plan to copy formulas make sure you set the relativeor absolute referencing appropriately.

#9 Paste Special Values.

Sometimesyou 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 theformulas, so if I right-click, choose Paste Special, check this box for Valuesand 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 youwant 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 thatyou 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 ischecked, and for the source you want to choose your options available in thelist, which is going to be these four.

Hit OK and now in each one of these rows youhave an arrow that you can choose from that list.

You can still type in data but if you try toenter it, it gives you an error because the value doesn't match the datavalidation list that you created.

You can go back to the list, you can change theinput message prompting you to enter the data, and you can also edit and create anerror alert whenever you type data that doesn't validate.

So if you want to makeit 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 in2020.

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'ssay we wanted to find all the East region sales results from this list.

Youcan use the FILTER() function by providing it an array, which is going to be thisentire 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 andUnits.

The FILTER() function spills the results over into multiple rows andcolumns based on what it finds and if you look at any individual one you cansee 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 theregion and multiplying it by the rep equaling your selected rep and now wehave the combination of both where the region and the rep match.

FILTER() is apowerful new dynamic array function that solves many issues with previous lookupfunctions.

Look for it in the 2020 or later versions of Excel.

#12 RemoveDuplicates.

If you have a list of data in Excel and you want to remove all theduplicates sets of information just highlight it, go to the Data tab, select Remove Duplicates from the menu, and in this case, it defaults to allthree headings – First Name, Last Name, and Amount.

Where all three match I'm going to remove one duplicate and it took thatrow from the list.

Let's try this again but this time let's do all of theduplicates where the first and last name match and it removed two duplicates.

Thisis a quick method to remove those duplicates and it works on large listsof data.

#13 Difference Between Lists.

Sometimes you want to see the difference between two lists of data.

This could be a monthly budgetreport or other similar list.

If you want to see the difference between two listshighlight 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 theCtrl key down when you click on it, then go to the Home tab, selectConditional Formatting, Highlight Cells Rules, and Duplicate Values.

This showsthe duplicates between the two lists.

But we can make it the Unique.

Hit OK andnow you've highlighted the differences between those two lists.

#14 Flash Fill.

Flash Fill is an automatic entry built into Excel to make you moreefficient.

For example, I've got a first name and a last name and I want tocombine them into a full name in column C.

I can start typing and itautomatically detects the pattern that I'm using with first and last name andwants to fill it in for the remaining rows on that column.

Just hit Enter andit Flash Fills the data.

Flash Fill is enabled by going into File, Options, Advanced, and under editing options make sure this check mark next toAutomatically Flash Fill is turned on.

If it doesn't appear to be workingautomatically then hit Ctrl-E to trigger it and Excel does a great job ofdetermining those patterns to do the Flash Fill for you.

#15 AutoFill.

Excel can complete patterns for you withAutoFill.

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 ofthose numbers in sequence.

I can do the same thing with dates.

You can usecombination 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 withand is great at saving you time in data entry.

#16 Data Types.

Data Types isa new feature added in 2020.

It's available by going to Data and into thiscategory for Data Types.

Currently there are Stocks and Geography, but over time Microsoft will be adding additional data types to the list.

I'vecreated a list of states and if I highlight those and select Geography, itcreates an icon showing the link to the data type and gives you an insert optionto add additional data from this list.

I can pick other variables of data likethe Area, the Largest City, Population, and a variety of other data elements.

You canreference Data Types as well.

I'm going to create a formula for this state ofOregon and I'm going to select a data element from here, hit enter and I'vejust referred to the largest city in that state.

You can use formulas anywherein your spreadsheet once you've created these data type links.

Data Types is agreat way to access information without having to create your own data sets andover time those new additional data types will come up that'll make thiseven more powerful.

#17 Transpose.

Transpose allows you to switch thecolumns and rows on a table.

I highlight this table, right-click, Copy, select the new location where I want to paste the reverse, right-clickagain, Paste Special, and choose Transpose.

Hit OK.

It's now reversed the columns androws.

#18 Freeze Panes.

Freeze Panes allows you to lock certain portions ofthe screen.

In this example, I have a large set of data and when I scroll downI lose the top row header information and when I scroll to the right I losethe left column header information.

You can solve this problem by clicking inthe 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 rowinformation stays and when I scroll right the left column header informationstays.

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 useFreeze Panes.

#19 Text to Columns.

When using Excel you'll often findyourself copying data from a web page or other application.

I have a set of dataand if I copy it and paste it into Excel it comes in as one line instead ofseparated out into columns like I want.

There is a way to solve that problem.

Goto Data, pick Text to Columns.

In this case, the data is all separated withcommas, so I'm going to change it from a tab to a comma delimited and in the datapreview you see that it has figured out where to separate the data and I hitFinish 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'seven spacing you can do it based on a fixed width.

Either way it can convert itinto columns and insert it into your spreadsheet.

#20 Recommended PivotTable.

Excel has made pivot table creation much easier by recommendingthem 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 datathat you have in your table.

You can choose from multiple different choiceshere and insert them into your spreadsheet on a new sheet, then you canedit the settings just like any normal pivot table.

So if you're not comfortablewith pivot tables let Excel recommend one for you.

#21 Slicers.

You'veseen 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 checkboxnext to My Table has Headers is turned on.

Hit OK.

Now go over to Insert, Slicer and select the different filtering optionsyou want and it creates a menu for each one of these slicers.

Now just select theones that you want and it filters them out based on that data.

If you hold theCtrl key down you can select multiples and filter just on that data.

Slicers islike the cool version of the Filter List.

#22 Conditional Formatting.

You sawa sneak peak of Conditional Formatting with the Quick Analysis Tool but thereare more options available from the menu.

Let's say, on this table, we want to showall the entries with units greater than 15, 000.

Just highlight the units, go toConditional Formatting from the Home tab, Highlight Cells Rules, choose Greater Than, and enter 15, 000 and it highlights everything over 15, 000.

To clear therules go back to Conditional Formatting and Clear the Rules from the SelectedCells.

You have many more options available not just highlighting cellsthat 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%, howmany are Above Average.

You can set Data Bars with differentcolor codes based on the values, Color Scaling, Icon Sets and you can evencreate your own new rules based on all these different rule types.

You can evenuse formulas.

Just make sure you get your absolute referencing correct.

So give yourself a better representationof your data using Conditional Formatting.

#23 IF().

It's very common to use the IF() function to evaluate data in yourspreadsheet.

For example, let's create an IF() statement that determines when theunits is greater than 15, 000.

That is the logical test.

If the value is true thenwe want to print the word BIG and if the value is false we want to print the wordSMALL.

That's the format for an IF() function.

Hit Enter and we have ourresults.

Just remember that the logical test can be as complex as you want it tobe and you can use a variety of different things for the value of trueor 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 adifferent sheet for each month and in this case I have a January, February, anda March sheet.

They're identical with the exception of the units.

Let's say I wantto make a total of those three months, so the first thing I'm going to do is I'mgoing to hold down the Ctrl key and drag March over to a new sheet, then renamethat Total and put a heading on it called Total.

Now let's use 3D Referencing toadd the quantities from each of the three tabs into this one cell, so clickon 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 ofthese sheets, and hit Enter.

That created a formula totalling January throughMarch.

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 ofthe sheets.

I'm gonna hold down the Shift key, and highlight all these again, andright-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 simpleas selecting multiple sheets when you do entries into formulas or type data intoyour sheet.

#25 Forecast Sheet.

It's really easy to create a forecast of datathat you have in a table.

Just highlight your table, go to the Data tab, click onForecast 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 changeother options such as the confidence level of your data, change the range ofyour 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 overany spot on the data and you can see values at different periods of time.

Soif you want a quick representation of a trend line and your data use ForecastSheet.

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 rightnow to subscribe to Sele Training and click the Bell icon to get notified whennew videos are released.

Don't forget the entire 50 tips andtricks list is in the description of this video with hyperlinks to each.

Nowlet's get back to it.

#26 SUMIFS().

The SUMIFS() function allows you to doconditional summing of data.

In this example, I have a set of data for monthsand items and the amount of each and I want to create a sum of January.

So I canuse the SUMIFS() function.

The first parameter it wants is the range andthat's going to be this amount right here.

Then it wants to know the criteriarange so we want the month where it equals January and that gives us a 128total, which is the total of these three entries.

Now for this SUMIFS() we want toadd 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 stillgoing to be the month equaling March with the second criteria where the itemequals Item 2, and that gives you the sum 245, which is the total of thesethree 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 sothat it takes the Total divided by the number of Quantity and gives me theprice Each.

This formula works fine except when I get down here to thequantity zero and I end up with a divided by zero error.

You can clean thisup with the IFERROR() function.

Just add the IFERROR() function in front of thisformula and if there is an error let's just say zero instead.

Now when I copythat down it corrects our divide by zero error.

So if you want to remove thoseerrors 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 aset of data.

If you hold the Ctrl key down and hit the right-arrow it goes tothe very end of the row, down-arrow goes to the bottom, left-arrow to the left, andup-arrow to the top.

This is a quick method to use to scroll around a tableof data.

#29 Filled Maps.

You can use Filled Maps to make ageographic chart of your data.

Make sure you have location specific informationhere.

It can be a state, a city, a zip code, GPS coordinates, or any other locationrelated 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 cancustomize it using a variety of different settings.

You can change thedata labels, pick a different chart style, change thelayout, and even change the color scheme.

#30 PMT().

The payment function isuseful 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 usingsixty months of payments on a loan amount of $20, 000.

In order to calculatethe monthly payment for this amount we're going to enter PMT(), pick the rateas the first parameter, and the key here is you need to divide this by 12 to getit into monthly periods instead of a total annual rate.

Then the number ofperiods is going to be 60 on a total amount of 20, 000.

There are otherparameters like Future Value for more complex calculations, but we won't usethose at this time.

End-parentheses on that and Enter and we get a monthlypayment 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 howyou want to use the data.

So if you intend to do any loan payments you'reprobably going to use the PMT function.

#31 Show Formulas.

When workingwith formulas in Excel it's sometimes helpful to see the formulas in anunderlying cell.

You can do that by double-clicking on a cell with a formulain it.

It brings up the formula and it shows color codes for the cells that it'sreferring to.

Another option is to go to File, Options, Advanced and scroll down toDisplay Options for this Worksheet.

Check this box to Show Formulas in Cells andhit OK.

That brings up all the formulas for all the cells in your sheet or youcan use the Ctrl-~ shortcut to turn it on and off.

These options make iteasy to see formulas at a glance.

#32 Advanced Select.

Here's a trick to making changes to multiple items all at once.

Come up toFind and Select, choose Find, type in something you want to search for, andselect Find All, hit Ctrl-A to select everything in this list, and ithighlights all of those items on the list.

Now you can close the search and itstill leaves them selected and at this point you can make changes.

Anything youdo will change all of those entries the same way.

That's just a shortcut to makechanges 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 thisname box in the upper left and give it a name.

That gives you the ability to referto it by that name anywhere in a formula on your spreadsheet, but what you may notbe aware of is if you have a wide area of named ranges you can select theentire list all at once, go to Formulas, choose Create from Selection, and checkthis 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 trickto name multiple ranges.

#34 Hide Cells.

There's a slick trick to hidingcells.

In this example, I have 4 times 5 equals 20 in a formula, but let's say wewanted 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 cellfrom view, but it still works both in the formula and its contained in that celleven though it's not visible.

To turn this off, go back to the same locationagain and remove the three semicolons.

A quick trick if you just want to hide acell.

#35 COUNTBLANK().

The COUNTBLANK() function is useful to count the numberof blanks in a range of cells.

Just type in COUNTBLANK() and put in the range thatyou want to count.

It shows 6 empty cells.

You can also use COUNTA() in thesame range to count the number of cells that are not empty.

#36 Natural Language Query.

We've seen previously how you can click on achart and go to the Ideas tab to see recommended charts of the data, but thereis a Natural Query area here as well.

You can type in things like Total Sales byCategory 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 CommonProduct, or Products that Start with 'S'.

Add to it the Total Sales of thoseproducts.

Natural Query Language uses artificial intelligence to analyze yourquestion and provide the data in the best format possible.

You can even seeit's question analyzed and the answer that it determines from your question.

It's very powerful.

#37 Goal Seek.

Goal Seek is anadvanced function in Excel that is part of the what-if analysis tools.

In thisexample, 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 knowhow 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 thingsrandomly until you reach your total of 6, 000 but GoalSeek will calculate this number for us.

To start a Goal Seek you need to clickon the target number.

This is the number that we want to change until we reachthis target of 6000, then go to Data What-if Analysis, and choose Goal Seek.

Itset our target as the Set Cell.

The value we want it to go to is going to be our6000 and what we want to change in order to reach that target is this cell righthere – the quantity.

Hit OK and it does the goal seek and determines that 196.

9is the quantity.

And that's just one example of Goal Seek but be aware youcan use your imagination to come up with all kinds of scenarios for the what-ifanalysis using Goal Seek.

#38 Insert Screenshot.

Here's a tip to adding images from other applications.

Go to Insert, select Screenshot and it showsthe open windows that you have maximized on your computer.

Select one and itinserts that image of that application in Excel.

You can then resize and move itaround however you want, and do the same with multiple images.

You can also insertjust a portion of your screen by using the Screen Clipping.

So if you want toadd an image from another application rather than doing copy and paste you caninsert it directly.

#39 Power Pivot.

Power Pivot is a free feature that'sinstalled as an add-in to Excel but it's not installed by default.

To enable it goto File, Options, Add-ins.

Come down here to the bottom and select COM Add-insfrom the list and hit Go.

Make sure to check this box for Microsoft Power Pivotfor Excel.

Hit OK and it adds this Power Pivot option to your menu.

When you openthat there's a whole new set of features available from the menu.

Power Pivotallows 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 limitof Excel to virtually unlimited numbers.

It turns Excel into a BusinessIntelligence Analysis tool.

I won't show you the details in this video but take alook at Power Pivot if you deal with large sets of data from a lot ofdifferent sources.

#40 3D Maps.

You can create stunning three-dimensionalmaps of your data.

In this example, I have a table of countries with variousamounts 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 representationof the Height on the globe.

I'm also going to pick a Timeline for the year, andit immediately creates a timeline on the chart, and I can adjust this and itreflects the data over that time-frame.

Let's position this to a differentlocation.

Now I can choose different themes but we'll stick with the default.

You canalso add labels which shows the country names, I can flatten the map or leave itas a globe, you can also find a location and it takes you to that location onyour map.

For the layer options let's change the thickness so it widens outthe data elements on the screen and we'll make the height a little bitlarger.

All the changes I make are recorded to this tour and if I click onthe settings I can give it a name, change the duration, or the amount of timebetween each transition.

I'm also going to set this one to rotate the globe.

Nowlet's play the tour and see what it looks like.

It moves while it's updatingthe data elements.

You can even choose a different location to begin your tour.

You can create a video from this andchoose from a variety of different resolutions, or you can capture screen andthen paste it back into your chart.

It also creates a note here that there is a3D Map tour associated with this workbook.

You can delete this but that'sthe only way you'll know there's a 3D map linked and your indication that youcan come back in here, click on 3D Maps, and you can see your tour that youcreated.

You can create multiple tours or you can select one that you are alreadycreated in the past and edit it however you like.

3D Maps is a great way to makevisually stunning 3D images of your data.

#41 ISBLANK().

Earlier we looked at how you can count blank cells but another usefultool is the ISBLANK() function.

ISBLANK() returns true if a cell is empty andfalse if it contains any data.

So if we check column C to see if it's blank, it'strue for this row.

You can combine it with the IF() statement to make it evenmore powerful.

You can use the ISBLANK() function anywhere there's a formula witha true/false value.

#42 Analysis ToolPak.

Excel has an advanced hiddenmenu option available called Analysis Toolpak.

You can access it bygoing to File, Options, Add-ins, and under the Excel add-ins hit Go, and check thisbox for Analysis Toolpak.

Hit OK and it creates a new menu under your Data tabcalled Data Analysis.

When you click that it brings up a menu with a number ofcomplex data analysis tools like Correlation, Exponential Smoothing, Moving Averages, and a variety of other options.

When you select one it prompts you forthe input variables and output range for the calculation.

Enter the pertinentinformation and hit OK and you get your data analyzed and the resultsoutput to your spreadsheet.

I won't go into details but just be aware that theData Analysis ToolPak is available by turning it on from the add-ins menu.

#43 CONVERT().

The CONVERT() function is useful to convert data from onemeasurement into another.

For example, let's type in CONVERT() and we're going tochoose this original data value.

It brings up a list of the options we havefor conversion so let's take Days, hit Comma and then it gives me only thechoices of values that I can convert days into, and let's pick Hours.

Andthat'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 intoExcel? For example, let's go to a web page called x-rates.

This website provideslive exchange rates for various currencies.

I'm going to take a look atthe US dollar conversion rates in the rate tables.

Now that I have this tableinformation 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 tothat web page, connects to the data, and brings up the different tables ofinformation available.

Now you can look through these tables to find one thatlooks like what you want and we'll take this table 1, which has a variety ofdifferent currencies against the US dollar.

I'm going to load that table.

Itestablishes a query and connection to that table on the webpage and it pullsmy data into the spreadsheet.

I can now reference this information just like anyother table in Excel.

Now that we have this data let's go up here to theRefresh menu and go to Connection Properties.

I can set this to RefreshEvery 60 Minutes or whatever time I like, and I can Refresh the DataWhenever 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.

Justgo to Insert and select this icon for People Graph.

That brings up a defaultgraph.

The first thing you need to do is Select the Data and choose a two-columntable.

The second column needs to have values that you can display in yourgraph.

Click Create and you can also come in here and change the title.

And thatdisplays a graph of your data.

You have multiple options available from theSettings.

You can change the Type of the graph, the Theme, and even the Shape ofthe items that show up on the graph.

Over the right-hand corner you can mark thisas a Saved Image and now it's locked so that you can't make changes anymore.

Youcan now resize and move it.

Click again in the upper-right corner and you canturn 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 suchas this list of states.

Let's enter the function and we'll choose the entiretable.

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 itdefaults to ascending order but you can put a -1 in here if you want toreverse it, and the final option is true or false whether you want to sort bycolumn or sort by row.

It defaults to row but column would be useful if you had ahorizontal list.

We'll choose the default and hit Enter and it sorts all thosestates alphabetically.

We can go back and change the sort_index to 2and then it sorts by the amount in the second column, and you can see it'ssorted in the second column now.

Dynamic Arrays will spill over into multiplecolumns and rows as it needs to fill the data, so even though this formula is inthe upper-left corner, you'll see that it's grayed out but spills over into theother columns and rows.

Just be aware these dynamic array functions are onlyavailable in the 2020 version or newer and will not function if you open aspreadsheet 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 yourspreadsheet and it contains information that's useful while you're working.

Forexample, if we highlight this list of numbers, down at the status bar it showsthe 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 themaximum 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 ifyou 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 butthere's a quick method to insert multiple rows.

Just select as many as youwant, right-click anywhere in that group, click Insert, and it inserts all of thoserows.

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 agiven position or index.

For example, if we wanted to choose the second item fromthe list of Blue, Red, and Green it returns Red, which is the second itemin the list.

Instead of explicitly listing the values you can select themfrom a cell location.

So let's pick the second item from this, this, or this valueand 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 rangebut watch what happens when we do that.

Let's choose the second value from thisrange.

Yyou might think it's going to return Washington but it just gives youan error and the reason why is because it treats the range all as one value.

Ifwe 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 valuewhich is that first range or we can return the second value which is thesecond range.

So it will work with ranges but it treats them all as one value.

Youcan make the position or index number a variable as well.

We'll put in 1 uphere and then we choose the position number 1 from these choices and ituses that cell to trigger which index number to select.

So if you're lookingfor 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 in2020.

It returns a unique set of values from a list.

For example, if we do aUNIQUE() of this list right here, it returns the four unique values from thatlist and ignores the duplicates.

The UNIQUE() function has a couple ofadditional parameters.

The first one is by column and you would use true here ifyou intend to do a horizontal list but it defaults to false, and the second parameter is exactly_once.

If this is false it returnsevery distinct item from the list which is the default.

If it's trueit returns items that appear exactly one time and, in this example, Pear is theonly item that shows up exactly once in the list.

As with any dynamic arrayfunction 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 soremember this if you have users with old versions of Excel because it won'tfunction correctly.

Hey, if you want to see more videos like this one pleasesubscribe, and if you've enjoyed this video be sure to click the thumbs-up andleave a comment.

I really do appreciate your support!.

Leave a Reply

Your email address will not be published. Required fields are marked *