Excel is a widespread 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 Analysis,” 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. For comprehensive explanation on all excel formulas follow
this link.
Excel Functions and Formulas
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.
Excel 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:A28)
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 categorize 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).
Commonly Used Excel Functions
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.
Excel Statistical Functions
When presented with large datasets, it is helpful to sort and summarize the information at hand.
COUNT, COUNTA, and COUNTBLANK FUNCTIONS
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.
These are some of most important functions for a startup business person to know in Excel.
AVERAGE, MEDIAN, MIN, and MAX FUNCTIONS
With large datasets, it is often helpful to run basic summary statistics before doing further analysis.
- AVERAGE – This function calculates the average value in a dataset.
- MEDIAN – This function calculates the median, middle value, in a dataset.
- MIN – This function returns the lowest value in a dataset.
- MAX – This function returns the largest value in a dataset.
QUARTILE
The quartile function helps users to understand the distribution of values. The first argument identifies the values or cells that users want to calculate and the argument tooltip identifies which quartile (0 – minimum value; 1 – first quartile or 25th percentile; 2 – median value or 50th percentile; 3 – third quartile or 75th percentile; and 4 – maximum value). Quartile is also one of the most Used Excel Functions you may not know about.
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 ranges 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 Functions
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 Formulas With Examples
The excel vlookup 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. This function is one of my favorite and most useful excel functions for data analysis.
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).
The second argument tooltip is for Excel to identify the appropriateness of the match. “False” denotes an exact match, while “True” denotes an appropriate match. We want an exact match for this scenario.
INDEX MATCH FUNCTION IN EXCEL
The excel index 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, see the below screenshot on how to calculate index in excel.
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.
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.
Text Functions
Text functions are helpful when we seek to only get a portion of the information contained within a cell or we are trying to combine information contained in multiple cells.
LOWER, UPPER, and PROPER
Formatting can be an issue when exporting data. Information may be in upper cases, lower cases, or a combination of both. To make clean tables and charts can require formatting or use of the following functions:
✓ LOWER – This function returns text values in a cell in all lower case.
✓ UPPER – This function returns text values in a cell in all upper case.
✓ PROPER – This function returns text values in a cell with the each of the first word capitalized.
CONCATENATE
CONCATENATE is helpful to combine information contained in separate cells into one cell. The function contains arguments referencing the cells containing the information.
The example below shows how the function returns values. In the first example, first name and last name is combined, but look closely because there isn’t a space. By adding to the argument by including spaces (“ “), commas (“, “), and other desired punctuation or words (“ of “) the appropriate desired value can be returned.
LEFT, RIGHT, and MID
These functions are helpful to extract a portion of a larger string. The example below shows is an account structure. The first four digits represent the department/division. In order to retrieve the first four digits, we use the left function.
The first argument identifies the cell containing the account information (A2) and the tooltip identifies how many digits from the left do we want to extract (4).
The RIGHT function follows a similar structure. In the example below, the last 5 digits represent the object. Thus, the argument identifies the cell containing the account information (A2) and the number of digits from the right we want to extract (5).
The MID function requires more details. Similar to LEFT and RIGHT, the argument identifies the cell containing the account information (A2). The first tooltip identifies which digit to start the extraction (5 to represent the fifth digit in the account string) and second tooltip identifies the number of digits to extract (5). Commonly Used Excel Functions.
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 FUNCTION IN EXCEL
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 Commonly Used Excel Functions return a value of “Not Overpayment.”
Please note the attention to the value we selected to return if the statement is false because the logic statement is also capturing instances where the payment amount equals the invoice amount (Rows 3 and 6).
AND
Similar to if statements, the AND function returns a value of “TRUE” if the statement is true and “FALSE” if the statement is false. The function tests multiple criteria. In the example below, the statement is testing two criteria. The first is whether there has been an underpayment (Column E is less than Column C) and whether the payment took more than 30 days from time of invoice (Column D minus Column B is greater than 30). Both conditions must be true in order for Excel to return the value “TRUE.” If only one of the conditions is true, either there is an underpayment or payment took over 30 days from time of invoice, then Excel returns the value “FALSE.”
OR
This function tests multiple logic criteria and returns a value of “TRUE” if one criterion is true. The example below is similar to the one for the AND function. However, if one of the conditions, e.g., underpayment (Column E is less than Column C) or payment taking more than 30 days from time of invoice (Column D minus Column B is greater than 30), is met, then Excel returns the value “TRUE.” If both conditions are not met, then Excel returns the value “FALSE.”
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
|
According to
Microsoft Official blog, the INDEX function applies to the following versions of Ms Excel:
Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Online Excel for iPad, Excel for iPhone, Excel for Android tablets, Excel for Android phones, Excel Mobile, Excel Starter 2010 Less.
This really are most commonly used excel functions and formula and examples.
Hope you enjoyed the article? Do share with friends and leave your comments if any questions.
