ADVANCE SPREADSHEET SKILLS

                    Now a days, many jobs require advanced Excel skills. Naturally, you are interested to know what are these Advance Spreadsheet Skills?Based on our research,more than 3,000 students in various online & physical training programs, the following 9 areas form the core of advanced Excel skills.

  • ADVANCED FORMULAS

                             Formulas make Excel smart. With out them, Excel is just a data keeping tool. But by using formulas, you can crunch data, analyze it and get answers to most complex questions. While anyone can use a simple SUM or IF formula, an advanced user of it would be able to seamlessly write & combine formulas like SUMIFS, SUMPRODUCT, INDEX, MATCH, LOOKUP formulas. Apart from knowing the formulas, advanced Excel users know how to debug them, audit them and how to use which formula for which occasion (and they also know few alternatives for any given formula problem).

RESOURCES TO LEARN ADVANCED EXCEL FORMULAS

SUMIFS
SUMPRODUCT
VLOOKUP
INDEX + MATCH
Handling Formula Errors
Array Formulas
Circular References
Formula Auditing

  • 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.

RESOURCES TO LEARN TABLES & FORMATTING

Introduction
Tables – tips
Table Flexibility
Using formulas with Tables
Formatting 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.

RESOURCES TO LEARN ADVANCED CONDITIONAL FORMATTING

Introduction
5 Tips on CF
Highlighting Duplicates
More

  • 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,

  1. Knowing how to pick right type of chart for any situation
  2. Ability to combine various charts in to one
  3. Use features like in-cell charts & conditional formatting charts
  4. Ability to set up dynamic & interactive charts
  5. Use sparklines

RESOURCES TO LEARN ADVANCED CHARTING

Selecting Right Chart
Combination Charts
In-cell charts
Dynamic Charts
Sparklines
More

  • 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.

RESOURCES TO LEARN PIVOT TABLES & PIVOT REPORTING

Introduction
Pivot table tips
Report Filters
Slicers
More

  • 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.

RESOURCES TO LEARN EXCEL VBA & MACROS

Introduction to VBA
Understanding VBA Variables, Conditions & Loops
VBA Examples
VBA Online Course
More

  • USING EXCEL PRODUCTIVELY

                                    It is not enough to know various features of Excel. An advanced user knows how to use Excel productively. This includes knowing important keyboard shortcuts, mouse shortcuts, work-arounds, Excel customizations & how to make everything looks slick.

RESOURCE TO USE EXCEL PRODUCTIVELY

Keyboard Shortcuts
Mouse Shortcuts
Excel Productivity Tips
Making better Excel workbooks
More

  • 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.

RESOURCES TO LEARN DATA TABLES, SIMULATIONS & SOLVER

Introduction to Data Tables
Statistics & Modeling using Excel
Introduction to Solver
Introduction to Scenario Analysis
Forecasting & Trend Analysis
Advanced Excel Tips

  • 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.

RESOURCES TO LEARN EXCEL INTEGRATION & OPTIMIZATION

Consolidating multiple workbooks
Fetching data from web
Using Excel as database
Sending emails from Excel
Excel Optimization Tips

-REY JOSEPH T. PUYAT

-CINDY CAMARILLO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s