Skills of An Advanced Excel User
Formulas and Functions
A prospective candidate, with expert Excel skills, should have a detailed knowledge of Excel's functions and formulas.They should be able to utilise formulas for financial modelling, engineering applications, statistical analysis, and high-level business purposes. A candidate with an advanced level Excel skill set could have a role such as an accountant, senior project manager, statistician, or engineer.
Visually Appealing Spreadsheet Creation
An employee that possesses advanced Excel knowledge should be able to create visually appealing spreadsheets using Excel formatting. They should be comfortable with using formatting to create spreadsheets that could be used as the basis for organisation-wide templates.
Data Organisation and Presentation
An advanced Excel user should be able to organise and present their data in Excel tables and use structured references. Structured references allow one to reference table data such as column names, both inside and outside of the created table. Furthermore, an advanced Excel user should know how to present data visually using standard and non-standard chart types, as well as customise their charts if the need arises.
Users with advanced level knowledge of Excel should be able to highlight certain cells or ranges using the conditional formatting options, create new conditional formatting rules, clear all existing rules and manage existing conditional formatting rules. The advanced user should know how to use advanced filtering to extract unique items from a list and to extract data to a different worksheet.
Number formatting allows one to change how numbers are displayed. Excel allows one to format numbers as currency, percentages, decimals, text, dates and other formats. Creating one's own number formats allows for further extensibility and one can create a number format that displays negative numbers in red, for example. Advanced users know when and how to apply number formats and when a situation calls for the creation of a custom number format.
Pivot Tables are considered to be one of Excel's most powerful and useful features. Pivot Tables provide a meaningful summary of complex data sets and allows one to group different elements of the data set together, in order to draw conclusions and insights.
A Pivot Chart, is a chart that displays data from a standard Excel Table or Pivot Table, using the same functionality the Pivot Table utilises. An advanced Excel user should be very comfortable with creating Pivot Tables and Pivot Charts from large, complex data sets.
Data Tables in Excel, enable one to see the effects, of one or two variables on a formula and the corresponding outcomes. Since Data Tables can only handle two variables at most, when one needs to see the effect of more than two variables on a formula, then one needs to use scenarios.
The Excel Solver Add-in allows one to calculate an optimal value, given a set of input variables and constraints. Goal Seek, Data Tables, creating scenarios, and the Excel Solver Add-in, all form part of What-If Analysis. An advanced Excel user should know when to use Data Tables versus creating scenarios and be comfortable with finding optimal values using the Solver Add-in. For more complicated statistical and engineering models, one can use the Analysis ToolPak. An advanced Excel user should be able to use tools such as ANOVA, and the Moving Average functionality provided in the Toolpak.
Auto Fill and Flash Fill are two features that rapidly populate cells and ranges, based on the patterns detected in other cells and ranges. An advanced Excel user would rapidly speed up the typical data entry process, since they would be comfortable with using both Auto Fill and Flash Fill.
Workbook and Worksheet Protection
Often workbooks contain critical, sensitive data and it is necessary to prevent users from deleting, adding, or moving worksheets in the workbook. Protecting one's workbook stops users from changing the worksheet structure. Protecting a worksheet in a workbook, prevents users from changing or editing the data in that specific worksheet. An advanced user should know the difference between workbook and worksheet protection and how to protect sensitive data using these features.