Top 10 Excel New Features

Aug 14, 2023

Top 10 Excel New Features

Check out the top 10 newest features of Excel for Office 365. This includes features added in 2019 as well as a sneak peak at the new Natural Language Query coming in 2020.

Support me with your Amazon purchases, click here: https://amzn.to/2Kxp533

Contents 0:00
1-Data Types 0:12
2-Recommended Pivot Tables 2:56
3-XLOOKUP Function 3:58
4-Ideas 6:11
5-Funnel Charts 7:55
6-Get Add-ins 8:40
7-SWITCH Function 9:41
8-Live Collaboration 10:42
9-3D Maps 12:00
10-Natural Language Query 13:57

#seletraining #excel #tipsandtricks

Content

0.03 -> There are several new features available in Excel for Office 365. Here are the top

5.43 -> 10. Data Types. You can reference geography and stock data in Excel using

16.92 -> Data Types. For example, take a list of countries and highlight the list. Go to

21.869 -> the Data tab and you'll see the Data Types group. Click on Geography. You'll

28.47 -> notice it creates an icon on the left of each country that is a data card so I'm

34.05 -> going to click on the show card for the United States and you can see a set of

38.19 -> data here for all kinds of statistics about that country and at the very

43.05 -> bottom it tells you the location where the data is coming from. This is a linked

47.85 -> reference to that data source and will stay updated any time you refresh the

52.23 -> data in the spreadsheet. Now it's a good idea to highlight the list and go to

57.87 -> Insert and create a table. Now that you've created a table you can add

65.309 -> columns and select data elements from that card list. It pulls up the data for

73.409 -> the capital. Click another data card. We can pick the GDP. How about another data card

81.09 -> for area. For Geographic data this can be a country, it can be a city, state,

88.979 -> territory, province, or other geographic information but not only can you do

93.54 -> geographic information, you can also do stock data. So let's take a look at that.

99.32 -> Let's do the same thing with this data. Highlight it. Pick Stocks from the Data

104.34 -> Types group. We'll also come back and highlight the group and change it to a

109.799 -> table.

113.88 -> Now you'll notice that with the data type for stocks it actually found the

118.66 -> stock symbol and filled it in but now I can pick from the list and choose the

124.48 -> 52-week high. Let's take the price and you'll notice up here on the tab there

133.51 -> is a refresh option for external data. It does that automatically for table design

139.93 -> so I can refresh and it will update these prices real-time. You can even

146.47 -> reference this data anywhere else in your spreadsheet. For example, if I want

150.67 -> to look up Amazon's high for the day I can put in equals, select Amazon, put a

158.26 -> period in, and then look at the high and you have the daily high for today. These

165.79 -> data types are very handy and you can expect that over time Microsoft will add

170.98 -> additional data types to the list with linked data to their source. Recommended

177.31 -> Pivot Tables. Pivot tables are useful to summarize sets of data. Excel now has a

183.73 -> Recommended Pivot Table option that gives you a jump start on common pivot

187.959 -> table designs. Just click anywhere in the table and go to the Insert tab and

193.959 -> select Recommended Pivot Tables from the list. It pops up a variety of pivot table

199.959 -> designs based on the data. You can see that it has the sum of total by region.

206.64 -> Sum of total by rep. Sum up total by product and all you have to do is select

214.239 -> the one that makes the most sense. Hit OK and it creates a new sheet with your

220.81 -> pivot table and now you can edit the pivot table and make adjustments however

225.22 -> you want. The recommended Pivot Table gives you a quick way to create standard

231.19 -> types of pivot tables without having to manually create them from scratch and is

235.18 -> available from the menu with one button. XLOOKUP Function. XLOOKUP is an

241.42 -> improvement over VLOOKUP and HLOOKUP functions. It's the most anticipated

245.86 -> new feature for Excel in 2019. XLOOKUP allows you to find data in a list

251.38 -> and return values from the associated column or row. Let's say we want to do an

