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

Download excel formulas cheat sheet pdf and more Cheat Sheet MS Microsoft Excel skills in PDF only on Docsity!

Commonly Used Excel Formulas and Functions

Supplement to “Excel for Budget Analysts”

Version 1.0: February 2016

Table of Contents

Introduction

Excel is a popular tool used in public finance offices. Using Excel functions, tools, and various shortcuts not only expedites the time it takes to perform analyses, but can also create outputs that are more dynamic and engaging to stakeholders. GFOA’s Excel webinar, “Excel for Budget Analysts,” provides a more detailed demonstration and application of pivot tables, graphs, debt calculations, and scenario analysis and this guide serves as a supplement to additional Excel features that can help users within the finance office.

GFOA compiled this list of functions and shortcuts with the assistance of member and instructors’ feedback and staff research. While this guide does not offer a comprehensive list of all the features within Excel, it does include some of the ones commonly used by Excel users within the public finance office.

Formulas and Functions

It is important that we make a distinction regarding formulas and functions for the purposes of Excel.

 Formulas are mathematical equations used to perform calculations in an Excel worksheet or workbook.  Functions are predefined formulas that perform calculations in an Excel worksheet or workbook.

Both need to be written in a specific way, which is called the syntax, in order to calculate properly. Both also need at least one argument, which on the most basic level identifies the values for which to perform the action.

 For formulas, the basic syntax is equal (=), function name (AVERAGE, in the example below), and argument.

=AVERAGE(A1:A20)

 For functions, the basic syntax is equal (=), function name (ROUND, in the example below), argument, and argument tooltip, which is an additional action to perform (2, in the example below represents 2 digits). =ROUND(A1,2)

Excel offers hundreds of functions and categorizes them based on their functionality. This guide will cover only a small portion of the functions, including math and trigonometry, statistical, date and time, lookup and reference, text, and logical functions. To learn more about the various categories, please reference the Microsoft Office Support page on Excel functions (by category).

Equal sign Function name

Argument

Equal sign (^) Function name

Argument (^) Argument

Math and Trigonometry Functions

Several math functions can help expedite analysis. This section highlights on a few.

ABS

When there is a need to get the absolute value of a number, the ABS function is helpful.

ROUND, ROUNDUP, and ROUNDDOWN

There are various options with rounding, depending on the need. The functions’ argument tooltip specifies how many decimal places or to which nearest integer it should round.

 ROUND – This function helps users to round to the nearest value.  ROUNDDOWN – This function helps users to round values down to the nearest value based on the desired decimal place or integer.  ROUNDUP - This function helps users to round values up to the nearest value based on the desired decimal place or integer.

Statistical Functions

When presented with large datasets, it is helpful to sort and summarize the information at hand.

COUNT, COUNTA, and COUNTBLANK

The counting functions are especially helpful with large datasets to identify anomalies and to get general summary statistics.

 COUNT – This function counts the number of cells that contain numbers.  COUNTA – This function counts cells containing any type of information, including error values and empty text (as shown in the example below, it counts the cell marked “VOID”).  COUNTBLANK – This function counts only the empty cells within the dataset, with no information contained in the cells.

RAND and RANDBETWEEN

This function is helpful when needing to create random values. Note that the random values Excel generates will recalculate as the fields are altered.

 RAND – This function generates a random value between 0 and 1.  RANDBETWEEN – This function generates a random value between a specified range of values.

Date and Time Functions

Sometimes when we export data from a database system, the date does not extract as neatly. Other times, we are looking to calculate the duration from one date to another.

DATE

This function is useful when information related to year, month, and date are in separate cells and the preference is to have the date in one cell.

YEAR, MONTH, and DAY

These functions are helpful to capture the appropriate piece of information in a date cell.

WEEKDAY

This function returns the day of the week for a given date. The argument tooltip defines when the week starts, with 1 being the first day of a given weekday.

DATEDIF

This function calculates the interval between two dates. The second argument specifies the type of interval, e.g., day, month, year, etc.

Lookup and Reference Functions

Sometimes we need to identify and search for a particular value in our dataset. This is when lookup and reference functions are helpful.

VLOOKUP

This function returns a value based on reference information presented in a vertical layout. In the example below, Columns A and B represent reference information. Columns D through F represent data we want to review. In Column H, we are telling Excel to use the value in E2 to find the value for H2 by looking at the information in Columns A and B.

The first argument tooltip in our example is a “2.” This represents the Column B. We have information on the revenue code (Column E) and want to return information on the revenue name (Column B).

INDEX

This function can take two forms. The first form is a reference where users instruct Excel to return values in a table based on headings. The argument first identifies the values in the table (B3:F8 in the example below). The first argument tooltip identifies the row number within the table of inquiry (4 for April and 5 for May, respectively) and the third tooltip identifies the column within the table of inquiry (1 for Dept_01 and 2 for Dept_02).

The array form returns the entire row or column of table. Note, it is important to first select the cells you want to contain the returned values (in the example below, B11:F11 were selected as the formula was entered). The argument first identifies the values in the table (B3:F8). The first tooltip identifies the row of inquiry (3 for March). The following argument tooltip references the column from the reference to which Excel should return (0 for no columns). Note to run the function in array form for a selected group of cells, click Ctrl + Shift + Enter to return values and not Enter.

MATCH

This function shows users where in a list they can find their values by providing Excel with search parameters.

The example below is identifying where the break is in the list for those earning less than $5,000. The argument first identifies the search value (5,000 in the example below). The first tooltip identifies the column or row of inquiry. In this example, the first tooltip identifies the cells with the salary information (D2:D16). The second tooltip identifies whether an exact (0) or approximate match (1 or -1) is desired. In this example, 1 is used to denote less than $5,000.

