Excel Explained
These are my notes on an Excel explained.
Table of Contents
Introduction to Excel
Excel is the most used spreadsheet software in the world. It is part of the
Microsoft Office suite. Excel is used to perform numerical calculations. We use
Excel for number crunching, creating charts, organizing lists, text
manipulation, and accessing other data.
Understanding Workbooks and Worksheets
Your work is done in a workbook. You can have many workbooks open at once. They
use an .xlsx extension. Workbooks will each open in a different window.
Each workbook contains one or more worksheets. Each worksheet is made up of
individual cells. Each cell can contain a value, a formula, or text. A
worksheet can also contain charts, images, and diagrams. Worksheets are
available by clicking a tab at the bottom of the workbook.
Every worksheet contains rows starting with the number 1. Columns start with the
letter "A". The intersection of a row and column is a single cell. Each cell has
a unique address made up of its column letter and row number. At any given time,
one cell is the active cell. It is the cell you have highlighted and it accepts
keyboard input. Its contents can be edited. You can identify the active cell by
its darker border.
The Ribbon has various functions and tabs built in. We will cover them as they
come up. You can access the Ribbon by using keyboard commands just as easy as
the mouse. Use the "alt" key to display shortcuts on the Ribbon. Once you have
used the keys a few times you will start remembering some of them. This is how
you improve your workflow.
Sometimes you will want to repeat a command. If you apply some particular
formatting to a cell then you can press ctrl-y to repeat it.
There are also shorcut menus everywhere. You can right click just about anything
and get access to another menu. These menus are just another way to accomplish
certain tasks.
Creating A Workbook
Start Excel with an empty workbook. If you have one open already you can create
a new one with the ctrl-n command.
If you enter in a column of numbers work on as data, you can format them to make
them easier to read and understand. You can select all the cells in a range by
dragging with your mouse from beginning to the end. In the Number group on the
Ribbon, click the drop down Number Format and select currency or any other data
type you might be using. If currency, make sure and choose the two decimal place
too.
We can now create a table with this data. Click on any cell that includes your
data.
- Choose insert
- Tables
- Table
Excel displays the create table box so youcan peruse the settings. Click ok and you get another tab to look at. You can choose different table types.
Now, we can sum the data.
- Activate any cell in the table
- Choose table tools
- Design
- Table Style Options
- Total Row
Excel automatically adds a new row to the bottom of your table. including a
formula that calculates the total of your data. If you would prefer to see a
different summary formula you can choose a different summary formula from the
drop down list.
We can create a chart using a similar process.
- Activate any cell in the table
- Choose insert
- Charts
- Recommended Charts
Select one of the options you see. You might even experiment and try a few
to see what you like for your data. Excel will insert the chart into the
center of your window. You can move it by dragging it in one direction.
Printing Your Worksheet
Printing is easy if you have a printer available to you.
- Click on any cell
- Click Page Layout to make sure everything looks good
- Choose File then Print
Finally, click the Print button at the top
Saving Your Workbook
You will want to save your work so you do not lose it.
- Click the Save button on the quick access bar
- It asks you to choose a location and name for your new file
- Click Browse to select the location
- In the File Name field, name it something that makes sense to you
- Click Save once more to have your file saved
By default, Excel saves a copy of your work every few min, but you should save
anyway to be sure you do not lose anything.
Worksheet Data in Excel
Intro
A worksheet has around 17 billion cells. A cell can be a numeric value, text, or
a formula. A worksheet can hold charts, diagrams, pictures, buttons, and other
things.
Numeric values represent a quantity of some type. They can also be dates or
times. Excel can display values in many different formats too.
Most worksheets include text in some of the cells. Text can serve as data,
labels, headings, or some type of instruction. It is often used to clarify what
the values in a worksheet mean or where the numbers came from. Text that begins
with a number is still considered text.
Formulas are what make a spreadsheet a spreadsheet. Excel enables you to enter
flexible formulas that use the values in cells to calculate a result. When you
enter a formula into a cell, the formula's result appears in the cell. If you
change any of the cells used by a formula, the formula recalculates and shows
the new result.
They can be simple mathematical expresisons or they can use functions that are
built in to Excel.
Entering Values
To enter a numeric value into a cell, move the cell pointer to the appropriate
cell, type the value, and press enter or one of the arrow keys. The value is
displayed in the cell and appears in the formula bar when the cell is selected.
You can include decimal points and currency symbols when entering values, along
with plus signs, minus signs, and commas. If you precede a value with a minus
sign or enclose it in parentheses, Excel considers it to be a negative number.
Entering text into a cell is just as easy as entering a value. Activate the
cell, type the text, then press enter or an arrow key. A cell can contain a
maximum of around 32000 characters. Even though a cell can hold a huge number of
characters, you can't actually display all of them.
If you type a long text entry into a cell, the formula bar may not show all of
the text. To display more of the text in the formula bar, click the bottom of
the formula bar and drag down to increase the height.
Entering Dates and Times
Excel treats dates and times as special types of numeric values. Dates and time
are values that are formatted so that they appear as dates and times. If you
work with dates and times, you need to understand Excel's date and time system.
Excel handles dates by using a serial number system. The earliest date that
Excel understands is January 1, 1900. This date has a serial number of 1 and so
on. This system makes it easy to deal with dates in formulas. For example, you
can enter a formula to calculate the number of days between two dates.
Most of the time, you do not have to be concerned with the serial number date
system. You can simply enter a date in a common date format and Excel will take
care of the details.
Entering Time Values
When you work with times, you extend Excel's date serial number system to
include decimals. In other words, Excel works with times by using fractional
days.
Modifying Cells
After you enter a value or text into a cell, you can modify it in several ways.
Delete the cell's contents, replace the cell's contents with something else, or
edit the cell's contents. You can also modify a cell by changing its formatting.
However, formatting a cell only affects a cell's appearance, it does not affect
the cell's contents.
To delete the contents of a cell, just click the cell and press the delete key.
To delete more than one cell, select all of the cells that you want to delete
and then press enter. Pressing delete removes the cell's contents but does not
remove any formatting that you may have applied to the cell.
For more control over what gets deleted, you can choose home, editing, then
clear. This drop down list contains:
1. Clear All
2. Clear Formats
3. Clear Contents
4. Clear Comments
5. Clear Hyperlinks
6. Remove Hyperlinks
Clearing formats doesn't clear the background colors in a range that has been
designated as a table unless you have replaced the table style background colors
manually.
To replace the contents of a cell with something else, just activate the cell
and type your new entry, which replaces the previous contents. Any formatting
applied to the cell remains in place and is applied to the new content.
You can also replace cell contents by dragging and dropping or by pasting data
from the Clipboard. In both cases, the cell formatting will be replaced by the
format of the new data. To avoid pasting formatting, choose:
home
clipboard
paste
values
If the cell contains only a few characters, replacing its contents by typing new
data usually is easiest. However, if the cell contains lengthy text or a complex
formula and you need to make only a slight modification, you probably want to
edit the cell rather than re-enter information.
When you want to edit the contents of a cell, you can use one of the following
ways to enter cell-edit mode.
Double click the cell
Select the cell and press F2
Select the cell then click inside formula bar
All these methods cause Excel to go into edit mode. When Excel is in edit mode,
the formula bar enables two icons: the X and Check mark. When you begin editing
a cell, the insertion point appears as a vertical bar, and you can perform the
following tasks:
1. Add new characters at the location of the insertion point
2. Select multiple characters
3. Select characters while you are editing a cell
Data Entry Techniques
You can simplify the process of entering information into your Excel worksheets
and make your work go quite a bit faster by using a number of tricks. By
default, Excel automatically moves the cell pointer to the next cell down when
you press the enter key after entering data into a cell. To change this setting:
File
Options
Advanced Tab
The check box that controls this behavior is labled after pressing enter, Move
Selection. If you enable this option, you can choose the direction in which the
cell pointer moves.
Instead of pressing the enter key when you are finished making a cell entry, you
can use any of the navigation keys to complete the entry.
When a range of cells is selected, Excel automatically moves the cell pointer to
the next cell in the range when you press enter. If the selection consists of
multiple rows, Excel moves down the column. When it reaches the end of the
selection in the column, it moves to the cell outside the range. To skip a cell,
just press enter without entering anything. To go backward, press shift+enter.
If you prefer to enter the datas by rows rather than by columns, press tab
rather than enter. Excel continues to cycle through the selected range until you
select a cell outside the range.
If you need to enter the same data into multiple cells, select all the cells
that you want to contain the data, enter the value, and press control+enter. The
same information is inserted into each cell in the selection.
Decimal Places
If you need to enter lots of numbers with a fixed number of decimal places,
Excel has a useful tool. Access the Excel Options dialog box and click the
Advanced tab. Select the automatically insert a decimal point check box and make
sure that the places box is set for the correct number of decimal places for the
data you need to enter.
When this option is set, Excel supplies the decimal points for you
automatically. To restore things back to normal, just clear the automatically
insert a decimal point box. Changing this setting does not affect any values
that you already entered. The fixed decimal places option is a global setting
and applies to all workbooks. If you forget that this option is turne don, you
can easily end up entering incorrect values.
Autofill
The Excel autofill feature makes inserting a series of values or text items in a
range of cells easy. It uses the autofill handle. You can drag the autofill
handle to copy the cell or automatically complete a series. If you drag the
autofill handle while you press and hold the right mouse button, Excel displays
a shorcut menu with additional fill options.
Autocomplete
The Excel autocomplete feature makes entering the same text into multiple cells
easy. With autocomplete, you type the first few letters of a text entry into a
cell, and Excel automatically completes the entry based on other entries that
you already made in the column. Besides reducing typing, this feature ensures
that your entries are spelled correctly and are consistent.
Autocomplete also changes the case of letters for you automatically. If you
start entering widgets in the second entry, Excel makes the w uppercase to be
consistent with the previous entry in the column. You can also access a mouse
oriented version of autocomplete by right clicking the cell and choosing Pick
From.
Keep in mind that autocomplete only works within a contiguous column of cells.
If you have a blank row, for example, autocomplete identifies only the cell
contents below the blank row. If you find the autocomplete feature distracting,
you can turn it off by using the advanced tab of the Excel options dialog box.
Remove the check box labeled enable autocomplete for cell values.
Long Text
If you have lengthy text in a cell, you can force Excel to display it in
multiple lines within the cell; press alt+enter to start a new line in the cell.
When you add a line break, Excel automatically changes the cell's format to wrap
text. But unlike normal text wrap, your manual line break forces Excel to break
the text at a specific place within the text, which gives you more precise
control over the appearance of the text than if you rely on automatic text
wrapping.
To remove a manual line break, edit the cell and press delete when the insertion
point is located at the end of the line that contains the manual line break. You
will not see any symbol to indicate the position of the manual line breal, but
the text that follows it will move up when the line break is deleted.
You can use the autocorrect feature to create shortcuts for commonly used words
or phrases. Excel includes quite a few built in autocorrect terms and you can
add your own. To set up your custom autocorrect entries, access the Excel dialog
options box and click the proffing tab. Then click the autocorrect options
button to display the autocorrect dialog box. In the dialog box, click the
autocorrect tab, check the option labeled replace text as you type, and then
enter your custom entries. You can set up as many custom entries as you like.
Just be careful not to use an abbreviation that might appear normally in your
text.
Fractions
Most of the time, you will want noninterger values to be displayed with decimal
points. But Excel can also display values with fractions. To enter a fractional
value into a cell, leave a space between the whole number and the fraction.
Forms
Many people use Excel to manage lists in which the information is arranged in
rows. Excel offers a simple way to work with this type of data through the use
of a data entry form that Excel can create automatically. This data form works
with either a normal range of data or with a range that has been designated as a
table.
Unfortunately, the command to access the data form is not on the ribbon. To use
the data form, you must add it to your quick access toolbar or add it to the
ribbon. Here is how to add it to the quick access toolbar:
1. Right click the quick access toolbar and choose customize quick access
2. In the choose commands from drop down list, choose commands not in the
ribbon
3. In the list box on the left, select form
4. Click the add button to add the selected command to your quick access toolbar
5. Clicvk ok to close the Excel options dialog box
To use a data entry form:
1. Arrange your data so that Excel can recognize it as a table by entering
headings for the columns into the first row of your data entry range.
2. Select any cell in the table and click the form button on your quick access
3. Fill in the information
4. When you complete the data form, click the new button.
When you use either of these shortcuts to enter a date or time into your
worksheet, Excel enters a static value into the worksheet.
Number Formatting
Number formatting refers to the process of changing the appearance of values
contained in cells. Excel provides a variety of number formatting options. The
formatting that you apply works with the selected cell or cells. Therefore, you
need to select the call before applying the formatting. Also remember that
changing the number format does not affect the underlying value. Number
formatting affects only the appearance.
Values that you enter into cells manually are unformatted. In other words, they
simply consist of a string of numerals. Typically, you want to format the
numbers so that they are easy to read or are more consistent in terms of the
number of decimal places shown.
If you move the cell pointer to a cell that has a formatted value, the formula
bar displays the value in its unformatted state because the formatting affects
only the way the value appears in the cell. There are a few exceptions. When you
enter a date or a time, Excel always displays the value as a date or time, even
though it is stored internally as a value. Also, values that use the percentage
format display with a percent sign in the formula bar.
Automatic Number Formatting
Excel is able to perform some formatting for you automatically. For example, if
you enter 12.2% into a cell, Excel knows that you wanty to use a percentage
format and applies it for automatically. If you use commas to separate
thousands, Excel applies comma formatting for you. And if you precede your value
with a dollar sign, the cell is formatted for currency.
A handy default feature in Excel makes entering percentage values into cells
easier. If a cell is formatted to display as a percent, you can simply enter a
normal value. To enter values less than 1%, precede the value with a zero. If
this automatic percent entry feature is not working, access the Excel options
dialog box and click the advanced tab. In the editing options section, locate
the enable automatic percent entry check box and add or remove the check box.
Formatting Numbers Using the Ribbon
The Home>Number group in the ribbon contains controls that let you qikcly apply
common number formats. The number format drop down list contains 11 common
number formats. Additional options in the home?number group include an
accounting number format drop down list, percent style button, and a comma style
button. The group also contains a button to increase the number of decimal
places and another to decrease the number of decimal places.
When you select one of these controls, the active cell takes on the specified
number format. You can also select a range of cell before clicking these
buttons. If you select more than one cell, Excel applies the number format to
all the number cells.