256.54 -> XLOOKUP to find the name Sally in this list and return the values from this

267.91 -> list. You can see that it found Sally and returned 540 dollars.

275.17 -> That's very similar to the VLOOKUP function but the nice thing about

279.52 -> XLOOKUP is you can do a horizontal lookup as well. So let's find

283.6 -> product in this list and return the values from the row for Sally and you

292.93 -> can see it shows A1 which is the intersection of product and Sally.

298.36 -> Another feature of XLOOKUP that is not available with VLOOKUP is doing negative

303.25 -> columns. So let's say we wanted to find A1 in this list but we want to return

312.1 -> the value for the rep from this list. It finds the first A1 and returns the

317.89 -> value Jim. That goes back 2 columns which is not something you can do with

323.89 -> the VLOOKUP function. Another feature of XLOOKUP,

326.919 -> let's look up Sally again in this list but the value we're going to return is

334.24 -> the entire list. You can see what it did is it found Sally in the list and

340.81 -> returned the entire row of data and it actually spans over multiple columns so

346.51 -> that's one of the benefits of the XLOOKUP function. It can replace the INDEX/MATCH

351.13 -> combination as well as HLOOKUP and VLOOKUP and from this point

355.33 -> forward with Excel you shouldn't need to use any of those other functions. Just

359.14 -> use XLOOKUP for everything. This is just a quick summary of the XLOOKUP function.

363.94 -> If you want a deeper dive make sure to take a look at the Excel XLOOKUP video

368.68 -> dedicated to just that topic. Ideas. Excel can analyze your data to detect trends

376.599 -> and patterns with Ideas and it's super simple to use. Just click inside of

381.99 -> a table of data that you have and on the Home tab click this lightning bolt for

387.63 -> Ideas. It brings up a task pane with a bunch of recommended summaries, rankings,

395.19 -> and correlations and these are very interesting. This one shows the amount by

401.01 -> product. This one finds that there is a quantity and total correlation which

409.14 -> makes sense because quantity times the amount is the total but it gives you a

414.66 -> nice graph to represent that data correlation. It notices for product B2

422.28 -> George and John have a noticeably higher totals. You can see down here

428.25 -> there are 32 results so let's show more.

433.67 -> It detects that Tom's region for South has the majority of the total dollars

440.1 -> and the majority of quantities. You can get an idea of all the different things

446.34 -> that it can detect from your data. Pick one that you like and insert a pivot

451.38 -> chart into your spreadsheet. It brings up an idea sheet and inserts the pivot

459.45 -> table with the chart. Ideas uses AI to look for patterns in your data that show

465.51 -> outliers, trends, higher rankings, or correlations worth highlighting. The more

470.37 -> detailed real-world data you provide the more interesting the results. Funnel

476.13 -> Charts. Newly added for 2019 is Funnel Charts. If you have data that fits nicely

483.06 -> into a funnel chart just click somewhere in the data, go to the Insert tab, and in

489.99 -> the Charts Group click the arrow and scroll down until you see a funnel chart.

496.47 -> Hit OK and you just created a funnel chart from your data. It works best if

502.59 -> you sort the data so that you get the traditional funnel look and then once

507.45 -> your chart is created you can use all the normal editing options for charts

511.79 -> including selecting standard layout and adjusting colors and

516 -> fonts within the chart as you need. It's as simple as that.

519.89 -> Get Add-ins. All Office 365 apps now have a new option called Get Add-ins. You go

528.27 -> to the Insert tab and click on this Get Add-ins button. It's not the same thing

533.94 -> as the old File, Options, Add-ins where you load an add-in file into Excel.

540.69 -> Instead, when you click the Get Add-ins button it brings up the Office Add-ins

546.33 -> window where you'll find thousands of add-ins available for Excel. Some of them

552.24 -> are similar to previous ones like Solver. This was available as an add-in to Excel

558.83 -> previously but now when you click Add it lets you install Solver directly into

565.83 -> Excel. The Get Add-ins gives you thousands of free and purchased add-ins

