Basic
Excel Concepts
© Mike Splane 2012
Also see: http://www.cob.sjsu.edu/splane_m/2010/excelguide.htm
Cell Names
An Excel
spreadsheet is similar to a tictactoe grid, only much bigger. The squares in
the worksheet are called cells. Every cell has a name. Select a cell with the
mouse and you will see a letter highlighted at the top and a number highlighted
on the left. The name of the cell is the combination of that letter and number.
The name of the cell also appears in the Name Box, above column A.
Moving Around the Worksheet
Pressing
Tab moves the cursor to the next cell on the right, Enter moves it to the next
cell down, Shift + Tab moves it to the next cell on the left. You can also
shift the cursor with the arrow keys and the Home, End, Page Up, and Page Down
keys. Use the Ctrl key with the arrow keys to move the cursor the maximum
distance. You can also move the cursor into a new cell by typing the cell
address in the Name Box. Doubleclicking on a cell wall moves you to the edge
of a row or column of data.
Entering Characters, Dates, and Numeric Data
Select a
cell. Enter text or numbers by typing in the cell, or in the formula bar that
is centered directly above the worksheet. Enter dates as mm/dd
or mmdd for the current year. Enter the date as
either mm/dd/yyyy or mmddyyyy
for other years.
Editing Cell Contents
·
Select the cell and then click in the formula bar to
edit its contents, or select the cell and type in a new entry, or select the
cell; then press F2 to edit directly in the cell, or select the cell and double
click to edit directly in the cell.
·
Click and drag to move the contents of a cell.
·
Use shortcut keys to undo or redo a step: Ctrl + Z
for undo, Ctrl + Y for redo.
Copying
There are several ways to copy and paste. Use the mouse to highlight the
contents in the cells that you want to copy from. Do not highlight the formula bar area!
That will delete the cell contents!
·
Press Ctrl + C. Highlight the area to copy into.
Press Ctrl + V. Whatever you pasted is still on the
clipboard, so you can paste multiple times with this method.
·
Or, click on the Copy icon. Highlight the area
you want to copy into. Click on the Paste icon.
·
Or, click the Copy icon. Highlight the area you want
to copy into and press Enter, or highlight the upper left corner of the area
you want to copy into and press Enter. This will clear the clipboard
·
Move the cursor around until you get a small black
cross in the lower right hand corner of the highlighted area. Click and
drag (down or across) to highlight the area you want the formula copied
into. Release the mouse.
Formatting Cells From the
Home tab
·
The Font group contains icons for text options (Font,
Size, Bold, Italics, and Underline) you’re probably familiar with from Word. It
also contains an icon to add borders to cells. The “A”
icons grow/shrink the font size.
·
The Alignment group contains text alignment options
(Left, Center, Right, Indent, and Decrease Indent). It also has an icon to
merge/unmerge cells. Other icons let you position text vertically in the cell,
wrap text, or rotate text.
·
The Numbers group contains icons for formatting
numbers as accounting style, percents, or comma style. Two icons increase or
decrease the number of decimal places. A pulldown menu gives you other
formatting options.
·
The Styles group provides options to apply special
formatting.
Working With Columns and Rows
·
The Home tab, Cells group has two icons, insert and
delete, used to manage formatting for columns and rows. Options include
deleting, inserting, changing column width or cell height, and hiding/unhiding
columns.
·
You can find these same options by rightclicking on
a column or row heading.
·
You can also use the mouse to change row heights and
column widths. Just click and drag between the row or column indicators.
·
To resize a column’s width to match its widest
content, doubleclick to the right of its columnletter indicator.
The Format Dialog Box
·
To quickly select large areas of the worksheet
·
mouse over large groups of cells,
·
or click on one or more column letters to select
columns,
·
or click on one or more row numbers to select rows,
·
or click on
the gray cell in the upper left hand corner, above the 1, to select all of the
cells in the worksheet.
·
After you have selected cells to format, rightclick
and select format cells to open a dialog box. You can also access this dialog
box by pressing Ctrl + 1
·
The numbers tab in the dialog box is used to change
the numbering style.
·
The alignment tab lets you choose how letters are
displayed within a cell.
·
Other tabs give you more options.
Copying Formatting
·
Select a cell with the desired format. This is called
the source cell.
·
Click the Paintbrush icon on the Home tab.
·
Drag the mouse across cells, row numbers (to select a
row), or column letters (to select a column) to apply the format. The format
will be copied into the selected cells. Try using the arrow keys to quickly
apply the format to any cell next to your source cell.
Formatting
Text and Numbers Practice Examples
Formatting
Cells Practice Examples
The AutoFill Handle
·
You can use a builtin feature of Excel, the AutoFill
handle, to enter a series of dates; to extend a series of numbers; to copy
formulas; and to copy text.
·
Position the cursor in the lower right hand corner of
a range. A small + appears. Drag in any direction.
Working with Formulas
A worksheet consists of a set of cells (boxes)
aligned in rows and columns. Each cell can contain a string (letters or numbers
mixed with letters), a number, or a date. Each cell is referred to by the
letter of the column it appears in, followed by its row number.

