Copyright Michelle Lee 2010 Š
Contents Contents
Chapter 1: Useful functions for everyday ...................................... 3 1. TODAY() = TODAY()........................................................................................................ 3 2. UPPER, LOWER AND PROPER .......................................................................................... 3 3. CONCATENATE = & ........................................................................................................ 4 4. LEFT AND RIGHT .............................................................................................................. 4 5. COUNTIF .......................................................................................................................... 5 6. VLOOKUP function ......................................................................................................... 5 7. HLOOKUP function ......................................................................................................... 6 8. INDEX and MATCH function .......................................................................................... 6 9. CONDITIONAL SUM - ARRAY FORMULA........................................................................ 8
Chapter 2: Understanding Pivot Tables......................................... 10 1.
Using the PivotTable Wizard ....................................................................................... 12
2. Multiple PivotTable – Consolidation ........................................................................... 13 3. Creating PivotCharts.................................................................................................... 14
Chapter 3: Macros ......................................................................... 17 1. What type of Macro should you record? .................................................................. 18 2. How to create a Macro............................................................................................... 18
Chapter 4: Hiding aspects of a spreadsheet ............................... 19 1. Hide worksheet(s) ........................................................................................................ 21 2. Hide Formulae and resulting data ............................................................................. 21 3. To hide toolbars, zero values and column/row headings:- .................................... 21
Excel Advanced Copyright Michelle Lee 2010 ©
Page 2
Introduction to Excel Advanced Training Course
Chapter 1:
Useful functions for everyday
TODAY() = TODAY()
1.
Returns the current date as a serial number. When entered in a cell, Excel formats the number as a date. This cannot be used in an argument Examples of use:• •
To show today’s date in a spreadsheet To calculate the age of a person and ensure it is up to date at any given time
•
To calculate elapsed time
•
To calculate a future date for action.
This table demonstrates how many years have elapsed since the runners ran these marathon times. With this formula, the data will always be up to date – the 365 divides the result into years.
2.
UPPER, LOWER AND PROPER
=UPPER Converts all letter characters in a text string to uppercase (capital letters) =LOWER Converts all letter characters in a text string to lowercase letters Excel Advanced Copyright Michelle Lee 2010 ©
Page 3
=PROPER Converts all letter characters in a text string to proper case (lowercase letters with initial capitals). Example: This following table shows each of these examples:-
3.
CONCATENATE = &
Example: where CONCATENATE can be useful is in taking surnames in one column and combining them with forenames in another to create a forename and surname in one field.
4.
LEFT AND RIGHT
Can be used to create a code from data in two columns. Example: To create an account code from someone’s name and date of birth. Here, UPPER is also used to convert characters to uppercase. In addition, the date is returned in serial format:-
Excel Advanced Copyright Michelle Lee 2010 Š
Page 4
5.
COUNTIF
Example: This is used if you are looking to count the number of rows that meet a certain criteria. Here, if the category changes, it will count the number of products types that fall into
6.
VLOOKUP function
VLOOKUP looks up vertical data within a table, and returns results from the column specified in the formula. Example: This VLOOKUP considers the average mark of the student and predicts a grade based on their results to date by referring to the Grade lookup table. 7.
Excel Advanced Copyright Michelle Lee 2010 Š
Page 5
HLOOKUP function Like VLOOKUP, HLOOKUP finds horizontal data in a table and returns result from a specified row.
8.
INDEX and MATCH function Index and Match used in unison are two of the most useful functions in Excel when understood. To break down the formula:INDEX:
This is the table containing multiple columns and rows.
MATCH:
This looks at a value in a specified cell and then matches it to a row reference in a table. It is possible to work forward or backward with column references with this formula:0 = refers to the cell that contains the match cell value 1 = refers to the first column reference after the match cell
value -1 = refers to first column to the left of the match cell value.
Excel Advanced Copyright Michelle Lee 2010 Š
Page 6
This is the index, or Database Table referred to in the first part of formula (=INDEX)
In this cell, the data is entered, and referred to as the MATCH
This Example uses the INDEX and MATCH formula, nested and with Absolute Cell references to return values in the Nutritional Information table, which could typically be located in a different worksheet or workbook. Note: In this scenario where a database table is being used, it can be useful to start naming ranges. For example, the Index Table in this example could be called Food_Database
Excel Advanced Copyright Michelle Lee 2010 Š
Page 7
9.
CONDITIONAL SUM - ARRAY FORMULA
An array formula performs multiple calculations. The result can be a single or multiple results. This works well for lists, where each record (row) has the same number of field headings:Example:
In this table, the formula looks for products within a certain category, and if the category is matched, it will produce a total value of stock for that category:-
Note: To activate the ARRAY {}, ensure that the formula is sselected and press CTRL + SHIFT + ENTER
Excel Advanced Copyright Michelle Lee 2010 Š
Page 8
The structure of the formula:-
Only perform this sum if the data in Cell B20 matches any row in the category (B2:B18)
Multiply Stock by Cost to give total Value (this appears in Column F). Otherwise, do nothing
Excel Advanced Copyright Michelle Lee 2010 Š
Page 9
Chapter 2: Understanding Pivot Tables A Pivot Table uses two dimensional data to create a three-dimensional table – in essence, a summary table based on multiple conditions that have intersecting points. PivotTables are a great way to summarise large amounts of information in a small amount of space with just a few short steps. They are interactive and dynamic, and after the pivot table has been created, it’s possible to drag a field to a different location, thus pivoting the structure of the table with a single step.
Pivot tables are often Viewed as too complex to understand, but they’re not that complicated if you think of an automated summary table. You could write a formula to sum a quantity with multiple conditions, or you could use a PivotTable to summarise the data. Both are effective Tools but the advantage of the PivotTable is its flexibility to View the detail that makes up the total number.
PivotTables enable you to audit your data as well. If you must manage costs on a weekly basis, for example, costs of your employees and the hours they’re generating, PivotTables are the primary solution.
Using PivotTables to analyse Employee Hours and Costs For this scenario:•
A table is created to note the employee’s rate of pay for both Basic and Overtime.
•
Their hours are
then
logged weekly •
A
table
calculates their • Excel Advanced Copyright Michelle Lee 2010 ©
Page 10
earnings for basic and overtime rates and then creates a total weekly pay. •
A VLOOKUP formula is used to identify their rates of pay
•
The pay is then divided up in to 3 periods of weeks
•
The first pivot table analyses data from period 1
•
The second Pivot table consolidates data from 3 tables to provide total weekly costs for all employees.
•
Both are dynamic.
This is the Pivot Table Layout Dialogue Box where the fields can be moved and formatted, 3and calculation set. Below is the
Excel Advanced Copyright Michelle Lee 2010 ©
Page 11
1.
Using the PivotTable Wizard
To use the PivotTable Wizard for the example shown:a. Go to Data b. Select PivotTable and PivotChart Reports c. Select First option on both questions as shown and Select Next d. Select the range of cells you wish to use in PivotTable – do not include merged cells as shown in Row 8 in example. e. Click Next f. Select
Create
in
existing
worksheet and select the cell you wish the table to start. g. Select Layout h. Move the fields to the preferred area on the layout grid.
NOTE
THAT AT THIS POINT THE CALCULATION AND FORMAT CAN BE CHANGED FROM COUNT TO SUM AND TO
CURRENCY
ETC
IF
REQUIRED i.
Click OK when Complete
j.
On the next Screen, select Options and remove Grand totals
from
Rows
and
Columns and click OK. k. Select the position that you would like your table to appear – this may be in the same worksheet or another worksheet. If you want to ensure that the PivotTable will include new data if the table is updated, extend the cell range from beyond the actual table to for example Row 2000 – be sure that your PivotTable is not
Excel Advanced Copyright Michelle Lee 2010 ©
Page 12
2.
Multiple PivotTable – Consolidation If you have more than one table with the same headings but perhaps for separate periods, as in this example, you can create a PivotTable that combines many ranges and tables – this is called Data Consolidation and works in the same way as the Consolidation Tool.
a.
Follow the steps for the previous exercise, but in the Wizard, Select Multiple Consolidation Ranges and Click Next
b.
In the next dialog box, Select Create a Single Page for me and Click Next
c.
Select each of the tables that you wish to include and select Add
d. Follow the steps as detailed in the previous exercise
This is the resulting table displaying only totals from the possible column headings.
Excel Advanced Copyright Michelle Lee 2010 ©
Page 13
3.
Creating PivotCharts Creating Pivot Charts are very similar to creating Pivot Tables, but allow you to see the data in a graphical rather than table format. They can be set into a separate spreadsheet or embedded above the associated table or PivotTable,
The PivotTable below has been created using the example of costs for This allows the user employees,:to show individual or all weeks
Excel Advanced Copyright Michelle Lee 2010 Š
Page 14
An Associated table will also be created:-
Excel Advanced Copyright Michelle Lee 2010 Š
Page 15
To set up a PivotChart using the wizard:a. Go to Data b. Select PivotTable and PivotChart Reports c. In the next screen, select the area that you wish to use for your chart d. Select the cell where the PivotTable associated with the chart will be placed e. Click Layout and Options to arrange the style of table f. Modify number to currency or % etc as required. g. Click Finish h. To change the chart type, click the chart area and right click, and select change chart type
More advanced training is available as a
separate course for PivotTables and Charts
Excel Advanced Copyright Michelle Lee 2010 Š
Page 16
Chapter 3:
Macros
A macro is a VBA procedure (a Visual Basic Application). A procedure is a group of VBA statements that either perform a specific task or return a result. There are two types of procedures that you can create in Excel. Subroutines are procedures that perform a specific. The code for subroutines begins with the word Sub and ends with the words End Sub. A macro is a subroutine-type procedure. Basically, a macro is a recorded procedure or shortcut of a repeated routine. Some examples of recording macros:•
Format and print a report
•
Assist in the completion of an Excel form, such as an expense report
•
Consolidate data from several workbooks into a master workbook
•
Import and plot data to a chart
•
Assign a keyboard shortcut to a frequently used command
•
AutoFit Columns on the current worksheet
•
Export newly inputted data to another location in the workbook or different workbook or File.
Any toolbar button is a pre-recorded procedure in which keystrokes can do the same job in some instances. It’s also possible to assign your own toolbar buttons to customised macros. Macros with command buttons can be used in conjunction with Excel forms to produce automated forms. Pre-requisites to success with macros:•
An understanding of naming ranges
•
Using keystrokes instead of mouse to navigate spreadsheet and activate commands
Helpful but not essential:•
An understanding of recorded procedures
•
An understanding of VBA in order to identify runtime errors with recorded procedures.
Excel Advanced Copyright Michelle Lee 2010 ©
Page 17
1.
What type of Macro should you record?
Macro Type
Example of use
A Macro for the current This would be relevant to the open workbook only workbook and could not be opened from another workbook A Macro for the Personal This could be a macro that is more likely to be related workbook to formatting or printing and less likely to relate to named ranges which may be specific to a certain workbook. A Macro workbook
2.
for
a
new This would be activated every time a new workbook was created. It might involve saving the File as a different File type than the default.
How to create a Macro
Example:- This macro has been created to copy the table of hours and costs for period 1 into a new spreadsheet. For this to work, it is necessary to name the table before creating the macro, and the table has been called salaries. This was achieved by:a. Highlight the table b. Go to the cell name box c. Type in Salaries d. Press return Now the Macro can be recorded as follows:a. Go to Tools b. Select Macro, Record new macro c. Name the macro and assign to this workbook d. Press CTRL + G (goto) e. Select Salaries from the list f. Press CTRL + C (Copy) g. Press CTRL + N (New Workbook) Excel Advanced Copyright Michelle Lee 2010 Š
Page 18
h. Alt + W (Window) i.
Scroll down to the workbook original table
containing
j.
Click
toolbar
Stop
from
the
floating
the
To allocate the Macro to the toolbar and a button:a. Go to View b. Select Toolbars c. Select Customize (at the bottom) d. Select Macros from the Categories List e. Click on the Button (in this example, smiley face) and drag to the appropriate toolbar f. To allocate the new macro to the button, click the button g. Select the macro from the list and click OK
Chapter 4: Hiding aspects of a spreadsheet What you can hide: •
Worksheet(s)
•
Formulae and results
•
Toolbars
Excel Advanced Copyright Michelle Lee 2010 ©
Page 19
•
Column and row headings
Excel Advanced Copyright Michelle Lee 2010 ©
Page 20
Here’s how:1. Hide worksheet(s) Go to Format on the menu bar and click worksheet, and select hide. To hide multiples, click on the first worksheet, hold down control and click on the additional worksheets and follow this same procedure.
2. Hide Formulae and resulting data a. Select the cell or cells that are to be hidden b. Right click and select Custom from the list under number c. Under the type, enter 3 semi colons d. Click OK To remove protection follow the same procedure but remove the semi colons from the type. 3. To hide toolbars, zero values and column/row headings:a. Go to Tools on the Menu Bar b. Select Tools c. Select View Tab d. Remove ticks from the boxes that you are trying to hide.
Excel Advanced Copyright Michelle Lee 2010 Š
Page 21