ADVANCED FORMULAS
Write & combine formulas usings SUMIFS, SUMPRODUCT, INDEX, MATCH, LOOKUP formulas. Can debug, audit and figure out which formula for which occasion (and can find a few alternatives for any given formula problem).
TABLES & FORMATTING
Advanced Excel users know how to structure & present their data so that it looks impressive. Good understanding of Excel features like tables, cell styles, formatting options is necessary to make awesome Excel workbooks.
CONDITIONAL FORMATTING
Conditional formatting is a powerful feature in Excel that is often underutilized. By using conditional formatting, you can tell Excel to highlight portions of your data that meet any given condition. For example: highlighting top 10 customers, below average performing employees etc. While anyone can set up simple conditional formatting rules, an advanced Excel user can do a lot more. They can combine formulas with conditional formatting to highlight data that meets almost any condition.
ADVANCED CHARTING
There is no use if all your analysis is buried in a massive spreadsheet. Advanced users of Excel know that by using charts, we can communicate effectively and present results in a stunning manner. The skills required for advanced charting are,
- Knowing how to pick right type of chart for any situation
- Ability to combine various charts in to one
- Use features like in-cell charts & conditional formatting charts
- Ability to set up dynamic & interactive charts
- Use sparklines
PIVOT TABLES & PIVOT REPORTING
Pivot tables & pivot reporting allows us to analyze massive amounts of data & answer questions with just a few clicks. Advanced users of Excel are very familiar with various features of Pivot tables & can use them really well. Some of the advanced pivot table features are – grouping, slicers, calculations & summary by different type of metrics.
VBA & MACROS
Excel’s own language – VBA, allows us to give instructions to Excel to get things done. This is a simple, but extremely powerful way to extend Excel’s functionality. Advanced users of Excel are familiar with VBA & can write macros to automate their day to day work, thus saving countless hours of time & money.
DATA TABLES, SIMULATIONS & SOLVER
Excel has many powerful & advanced features packaged in to it. Data tables: help us model practical problems & analyze massive amount of data for a solution. Solver: helps us model practical problems & find a solution by iterating thru all possibilities. For example, finding cheapest way to ship goods from one location to another.Simulations: We can simulate real world data & situations in Excel using various random functions & statistical methods.
Trend analysis: We can use built in functions & charting features to understand trend & forecast future values from available data.
INTEGRATING EXCEL WITH OTHER TOOLS & OPTIMIZING EXCEL
Advanced users of Excel know that when you combine the power of Excel with flexibility of other applications like MS Access, Outlook or PowerPoint, you can achieve wonders.
Also, they know how to optimize an Excel workbook to make it fast.