Excel Data Presentation Guide [Download]
| Copy & Paste Values | Copying the the contents of a cell and pasting just the value and not the underlying formula that is displayed in the formula bar. |
| Remove Duplicates | Delete duplicate rows from a sheet. |
| Number Formats | Changes the appearance of numbers, including dates and times, without changing the actual number. |
| Data Validation | Pick from a list of rules to limit the type of data that can be entered in a cell. |
| Sort | Values in a list with their corresponding values in adjacent columns are ordered by criteria to make them more legible. |
| Filter | Create a subset of data that meet certain criteria from the current dataset. |
| Freeze Panes | Freeze a portion of the sheet to keep it visible while you scroll through the rest of the sheet. |
| Conditional Formatting | Easily spot trends and patterns in your data using bars, colours, and icons to visually highlight important values. |
| Graphs | Easily spot trends and patterns in your data using bars, colours, and icons to visually highlight important values. |
Excel Formula Reference Guide [Download]
Some of you might have seen this already but before Lee left us he gave us a great reference document of the top Excel formulas you might find helpful, it includes:
| Anchoring Cells | – Defines whether the cell or range of cells referenced changes relative to the position of the copied cell or range of cells or remains constant regardless. |
| SUM | – Adds all the numbers in a range of cells. |
| SUMIF and SUMIFS | – Adds the cells specified by a given condition or criteria. – Adds the cells specified by a given set of conditions or criteria. |
| ROUND, ROUNDUP and ROUNDDOWN | – Rounds a number to a specified number of digits. – Rounds a number up, away from zero. – Rounds a number down, towards zero. |
| Wildcards | – Used when the full text reference is unknown or if it needs to be non-specific. |
| COUNT and COUNTA | – Counts the number of cells in a range that contain numbers. – Counts the number of cells in a range that are not empty. |
| COUNTIF and COUNTIFS | – Counts the number of cells within a range that meet the given condition. – Counts the number of cells specified by a given set of conditions or criteria. |
| VLOOKUP and HLOOKUP | – Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. By default the table must be sorted in an ascending order. – Looks for a value in the top row of a table or array of values and returns the value in the same column from a row you specify. |
| MATCH | – Returns the relative position of an item in an array that matches a specified value in a specified order. |
| INDEX and INDEX with MATCH | – Returns a value or reference of the cell at the intersection of a particular row and column, in a given range. – Returns a value or reference of the cell at the intersection of a particular row, based on the relative position of an item in an array that matches a specified value in a specified order, and column, based on the relative position of an item in an array that matches a specified value in a specified order, in a given range. |
| OFFSET and OFFSET with MATCH | – Returns a reference to a range that is a given number of rows and columns from a given reference. – Returns a reference to a range that is a given number of rows, based on the relative position of an item in an array that matches a specified value in a specified order, and columns, based on the relative position of an item in an array that matches a specified value in a specified order, from a given reference. |
| RANK | – Returns the rank of a number in a list of numbers: its size relative to other values in the list. |
| LARGE and SMALL | – Returns the k-th largest value in a data set. For example, the first largest number. – Returns the k-th smallest value in a data set. For example, the first smallest number. |
| AND, OR and NOT | – Checks whether all arguments are TRUE, and returns TRUE if all arguments are TRUE. – Checks whether any of the arguments are TRUE, and returns TRUE or FALSE. Returns FALSE only if all arguments are FALSE. – Changes FALSE to TRUE, or TRUE to FALSE. |
| IF and IS Functions | – Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE. – Checks the specified value in relation to the function and returns TRUE or FALSE depending upon the outcome. |
| Errors and IFERROR | – Errors are returned when Excel cannot properly evaluate a worksheet formula or function. – Returns value_if_error if expression is an error and the value of the expression itself otherwise. |
This is great! Than’s for sharing it!
A useful reminder to help brush up my excel skills!