Excel 2016 - Quickly enhance your results

Time Length Icon 45:00 Video Icon 111 videos
Like Icon
Button

Quickly enhance your results: Mouse over a table, a column or a cell, and view all automatic and/or conditional formatting

Teaching Format:

  • Remote e-learning training session
  • Interact live with your trainer throughout the e-learning session. Ask questions and discuss points as they arise

Training aims:

  • Save time on a daily basis
  • Use the new features of Excel 2016 to improve the appearance of your table 

Training content:

  • Tables in Excel 2016 (creation mode)
  • Automatic formatting
  • New formula references in table area
  • Conditional formatting
  • Insert and personalize a Sparkline
  • Create and customize a graph
00:00:10
Hello and welcome to this Office 365 MOOC training.
00:00:14
The topic of this session will be to see how to quickly enhance your results in an Excel worksheet.
00:00:23
Welcome to this session my name is Charles Bonachera and we're going to spend the next 30 to 45 minutes together.
00:00:35
The plan: What are we going to see during this little time range?
00:00:39
We will see the concept of tables and specifically the automatic formatting.
00:00:45
Then we will see the conditional formatting and how it can help to enhance your tables.
00:00:59
Then we will see how to insert and customize sparklines,
00:01:03
which is something new in Office 2016.
00:01:10
Finally, we will see how to create and customize charts.
00:01:14
Let's go to Excel.
00:01:18
I created a table.
00:01:21
And let's see how we can customize this table.
00:01:26
First, we are in the table and you have to be in the table before formatting.
00:01:33
In the upper part of the screen, you know that now for sure,
00:01:38
what is called the ribbon,
00:01:40
which changes depending on where you are and which tab you select in the list of tabs.
00:01:50
Here in the home tab,
00:01:54
with the ribbon corresponding to the home tab,
00:01:57
we can find here "Format as Table".
00:02:01
Here appears different types of tables
00:02:07
for the format.
00:02:08
You can select any of these depending on your what you prefer.
00:02:14
Perhaps the the type of the table itself.
00:02:19
For example, I can select in the medium part of it this format.
00:02:25
The system requests where the data are
00:02:30
in your worksheet.
00:02:38
So you can change it.
00:02:45
This is your table.
00:02:49
The system has normally detected the table on its own.
00:02:55
Indicate if your table has headers, which is the case.
00:02:58
It has 3 headers:
00:03:00
Category, January 16 and January 17.
00:03:03
If we click on OK, the table is formatted directly with the color that we have decided to apply to this table.
00:03:18
If you go back to Home
00:03:20
and "Format as Table",
00:03:22
you can change design.
00:03:23
Just by hovering over a color or a type of table,
00:03:31
you have a preview of what would be the result of your different choices.
00:03:47
That's the first possibility to format your table.
00:03:52
There is another one that you can get to in Page Layout.
00:04:00
You have selected your table. On the left part of the ribbon, of the Page Layout tab,
00:04:06
you find different themes.
00:04:08
And again your table is adapted
00:04:14
not only in terms of color, but also in terms of font
00:04:22
and size.
00:04:24
So that's another way to apply a new theme to your table.
00:04:35
Let me just see that very quickly.
00:04:39
So you can decide which theme you prefer to apply to your table.
00:04:50
Then in terms of calculation and table.
00:04:54
Excel is very quick to calculate and analyze data.
00:04:58
Here we have a set of analyses and data.
00:05:01
We are going to see a few things that we can do with the table.
00:05:09
First, you probably know that you can have totals for a column.
00:05:17
If you go to Home, at the far right,
00:05:22
we have an "AutoSum" button
00:05:27
that will automatically select the cell in the table.
00:05:35
From the drop-down list, we will be able to decide what the content of this cell will be.
00:05:43
Is it just a sum?
00:05:48
Or it can be the average of this column.
00:05:55
Or it can be the maximum or the minimum value of these data.
00:06:05
Let's try the sum.
00:06:10
On the next cell, the table has been configured with the Format as Table button.
00:06:22
We have select the theme so this last row has been dedicated to the total.
00:06:32
It means that as soon as I have selected my AutoSum,
00:06:36
I can select the drop-down list with what I prefer to have.
00:06:41
On the left, I have the sum of all the figures in the G column.
00:06:48
Here, I have the average number of the H column.
00:06:54
Of course, I change it.
00:06:58
Again, here I have the sum.
00:07:01
What you can also have from here in this table,
00:07:07
in terms of formatting,
00:07:09
is that you can just by clicking anywhere in the table,
00:07:14
if you then go to Data,
00:07:17
you will be able, first, to ask for a filter.
00:07:22
You see that a few arrows, drop-down lists, have been added to each header of my columns.
00:07:31
If I click on the header, I can deselect everything and just see these figures.
00:07:41
These will be for food and I can have my January 16 purchase.
00:07:51
And here, the January 17 purchase.
00:07:55
You can see that the icon has changed, showing that a filter has been applied to this column.
00:08:02
If I click, I see that food has been selected
00:08:09
If I reselect all, I get back all elements.
00:08:15
So that the first possibility to apply filters.
00:08:21
I can have a list of each row being a different value.
00:08:29
It's not very interesting.
00:08:31
But if you have same values and the same columns, in this case you will get all the results of the same value for this column.
00:08:41
So that's the first thing. Another thing that you can do in your table
00:08:45
is to apply a sort.
00:08:49
If you click in the table,
00:08:54
you will be able to decide on which column you can sort.
00:08:58
Here for example, I can say that I want to sort by category.
00:09:03
What would I prefer to sort on:
00:09:07
of course, values.
00:09:08
And I choose, as these are alphabetical values,
00:09:14
I can decide to have A to Z or Z to A
00:09:16
or perhaps even have a custom list.
00:09:18
If I decide to have A to Z
00:09:21
and apply my choice,
00:09:22
my table is ordered in terms of category A, B, etc.
00:09:32
That's the first possibility.
00:09:34
If you click again on Sort, you can choose January 16,
00:09:40
values again, to sort on values,
00:09:44
and here I have Smallest to Largest which I can change to Largest to Smallest.
00:09:49
This time, the whole table will be sorted on January 16 column.
00:10:00
You can see that this is the largest field value, and at the bottom is the smallest value.
00:10:08
You can do it for the last one.
00:10:14
And now you can remove these totals.
00:10:28
These were the first possibilities of formatting.
00:10:35
If I go back to Home,
00:10:38
I can get just beside the Format as Table
00:10:42
something called "Conditional Formatting".
00:10:46
Here I have a list of possibilities.
00:10:48
The first one is Highlight Cells Rules.
00:10:51
For example, Greater Than.
00:10:53
If I select this and first I select my table.
00:11:00
Highlight Cells Rules, Greater Than.
00:11:02
I want to see everything that is over 500
00:11:09
in light red.

No elements match your search in this video....
Do another search or back to content !

 

Please login to the platform to play this video