This training video is for beginning users of Microsoft Excel. Follow along using any version.
By the time you finish this short video you'll know all the steps to turn a blank
Excel spreadsheet into this. You'll learn the seven basic components of Excel - Opening Excel,
the Excel screen layout, Entering data, formatting cells, creating formulas,
creating charts, and saving and printing. In less than 15 minutes of training you'll be
equipped to create powerful spreadsheets all on your own. Let's get started! #1 Opening Excel.
When you open Excel it gives you the choice of creating a blank workbook or choosing from
several predefined templates. You can filter the templates by category or enter a name to
search for an online template. We'll start with a blank workbook. #2 Screen Layout. Let's start
with some terminology. For the Excel screen layout at the top of the Excel window is the title bar.
It contains the quick access toolbar, the workbook name, the search, the owner, and various window
controls. Directly below the title bar is the ribbon. It contains menu tabs and a share
button. Within each tab the ribbon contains commands you use to apply to your worksheet.
These commands are organized in groups with additional commands available by clicking
on the mini toolbar when shown. Directly below the ribbon is the name box and formula bar. The
main worksheet area contains columns and rows. The intersection of columns and rows is called a cell.
The currently selected cell is called the active cell. Use scroll bars on the right and bottom of
the worksheet to extend beyond the current view to other parts of your worksheet. Each worksheet
is represented by a worksheet tab and finally at the bottom of the screen is the status bar.
It contains the status page view buttons and zoom controls. #3 Entering Data. Did you know Excel
can handle 16,000 columns and a million rows. That calculates out to over 17 billion cells.
Let's look at how we can use a few of them. You enter data into cells in your worksheet.
Start by clicking on the first cell. In the upper-left the name box tells us this is cell
A1. The intersection of column A with row 1. In a worksheet you have three types of data - Text,
Value, or Formula. Text are identifiers such as names, headings, or other information and usually
include alphabetic characters. Values can be numbers or dates. Formulas are expressions using
financial, date, logical, or text functions to convert data and perform calculations. Type some
data in A1 and press enter. Now type some numbers in A2 and press enter. Notice the active cells
move down each time we press enter and notice text is left justified and numbers are right justified.
Excel knows we generally work with text and values this way in a worksheet. In A3 type =1+1 and press
enter. This is a formula. Formulas always begin with an equal sign. The result of this formula
is in A3. However, when you click on A3 you'll see the underlying formula in the formula bar rather
than the calculated result. Click on A1. If you type text it overwrites the data. Now double-click
and it inserts text without overwriting. This is the difference between being on a cell and being
in a cell. When you're on a cell you're replacing data. When you're in a cell you're editing you can
also click on a cell and make changes directly in the formula bar. This is how you enter text,
value, and formula data into your spreadsheet. Over time you will learn more efficient methods
for entering data and moving around the worksheet. Clicking on cells with the mouse works but you can
also use keyboard shortcuts. Use the arrow keys to move up, down, right, and left. Hold the Ctrl key
with the arrow key and it jumps to the beginning and ending of a block of data. Ctrl Down moves
to the end of this column of data. Ctrl Up moves to the beginning. As mentioned earlier pressing
enter moves down one cell. Use Tab to move right one cell. Hold the Shift key to do the reverse.
Shift Enter moves up and Shift Tab moves left. So far we've worked with one cell at a time but you
can also select a range of cells. Hold the left mouse button down and highlight a group of cells.
The name box tells you this is three rows by one column. We refer to this range as A1:A3.
This will be useful when we start using ranges and formulas in the worksheet.
Now that we've learned some Excel basics let's use these tools to build a 2021 budget spreadsheet.
Start with a clean worksheet. Highlight these three lines and press the delete key.
Let's call this 2021 Budget. Don't worry about formatting yet. With Excel it's easiest to enter
data first then add formatting later. In the first column we're going to enter expenses.
Enter Expense1 and Expense2. We could manually enter 3 through 6 but Excel provides a shortcut.
Highlight the first two and in the bottom-right corner drag this down to 6. It fills it in for us.
This is called Flash Fill. Excel recognizes patterns and fills in the remaining cells for us.
Across the top of our budget we want each month of 2021 starting with January. Will
it work? Let's grab the bottom-right corner and drag it out and see what happens. Yep,
it fills in the months for us. Now let's fill in this table with some expense data.
#4 Formatting Cells. Now it's time to focus on formatting and appearance. To make this data
easier to read I'm going to add an extra row above and below the title. Just right-click on
row 1 and select Insert and again on row 3. Now click on the title. On the ribbon under the Home
tab let's change the font size to 24 and make it bold. Let's also capitalize the word Budget.
It would look better if the title is centered over the entire table. Starting with the title in cell
A2 highlight the entire width then click merge and center in the alignment group on the ribbon.
The expense column looks a little tight so hover over the divider line between column A
and B and it will change to a cross. Once you see that click and grab it and drag it to the
desired width. An even quicker method is to double-click. This will adjust the width to fit
the data in the column automatically. Select all the months columns and let's do the same thing.
Now let's highlight the monthly column headings and center them. While we're here let's fill it
in with dark gray color and do the same for the expenses with a slightly lighter color.
These grid lines are just guides but won't show up when printed so let's add some permanent borders
to make this look more like a table. Highlight the entire table. We don't want borders to show
up in that upper-left corner so as an added trick hold down the Ctrl key and deselect that cell.
Now select Borders from the ribbon and choose All Borders. This is starting to look good. #5
Creating Formulas. Formulas provide the real power in Excel. Let's add some formulas and
see how this will enhance our spreadsheet. It's helpful to know each month's total expenses so
at the bottom of the table we'll add a row heading called totals and right-justify it.
Click on the cell under the January column where we want our first total to appear.
Since summing a column of numbers is a common function in Excel a selection for it
is available in the editing group on the Home tab called AutoSum. click it and it creates a
formula starting with equals then the function SUM followed by the range of cells B5 to B10,
inside parentheses. The AutoSum function does several things for us. It fills in the function
and detects the range of cells above it. It looks for a continuous block of numbers to total. If
there was an empty cell it would stop and it also knows that January is not a number so it ignores
that too. Now press Enter and it totals the column for us, filling in the results of the formula. As
we've done before let's use our bottom-right corner to copy this formula across all months.
You can see from each total that Excel understands what you're trying to do and copies the formula
but changes each range automatically. How about a total for each expense for the year. Enter Total
after the month column. Again Excel is smart and applies the formatting to the heading for you.
For the Expense1 total click on AutoSum. This time it correctly detects all the numbers to the left.
Excel isn't perfect all the time so if the range detected is wrong just highlight the correct range
and press Enter. Now let's add another formula but this time we'll use the Average. Enter another
column heading called Average. Average is not in the editing group like AutoSum so we'll have
to enter it manually. For the Expense1 average click the function icon on the formula bar. This
tool helps us build formulas using any of the Excel functions and you can see that it already
starts the formula with equals. Type average in the search and click GO. Excel returns all the
functions related to averaging and we'll make our selection from the list. Notice Excel detects the
range B5 to N5. We don't want the total included so let's highlight only the monthly amounts for
Expense1 instead. Now it's correct. Press OK and we have our result. We can drag the corner
again to copy the formula but here's a shortcut. Double-click the corner instead. See what it does?
I also don't like the formatting on those numbers so let's right-click and format those cells.
Choose number and zero decimals. Click OK.
Finally, add those borders. Let's also add a bottom double border on the total row.
#6 Creating Charts. Tables are great but charts are an even better way to view this data.
Let's turn this table into a chart. Highlight the table including the headings. No need for
totals. Click on the Insert tab and select the column chart. You have several choices
but a stacked column chart fits well with this data. Now we can drag this chart anywhere. Let's
move it down below and use the scroll bar to move down. Let's fix that chart title.
Double-click on the chart and it brings up the Design tab.
Choose from several quick layouts and chart styles. You can change the chart
type and you have several options available to change the chart area. It's that easy!
Let's go back to our data. Another way to display your data is with Sparklines. Click to the right
of the table on Expense1. From the Insert tab under the Sparklines group click Line. For the
data range select your Expense1 data and hit OK. This creates a mini graph of your Expense1 data.
Now drag the corner down for all the other expenses. Excel provides several ways to view
data through charts and this is a look at only some of them. #7 Saving and Printing. The final
steps in mastering this introduction to Excel are saving and printing. In the title bar it shows our
workbook name as the default Book1 and it has an icon on the quick access toolbar for saving.
You can also choose to save your workbook from the File tab. Notice the workbook is
saved with autorecovery every few minutes. This helps recover a lost or damaged file but it's a
good idea to save often anyway. Choose Save As to rename your workbook and select a file location.
This screen also displays the last few workbooks you've edited. Change the name and click Save.
Notice it updated the workbook name. To print our workbook go to the File tab and select
Print. Just like other applications you can control the printing layout
and see a preview on the right. Our budget worksheet doesn't fit very well on a letter size
page so let's make some adjustments. Change the Orientation to landscape
and set the Scaling to Fit Sheet on One Page. That looks better now. We can print our workbook
now. Congratulate yourself for mastering this introduction to Excel. What you've just watched
is more than enough to get you started using Excel in real world situations. You'd be surprised at
how much you can accomplish using only these seven components and you'd be surprised at how much more
Excel has to offer beyond this introduction. Make sure to visit youtube.com/seletraining
for more Excel tips and tricks to take Excel to the next level. Thanks for watching! Hey
,if you want to see more videos 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!