Microsoft Excel can help you organize information,
perform calculations, and discover patterns in your data
all in one place, and you can get to it on your PC,
your Mac, your phone, or on the web.
I'm Jeremy Chapman,
and I've been part of the product team
responsible for Office at Microsoft since 2012.
And today, I'll walk you through the essentials
of Excel and how to use it.
So first, if you have a Microsoft account,
like outlook.com, OneDrive, or Xbox,
or if you use Microsoft 365 at work,
you can use Excel on the web, in your browser,
and you can get to Excel by navigating to excel.new.
And by the way, if you have the Excel app installed,
you can open that on your computer or your phone
and follow along.
When you're signed into your work
or personal Microsoft account,
Excel saves your files to OneDrive,
so you can easily find them
and pull them up on other devices later.
So for today, I'll keep things simple.
So I'll start with a blank workbook Using Excel on the web.
Wherever you use Excel,
it's designed to be a consistent experience
on large screen devices,
so you can follow along if you're using
the local app on Windows or on a Mac.
And Excel is designed to organize any kind of information,
numbers, dates, texts, and more.
In the main view, you can see that I have columns and rows
all ready to enter data.
In most cases, there's a one-time step
to create what's called a workbook in Excel,
which I have one open here.
Now this is where you'll use and create a blank workbook,
or you can choose from dozens of different templates
that are filled in with sample data and formatting
to get you started.
At that point, you can enter your data, your headers,
and start formatting your cells.
Now if you have existing data in a table in another app,
you can open it with Excel
or just paste in the contents to start working with it.
On top, Excel has what's called the ribbon,
with groups of controls presented as tabs
that you can use.
Within each tab, there are smaller groups of controls,
like you can see here with the fonts, alignment, and number.
Now let me define a few core names and concepts
that you'll use when you work with Excel in this workbook
to manage data.
So each field or rectangle that you can see here
as I'm highlighting them, these are called cells.
Then you have columns,
and those are the vertical lines of cells,
and those are represented with letters on top.
Next you have rows, and those are the horizontal lines,
and those are represented by numbers.
For example, the upper left cell is called A1,
A for the column name and 1 for the row name.
Now a block of multiple connected cells is called a range.
So here, for example, I've selected range A1 to D4.
Right now I'm in a sheet called Sheet1,
and you can see in the lower left corner,
I can add more sheets, like I'll do now,
and then I can move between multiple sheets
and reference data across them as well.
But I won't do that today.
So now I'm going to go ahead and go back to Sheet1.
And if you right-click and go to Format Cells,
you'll find options for things like number formats,
for example, currency, date, time, and percentage.
And on the Home tab,
the font group is another place to change these settings,
as well as Fill,
which lets you change the background color
for cells, columns, or rows.
I'm going to add some text in this cell
as a title for what I want to create today,
a monthly expense tracker.
Now this text looks like it's spilling into cell B1,
but it's actually just in cell A1.
So I can widen or narrow the columns as much as I want.
And if I want this title to span several columns,
like in my case, I know that I'm going to need 12 months.
So I'll go ahead and select rows M1
all the way back to A1.
Then in the alignment group,
I'll choose the Merge & Center option right here,
and that makes my 13 cells into one with the text centered.
So now, in the font group,
I can choose the fill color that I want.
So in my case, I'll pick blue.
Then for the font color,
I'd like to choose something contrasting.
So I'll choose white in my case.
And by using these formatting options,
you can make things a lot easier to understand
as you work with your data.
But we still need some content, so let's add some.
So for that, I can use AI with Copilot
to generate sample data.
So I'm going to go ahead and pull up Copilot
and type, "Generate monthly personal finance data
for one year with months for columns
and expense categories as rows, including sample data.
Do not add columns or rows with totals."
Now I added that last sentence because I want to show you
how to calculate totals yourself in a moment.
The Copilot is part of Excel on the web and in the desktop
and mobile apps if you're using Microsoft 365 Personal
or a work or school account.
And you'll see, once it's finished,
that Copilot generated a Category column
and several month columns, as well as multiple rows
with different expense types
all filled in with the sample data that I asked for.
Now notice that it also formatted the row 2 and column A
using formatting options that I mentioned before.
And each cell in the middle is also formatted
as a currency number with a dollar sign.
So I want to add a row here, in my case, for car payment.
And you'll see that it doesn't match the others yet,
and I'll fix that in a second.
Now I'll add an amount for January, 300.
And since this is the same amount every month,
I can just select the cell.
Then using this square in the lower right corner,
I can just drag across the other months,
and each, in this case, will have the same number, 300.
Let's fix our formatting.
Now, to make the dollar amounts match the cells above,
I'll select this one above my new row,
then click on the Format painter, this paintbrush icon here,
then I'll select my new cells.
And now they all match.
Now I can do the same thing for my Car Payment label
in cell A16.
So now I have some formatted data to work with
and I can show you how to work with those numbers.
I'll use the Formulas ribbon
where you'll see the most common options to analyze data.
For example, if I select all the cells
with numbers in column B,
then I go up and click on AutoSum,
it adds all of the numbers in that column.
In fact, now if I click on that cell in the formula bar,
I can see a simple formula.
Now these start with an equal sign,
in my case, SUM as the function itself.
Then I have an open parentheses with my range,
in my case, B3 to B16,
and close parentheses for what I want to calculate.
Now that was an example of a very simple formula.
Like I did before with the numbers,
I can even drag formulas into blank cells.
So I'll go ahead and grab this one again
by the lower right corner square
and drag it across all of my columns.
So that now has copied the original formula
from the B column and duplicated it
for each of the other columns.
But as I click into each one,
notice something that just happened,
I have the column letters B all the way through M
to each corresponding formula.
That makes each sum specific to each of these column months.
Likewise, I can select and drag entire columns
into blank areas to fill in that data too.
And because Excel detected a series of month names in row 2,
it even filled in Jan as the new month name
for the new cells that I added.
Now let's try another basic formula.
For that, I'm going to select all the numbers
above the totals row in column B.
Now I'm going to choose Average,
and that adds a cell with the average
across the entire range that I just selected.
So now I want to clean up a few cells.
And when you go to delete data,
you'll need to know a few different options.
So first, I'll select the month cells that I just added.
And if I just hit the Delete key,
it leaves the formatting in those columns,
like this blue cell here.
This is also called clearing content.
I'll use the Control key + Z simultaneously
to bring that content back and undo changes.
Now I'm going to go ahead and select the same cells.
And when I right-click,
you'll see that there are options to Insert or Delete
along with Clear Contents
like I just did using the Delete key.
So this time, I'll choose Delete,
and then I have options to delete a column
or shift cells left or up.
In my case, deleting column N and shifting cells left
will clear the contents and formatting.
I'll choose Shift cells left.
So now I'll clear the contents of rows 17 and 18
with my sums and the average
to get my content data ready for other ways to analyze it.
And there are hundreds of formula options in Excel.
In fact, if I expand Financial functions,
there are dozens related to accounting and finance.
and hovering over each explains how they are used.
And in math and trig, for example,
there are dozens more that may look familiar
if you've ever used a scientific calculator.
And here I'm just scratching the surface.
Those are just a few highlights of the functions
that you can use.
But what if you know how to describe what you want
but don't know the function for it?
And that's another area where Copilot helps you get started.
So this time, I'll use Copilot to calculate the totals.
I'll type, "Add a row and column with totals
for each month in category."
And Copilot adds the totals by month
and even a new column with the totals per category.
Copilot will also help with cell formatting.
So if I add, "Make the cells you just added
with formulas white and bold text in black," in my prompt,
Copilot then reformats those cells too.
And you can also add colors to each cell
to easily spot differences across these numbers
using something called conditional formatting,
which is something else that Copilot can help with.
I'll type, "Add conditional formatting in each row
to highlight low and high numbers."
And now we can see where the numbers are the lowest
and the highest
compared to the others in the same expense category
for each month.
So you just need to describe what you want
and Copilot will do the rest.
Now let's go ahead and move on
to deeper analysis of our data.
With conditional formatting applied,
it's easier to see each month
and how it varies in costs across our different categories.
So let's find some outliers.
So I'll ask Copilot,
"What months have the highest expenses and why?"
And Copilot analyzes the information
and finds the months with the highest expenses.
Then for each, it explains why with the most likely reasons.
In this case, December is my highest,
and that's likely due to holiday spending and seasonality.
July is the next highest,
likely due to air conditioning for utilities costs
and the rest of the summer activities
that were happening in July.
Then August was third highest,
also with more travel, AC costs, and dining out.
The key insights here summarize what Copilot found
with reasoning for increases and decreases
along with the lowest months as well.
And one more core component that I'll touch on today
is how Excel lets you edit workbooks
simultaneously with others.
As I mentioned in the beginning, when you're using Excel,
signed in with a Microsoft account,
or using Microsoft 365 at work or at school,
it stores your files in OneDrive by default.
Now, it also means that when you share an Excel workbook
with other people using their name, group, or email,
I'll add Adele here, for example, and hit Send.
Then they will be able to open the Excel workbook
on their computer or phone
and simultaneously edit it with you.
And while you co-author with other people
as changes are made,
like with Adele here, changing the amounts for dining out
and entertainment in January,
they are saved to the same file.
So those are the basic concepts to navigate Excel,
format data, analyze it, and work with others using sharing.
And I showed you how Copilot AI
can help you as you get started.
To learn more, check out microsoft.com/excel.
And be sure to subscribe to Microsoft Mechanics
for the latest updates, and thanks for watching.