Column A 
Column B 
Column C 
Column D 
Row 1 
3 
5 


Row 2 
Text Message 
March 18, 2002 


·
In Column A Row 1 the
content of cell A1 is a number with the value 3.
·
In Column B Row 1 the content of cell B1 is a number
with the value 5.
·
In Column A Row 2 the
content of cell A2 is a label “Text Message.”
·
In Column B Row 2 the content of cell B2 is a number,
37333. The number has been formatted to appear as a date. Why is it a number?
So you can add and subtract dates or easily put them in calendar order. Excel
assigns a number to each date by counting the number of days since January 1,
1900.
·
A cell can also contain a formula. A formula is a
mathematical equation. Formulas always start with an = sign. The result of
calculating the equation will appear in the cell. The actual formula appears in
the formula bar area, above the worksheet, when the cell containing the formula
is selected.
·
If you use a cell name (called a cell reference) in a
formula, Excel uses the value found in that cell to calculate the result of the
formula. For example, if you enter =A1 + 5 as a formula in cell C1 Excel will
look into cell A1. It sees a 3 in that cell, so it converts the formula into
the form = 3 + 5. The answer, 8, will be displayed in cell C1. The formula bar
will display the formula, =A1+ 5.
Remember: formulas always
start with = signs! When you enter these statements or formulas in cell
C1, here is what will be displayed:
These strings in cell
C1 
Display
this in cell C1 

These
formulas in cell
C1 
Display
this in cell
C1 
5+3 
5+3 

=5+3 
8 
B1 + 3 
B1 + 3 

=B1 + 3 
8 
A1+B1 
A1+B1 

=A1+B1 
8 
5+B1 
5+B1 

=5+B1 
10 
Order of
Operations (PEMDAS)
If a
formula contains multiple calculations, Excel calculates the parts of the
formula in this order: everything in parentheses, then exponents, then
multiplication and division, and finally addition and subtraction, always
moving from left to right. For example = 3+4*2 will display 11 because Excel
would do the multiplication first, while = (3+4)*2 will display 14because Excel
would do the addition inside the parentheses first.
Formulas
Combining Strings of Text
To combine text strings use the ampersand (&) symbol in the formula. Strings
must be enclosed in quotes. For example =”Splane”&”,”&”Mike”
would display Splane,Mike.
You don’t have to type a string, instead you can use a
cell reference to a cell with text in it.
If cell A4 contained the name Splane, and cell B4 contained the name
Mike, you could combine the names using the formula =A4&”,”&B4. Note
that cell references are NOT in quotes.
Displaying
Formulas
Select a
cell and its formula will be displayed in the formula box located above the
worksheet area. To view all of the cells as formulas, press Ctrl + ~ (tilde).
The tilde key is in the upper left corner of the keyboard. This same keystroke
combination returns you to the normal view. There is also a checkbox on the
View tab where you can reset the worksheet to display formulas.
More
Tools & Tips Practice Examples
Working
with large groups of cells:
·
Rather than listing all of the cells in a large block
of the worksheet you can use a shortcut method to describe any group of cells
that form a rectangle.
·
The term range is used to refer to a rectangular
block of cells.
·
Enter the cell address from the upper lefthand
corner of the range, followed by a colon and the cell address from the lower
righthand corner. Excel will recognize this as a range and will include all of
the cells inside its borders when it performs calculations.
·
You can also assign a word to describe a range. Start
by mousingover the cells in the range. Then type the
word you want to use to describe the range into the name box and press enter
key. The name box is right above column A in the worksheet.
·
If you use a range name in a formula, do not include
quotation marks.

