Advanced Excel refers to features and functions of Microsoft Excel tool which helps the user to perform complex and large calculations, data processing on the huge amount of data, performing data analysis, better representation of data, etc. Advanced Excel functions allow business organisations to increase their productivity and performance by easily sorting and filtering relevant information and using it for better decision making.
Some of the most important advanced Excel functions include date and time function, lookup and reference function, statistical and logical function, data validation and text function, pivot charts and tables, multidimensional analysis, advanced dashboard, etc.
It is important for every working professional to have knowledge in advance Excel so that they can efficiently perform their business operations as it would save them time and effort and deliver consistent and accurate performance. Acquiring advanced Excel skills have also become necessary for fresher candidates as it gives them additional skills and expertise which can help them attract the attention of the employer. Having certification in advanced Excel will allow you to stand out of the crowd and you will be preferred over other candidates by the recruiter during the interview.
Features of Advanced Excel
- Conditional Formatting
Making sense of our data-rich, noisy world is hard but vital. Used well, Conditional Formatting brings out the patterns of the universe, as captured by your spreadsheet. That’s why Excel experts and Excel users alike vote this the #1 most important feature. This can be sophisticated. But even the simplest colour changes can be hugely beneficial. Suppose you have volumes sold by sales staff each month. Just three clicks can reveal the top 10% performing salespeople and tee up an important business conversation.
- PivotTables
At 4 hours to get to proficiency, you may be put off learning PivotTables but don’t be. Use them to sort, count, total or average data stored in one large spreadsheet and display them in a new table, cut however you want. That’s the key thing here. If you want to look only at sales figures for certain countries, product lines or marketing channels, it’s trivial.
- Paste Special
Grabbing (ie Copying) some data from one cell and pasting it into another cell is one of the most common activities in Excel. But there’s a lot you might copy (formatting, value, formula, comments, etc) and sometimes you won’t want to copy all of it. The most common example of this is where you want to lose the formatting – the place this data is going is your own spreadsheet with your own styling.
- Add Multiple Rows
Probably one of the most frequently carried out activities in spreadsheeting. Ctrl Shift + is the shortcut, but actually it takes longer, so Right Click is what we recommend. If you want to add more than one, select as many rows or columns as you’d like to add and then Right Click and add.
- Absolute References
Indispensable! The dollar in front of the letter fixes the column, the dollar sign in front of number fixes the row F4 toggles through the four possible combinations.
- Print Optimisation
Everyone has problems printing from Excel. But just imagine if what you printed were always just what you intended to print. It IS actually possible. There are a few components to this though: print preview, fit to one page, adjusting margins, print selection, printing headers, portrait vs landscape and spreadsheet design. Invest the time to get comfortable with it.
- Extend formula across/down
The beauty of Excel is its easy scalability. Get the formula right once and Excel will churn out the right calculation a million times. The + cross hair is handy. Double clicking it will take it all the way down if you have continuous data. Sometimes a copy and paste (either regular paste or paste formulas) will be faster for you.
- Flash Fill
Excel developed a mind of its own in 2013. Say you have two columns of names and you need to construct email addresses from them all. Just do it for the first row and Excel will work out what you mean and do it for the rest. Pre-2013 this was possible but relied on a combination of functions (FIND, LEFT, &, etc). This is much faster and WILL impress people.
- INDEX-MATCH
This is one of the most powerful combinations of Excel functions. You can use it to look up a value in a big table of data and return a corresponding value in that table. Let’s say your company has 10,000 employees and there’s a spreadsheet with all of them in it with lots of information about them like salary, start date, line manager etc. But you have a team of 20 and you’re only really interested in them. INDEX-MATCH will look up the value of your team members (these need to be unique like email or employee number) in that table and return the desired information for your team. This is worth getting your head around this as it is more flexible and therefore more powerful than VLOOKUPs.
- Filters
Explore data in a table quickly. Filtering effectively hides data that is not of interest. Usually there’s a value e.g. ‘Blue cars’ that you’re looking for and Filters will bring up those and hide the rest. But in more modern versions of Excel, you can now also filter on number values (e.g. is greater than, top 10%, etc), and cell color. Filtering becomes more powerful when you need to filter more than one column in combination e.g. both colours and vehicles to find your blue car.
2 thoughts on “Advanced Excel: Introduction, Features”