Excel Formulas Every Admin Needs to Know

If you’re an administrative professional, you probably rely on Microsoft Excel to perform many of your daily tasks. Excel is excellent for record-keeping and managing and analyzing data. The number of uses for Excel is endless, and that’s why all admins use it on a regular basis.

Whether you’re still learning the ropes of excel or consider yourself a tried and true veteran of the software, there may still be some formulas you’re unaware of that can make your life easier when using an excel sheet.

Learning even a few simple formulas can save you time and make you a more efficient administrator. Let’s take a look at some of the excel formulas that every admin should know. 

SUM

If you need to calculate the total in a column of data, you don’t want to manually add up every number. Not only does it waste time, but it provides room for error. The sum function is a simple way to get the total in a range of cells. 

For example, if you have a column of cells listing sales of each unit, and you want to know the sum total of all the sales, you could use the SUM function. Select the cell at the bottom of the column where you want to write the total. Then use “=SUM(X1:X22)”, where X1 is the first cell in the range and X22 is the last.

AVERAGE

The AVERAGE excel formula is another easy tool that eliminates the need to make calculations yourself. So if you’re looking to find the average of two or more cells, simply use “=AVERAGE(X1, X2)” or if you want to find the average in a range use “=AVERAGE(X1:X22)”.

COUNT

“=COUNT(X1:X22)”

This formula is used for counting cells with numerical data. It ignores cells that include anything other than numbers and can be useful for keeping track of numerical data for a variety of reasons. For example, if you want to know the final number of orders listed on your excel sheet, you can use the COUNT formula, without having to count each cell yourself. 

There are several other COUNT formulas that are more specific in how they count cells. Check out our article on COUNT formulas to learn more.

Convert Decimal to Percentage

If you need to convert cells from a decimal to a percentage, changing each individual cell can be time-consuming. An easier way to do it is to highlight all of the cells you want to change and write “A1/B1”, to convert all of the decimals to percentages automatically.

TRIM

The TRIM formula is a great one to use if you are copying and pasting text into your excel sheet. Oftentimes when you copy and paste text into cells, you may find that there are erroneous spaces included. Especially when it’s a larger chunk of text and you have to delete multiple spaces for several cells, it can feel like a waste of time. Using “=TRIM(text)” will remove any extra spaces in between words automatically.

CONCATENATE

“=CONCATENATE(cell1, cell2,...)” is used to combine the text of multiple cells into an individual cell. For example, if you have a list of clients with two columns of text designating a first name and last name and you want to combine them, the CONCATENATE function will make it much easier. So if “Doe” is in cell B2 and “John” is in C2 and you want them combined in one cell you’d write “=CONCATENATE(B2, C2).

REPLACE

Excel sheets are constantly updated. Sometimes new terminology is used and you have to replace words and phrases with updated ones. Using the “command F” function and manually replacing every phrase is a slow process. The “REPLACE()” function works to replace an existing string of text with a new one. 

“=REPLACE(old_text, start_num, num_chars, new_text)” is the syntax for this formula. “Old_text” is the text you are going to replace, “start_num” is the position in the text you want to start replacing the new characters with, “num_chars” represents how many characters you want to replace, and “new_text” is the new text you are going to replace the old text with.

UPPER, LOWER, PROPER

UPPER, LOWER, PROPER functions are lifesavers if you need to go through an excel sheet and change the capitalization. “UPPER()” will change the text of any given cell to uppercase, “LOWER()” will convert all letters to lowercase, and “PROPER()” will change a string of text to “proper case”. In other words, the PROPER function will capitalize the first letter of a text string and convert the rest to lowercase.

NOW/TODAY

Administrative professionals are no strangers to logging the exact date and time on Excel sheets. Going through the month, day, year, and exact time for certain cells is time-consuming. Using “=NOW()” will log the exact time, month, day, and year in a given cell.

Similarly, the TODAY function provides the date without the exact time. The formula is “=TODAY()”.

VLOOKUP

VLOOKUP is incredibly useful if you need to find data in an array or table. For example, if you need to find an employee's name with just their employee ID number, VLOOKUP will save you lots of time manually scanning the table. 

The formula for VLOOKUP is “=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). “Lookup_value” represents what you want to lookup (i.e., the employee ID number), “table_array” indicates where you want to look it up, and “col_index_num” is the column number in the range that you’re searching that will return the value (i.e., the employee’s name). 

You can also specify if you want an exact match or approximate match for the value you’re searching for in “[range_lookup]”. “TRUE” will give you an approximate match, and “FALSE” will only return an exact match. Not specifying anything will default to TRUE and deliver you an approximate match.