First of all, it's great to see that some still realize the potential of MS Excel and is showing interest in learning Excel. I will assume here that you already know the basics excel as the question is about Advanced skills.
In business, about 80% of your work will come from 20% of the functionality of the Microsoft Excel.
Excel can be a very powerful tool especially if used the right way. Here are a few ways in which you can pick up advanced Excel skills:
1. Master Advanced Formulas:
Formulas make Excel smart. If you learn how to use formulas you can crunch a large amount of data, analyze and find solutions to very complex problems. While most people are familiar with the simple IF and SUM formulas, they are not aware of the advanced formulas such as SUMIFs, LOOKUP, SUMPRODUCT, INDEX, MATCH, dynamic array formulas, circular references etc. Learn how these formulas work and where to use them. Advanced users also know how to debug formulas and audit them. They are also aware of alternative formulas or alternative methods of solving a given problem.
2. Learn advanced keyboard shortcuts:
Learning a few keyboard shortcuts can save you a lot of time. Most computer users today cannot imagine using a computer or navigating online without a mouse or at least a touchpad, but the truth is you can save a great deal of time by using just the keyboard.
For example, if you are a regular Excel user then instead of manually entering the SUM formula you can use the shortcut Alt + = after you have selected the first empty cell in the column (the cell located at the end of all the numbers you want to add). After you execute this command, press the Tab key to get the result.
Some of the commonly used 50 shortcuts are as follows:
3. Learn how to import data from any website:
Picking up this skill can speed up your work. Whenever you come across a website with a large amount of data that is useful or relevant for your project, you can immediately convert it into a worksheet by doing these things:
1. Click on the file menu
2. Click on the import external data option and click on new web query
3. A new window will open up with your browser homepage and the URL of the homepage will be highlighted
4. Copy paste the URL of the website that you want to import data from
Voilà! Your worksheet with all the data is ready.
4. Master VBAs and Macros
VBA is Excel’s own language. VBAs allow users to give instructions to Excel to get things done. VBAs and Macros can help you automate your day to day work thus saving you money and time.
The best way to learn Excel VBA is through necessity. Are you working on something in Excel that requires manual work? or needs to be replicated?
Pick a project.. maybe automate data entry of something in excel. Use the record macro function in Excel and record a macro on how you want to do it. (Google: "how to record macro excel"). Then, go to VBA Editor (Google: "how to get to vba editor in excel"), Module 1, and look at the code. Try to understand it and modify it for your purpose.
That is how a lot of people learned to use VBA. You don't really need a refence book or take courses as there are a a wealth of knowledge from forums, websites, etc on Excel VBA programming within just a google search away.
5. Learn Pivot Tables and Pivot Reporting:
Both of these things allow users to crunch massive amounts of data and find answers to complex questions with just a few clicks. Master advanced pivot table features such as relationships, multi-tale pivots, grouping, slicers, measures (Power Pivot), etc. Learn how to link tables with relationships to weave magic with multi-table pivots.
6. Master advanced Charting / Dashboard Preparation:
Analyzing large amounts of data is meaningless if your insights are buried in a massive spreadsheet. Knowing how to visualize your insights or how to represent conclusions drawn from analyzing data is also important.
You need to know:
1. Which type of chart to use for a specific situation
2. How to combine several charts into one
3. How to setup dynamic and interactive charts
4. Use features such as in cell charts, conditional formatting charts, slicers, timelines and spark lines.
7. Use Autocorrect and Autofill
Mastering these aspects will ensure that you type even less and save precious time.
AutoCorrect is a feature that automatically corrects typos and misspelled words. To enable this, click the Tools tab and select AutoCorrect.
AutoFill comes in handy when you are typing a numbered list manually. To activate AutoFill, go to the Edit menu, point to Fill and click Series.
8. Learn How to Manage Page Layout:
You’ll want to ensure that your printouts look as good as they are displayed on the screen. This is why you’ll have to learn how to manage page layout. You’ll find the option by clicking the Page Layout tab. Consider playing around with options such as page numbering, page borders, columns, etc to see how they work, then branch out.
Here’s a list of other things you should master to become an advanced Excel user:
1. Powerful nested functions
2. Advanced table and cell referencing
3. Scroll bars, check boxes and combo boxes
4. Conditional formatting
5. Integrating Excel with other tools
6. Data tables, solvers, simulations, forecasting, trend analysis, power query
9. Data Validation :
You cannot harness the power of Excel and use features such as filters, PivotTables and formulas if your data is not accurate. Even an Excel superhero will produce wrong results if the data being used is incorrect. It is therefore a useful skill to do what you can to aid data entry, and then validate the data to prevent inaccurate data getting onto your spreadsheets.
𝗡𝗼𝘁𝗲: 𝗘𝘅𝗰𝗲𝗹 𝗶𝘀 𝘆𝗼𝘂𝗿 𝘁𝗼𝗼𝗹 𝘁𝗼 𝗱𝗼 𝘀𝗼𝗺𝗲𝘁𝗵𝗶𝗻𝗴. 𝗜𝘁 𝗶𝘀 𝗻𝗼𝘁 𝘆𝗼𝘂𝗿 𝗳𝗶𝗻𝗮𝗹 𝗱𝗲𝘀𝘁𝗶𝗻𝗮𝘁𝗶𝗼𝗻, 𝗶𝘁 𝗶𝘀 𝘆𝗼𝘂𝗿 𝘃𝗲𝗵𝗶𝗰𝗹𝗲 𝘁𝗼 𝗿𝗲𝗮𝗰𝗵 𝘁𝗵𝗮𝘁 𝗱𝗲𝘀𝘁𝗶𝗻𝗮𝘁𝗶𝗼𝗻.