There are several new features available in Excel for Office 365. Here are the top
10. Data Types. You can reference geography and stock data in Excel using
Data Types. For example, take a list of countries and highlight the list. Go to
the Data tab and you'll see the Data Types group. Click on Geography. You'll
notice it creates an icon on the left of each country that is a data card so I'm
going to click on the show card for the United States and you can see a set of
data here for all kinds of statistics about that country and at the very
bottom it tells you the location where the data is coming from. This is a linked
reference to that data source and will stay updated any time you refresh the
data in the spreadsheet. Now it's a good idea to highlight the list and go to
Insert and create a table. Now that you've created a table you can add
columns and select data elements from that card list. It pulls up the data for
the capital. Click another data card. We can pick the GDP. How about another data card
for area. For Geographic data this can be a country, it can be a city, state,
territory, province, or other geographic information but not only can you do
geographic information, you can also do stock data. So let's take a look at that.
Let's do the same thing with this data. Highlight it. Pick Stocks from the Data
Types group. We'll also come back and highlight the group and change it to a
table.
Now you'll notice that with the data type for stocks it actually found the
stock symbol and filled it in but now I can pick from the list and choose the
52-week high. Let's take the price and you'll notice up here on the tab there
is a refresh option for external data. It does that automatically for table design
so I can refresh and it will update these prices real-time. You can even
reference this data anywhere else in your spreadsheet. For example, if I want
to look up Amazon's high for the day I can put in equals, select Amazon, put a
period in, and then look at the high and you have the daily high for today. These
data types are very handy and you can expect that over time Microsoft will add
additional data types to the list with linked data to their source. Recommended
Pivot Tables. Pivot tables are useful to summarize sets of data. Excel now has a
Recommended Pivot Table option that gives you a jump start on common pivot
table designs. Just click anywhere in the table and go to the Insert tab and
select Recommended Pivot Tables from the list. It pops up a variety of pivot table
designs based on the data. You can see that it has the sum of total by region.
Sum of total by rep. Sum up total by product and all you have to do is select
the one that makes the most sense. Hit OK and it creates a new sheet with your
pivot table and now you can edit the pivot table and make adjustments however
you want. The recommended Pivot Table gives you a quick way to create standard
types of pivot tables without having to manually create them from scratch and is
available from the menu with one button. XLOOKUP Function. XLOOKUP is an
improvement over VLOOKUP and HLOOKUP functions. It's the most anticipated
new feature for Excel in 2019. XLOOKUP allows you to find data in a list
and return values from the associated column or row. Let's say we want to do an
XLOOKUP to find the name Sally in this list and return the values from this
list. You can see that it found Sally and returned 540 dollars.
That's very similar to the VLOOKUP function but the nice thing about
XLOOKUP is you can do a horizontal lookup as well. So let's find
product in this list and return the values from the row for Sally and you
can see it shows A1 which is the intersection of product and Sally.
Another feature of XLOOKUP that is not available with VLOOKUP is doing negative
columns. So let's say we wanted to find A1 in this list but we want to return
the value for the rep from this list. It finds the first A1 and returns the
value Jim. That goes back 2 columns which is not something you can do with
the VLOOKUP function. Another feature of XLOOKUP,
let's look up Sally again in this list but the value we're going to return is
the entire list. You can see what it did is it found Sally in the list and
returned the entire row of data and it actually spans over multiple columns so
that's one of the benefits of the XLOOKUP function. It can replace the INDEX/MATCH
combination as well as HLOOKUP and VLOOKUP and from this point
forward with Excel you shouldn't need to use any of those other functions. Just
use XLOOKUP for everything. This is just a quick summary of the XLOOKUP function.
If you want a deeper dive make sure to take a look at the Excel XLOOKUP video
dedicated to just that topic. Ideas. Excel can analyze your data to detect trends
and patterns with Ideas and it's super simple to use. Just click inside of
a table of data that you have and on the Home tab click this lightning bolt for
Ideas. It brings up a task pane with a bunch of recommended summaries, rankings,
and correlations and these are very interesting. This one shows the amount by
product. This one finds that there is a quantity and total correlation which
makes sense because quantity times the amount is the total but it gives you a
nice graph to represent that data correlation. It notices for product B2
George and John have a noticeably higher totals. You can see down here
there are 32 results so let's show more.
It detects that Tom's region for South has the majority of the total dollars
and the majority of quantities. You can get an idea of all the different things
that it can detect from your data. Pick one that you like and insert a pivot
chart into your spreadsheet. It brings up an idea sheet and inserts the pivot
table with the chart. Ideas uses AI to look for patterns in your data that show
outliers, trends, higher rankings, or correlations worth highlighting. The more
detailed real-world data you provide the more interesting the results. Funnel
Charts. Newly added for 2019 is Funnel Charts. If you have data that fits nicely
into a funnel chart just click somewhere in the data, go to the Insert tab, and in
the Charts Group click the arrow and scroll down until you see a funnel chart.
Hit OK and you just created a funnel chart from your data. It works best if
you sort the data so that you get the traditional funnel look and then once
your chart is created you can use all the normal editing options for charts
including selecting standard layout and adjusting colors and
fonts within the chart as you need. It's as simple as that.
Get Add-ins. All Office 365 apps now have a new option called Get Add-ins. You go
to the Insert tab and click on this Get Add-ins button. It's not the same thing
as the old File, Options, Add-ins where you load an add-in file into Excel.
Instead, when you click the Get Add-ins button it brings up the Office Add-ins
window where you'll find thousands of add-ins available for Excel. Some of them
are similar to previous ones like Solver. This was available as an add-in to Excel
previously but now when you click Add it lets you install Solver directly into
Excel. The Get Add-ins gives you thousands of free and purchased add-ins
to install in Excel. Be sure to check out my Top 10 Free Excel Add-ins video when
it becomes available. SWITCH Function. The new SWITCH function switches one value
for a corresponding value in a list. It's a quick way to substitute values without
having to create tables or complex IF statements. For example, if we have some
dates we can use the switch function to take the weekday
of that date and look it up in a list. If it's a weekday with 1 then we want it
to be Sunday. If it's a weekday 2, then we want it to be Monday. 3, Tuesday and
if there is no matching value then we'll just say No Match and as you can see it
detected Monday, Tuesday, and we didn't do Wednesday so there's no match. You can
use up to 126 values in the list to handle just about any complexity. Live
Collaboration. One of the great work team features available in Excel for Office
365 is Live Collaboration. It allows you to share a spreadsheet with other team
members and see each other's changes in real-time. All you do is click on the
Share button in the upper right corner and enter the name of the person that
you want to send it to or an email address of somebody that you want to
forward the email. You can also click this Copy Link option and copy that link
and send it to somebody via email. You can choose the option to allow them to
edit or turn that off and you can set an expiration date and a password. Once
that's done, you apply that setting and send the link. The other person opens up
the email and you can see a cursor where they're working on your spreadsheet. They
change the values those, values change on your screen as well. Hover over their
colored cell location and you can see the name of the person that's doing the
collaboration with you. You can share with as many people as you want to and
have everyone working on the same spreadsheet at the same time. 3D Maps.
Take your location related data to the next level using 3D Maps. Let's say we
want to map the population for all US states. I have a table with this data.
Click anywhere in the table, go to Insert and choose 3D Map,
then Open 3D Maps. It brings up a 3D map of the world and adds the state field to
the location for you automatically. We're going to take the Population and put it
in the Height field, and now we can zoom in to parts of this map and see the data
up close. You can also type in a Find Location and it'll take you directly to
that particular State.
You can make it a Flat Map or switch it back to 3D. You can turn on Map Labels
which will show all the state names. You can also refresh the data if there's
changes to the numbers, and you can choose from different chart types. Bubble,
Heat map, or Region to get the look the way you want it. All this information is
collected into a video tour of your 3D map. Combine it with time-based changes
to your data and you can watch data grow or shrink based on those changes. Once
everything's set, you can close this window. It adds a 3D map tours
notification and reminds you that you can click on the 3D maps in order to
view your tours. When you do, it lists all the tours you have available in the
spreadsheet and you can select the one that you want to play. And, as expected,
the more data you provide the more detailed 3D maps you can create. Natural
Language Query. Microsoft just announced the addition of a natural language query.
This new feature will be added to the Ideas window in Excel. You'll be able to
type a question asking Excel about your data and it will provide a chart of the
results. It's so new I still don't even have the option available in my preview
version of Excel but it's coming in 2020. You'll see it here under the Ideas and
there'll be an area right here where you can enter your question. Stay tuned for
more. Hey, if you want to see more 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!