1	Excel – Tips & Tricks PayForce National User Group Meeting
2	Exporting to Excel Exports CSV files CSV files can only contain one sheet per workbook You will have to copy each sheet to a new workbook and save as XLS
3	Spreadsheet size Exported data is large – some information covers all 256 available columns Freeze/unfreeze feature Allows columns above and to the left of the selected cell to remain visible at all times Hide/Unhide feature Allows columns or rows to be hidden from view Example
4	Freeze/Unfreeze feature Select the cell
5	Hide/Unhide feature Select the column or row
6	Other features (for large sheet navigation) Split screen Screen splits into 2 or 4 independently scrollable areas (vertically and horizontally) Crtl+Home Will always return to A1 Crtl+right arrow Moves 256 columns to the right Crtl+down arrow Moves down 65,536 rows Pg Up/Pg Dn Moves up or down one screen Alt+Pg Up/Pg Dn Moves right or left by one screen F5 is used to move to a specific cell
7	Spreadsheet functionality Performing calculations Numbers are entered into cells and formulas/functions that use these numbers are instantly recalculated. Creating graphs Charts (as they are known in Excel) allow you to view data pictorially. Charts are also automatically updated when data is changed. Working with lists Lists provide the ability to filter for specific data, sort data either numerically or alphabetically and the ability to summarise such as displaying the total of a particular column.
8	Formulas/Logical Functions All formulas MUST start with an = The operations addition, subtraction, multiplication & division are available and used to create formulas ranging from the very simple to the very complex. Excel follows the BODMAS rule so the equation 6 + 7 x 10 = 76 not 130 Logical functions enable you to look at the contents of a cell, or perform a calculation and then test the result against a required figure or value. IF function =IF(condition,true,false) =IF(B2>400,”High”,”Low”) AND function =AND(condition1,condition2) =AND(B2>400,C2<300) OR function =OR(condition1,condition2) =OR(B2>400,C2<300) Other functions available include financial, date & time, lookup & reference, statistical etc To access other functions Select INSERT – Function Insert Function dialogue box will appear
9	Charts A picture is worth a thousand words Used to summarise data, reflect proportions, trends and anomalies very effectively.
10	Create a chart
11	Pivot Tables/Charts What is a Pivot Table? A Pivot Table is an interactive table that quickly summarises, or cross-tabulates, large amounts of data. You can rotate its rows and columns to see different summaries of the source data.
12	Features of a Pivot Table Rotate rows and columns to see different summaries of the source data Filter the data Drill down to details A pivot table can help you see the "big picture" by summarising and analysing your data. You can control how Excel summarises the data - for example, by sum, average, or count - without entering a single formula.
13	Creating a Pivot table Position your active cell within the list and select DATA/PivotTable and PivotChart Report
14	"Choose where the data is..." Choose where the data is to come from, select the appropriate option.
15	"If you were in the..." If you were in the list when you started the wizard, Excel will display the range of cells. If not select the data you want to use (Click and Drag) It is IMPORTANT to make sure that all columns have headings
16	"The screen displayed asks for..." The screen displayed asks for the position of the pivot table, but first you must decide which columns and summaries you want to display. Simply click on Layout...
17	"Here you can design the..." Here you can design the table by dragging the field buttons to the right of the dialogue box to the appropriate row, page and data fields. Click on OK.
18	How much is Manufacturing paying for uniforms? Drag and drop fields Select OK
19	Result Table/chart is created and can be filtered for specific information Example
20	A final word… Over 60% of Excel spreadsheets contain errors…be careful. ##### - the column is not wide enough #Value! – Excel expects a number not text #Ref! – the formula references a non-existent cell #Div/0! – cannot divide by zero #NAME? – Excel doesn’t recognise the text used in the formula Circular reference – the formula references its own cell When in doubt use the ESC Key to close formula cells and clear memory.