A 
B 
C 
D 
E 
F 
1 






2 




Range 2 

3 

Range 1 
Range 1 

Range 2 

4 

Range 1 
Range 1 

Range 2 

5 






6 


Range 3 
Range 3 
Range 3 

7 


Range 3 
Range 3 
Range 3 

8 






Relative Cell References

C 
D 
E 
Comment 
4 
= A1+B2+C3 

= C1+D2+E3 
Letters change when you copy a formula into a different column. 
5 




6 
= A3+B4+C5 

= C3+D4+E5 
Numbers change when you copy a formula into a different row. 
The formula in cell C4 was copied into cells E4, C6, and E6. Note how
the letters and numbers change. 
Cell
references, when
written like those in the table above, are called relative cell references.
Cell addresses written in this format will change when the formula is copied
into other cells. The rowindicator numbers change when the formula is copied
into a different row. The columnindicator letters change when the formula is
copied into a different column.
Absolute and mixed cell references
·
You can write cell references in a different form.
Adding dollar signs to cell references tells Excel that you don’t want those
cell references to change when the formula is copied. The part of the cell
reference following each $ will not change.
·
The dollar sign does not change the result of the
formula. = A1+B2 and = $A$1 + $B$2 will display exactly the same thing.
·
Cell references can have
dollar signs in front of just the number, = C$3, or in front of just the
letter, = $C3. These are called “mixed cell references.” Cell references with dollar signs in front of
the letter and number, = $C$3, are called “absolute cell references.”
·
If you want
to create a set of formulas that will always refer to either one specific
row: A$1, or one specific column: $B2, use a mixed cell reference.
·
For a formula with a fixed reference to a specific
cell, use an absolute cell reference.
·
You can combine absolute, mixed, and relative cell
references in a single formula. In the following table, the formula in cell C4
was copied into other cells. Observe which row and column numbers changed and
note the effect of the dollar signs.

C 
D 
E 
Comment 
4 
= A$1+$B2 

= C$1+$B2 
Notice that only the A and the 2 (in cell C4) change. Because of the $
symbol, the others don’t change. 
5 




6 
= A$1+$B4 

