Intermediate Excel Skills – Which Ones Do You Need?
Definitions of what Excel tasks are considered “basic”, “advanced”, “intermediate” and in between can vary from company to company or industry to industry. An accounting firm, for example, may consider specific accounting functions, VLOOKUP and INDEX/MATCH “basic” tools for the job while an HR department might consider these unnecessary but require knowledge of Power Query and advanced conditional formatting. Neither of the above may require knowledge of VBA, but an Analyst would be expected to have extensive experience in designing VBA scripts.
Indeed.com, the well-known job search and resume platform, describes an intermediate Excel user as one who “uses more complex formulas” and “understands how cells function when being copied and pasted from one spreadsheet to another. Intermediate Excel users understand the difference between a function and a formula” as well as being “familiar with how to use pivot tables, VLookup, and date functions among others.” https://ca.indeed.com/career-advice/resumes-cover-letters/intermediate-excel-skills
Here is a short list of skills to tackle when moving from a basic user to a solid intermediate user in any industry:
Workbook Setup – Once you master basic Excel formulas and begin creating more complicated calculations, you also have to level up your knowledge about how to prepare your data and your sheets. A solid understand of when and how to use Relative and Absolute cell references means that those useful, complex formulas you are building can be easily and accurately copied across multiple rows and columns. If you share work with others, data validation and cell and password protection techniques keeps others from making changes you don’t want made. And knowing how to use Formula Auditing will be useful when you need to find and fix any problems that creep up.
SAVE $10 AND TRAIN ON THIS TOPIC TODAY
Pivot Tables – Pivot Tables summarize large sets of data without having to create dozens of nested IF functions and filter settings. It is particularly useful for analyzing table data organized by records (rows). By using any cell of data as a category and performing calculations on values contained within the same record, Pivot Tables allow you to turn raw data into meaningful information.
Example Use Cases:
Sales data – learn how many orders come from each state that you sell in
Marketing data – learn how much money each product type is making for your business
Financial data – learn what expenses are costing you the most to run your business
HR/Employee data – learn who has been at your company the longest and who is up for an anniversary bonus
To learn more about Pivot Tables:
Microsoft® Excel® PivotTables Made Easy 2013
How to Create PivotTables Using the Quick Analysis Tool in Excel
How to Create a Report in Excel: The PivotTable
Lookups – At the intermediate level, you will begin to answer questions about your data that requires finding information buried on multiple sheets and in multiple ranges of records. Excel provides several ways to look up values. You might want to enter someone’s last name and get their birthday, for example.
The VLOOKUP, HLOOKUP and XLOOKUP functions all allow you to look up values in one column or row and return values from a matching column or row. INDEX and MATCH functions used together produce a similar result, but this method has been mostly outdated by the more powerful XLOOKUP function. Become familiar with these five functions and their arguments, as well as when you will use them in your business or industry.
Example Use Cases:
- Sales data – look up products by product number and return prices
- Marketing data – look up ad campaigns by date and return impressions
- Financial data – look up investments by account and return balances
- HR/Employee data – look up employees by social security number and return name and address
To learn more about Lookup Formulas:
Excel Lookup Formula to Create Combined Tables
Finding Information with the Index Formula in Excel
Use VLOOKUP to Find Values from an Excel® Table
How to Use the Excel Match Function to Find Data
Functions – No one needs to learn every function Excel offers. But a confident intermediate will understand how to use the functions that are most important to their work and understand the arguments.
Intermediate Functions by Industry:
- Sales & Marketing – IF and IFS functions such as SUMIF(S), COUNTIF(S), AVERAGEIF(S) and other logical functions such as AND and OR let you manipulate data based on specific criteria. ROUND, ROUNDUP, ROUNDDOWN, MROUND gives you control over the detail included in a report by letting you specify how calculation results are displayed.
- Finance – If you aren’t an accountant or financial pro, but work with money, you’ll want to learn the functions that support your needs. A realtor might help clients estimate mortgage payments using the PMT function, for example. SUMPRODUCT lets you quickly multiply a range of numbers and then add the results together.
- HR & Management – Those who work with lots of text data will want to master text functions such as TRIM, CONCAT, LEFT, RIGHT, MID. Date functions such as DAYS, DATEDIF, WORKDAY can also help manage deadlines and calculate ages.
To learn more about functions:
About Excel Formula Syntax: The Language of Formulas and Functions
Reaching the next level of Excel proficiency can be as easy as taking on the next task you want to accomplish. Solve the problem you need to solve and learn the tools to do it as you go. If you need a little help on your path, Pryor Learning has Excel courses at all levels.
