Download Report

Contact: [email protected]
www.piuha.fi
Excel 2013
BASIC LEVEL AND ADVANCED TOPICS
Microsoft Excel 2013
Language: English
Revision 1.0
Updated 3.3.2015
Copyright © 2015 Piuha Works Oy
Spreadsheets – Microsoft Excel 2013
3/75
Table of Contents
ABOUT THIS MATERIAL ...................................................................................................... 6
A COMBINATION: BASIC LEVEL AND ADVANCED TOPICS ...................................................................... 6
VERSION COMPATIBILITY AND LANGUAGE ........................................................................................... 6
STARTING EXCEL ................................................................................................................ 6
THE RIBBON-BASED USER INTERFACE ........................................................................... 7
THE FILE TAB ................................................................................................................................... 8
THE RIBBON AND TABS ..................................................................................................................... 8
THE QUICK ACCESS TOOLBAR ........................................................................................................... 9
THE MINI TOOLBAR ......................................................................................................................... 10
KEYBOARD SHORTCUTS .................................................................................................................. 10
SHORTCUT MENUS AND THE MINI TOOLBAR – THE RIGHT MOUSE BUTTON ........................................ 10
LIVE PREVIEW ................................................................................................................................ 11
SAVE FORMATS, THE ONEDRIVE CLOUD SERVICE, AND CREATING A PDF ............. 11
EXCEL 2013'S FILE FORMAT AND COMPATIBILITY ............................................................................. 11
THE ONEDRIVE CLOUD SERVICE AND EXCEL ONLINE ....................................................................... 12
CONVERTING A FILE TO PDF FORMAT.............................................................................................. 13
THE BASICS OF EXCEL ..................................................................................................... 14
THE EXCEL W INDOW 'S BASIC PARTS AND TERMS ............................................................................. 14
THE EXCEL W ORKBOOK AND W ORKSHEETS ..................................................................................... 15
Adding, Deleting, and Renaming worksheets ............................................................................ 15
Moving and Copying Worksheets .............................................................................................. 16
THE PARTS OF THE W ORKSHEET: COLUMN, ROW , AND CELL............................................................. 17
MOVING IN THE WORKSHEET AND ENTERING DATA ................................................... 17
CHANGING COLUMN W IDTH AND ROW HEIGHT ................................................................................. 18
SELECTING THE WORKSHEET AND ITS PARTS ............................................................ 18
SELECTING THE ACTIVE CELL .......................................................................................................... 19
SELECTING A CELL RANGE .............................................................................................................. 19
SELECTING A W HOLE COLUMN OR ROW ........................................................................................... 19
SELECTING SEVERAL DIFFERENT RANGES ....................................................................................... 19
SELECTING A W HOLE W ORKSHEET .................................................................................................. 19
DELETING, MOVING, AND COPYING DATA ..................................................................... 19
EMPTYING CELLS ........................................................................................................................... 19
DELETING, ADDING AND HIDING ROWS AND COLUMNS ...................................................................... 19
UNHIDING ROWS AND COLUMNS ...................................................................................................... 20
MOVING CELL RANGES ................................................................................................................... 21
COPYING CELL RANGES .................................................................................................................. 21
COPYING WITH THE FILL HANDLE ..................................................................................................... 21
SERIES AND LISTS WITH THE FILL HANDLE........................................................................................ 22
FLASH FILL ..................................................................................................................................... 22
FORMATTINGS TABLES AND CELLS .............................................................................. 23
FORMATTING TABLES AND CELLS WITH THE HELP OF STYLES ............................................................ 23
Table Styles .............................................................................................................................. 23
© 2015 Piuha Works Oy
www.piuha.fi
Spreadsheets – Microsoft Excel 2013
4/75
Cell Styles ................................................................................................................................. 24
OTHER FORMATTING TOOLS ........................................................................................................... 24
Borders ..................................................................................................................................... 25
Cell Background and Font Color ............................................................................................... 25
COPYING FORMATS WITH THE FORMAT PAINTER .............................................................................. 26
COMBINING CELLS .......................................................................................................................... 26
CELL NUMBER FORMAT (NUMBER CATEGORY) ................................................................................. 27
Changing Number Format ......................................................................................................... 28
FORMULAS AND CELL REFERENCES ............................................................................. 29
USING THE AUTOSUM BUTTON ........................................................................................................ 29
OTHER BASIC FUNCTIONS OF THE AUTOSUM BUTTON ...................................................................... 29
FORMULAS AND CALCULATION OPERATORS ..................................................................................... 30
CELL REFERENCES ......................................................................................................................... 30
Relative Reference ................................................................................................................... 32
Absolute Reference................................................................................................................... 32
Mixed References ..................................................................................................................... 32
ERROR MESSAGES ......................................................................................................................... 34
FUNCTIONS......................................................................................................................... 35
SOME COMMON FUNCTIONS ............................................................................................................ 37
AVERAGE, COUNT, MIN and MAX .......................................................................................... 38
CHARTS, HEADERS/FOOTERS AND PICTURES ............................................................. 39
CREATING A CHART ........................................................................................................................ 39
Circle (Pie) Charts ..................................................................................................................... 39
Bar Charts................................................................................................................................. 39
Line Charts ............................................................................................................................... 40
ADDING A CHART ............................................................................................................................ 40
Changing the Location of a Chart .............................................................................................. 42
ADDING A HEADER OR FOOTER TO A W ORKSHEET ........................................................................... 42
ADDING A PICTURE TO A WORKSHEET.............................................................................................. 43
DATA MANAGEMENT WITH EXCEL ................................................................................. 43
CREATING A TABLE (FORMERLY A LIST) ........................................................................................... 43
FORMATTING A RANGE AS A TABLE .................................................................................................. 44
SORTING ........................................................................................................................................ 45
FILTERING ...................................................................................................................................... 45
THE DATA TAB AND THE SORT AND FILTER GROUP ........................................................................... 46
MORE FUNCTIONS ............................................................................................................. 47
ABS .......................................................................................................................................... 47
SUMIF....................................................................................................................................... 47
ROUND..................................................................................................................................... 48
COUNT and COUNTIF ............................................................................................................. 48
CONCATENATE ....................................................................................................................... 49
LOGICAL FUNCTIONS AND COMPARISON OPERATORS ....................................................................... 50
IF .............................................................................................................................................. 50
AND .......................................................................................................................................... 52
OR ............................................................................................................................................ 52
NOT .......................................................................................................................................... 52
LOOKUP AND REFERENCE FUNCTIONS ............................................................................................. 53
VLOOKUP ................................................................................................................................ 53
© 2015 Piuha Works Oy
www.piuha.fi
Spreadsheets – Microsoft Excel 2013
5/75
HLOOKUP ................................................................................................................................ 55
FINANCIAL FUNCTIONS .................................................................................................................... 55
PMT (Payment) ......................................................................................................................... 55
Nested Functions ...................................................................................................................... 56
FV (Future Value) ..................................................................................................................... 57
USING DATES AND TIMES IN CALCULATIONS .............................................................. 57
CUSTOM NUMBER FORMATS IN DATES AND TIMES ............................................................................ 58
Calculating with Dates............................................................................................................... 59
Calculating with Time Values .................................................................................................... 60
PROTECTING WORKSHEETS AND SAVING AS A TEMPLATE ...................................... 60
PROTECTING W ORKSHEETS ............................................................................................................ 60
EXCEL FILE FORMATS AND SAVING A W ORKBOOK AS A TEMPLATE ..................................................... 61
Save Location of Templates ...................................................................................................... 62
MANAGEMENT OF LARGE TABLES ................................................................................. 62
FREEZING HEADINGS ...................................................................................................................... 63
Selection-Based Freezing ......................................................................................................... 63
SETTING PRINT TITLES.................................................................................................................... 64
CALCULATING SUBTOTALS .............................................................................................................. 65
SETTING PRINT AREA ..................................................................................................................... 66
PAGE BREAK PREVIEW ................................................................................................................... 66
PIVOT TABLES ................................................................................................................... 67
WHAT ARE PIVOT TABLES USED FOR? ............................................................................................ 67
CREATING A PIVOT TABLE ............................................................................................................... 67
OTHER USEFUL ACTIONS ................................................................................................ 69
DATA VALIDATION ........................................................................................................................... 69
The List as a Validation Criterion.............................................................................................. 70
THE GOAL SEEK FEATURE .............................................................................................................. 70
NAMED CELL RANGES..................................................................................................................... 72
ADDING COMMENTS TO CELLS ........................................................................................................ 72
RECORDING MACROS ..................................................................................................................... 74
OTHER ................................................................................................................................. 75
SELECTING AND DELETING EMPTY ROWS......................................................................................... 75
© 2015 Piuha Works Oy
www.piuha.fi
Spreadsheets – Microsoft Excel 2013
8/75
The File Tab
When you click the File tab you get to the so-called Backstage view:
Back to the basic view
Most of the settings for
Excel are located here!
Here you can find many of the most basic functions having to do with work files:
•
•
•
•
•
•
Info (open in the picture), where among other things the file's properties – the author's
name and other meta information – can be changed
New, where you can find a new blank workbook or ready-made templates if desired
Open, Save and Save As
Print – Printing, Print Options and Preview handily in the same place
Share – Share a saved file with others via the OneDrive cloud service (see page 12)
Export – Here you can e.g. convert a file to PDF format
Hint: If you want to browse the open/save files directly in the old way 1) in Options select
Save > Don't show Backstage when opening or saving files and 2) add the buttons Open and
Save As to the Quick Access Toolbar (see page 9).
The Ribbon and Tabs
Excel's commands and functions have been arranged in The Ribbon in action-specific tabs.
The tabs contain logical groups of commands and functions.
© 2015 Piuha Works Oy
www.piuha.fi
Spreadsheets – Microsoft Excel 2013
9/75
The Home tabs contains the most important tools from the familiar standard and formatting
toolbars of the previous versions:
Home tab and its groups:
Clipboard, Font, Alignment,
Number, Styles, Cells and
Editing
Also note the little
arrow that opens the
dialog for this group
with all the settings
The Most Important Ribbon Tabs in Excel
File
Home
Insert
Page Layout
Formulas
Data
Review
View
Developer Tools
Opening, saving, printing and sharing
Most frequently needed basic tools
Pictures, charts, and other objects to be inserted into
a table
Settings affecting the appearance of a table
Writing and checking formulas
Data and database tools, sorting and filtering
Reviewing, commenting and protecting
Various views and view settings
Macros and forms among other things (make visible
in Options!)
More action-specific tabs also come available as needed. For example when a chart is
selected the tabs pertaining to chart structure, layout, and modification show up (Chart
Tools: Design and Format).
The Quick Access Toolbar
You can customize the Quick Access
Toolbar by clicking this arrow!
The quick access toolbar, found in the upper left corner, contains general functions needed
in many stages of work. In the diagram are the default tools Save, Undo and Redo. You may
also add your most frequently used functions to the Quick Access Toolbar.
© 2015 Piuha Works Oy
www.piuha.fi
Spreadsheets – Microsoft Excel 2013
12/75
The OneDrive Cloud Service and Excel Online
In order to use Microsoft's OneDrive cloud storage from normal Excel, first make sure that
you have the necessary personal OneDrive account or the organization's Office 365
Sharepoint account. You can see your accounts and add new ones with the command File >
Account:
Personal OneDrive Account
has been set up. Cloud saving
is already possible.
Add the Office 365
SharePoint account for your
organization
© 2015 Piuha Works Oy
www.piuha.fi
Spreadsheets – Microsoft Excel 2013
18/75
Using the Keyboard to Move in the Worksheet
Key Command
Action
Tab
Next cell to the right (accepts entered data/formula)
Shift + Tab
Previous cell to the left (accepts data/formula)
Enter
Next cell down (accepts data/formula)
Ctrl + Enter
Accepts the same value for all selected cells at once
Arrow Key
The next cell in the arrow's direction (accepts data, not formulas)
Ctrl + Arrow
The table’s last (data-containing) cell in the arrow's direction
Ctrl + Shift +
Arrow
Selects from the current cell to the end of the table in the direction of
the arrow
F2
Start modifying data already existing in the chosen cell
ESC
Discard entered data (old data remain)
Home
Beginning of row
Ctrl + Home
Beginning of worksheet
Ctrl + End
End of worksheet
Ctrl + Page Down
Next worksheet in workbook
Ctrl + Page Up
Previous worksheet in workbook
Changing Column Width and Row Height
You can change the width of columns by clicking between the column headings (letters A-C
in the picture) and dragging with the mouse:
You can change the height of rows in the same way by clicking
and dragging between the row numbers.
Double clicking between the desired column headings creates an automatic fit with the
column's longest data entry. You can also autofit several columns at once by painting over
several columns with the mouse and double clicking between two selected column headings.
You can adjust several columns to equal width by selecting the desired columns with the
CTRL button and dragging one of the columns to the desired width. You can adjust the row
height in the same way.
Selecting the Worksheet and Its Parts
It is necessary to select a certain area of cells for many purposes: formatting cells, emptying
cells, adding rows or columns, and referring to cell references.
© 2015 Piuha Works Oy
www.piuha.fi
Spreadsheets – Microsoft Excel 2013
20/75
Add an empty row or column (cell
references and formulas still function).
Delete a selected row or column (will
not leave an empty row, cell
references and formulas still function).
Only clears contents (an empty row or
column is left, not the same as
DELETE).
Hide row or column.
You can also use the Add and Delete buttons in the Cells group in
the Home tab. Then you can delete a column by simply selecting
one cell in the column and Delete → Delete Sheet Columns
Unhiding Rows and Columns
In the example in the image the hidden C column is being unhidden:
1
Select columns B to D with the mouse. (All the
hidden columns between the selected columns will
be unhidden.)
2
Right click on one of the column headings
3
Choose Unhide from the shortcut menu.
© 2015 Piuha Works Oy
www.piuha.fi
Spreadsheets – Microsoft Excel 2013
24/75
Cell Styles
Ready-made styles are also available for the smaller parts of a table. These can help the
reader discern content. Select your cells, click Cell Styles and find a suitable style for the
cells in the menu:
2
1
3
Other Formatting Tools
The above-described ready-made table and cell styles are an easy and quick way to format
a table. When you want to finish the end result you can use the formatting tools, already
familiar from the previous version, found in the Home tab or the cell’s quick access toolbar.
Border
Cell
background
color
Font color
Opens Format Cells dialogue
box where you can find all
possible formats
© 2015 Piuha Works Oy
www.piuha.fi
Spreadsheets – Microsoft Excel 2013
29/75
Formulas and Cell References
Using the AutoSum Button
The most common calculation procedure carried out in Excel is addition. The AutoSum
button in the Formulas group of the Home tab exists for this purpose. The button is easy to
use when you want a sum to appear immediately under or beside the added numbers:
1. Select the cell where you want the sum to
appear
2. Click the AutoSum button, at which point
Excel will suggest a range above or beside
the cell with a dotted line
3. Accept the suggested range by pressing
Enter
If you want you can also first choose the cells you
want to add together and click the AutoSum
button. The sum will appear underneath or beside
the selected numbers. This is useful when the
added numbers are in the middle of other numbers
– the sum will contain only the desired numbers
and a formula will not be accepted separately
when pressing Enter.
The shortcut key for calculating a sum is Alt + =
(so Alt + Shift + 0).
Other Basic Functions of the AutoSum Button
Through the arrow on the right edge of the AutoSum button you can
open a menu in which you can find frequently used basic functions:
Average returns the average of the numbers in the range, Count
Numbers returns the number of cells containing numbers, Min
returns the range's smallest number and Max the largest.
These basic functions are used in the same way as the Sum function
above. More about functions on page 35.
© 2015 Piuha Works Oy
www.piuha.fi
Spreadsheets – Microsoft Excel 2013
32/75
Now the formula will work in other rows as well when you copy it by dragging with the fill
handle. If you try changing the discount percentage in cell F3 you will notice that all the
prices are updated immediately.
Relative Reference
Ordinary references made with the mouse or keyboard, for example A1, C3 or B5:B10 are
called relative references. When a formula is copied, these references automatically
change relative to the original location.
The formula in the above example had the relative reference B2 (reduced price), which
changed to B3 when the formula was copied to the next row down, in the next row to B4,
and so on. So the reduced price is always calculated using the neighboring cell in the same
row. This is useful in most situations.
Absolute Reference
In the same example we already encountered a situation where a reference must not change
when copying the formula. First we used the mouse to put a normal relative reference in cell
F3 (discount percentage) and then we changed it to an absolute reference by pressing F4
key on the keyboard. The reference then changed to the form $F$3: the dollar signs in front
of the column letter and row number show that it has been locked into an absolute reference.
An absolute reference does not change when you copy the formula. If you want to
make a reference absolute, press F4 key at the reference’s location in the formula. Then
Excel will lock the reference to always mean this cell even if you copy the formula.
Relative
Reference
• A1
• F3
• A1:C10
Mixed References
Absolute
Reference
• $A$1
• $F$3
• $A$1:$C$10
DOES NOT CHANGE
WHEN COPIED!
In certain cases you need to create a formula where only either the row or column changes
and the other stays the same. In these cases the $ sign shows up in front of only either the
column letter or row number to be locked. For example, G$3 (the column changes when
copied, the row does not) or $J22 (the row changes when copied, the column does not).
By pressing the F4 key several times in the formula at the location of the reference you can
browse through all the reference methods.
© 2015 Piuha Works Oy
www.piuha.fi
Spreadsheets – Microsoft Excel 2013
35/75
Functions
In Excel a function is a ready-made calculation formula. A function does a calculation based
on given values or arguments and gives one result:
ARGUMENT 1
ARGUMENT 2
ARGUMENT 3
FUNCTION
RESULT
ARGUMENT 4…
There can be from 0 to 255 arguments depending on the function. The arguments can be,
for example, cell references, numbers, text, or other functions.
One argument has
been given for the
SUM function, the
cell range from D2
to D4. The result is
442,40€.
Formulas contained by functions also start with the = sign. The function's arguments are
added in parentheses immediately after the name of the function. If there are several
arguments they are separated from each other with semicolons (or commas, depending on
your regional settings). Note that that colons are used to mark cell ranges.
Adding a Function
1. Select the cell where you want the function and its result
2. Click the formula bar's Insert Function button (or the same button at the beginning of
the Formulas tab):
© 2015 Piuha Works Oy
www.piuha.fi
Spreadsheets – Microsoft Excel 2013
52/75
AND
The AND function returns the value TRUE (1) if all the conditions given are TRUE. If even
one of the conditions or arguments is FALSE (0), AND will return the value FALSE.
Sellers will be given a 500€ bonus
IF
Sales exceed 4000€
AND
Average feedback value is higher
than 3,5
OR
The AND function returns the value TRUE (1) if all the conditions given are TRUE. If even
one of the conditions or arguments is FALSE (0), AND will return the value FALSE.
Sellers will be given a 500€ bonus
IF
Sales exceed 4000€
OR
Average feedback value is higher
than 3,5
NOT
The NOT function switches a given truth value (FALSE TRUE, TRUE FALSE). With the
help of the NOT function you can ensure doen not equal to a certain known value. You can
also usually test the same with the comparison operator <> (not equal to).
© 2015 Piuha Works Oy
www.piuha.fi
Spreadsheets – Microsoft Excel 2013
53/75
Lookup and Reference Functions
VLOOKUP
With the VLOOKUP function you can search for data from a certain vertical column and
return a selected value from the column:
Extranet Exercise File: lookup-functions.xlsx
VLOOKUP(lookup_value; table_array; col_index_num; range_lookup)
1.
column
2.
3.
4.
5.
In the larger pricing table are all the company’s products. The VLOOKUP function can be
used to easily retrieve particular data about the product in question from the Products table
on the basis of the product code. The arguments for the VLOOKUP function are a condition
(the product code), the area to be searched (the whole table from the A to the E column) and
which column (number order) the desired data will be found in (e.g. the name from the
second column or the price from the fifth column).
© 2015 Piuha Works Oy
www.piuha.fi
Spreadsheets – Microsoft Excel 2013
54/75
This is utilized in the Order form table, where an ordered product ID is entered into column A
and the amount ordered into Column D. The other data are retrieved and calculated
automatically. First the VLOOKUP function is used to find the product’s name in Column B:
=VLOOKUP(A4;Products!$A$E;2;FALSE)
as well as the price for Column C, but the third argument (col_index_num) is 5 so the
formula returns the price data in the fifth column:
Note that the last argument range_lookup is not obligatory, but here the truth value FALSE
has been entered. This changes the operation of the function such that it searches for an
exact match, so the product code must be exactly right (rational). Now the data also don’t
need to be sorted in a particular order.
With the default value (TRUE) you can search for the closest match, but then you have to
remember to sort the table’s data by the first column in ascending order! TRUE is useful
when, for example, you want to calculate a discount or bonus percentage according to a
certain graduated table.
VLOOKUP Function Arguments
lookup_value
The value to be retrieved from the table
table_array
The range from whose first column the lookup value is retrieved and a
the desired data is returned from a certain column
col_index_num Number of the column in which the returned value is found
range_lookup
TRUE: Returns the closest answer (a smaller value than the greatest
lookup value). The data must be sorted in ascending order including the
first column! FALSE: Searches for an exact value. If it can’t be found the
error message #N/A shows up.
© 2015 Piuha Works Oy
www.piuha.fi
Spreadsheets – Microsoft Excel 2013
67/75
Pivot Tables
What Are Pivot Tables Used For?
Pivot tables are used to quickly make a summary of large quantities of data. In summaries
you can decide which point of view you want to analyze the data. Completed pivot
summaries can also be updated with one click when the source data change.
Creating a Pivot Table
The pivot action always uses table-formatted data as its source data (see p. 43), where the
first row contains the headings and the data records follow as regular data rows.
Extranet Exercise File: sales-report.xlsx
2
1
3
Click the PivotTable button in
the Insert tab
Select one cell from the source data table
Accept the table
range and default
settings by clicking
OK
© 2015 Piuha Works Oy
www.piuha.fi