excel cheat sheet pdf, Cheat Sheet of MS Microsoft Excel skills

Download excel cheat sheet pdf and more Cheat Sheet MS Microsoft Excel skills in PDF only on Docsity! Excel Cheat Scheet Last updated July 2018 Faye Brockwell Information Services documents are online at: https://staff.brighton.ac.uk/is/training 1 See https://staff.brighton.ac.uk/is/training/Pages/Excel/formulae.aspx for videos and exercises to accompany this quick reference card. Formulae & Functions Basics When building a formula:  All formulae and functions begin with =  Use your mouse to select a cell or range of cells to be used in a formula  The operators for building formulae are: + Add * Multiply - Subtract / Divide  BODMAS rules apply to arithmetic (Brackets Over Division, then Multiplication, then Addition, then Subtraction).  Avoid typing variables (such as tax rates) in formulae; instead type the variable in a separate cell and refer to that cell in the formula  To repeat a formulae down a column, build the formula in the first cell of the column, then use autofill to copy the formula down the column. Functions follow the format =name(arguments) where: name the name of the function (e.g. SUM, VLOOKUP) arguments the cell or range references containing the values used in the function  Where a function contains more than one argument, each argument must be separated by a , (comma).  Text criteria in an argument must be surrounded by “” (quotation marks) Checking for formulae If you are using a spreadsheet set up by someone else, before typing data into a cell, check whether the cell contains a formula. If a cell contains a formula, the cell will usually show the result of the formula. The formula itself can be seen in the formula bar.  Click on the cell to select it. The formula bar will display the content of the selected cell.  If the cell does contain a formula, double click on the cell. This will colour any cells on the current worksheet that feed into that formula, to help you work out what that formula does and how it works.  Always press ESC to stop checking/editing a cell containing a formula. This guarantees that you will leave the formula as you found it. Do NOT click your mouse elsewhere on the sheet to stop checking as this may break the formula. How to check which cells on a sheet contain formulae There is a way to show all formulae on a worksheet before you start using it:  On the Formulas tab, click on the Show Formulas icon  Any cells with formulae will show the formula instead of the result  To switch this off, go back to the Formulas tab and click on the Show Formulas icon The shortcut for this is CTRL ` Last updated July 2018 Faye Brockwell Information Services documents are online at: https://staff.brighton.ac.uk/is/training How to check what a formula is doing Use this technique to check that your formulae are doing what you think:  Click on the cell containing the formula.  Click once on the formula in the formula bar.  The cells used in the formula will be colour coded within the sheet, making it easy to spot mistakes. Building a formula to add 1. Click in the cell where the result of the formula will appear 2. Type = 3. Click on the first cell containing data to be included in the sum 4. Type + 5. Click on the next cell containing data to be included in the sum 6. Repeat steps 4 and 5 as required. 7. Press ENTER on the keyboard. Autosum to add row or column totals This only works where the total is to appear at the end of the column or row of data. This technique will not work across worksheets.  Select the range of cells to add up  On the Home tab, click on the Autosum icon The total will be put in the cell at the end of the selected cells. Building a formula to subtract 1. Click in the cell where the result of the formula will appear 2. Type = 3. Click on the first cell containing data to be included in the calculation 4. Type – 5. Click on the next cell containing data to be included in the calculation 6. Press ENTER on the keyboard. Building a formula to multiply or divide 1. Click in the cell where the result of the formula will appear 2. Type = 3. Click on the first cell containing data to be included in the calculation 4. Type * to multiply or / to divide 5. Click on the next cell containing data to be included in the calculation 6. Press ENTER on the keyboard. Building a formula to calculate a percentage To calculate a percentage, use the % sign within your formula. A formula to calculate 20% of cell E2 would read =E2*20% Last updated July 2018 Faye Brockwell Information Services documents are online at: https://staff.brighton.ac.uk/is/training 5 Function Used for Format Example Tips SUM Add values in a range of cells =SUM(range of cells to add) =SUM(A1:A10) AVERAGE Average the values in a range of cells =AVERAGE(range of cells to average) =AVERAGE(A1:A10) MAX Find the highest value in a range of cells =MAX(range of cells) =MAX(A1:A10) MIN Find the lowest value in a range of cells =MIN(range of cells) =MIN(A1:A10) IF Display different information depending on the outcome of a condition test =IF(condition test, what to display if outcome is true, what to display if outcome is false) =IF(A1>20, “Great!”,”Oops!”) or =IF(A1>20, A1*E1,A1) AND Test that more than one condition is true. Test result is TRUE only if all conditions are met. =AND(condition test 1, condition test 2, ...) =AND(A1>20,B1=”Gold”) TRUE and FALSE are the only possible answers. To change the content of a cell as the result of an AND function, use the AND function as the condition test in an IF statement OR Test that more than one condition is true. Test result is TRUE if any of the conditions are met. =OR(condition test 1, condition test 2, ...) =OR(A1>20,B1=”Gold”) COUNT Count numerical cells =COUNT(range of cells to count) =COUNT(A1:A10) COUNT does not count cells containing text, use COUNTA for this COUNTA Count cells =COUNTA(range of cells to count) =COUNTA(A1:A10) COUNTBLANK Count empty cells =COUNTBLANK(range of cells to count) =COUNTBLANK(A1:A10) COUNTIF Count cells that meet a certain condition =COUNTIF(range of cells to count,critera to satisfy) =COUNTIF(A1:A10,”>20”) Counts all cells containing a value greater than 20 COUNTIFS Count cells only if multiple conditions are met =COUNTIFS( range of cells for criteria check 1, criteria 1 to satisfy, range of cells for criteria check 2, criteria 2 to satisfy,...) =COUNTIFS(A1:A10,”Gold”,B1:B10,”>20”) Counts number of rows where column A contains the word Gold AND column B is greater than 20 Can use pivot table instead. Last updated July 2018 Faye Brockwell Information Services documents are online at: https://staff.brighton.ac.uk/is/training Function Used for Format Example Tips SUMIF Add values in a range only if cells meet a certain condition =SUMIF(range of cells for criteria check; criteria to satisfy, range of cells to add) =SUMIF(A1:A10,”Gold”,C1:C10) Adds all cells in column C if column A contains the word Gold You only need the second range if the range to add is different to the range to be searched. SUMIFS Add values in a range only if cells meet multiple conditions =SUMIFS(range of cells to add, range of cells for criteria check 1, criteria 1 to satisfy, range of cells for criteria check 2, criteria 2 to satisfy,...) =SUMIFS(C1:C10,A1:A10,”Gold”,B1:B10,”>20”) Adds all cells in column C if column A contains the word Gold AND column B is greater than 20 Can use pivot table instead VLOOKUP Where several spreadsheets use the same data, it is useful to have one sheet holding that data and then allow the other sheet s to reference that information using the VLOOKUP function. This allows you to maintain data centrally. Any updates will automatically update the other sheet s. The format of the VLOOKUP function is: =VLOOKUP(lookup value, table range holding the master data, number of the column containing the answer to your question, FALSE) For example, in the diagram above cell D2 would contain: =VLOOKUP(C2,Main_data_sheet A2:D4, 3,FALSE) to search the table on the right, for the relevant fee for course code UB200. FALSE tells excel to only find an answer if an exact match is found for the lookup value. If no match is found (e.g. course code UB150), Excel will display #N/A. When using VLOOKUP for numerical data, you can omit FALSE from the function to find the closest match (next lowest value): =VLOOKUP(lookup value, table range holding the master data, number of the column containing the answer to your question) For VLOOKUP to work, the data table must be sorted in ascending order on the lookup column (e.g. the course code column A in the example above) Main data sheet Last updated July 2018 Faye Brockwell Information Services documents are online at: https://staff.brighton.ac.uk/is/training 7 Flash Fill (Excel 2013 & 2016 only) This tool is amazing for working with text in databases. In earlier versions, you needed to know several text functions to achieve the same results.  Type the desired result in the first cell of the series and press ENTER  Start typing the desired result in the second cell in the series. Excel should suggest content for that and all other ce lls in the column.  Press ENTER to fill the column. Some examples: To merge first name and last name in one column  Type the full name in the first cell of a new column  Start typing the full name in the second cell of the new column  Press ENTER when Excel suggests the full name for every cell in the column To extract the initials from 2 columns  Type the initials in the first cell of a new column  Start typing the initials in the second cell of the new column  Press ENTER when Excel suggests the initials for every cell in the column TOP TIP: if the technique above does not work:  Type the desired result in the first cell of the series and press ENTER.  Type the desired result in the second cell in the series and press ENTER.  Select both cells.  Use the Autofill technique to copy the cells down column.  Click on the icon and choose Flash Fill. To split the contents of a column into 2 columns For example, you can separate a column of full names into first and last name columns.  Insert a new column to the right of the column you want to spl it  Select the column that you want to split  On the Data tab click on the text to columns icon  In the pop-up window, check that Delimited is selected and click on Next  In the Delimiters section, indicate what separates the first bit of text from the second. o E.g. if a space separates first and last name, click on Space o The example shows how to indicate that 2 pieces of information are separated by a hyphen o Click on Next and then on Finish