This training video is geared toward beginning users of Microsoft Excel. By
the time you finish this short video you'll know all the steps to turn a
blank Excel spreadsheet into something like this. You'll learn all the basic
tools of Excel such as opening Excel for the first time, the Excel screen layout,
entering data into cells, formatting and appearance,
adding formulas, creating charts and graphs, and saving and printing your
worksheet. Even though these are basic Excel functions,
you'll quickly see how to create powerful spreadsheets with just a few
minutes of training. First let's talk about what is Microsoft Excel. Excel is a
program that uses columns and rows to organize and manipulate data. Its primary
use began with numerical calculations in a spreadsheet form but over time Excel
has grown to include charts, databases, application development, statistical
analysis, and a myriad of other functions. Excel for Windows has been around since
1987. Major upgrades are released every three years as seen in 2007, 2010, 2013,
and 2016, with 2019 due soon. From 2016 onward the Mac version releases around
the same time as the Windows version. For this training video i'll be using Excel
2016 with the latest updates through august 2018 included but don't worry,
since I'm covering the basics you'll be able to follow along with any previous
version. When you open Excel it gives you the choice of creating a blank workbook
or choosing from a number of 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. 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 Owner, and various Window Controls. Directly below
the Title Bar is the Ribbon. It contains Menu Tabs, the Smart Lookup,
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 available. 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.
Did you know Excel can handle sixteen thousand columns and a million rows? That
calculates out to over seventeen 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. 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 are 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 worksheet. 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. To see
a comprehensive list of these keyboard shortcuts search for shortcuts in the
Smart Lookup box. 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 also refer to this range as A1:A3. This will be useful when we
start using ranges and formulas and for other purposes in the worksheet. Now that
we've learned some Excel basics let's use these tools to build a 2018 budget
spreadsheet. Start with a clean worksheet. Highlight these three lines and press
the Delete key. Let's call this 2018 budget.
Don't worry about formatting yet. With Excel it's easiest to enter data first
than 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 the pattern and fills in the remaining cells for us. Across the top of
our budget we want each month of 2018. Starting with January. What do you think?
Will it work. Let's grab the bottom-right corner and drag it out and see what's
happening there. It fills in the months for us. Let's enter our expense data. 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.
I'll 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 drag
it to the desired width. An even quicker method is to double-click. This will
adjust the width to fit automatically. Now let's highlight the monthly column
headings and Center them in the columns. While we're here let's fill it in with
dark grey color. We'll 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!
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 =, 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 knows that Jan 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 create an average for the
months. Enter another column heading called
Average. Average is not in the Editing group like AutoSums 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 =.
Type average in the search and click Go. Excel returns all the functions related
to averaging. Make our selection. We want the average of all months but Excel
detects the range B5 to N5. We don't want the total included so let's
highlight just the monthly amounts for Expense1 instead. Now it is correct.
Press OK and we have our result. We can drag the corner again but here's a
quicker shortcut. Double-click the corner instead. See what it does? I don't like
the formatting on those numbers so let's right-click and format those cells.
Choose Number and zero decimals. Press OK. Finally, add those borders. Let's also add
a bottom double border for the total row.
Tables are great but charts and graphs 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. We'll 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 even change the Chart Type
and you have a number of 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 graphs and
this is only a look at some of them. The final steps in mastering Excel basics is
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 have 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 the basics of Excel. What
you've just watched is more than enough to get you started using Excel for
real-world situations. You'd be surprised at how much you can
accomplish using only these 7 tools and you'd be surprised at how much more
Excel has to offer beyond the basics. Make sure to visit youtube.com/seletraining
for more Excel tips and tricks to take Excel to the
next level. Thanks for watching! This has been a Sele Training Professional
Series video. If this is your first time visiting Sele Training, please
subscribe by clicking on the link right here. If you found this video useful give
it a thumbs-up and share your comments or questions below and don't forget to
check out the other videos in the series.