Download Excel Cheat Sheet: Functions, Pivot Tables, and IF Statements and more Exercises MS Microsoft Excel skills in PDF only on Docsity! Excel – Part 2 Cheat Sheet fdmgroup.com Simple Aggregation Function Examples: V Look Up = Pivot Table = Pivot Table Fields: Type Description SUM Adds all the numbers in a range of cells. COUNT Counts the number of cells in a range with numbers. AVERAGE Returns the average (arithmetic mean). MIN Returns the smallest number in a set of values. MAX Returns the largest number in a set of values. Formula: Example: =SUM(number1, number2, number3) =SUM(D20,D21,D22) Replace ‘SUM’ with the word of the function you wish to run e.g. MIN Looks for a value in the leftmost column of a table and returns a value in the same row from a column you specify. Formula: =VLOOKUP(lookup_value , table_array, col_index_num, [range_lookup]) Lookup_value = the value we want to find in the data. Table_array = the data where we want to search for the value. Col_Index_Num = the column where you want the result returned. Range_Lookup ; TRUE = approximate match or FALSE = exact match. Example: =VLOOKUP(G4,FDMApplicants[#All],2,FALSE) An analysis feature that enables you to summarise data. To Create a Pivot Table: Insert > Pivot Table > Existing Worksheet OR New Worksheet. Columns must be named at the top! Blank Rows and Columns must be cleared! The Lookup Values MUST be sorted in ascending order. You can modify the outcome of a Pivot Table by arranging data using the Pivot Table Field (on the right of the Excel window). Value = Initial Total: Calculates something by showing the total amount, so make sure you drag the ‘Total’ of the table to this section to view the Sum of this. The Value Field Settings allow you to display the Sum, Count, Average, Min, Max etc. of the total as well. Rows = Unique identifier that Pivot Tables will organise your data by. E.g. organising data by ‘Item’ - Displays Data Vertically. Columns = Displays the information by column/horizontally. Filters = Allow you to manage data, filtering it using subheadings within your table. Key: Division = / Multiplication = * Subtraction = - Addition = + 2016 version of Excel onwards: Forgot what the function is you need to use? There is a button in Excel which automatically provides the functions! AutoSum Checks to see if the criteria of a scenario has been met; returns the value as true if criteria has been met and false if not. Formula: =IF(logical_test,[value_if_true ], [value_if_false]) Example: =IF(D16=2.2, “Minimum requirement met”, “Minimum requirement not met”) If a number is inputted, the speech marks are not needed e.g. 2.1 If text is inputted, the speech marks are needed e.g. “1st” You must have the same number of opening and closing brackets for the formula to work! allows you to replace an error outcome with your own words. Example: =IFERROR(SUM(C71*C73),”This sum cannot be done”) The formula you wish to run. The outcome you wish to be produced in case of an error. IF Statements = IFEFFOR Statement =
