Microsoft has released so many new powerful
Excel features over the past few months. In this video, we're going to look at the top five
new Excel features you need to know. Let's get started. [Music] First up is one of my favorite new features: Checkboxes. And I know what you might be thinking, "Doesn't Excel already have checkboxes?" Technically, yes, you can insert a checkbox
form control under the Developer tab, but these new checkboxes
are much easier to work with and we can do so much more with them
beyond checking off a task. Let's look at an example. Here we have a basic to-do list and want to add
checkboxes to check off each task once it's complete. To insert the new checkboxes, all you have to do is select the cells that you
want to add a checkbox to, navigate to the Insert tab, select the Checkbox command and a checkbox is automatically
inserted in each cell. You can also format these checkboxes
using the Home tab of the ribbon. You can update the color of the checkboxes
using the color dropdown, make them larger or smaller
using the font size dropdown, and realign them using the alignment commands. Now let's take a closer look
at what happens when we check off a task. If you look in the formula bar, the value of the cell is automatically set to "TRUE"
when the checkbox is checked, or "FALSE" if it is unchecked. This is so valuable because it allows
us to reference the checkboxes in formulas and calculate values depending on
whether a checkbox is checked or not. For example, if we want to count the number of tasks
we've completed in this list we can use the COUNTIF function to count
the number of checkboxes checked by counting the number of cells
that are equal to TRUE. All we have to do is enter the COUNTIF function, select the checkboxes as the range argument, and then enter TRUE as the criteria argument. Now we can enter the function to count the number
of checked check boxes and every time we check off a task, the calculation is automatically updated. This is just scratching the surface of what you
can do with these new check boxes, so if you want to learn more about their capabilities, make sure to check out my full YouTube tutorial linked below. Next up, we have the GROUPBY and PIVOTBY functions. GROUPBY and PIVOTBY are two new functions
that Microsoft just released to make summarizing data easier than ever. They may have even replaced pivot tables but I'll let you decide that for yourself. Let's take a look at an example. Here we have sales data
and want to summarize it by region. Instead of creating a pivot table, we can quickly summarize the data using the new GROUPBY function. To do this, enter the GROUPBY function and then we need to select the field that we want to summarize the data by as the row fields argument. We want to summarize sales by region, so I'm going to select the region column. Next up, we need to enter the values argument. The values argument is the data you want to summarize in the table. We want to summarize sales, so I'm going to select the sales column as the values argument. Finally, we need to enter the function argument which is how we want to aggregate the data. We need to enter SUM to calculate the sum of sales for each region but there are many other calculation options to choose from. Now that we've entered
all the mandatory arguments, we can enter the function and we've officially summarized sales by region. The PIVOTBY function works the same except it allows you to add a field in the columns of your summary table to drill down into your data further. For example, if we wanted to summarize sales by region, broken down by year, we could utilize PIVOTBYs col field argument to add year columns to our summary table. So I'm going to go ahead and update the function to PIVOTBY and enter the year column as a col field argument. Now we can enter the function and we've officially created a formula-built pivot table that summarizes sales by region, by year. Both of these functions also include
multiple optional arguments that allow you to customize your summary table so if you want to dive deep into these
functions full capabilities, check out my full PIVOTBY YouTube tutorial linked below. Number three is the Data from Picture tool. Data from Picture allows you to import data
from a picture into Excel. For example, our boss sent us this picture of this data and asked us to enter it into Excel for analysis. Instead of manually entering this data we can use the Data from Picture tool to
import the data directly into our workbook. To do this, select the cell
where you want to insert the data, go to the Data tab, open the From Picture dropdown and choose Picture From File. Now we just need to select the image
that contains the data we want to import and then hit Okay. From here, the Data from Picture window will pop up
with a preview of the data. Once we've reviewed the data for any
potential errors, we can insert the data into the worksheet
by pressing the Insert Data button. The next feature allows us to
insert an image directly inside of a cell. For example, here we have a list of employees and we need to insert a picture
of each employee next to their name. Instead of inserting the image
and trying to resize it to fit in the cell, we can now insert the image
into the cell. So to add Justin Bieber's image to the table, all we would have to do is select
the cell that we want to insert the image into, navigate to the Insert tab, open the Pictures dropdown, and now you'll see the new
Place in Cell option listed here. Once we select Place in Cell,
we just need to locate the image. In this case, the image is located on my device so I'm going to click This Device,
select Justin's image and then hit Okay to insert the image into the cell. Now we can write formulas
that directly reference this image, we can view a preview popup of the image and the image automatically resizes
when we adjust the size of the cell. If you change your mind and want
this image back over the cells, all you have to do is click the Place over Cells button. Similarly, you can move a picture over cells into a cell by right clicking the image and selecting Place in Cell. Last but not least, we have the new Navigation Pane. The Navigation Pane is like a workbooks
table of contents on steroids making it easier than ever to locate and manage elements within a workbook. For example, here we have a workbook with all of these tabs and each tab contains
different elements such as tables, charts, pivot tables, images, etc. Let's take a look at how we
can use a Navigation Pane to help us navigate the workbook. To open the Navigation Pane, go to the View tab and select Navigation. Now we can easily view
all of the worksheets in the workbook and if you open a worksheets dropdown you can see all of the elements within the worksheet. You can jump to any of these elements in the workbook by simply selecting them in the Navigation Pane. This is so much more efficient
than scrolling through all the tabs. Just when you thought it couldn't
get any better, it does. You can also search for
what you're looking for in the search bar instead of going through all the worksheets listed. For example, if we want to open the Sparklines tab, all we would have to do is search "sparklines" and then just select the worksheet. To be even more efficient, you can add the Navigation Pane
to your status bar by right clicking the
status bar and selecting Sheet Number. Now we have a sheet count in the status bar and we can select the sheet number
to automatically open the Navigation Pane. To wrap up, we learned the top five
new Excel features that Microsoft has released in the last few months. Which new feature is your favorite? Let me know in the comments below. As always, if you found this video helpful make sure to subscribe to our channel for more. [Music]