= C$1+$B4 
·
One use for an absolute reference is to divide
numbers in several cells by the same divisor, to find percentages. The formula
can be written once, using an absolute cell reference to refer to the cell
containing the divisor. Then it can be copied into other cells. For example =
A4 / $B$7.
Working with Multiple Worksheets
Look at
the bottom of your worksheet. You are given three worksheets to work in when
you open a new Excel file. Click on a worksheet tab to move into a different
worksheet.
By
rightclicking on a worksheet tab, you can insert and delete worksheets, move
and copy worksheets, rename a worksheet, select multiple worksheets to work on
simultaneously, and add colors to the tabs.
Formulas Using Cells in Other Worksheets
To write
formulas that refer to cells in other worksheets, add the worksheet name,
followed by en exclamation mark, before the cell address. For example, =Sheet2!C4 will show the contents of cell C4 in Sheet 2. If a
worksheet is renamed, Excel automatically corrects any cell references that
referred to cells in the renamed worksheet.
Formulas Involving Arrays
You can write
formulas using range notation instead of cell references. For example you might
want to multiply two columns, multiplying the number in each row by its
counterpart in the same row. Start by selecting the output area. Then type in
the formula, using range notation. For example highlight cells D1 to D4 then
type in this formula = A1:A4 * B1:B4. Press the key combination Ctrl + Shift +
Enter to simultaneously enter the formula into all the cells in the selected
range.
Boolean Formulas
You can write
formulas in Excel to test the value in a cell. If the value matches the Boolean
formula’s terms, Excel displays “True.” If they don’t match, “False” is
displayed.
Examples: = A1 < 4, = A1 = “Red” =
A1 < 10 = A1 <>
5
These formulas
will all evaluate as either True or False, depending on the contents of cell
A1. Note that when you compare text strings, the string must be in quotes.
Boolean formulas are used as a test condition in IF Functions, as described
below.
Some common error messages:
Error
messages start with a pound sign (#).
·
#####
If you see railroad tracks, your
column is too narrow. Solution  widen the column.
·
#DIV/O! You are dividing by an empty cell or zero. Solution
 fix the formula’s denominator to refer to a cell that contains a nonzero
number.
·
#REF!
Your formula refers to a range or cell that no longer exists, due to a
change in the worksheet.
·
#NAME?
Your formula contains text that
Excel doesn’t recognize. This could have many causes. Usually it’s a typo or
missing punctuation in a formula.
·
CIRCULAR If a cell
reference in a formula refers to the cell where the formula is entered, Excel
will not be use the formula. For example, if =C4+5 was entered into cell C4.
The “circular “error message will appear in the status bar.
Troubleshooting
Practice Examples
Examples of commonly used formulas: There is no one right way to write
a formula; getting the correct result is what matters. Spaces are not necessary
in formulas, but were included in the examples to make them easier to read.
This table is used with the sample
formulas below:

A 
B 
C 
D 
1 
25 
28 
95 

2 
42 
91 
14 

3 
3 
4 
5 

·
SAMPLE FORMULAS:
Type of
Equation: 
Entered in
Cell D3: 
Result Displayed in D3: 


Addition of Two
Cells 
= A2 + B3 
46 


Addition of a
Constant 
= B1 + 25 
53 


Addition of a Row of
Cells 
= SUM (A1:C1) 
148 


Addition of a Column
of Cells 
= SUM (B1:B3) 
123 


Addition of a 
= SUM (B1:C3) 
237 


Addition of
Scattered Cells 
= SUM (A2,B1,C3) 
75 


Subtraction of a
Constant 
= C1 – 10 
85 


Subtraction of a
Cell 
= B2 – B1 
63 


Multiplication by a
Constant 
= A3 * 20 
60 


Multiplication of
Two Cells 
= B3 * C3 
20 


Multiplication by a
% 
= A1 * .40 
10 


Multiplication by a
% 
= B1 * 25% 
7 


Division by a
Constant 
= C1 / 5 
19 


Division by a Cell 
= A2 / C2 
3 


Exponentiation
(Squaring) 
= B3 ^ 2 
16 


Exponentiation
(Cubing) 
= A3 ^ 3 
27 


Square Roots 
=SQRT(A1) 
5 


Square Roots 
= A1 ^ 0.5 
5 


Cube Roots 
= B1 ^ (1/3) 
3.036589 


Increasing by a
Percentage (4%) 
= A1 + (A1 * .04) 
26 


Increasing by a
Percentage (4%) 
= A1 * 1.04 
26 


Increasing by a
Percentage (4%) 
= A1 + (A1 * 4%) 
26 


Decreasing by a
Percentage (8%) 
= A1  (A1 *.08) 
23 


Decreasing by a
Percentage (8%) 
= A1 *.92 
23 


Decreasing by a
Percentage (8%) 
= A1 – (A1 * 8%) 
23 


Calculate a
Percentage (Part/Sum) 
=A3 / $D$3 
25% (Format as a %) 


Average of a Column 
= AVG (B1:B3) 
41 


Average of a Row 
= AVG (A3:C3) 
4 


Average of a Range 
= AVG (B1:C2) 
57 


Formula referring to
a cell in another worksheet 
= Sheet2!C4 
The contents of cell C4 in Sheet2. 







Functions
Excel has a builtin set of advanced formulas, called functions. Usually
a function needs to be given one or more pieces of data (each piece is called
an argument) before it will work. The arguments are listed inside parentheses
following the name of the function. Each argument is separated by a comma.
Here are some commonly used functions:
SUM Example
= SUM(C3:G9)
A range is the required argument.
Result: Total of all numbers in the range.
AVERAGE Example
= AVERAGE(C3:G9)
A
range is the required argument.
Result: Average of all numbers in the range.
ROUND Example = ROUND(A4/3,2)
A
formula is the first argument.
The second argument is the number of decimal places
to keep .
Result: if A4 = 5 then 5/3 = 1.6666 which is rounded
to 1.6700.
TODAY() Example = TODAY()
This
function does not need an argument.
Result: The current date is displayed.
SUMPRODUCT Example = SUMPRODUCT(A1:B3,C1:D3)
This function multiplies the contents of each
individual cell in one range by its counterpart in another equally sized range
and displays the total.
The result is a single number.
SUMIF Example = SUMIF(B1:B3,”>5”,C1:C3) This
function searches the range given in the first argument to find cells which
match the criteria given in the second argument. The values from the
corresponding cells in the range in the third argument are summed. In our
example the result = 104 (91 plus14)
·
The Sum
Function
You can create Sum functions with the AutoSum icon (S) found on the Home tab. There are several different ways to
create functions with the icon.
1.
Position the cursor in a cell where you want a total
and click on the icon. Use the mouse to select the range of cells whose
contents will be included in the total.
2.
Highlight a column of data and a blank cell
underneath). Click the icon.
3.
Highlight a row of data (include a blank cell at the
right). Click on the icon.
4.
Highlight a range, including a column of blank cells
to the right and a row of blank cells below the range. Click on the icon to
quickly total all the rows and columns.
5.
Position the cursor in a cell where you want a total.
Click on the icon. Hold down Ctrl while selecting cells from different areas.
IF Functions
The IF function works with
Boolean formulas. The words TRUE and FALSE that a Boolean displays are replaced
with numbers, formulas, functions, or text strings.
In Excel, an IF Function has three arguments.
The first argument is the Boolean
formula that compares the contents of a cell to a value.
The second and third arguments give two options of what to display. If
the Boolean formula evaluates as “True” the second argument is displayed. If
the equation evaluates as “False” the third argument is shown.
Boolean 
C3 is > 80 


