From Excel to Sheets: Essential Spreadsheet Formulas for Administrative Success

Mastering Microsoft Excel functions empowers administrative professionals with a potent time-saving and productivity-boosting tool. Plus, proficiency in advanced Excel formulas is one way to elevate your role within your company and skillfully handle day-to-day administrative tasks. 

Before we dive in, a quick heads-up: while these formulas are commonly used in Microsoft Excel, they’re also compatible with Google Sheets and other spreadsheet software. 

Explore these three essential Excel functions: VLOOKUP, SUMIFS, and CONCATENATE, with tips and hacks for each that every admin should confidently master.

Easily combine two spreadsheets with VLOOKUP

VLOOKUP can save admins from manually scrolling through huge amounts of data to locate specific, correlated pieces of information. This Excel function scans a large dataset, searches for a specific value, and returns the corresponding value from a different column in the same row. 

For example, the next time you need to create a company report that contains details about each employee’s job title, department, manager, and email address, use the VLOOKUP function to effortlessly create comprehensive reports from various tables instead of digging through your organization's database for hours.

Breaking down the VLOOKUP Function

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• Lookup_value: The value you’re using to fetch data from another table

• Table_array: The table where the data you want to pull into your current table resides

• Col_index_num: This indicates which column in the table_array contains the data you want to retrieve

• Range_lookup: Here, you decide whether you want to pull an exact match or an approximate match for the lookup_value 

3 Tips for Using the VLOOKUP Function

Get the most out of this function with these hacks:

1. Position the column you want VLOOKUP to search through in the far-left, A column.

2. Name data ranges for lookup tables to improve readability.

3. Choose the approximate match option for instances where there may not be exact matches in your data.

Find sums of specific types of data with SUMIFS 

Administrative professionals know that when it comes to budgeting and expense reports, you need flexibility to understand the totals of specific types of data in a column, rather than seeing just the total of a full column. The SUMIFS formula handles complex criteria, allowing you to sum up expenses based on factors like category, department, specific time ranges, and more.

Suppose you need to summarize how much your company's marketing department has spent on conferences in the last three years. SUMIFS can help you quickly and easily review the company-wide event expenses over the previous three years, specifically focusing on just the marketing team's expenses. 

3 SUMIFS Function Hacks

Take your SUMIFS skills to the next level with these tips:

1. Employ wildcard characters like asterisks (*) and question marks (?) to help find matches that are similar but not exact. This way, accidental misspellings or abbreviations don’t hold you back. 

Tip: To find an actual question mark or asterisk, type a tilde (~) in front of the character.

2. Use logical operators (AND, OR) for complex conditions to create filtering options.

3. Ensure consistency in range arguments by using the same number of rows and columns.

Breaking Down the SUMIFS Function

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2] ...)

• Sum_range: The range of cells containing data you want to summarize

• Criteria_range1: The range you’re checking against criterion1

• Criteria1: The pattern or test you’re applying to criteria_range1

• You can stop with Criteria1 or continue adding additional criteria (criteria_range2, criteria2, etc.) to further refine the data you’re summarizing.

Combine Data from Two Columns with the CONCAT Function

The word "concatenate" means linking things together, and the CONCAT function does just that. Eliminate the hassle of copying and pasting information from multiple cells into a single column by easily merging them using the CONCAT function.

Imagine you have an Excel sheet with client details like first names, last names, street addresses, cities, states, and zip codes, and you need to combine these components for a formatted label. Knowing the CONCAT function makes it easy to merge the necessary columns. 

3 Ways to Improve Your CONCAT Skills

Use these CONCAT hacks to advance your Excel proficiency:

1. Combine CONCAT with TEXT and TRIM for complex text manipulation and formatting.

2. To remove quotation marks from your formula, leave spaces between them.

3. Separate the CONCAT text string from other characters with asterisks and forward slashes. 

Breaking down the CONCAT function

=CONCAT(text1, [text2],…)

• Text1: The first text item you want to join. It could be a string or an array of strings, like a range of cells.

• Text2: The second text item you want to join. Also a string or an array of strings, like a range of cells.

• You can continue adding additional text items to be joined as needed. 

Excel Skills Are Important for Professional Growth

Practice VLOOKUP, SUMIFS, and CONCAT to increase your comfort with Excel. 

Next, Learn About the Power of Pivot Tables

Click Here!