Please note that if the inquiry is for less than the search value (1), then the column or row needs to be sorted in ascending order. Conversely, if the inquiry is for greater than the search value (-1), then the column or row needs to be sorted in descending order before proceeding with the match function. Note that the salary column in the example is sorted in ascending order.

The return information is 7 to identify the position in the cell range (D2:D16) that contains the information, e.g., the split in the list of those earning less than $5,000.

GETPIVOTDATA

To avoid copying and pasting information from a pivot table, this function helps to return values using appropriate commands. The example below shows the level of details that can be captured using this function. In the first example, we are identifying the grand total of revenues from the pivot table. To do so, the first argument is the data field of inquiry where the data we want is contained, e.g. “Sum of Revenues ($000). The first tooltip is the reference cell in the PivotTable to help determine which report to Excel should pull from (this is especially useful when you are entering this function in one worksheet and have multiple PivotTable reports in the workbook.)

The second example builds off of the first, but wants to identify the total for February. This requires additional tooltips on the field name (Month), which is field heading in original dataset, and the actual item name (February).

The third example is more specific than the second and contains additional tooltips to identify sales tax revenues in March. Other tooltips for field name (Source) and item (Sales Tax) is included.

LEN and TRIM

LEN is helpful to return the length of a string in a cell. The function contains one argument and that is the cell of inquiry. Note from the example below that Excel calculates extra spaces in the string in the length number. For example, the name Eli is shown as having a length of 5 and Tina has a length of 6.

One common use of the TRIM function is to remove extra spacing. Following the example above, the TRIM function is used below to remove the extra spacing, which shortens the length of the cell. The function contains one argument and that is the cell of inquiry.

TEXT and VALUE

When exporting data, numbers can sometimes appear with formatting issues or come in as text rather than number.

TEXT converts a numeric value to text. There are also different ways users can specify the display formatting by using special format strings. The first example below shows a figure with many decimals, but we want only the whole number. Thus, the TEXT function is used to identify the cell that contains the information (A2) and specifies it should be the nearest whole number (“0”). In the second example, the figure is 21.3, but we want it to display as a dollar value. Using the TEXT function, A3 is identified as the cell that contains the information and “$0.00” is specified as the display.

Above in A4 contains a number, but Excel recognizes it as text (a simple way to determine that Excel has identified this as text is the green triangle on the upper left corner). If the figures are recognized as text instead of numbers, then calculation and analysis cannot be performed accurately. The VALUE function contains one argument, which identifies the cell that contains the information (A4).

Logical Functions

Logical functions are useful when we are seeking to perform conditional actions or calculations.

COUNTIF

This function counts the number of cells that meet a criterion. The two examples below show how COUNTIF operates. The argument is the range of cells that we want Excel to review. The tooltip specifies the criterion, e.g. code 100 and vendor ABC, respectively.

SUMIF

This function sums the values in a range that meet criteria specified. The two examples below show how SUMIF operates. The first part of the argument is the range of cells we want evaluated based on our criteria. The first tooltip is the criteria itself, e.g. code 100 and vendor ABC, respectively. The following tooltip is the range of cells for Excel to perform the summarization function, e.g., amount in both instances.

IF

If statements are helpful to return one value if the statement is true and another if the statement is false. In the example below, we are comparing the payment amount (C2:C7) to the invoice amount (B2:B7). If the payment amount is greater than the invoice amount, then Excel returns a value of “Overpayment.” If the payment amount is not less than the invoice amount, then Excel

Shortcuts

Formatting

Shortcut Name Keystrokes Purpose

Border Ctrl+Shift+7 Places border around selected cell(s)

Remove Border Ctrl+Shift+– Removes border around selected cell(s) Clear Alt+H+E Opens clear editing features. Keying additional letters will perform the functions listed below:

Paste Special Ctrl+C, Alt+H+V Opens paste special features Keying additional letters as indicated in the underlined word performs the functions listed below:

Change Font Size Alt+H+F+S Goes to font size dropdown

Format Cell Ctrl+1 Opens format cell window

Group rows or columns Alt+A+G+G or Shift+Alt+→^ Opens group window

Ungroup rows or columns

Alt+A+U+U or Shift+Alt+← Ungroups grouped rows or columns

Highlight Row Shift+Spacebar Selects entire row

Editing

Shortcut Name Keystrokes Purpose

Replace Ctrl+H Opens Find and Replace Window

Redo Ctrl+Y Redos last edit

Undo Ctrl+Z Undos last edit

Calculations

Shortcut Name Keystrokes Purpose

Auto Sum Alt+= Summarizes column or row information

Edit Cell F2 depending on location of cellEdits formula or function

Naming

Shortcut Name Keystrokes Purpose

Name Cell Ctrl+F3 Opens Name Manager window

Rename Worksheet Alt+H+O+R Renames worksheet

Navigation

Shortcut Name Keystrokes Purpose

Go To F5 or Ctrl+G

Opens Go To window to go to a different cell

Go To End of Continuous Range

Ctrl+↓ Goes to last cell in the range

Select to End of Continuous Range

Ctrl+Shift+↓ Selects cells to the end of range

Highlight Column Ctrl+Spacebar Selects entire column

Toggle Workbooks Ctrl+Tab Navigate between open workbooks

Move Between Worksheets

Ctrl+Page Up, Ctrl+Page Down

Page Up moves to workpages to the right and Page Down moves to workpages to the left

Reference

Shortcut Name Keystrokes Purpose

Anchoring Cells F4 Locks reference cells

Trace Precedents Alt+M+P

Identifies cells that are used to calculate a given formula using arrows

Trace Dependents Alt+M+D

Identifies cells where a formula contain information on a given cell using arrows

Remove Trace Arrows Alt+M+A+A

Removes arrows from trace precedent and trace dependent features