If True 



If False 


Show “FAIL” 
We would write the function like this: =IF(C3>80,”PASS”,”FAIL”)
Commas separate each argument.
If the value in cell C3 is greater than 80, then the condition is met.
The word PASS will appear. If the value in cell C3 is NOT greater than 80 then
the condition is not met. The word FAIL
will appear.
The second and third arguments in IF
Functions can be:
·
Text (called strings), always written between double
quotes, or
You can even use an IF Function as an argument. This is called a nested IF.
Example: =IF(B9
= “Yes”, 21, IF(B10 = “Yes”, 22,23))
If Functions Practice Examples
VLOOKUP Functions
VLOOKUP functions are used to find information from a table. For
example, you could have a table showing the sales tax rates for different
counties. You can give Excel the name of a county and it will show the tax rate
for that county.
A VLOOKUP Function has three arguments and looks like this: =VLOOKUP(C4,G5:H7,4)
The first argument is always a cell reference to the input cell where
the user types in what to look up. Excel will look in the table for a match to
the contents of the input cell and extract data from that row.
The second argument tells Excel the location of the table. It can be
expressed as range notation or as a range name. The range includes the data and
the row labels but not the column labels. The labels must be in the first
column of the table and must be in alphabetical order.
The third argument, which is always a number, is the location of the
column in the table which will contain the requested information. The first
column in the table is designated as 1, the second is
designated as 2, etc.
LOOKUP Functions Practice Examples
A common problem with Vlookup functions occurs
when the entry in the input cell does not match anything in the table. Excel
shows the closest match. To make sure the output data is always accurate you
should prevent invalid entries into the input cell. Select the cell(s) where
data entry should be restricted. On the Data tab, in the Data Tools group,
click the Data Validation icon. On the Settings tab, under Validation Criteria,
choose List in the Allow box. Then either type a list directly into the source
box, using commas to separate each item in the list, or click on the icon in
the Source Box. Then use the mouse to highlight the cells that contain your
list. Press Enter and click OK.
Data Restriction Practice Examples
Installing the Analysis DataPak in Office 2007
The
Analysis ToolPak contains some useful statistical
analysis tools. It does not come preinstalled; you have to install it
yourself. Here’s how:
Open
Excel. Click on the Office button. At the bottom of the task pane click on
Excel Options. A new task pane will open. On the left hand side, find AddIns
and click on it. A dialog box will open.
Find the
“manage” box at the bottom and select “Excel Addins.” In the AddIns Available
list, select Analysis ToolPak and also Analysis ToolPak – VBA. Click OK.
Follow any
instructions that appear on the screen. After the ToolPak
installs, you will see a new tab on the ribbon, labeled AddIns.
Building
Charts Using Excel
Building a chart is a process with
several steps.
Select the cells containing information needed to build the
chart. Include cells containing data and cells with text for making labels.
Select a chart type from the Insert tab.
Create the chart.
Reposition the chart in the datasheet or move it to a
separate worksheet.
Use the
three Charts Toolbars to customize the chart.
To select data before building the chart: Mouse
over the cells that contain the information you need for the chart. If the
cells are in separate areas of the worksheet, mouse over one area, then hold
down the Ctrl key while you mouse over additional areas. If only one of the
cells appears to be selected, that’s ok; Microsoft didn’t make the shading
color dark enough to show up on a bright monitor. Release Ctrl.
To change the chart after it has been
created. Rightclick
in the chart background. Choose from the options.
To change the appearance of any object in the chart:
Rightclick on it. Choose the format option at the bottom of the menu. To add
colorful background effects click on “Fill Effects.”
To work with individual data points: Click on
the data series. All points are selected. Click again on the individual data
point. The single point is selected.
To edit the title: You can replace text in titles by clicking on
the text box, then entering new text, then pressing Enter. To add additional
lines to a title, click twice in the text. Move the cursor to the end of the
line and press Enter. Then type another line of text. When you finish click
anywhere on the chart.
To combine graphics with a chart: Use icons
on the Insert tab to add graphic elements. Text boxes, arrows and lines, clip
art, WordArt, and pictures can make a plain chart look spectacular.
Graphing an equation: Create a table in the worksheet. Enter a label at the top of
the X values column. In the next column add a label to describe the equation.
Enter numbers into the X Values column. In the top cell of the Equations
column, next to the first X value, enter your equation as a formula or
function. Copy the formula down so the formula is alongside each X value. Mouse
over the equation column. On the Insert tab, click on the Line icon and select
the first chart.
Now add
labels to the X Axis of the chart. On the Chart Tools Design tab click on
Select Data. In the Horizontal Category Axis Labels box click on Edit. Click on
the cell selector icon and then mouse over the X values. Press Enter and OK
twice to add the labels to the chart.
Chart Creation Practice Examples
Chart Formatting Practice Examples
Excel has database capabilities that are suitable for
small and simple tasks. Microsoft Office includes a full database application
called Access. Access can be used for tasks that require more complex data
management capabilities.
A data table consists of one row of labels, called
field names or headers, and several rows of data. The headers row is always the
first row, above the rows of data. The rows of data should not contain any
blank cells.
Tables should be separated from other areas in the
worksheet by a blank row and a blank column. This helps Excel determine which
cells are included in the database table. You can select any cell in the data
table to work with the entire set of data; you do not have to select the entire
table.
Sorting
Data
You can use the AZ or ZA icons on the Data tab to
sort data in a table. First click on any cell in a column that contains data.
Then click on the AZ or ZA icon. Data in the selected column will be sorted.
The other columns in the table will be rearranged so each row of data stays
intact.
If your column contains numbers, the AZ icon will
put the numbers in order, starting with the smallest. The ZA icon will put the
numbers in order, starting with the largest.
You can use the Sort icon to simultaneously sort by
more than one field. Click in the data. Click on the icon. Select a column to
sort by and then select the order Click on Add Level to sort the data into
subsets of the first selection. For example, you might want to sort by last
name and then by first name.
Creating a
Filtered List
Click on any cell in the data table. On the
Data tab, select Filter. Pulldown arrows will appear next to the column
headings. To hide some of the records, click on the pulldown arrow in a field.
Every entry that has a checkmark next to it will be displayed. Remove the
checkmarks for any records that you wish to hide.
Displaying Subtotals
You can do mathematical calculations on any columns that contain
numbers. These calculations can be broken down to include subtotals for the
results of similar records. Sort one of the columns that contain text, to group
similar records. Click on any cell in the data table. On the Data tab select
Subtotals. In the first dialog box select the column you just used to sort the
data. Then select the type of calculation and the fields to work with.
Database Tools Practice Examples
Pivot Tables
Pivot tables
give you tremendous flexibility in studying and manipulating large amounts of
data. You can sort multiple fields of data using multiple criteria, and
manipulate the data in several different ways. Pivot tables are particularly
useful for analyzing marketing and manufacturing data involving multiple
factors.
Create a
Pivot Table Report
Click anywhere in the data table.
From the
Insert tab, Tables group, click the Pivot Table icon to open the Create Pivot
Table dialog box. Click OK.
The Pivot
Table Field List should be open on the right side of the worksheet. To create
the pivot table you choose fields and drag them from the top of the task pane
into the boxes in the lower right corner. Fields containing text can be are
used as filters, as row names, and as column names. Fields containing numbers are used in the
values area.
You can
add more than one field to any section.
You can
add multiple instances of a single value field, and then customize them to show
different mathematical operations: count, average, sum, etc.
If the
task pane disappears, click on the pivot table to reopen it.
Pivot tables Practice Examples
Macros
A macro is
a recorded set of keystrokes and mouse clicks. When you create a macro, Excel
records every mouseclick and keystroke you take as you perform the task. The
steps are converted into a series of Visual Basic commands. After the macro is
created you can run it, as often as you like, to repeat the steps.
To create
a macro, go to the View tab and click on the pulldown arrow below the Macro
icon. Select record macro. Fill in the name and description of your macro, and
then start executing your planned series of keystrokes. If you make a mistake
when you record the macro, corrections you make are also recorded. When you
have completed the macro, go back to the macro icon pulldown box and select
stop recording, or click on the stop macro icon in the status bar.
After you
record a macro, you can review the Visual Basic code and make changes.
To play a
macro, go to the View tab and click on the pulldown arrow below the Macro
icon. Select play macro. An alternative method is to first insert a shape into
your worksheet and then rightclick and select assign macro. Pick a macro from
the list. Add a label to the shape to you know which macro it runs. Click on
the shape whenever you want to run that macro.