573.09 -> to install in Excel. Be sure to check out my Top 10 Free Excel Add-ins video when

578.67 -> it becomes available. SWITCH Function. The new SWITCH function switches one value

585.84 -> for a corresponding value in a list. It's a quick way to substitute values without

591.24 -> having to create tables or complex IF statements. For example, if we have some

596.91 -> dates we can use the switch function to take the weekday

603.92 -> of that date and look it up in a list. If it's a weekday with 1 then we want it

610.279 -> to be Sunday. If it's a weekday 2, then we want it to be Monday. 3, Tuesday and

620.589 -> if there is no matching value then we'll just say No Match and as you can see it

628.49 -> detected Monday, Tuesday, and we didn't do Wednesday so there's no match. You can

635.42 -> use up to 126 values in the list to handle just about any complexity. Live

643.459 -> Collaboration. One of the great work team features available in Excel for Office

649.43 -> 365 is Live Collaboration. It allows you to share a spreadsheet with other team

654.92 -> members and see each other's changes in real-time. All you do is click on the

660.889 -> Share button in the upper right corner and enter the name of the person that

665.269 -> you want to send it to or an email address of somebody that you want to

671.54 -> forward the email. You can also click this Copy Link option and copy that link

678.05 -> and send it to somebody via email. You can choose the option to allow them to

682.76 -> edit or turn that off and you can set an expiration date and a password. Once

689.72 -> that's done, you apply that setting and send the link. The other person opens up

698.329 -> the email and you can see a cursor where they're working on your spreadsheet. They

702.589 -> change the values those, values change on your screen as well. Hover over their

708.41 -> colored cell location and you can see the name of the person that's doing the

711.829 -> collaboration with you. You can share with as many people as you want to and

716.269 -> have everyone working on the same spreadsheet at the same time. 3D Maps.

722.319 -> Take your location related data to the next level using 3D Maps. Let's say we

728.029 -> want to map the population for all US states. I have a table with this data.

732.8 -> Click anywhere in the table, go to Insert and choose 3D Map,

739 -> then Open 3D Maps. It brings up a 3D map of the world and adds the state field to

746.36 -> the location for you automatically. We're going to take the Population and put it

750.83 -> in the Height field, and now we can zoom in to parts of this map and see the data

759.5 -> up close. You can also type in a Find Location and it'll take you directly to

765.86 -> that particular State.

770.35 -> You can make it a Flat Map or switch it back to 3D. You can turn on Map Labels

779.18 -> which will show all the state names. You can also refresh the data if there's

783.77 -> changes to the numbers, and you can choose from different chart types. Bubble,

790.75 -> Heat map, or Region to get the look the way you want it. All this information is

798.02 -> collected into a video tour of your 3D map. Combine it with time-based changes

803.24 -> to your data and you can watch data grow or shrink based on those changes. Once

807.83 -> everything's set, you can close this window. It adds a 3D map tours

811.94 -> notification and reminds you that you can click on the 3D maps in order to

816.41 -> view your tours. When you do, it lists all the tours you have available in the

824.45 -> spreadsheet and you can select the one that you want to play. And, as expected,

832.31 -> the more data you provide the more detailed 3D maps you can create. Natural

837.98 -> Language Query. Microsoft just announced the addition of a natural language query.

843.47 -> This new feature will be added to the Ideas window in Excel. You'll be able to

848.69 -> type a question asking Excel about your data and it will provide a chart of the

852.86 -> results. It's so new I still don't even have the option available in my preview

857.72 -> version of Excel but it's coming in 2020. You'll see it here under the Ideas and

864.37 -> there'll be an area right here where you can enter your question. Stay tuned for

869.45 -> more. Hey, if you want to see more like this one please subscribe and if

874.29 -> you've enjoyed this video be sure to click the thumbs-up and leave a comment.

877.83 -> I really do appreciate your support!

Source: https://www.youtube.com/watch?v=SxNStzkzSY8