To those who don’t use Microsoft Excel regularly, it can appear as quite a confusing and complicated program to understand. However, nothing could be further from the truth. Once you are able to understand its key features, how to use them and how they might potentially benefit you in the workplace, you will be on your way to mastering Excel before you realise it.
By becoming familiar with some key Microsoft Excel formulas, features and functions you can realise just how powerful a program it can be. One of Excel’s key benefits is just how versatile it is, and how it works superbly for a variety of platforms. It is able to deliver a huge range of features, some of the more frequently used ones are outlined below, all of which can prove useful when you are handling copious amounts of data in a spreadsheet.
Each feature has varying levels of complexity, so some might need a bit more practice than others to perfect. Here at XL Intelligence, we provide specialist Excel consulting services for businesses of all sizes. Our courses are designed to help your decision-making by increasing your productivity with Excel. Each Excel spreadsheet consultant can talk you through the program, and will help you fully understand its flexibility, discover its features in more detail, explore and manage your hidden data, and understand how to make your data visually impactful.
Here, we’ve outlined some of Excel’s key features. You may be familiar with some of these features, therefore you might be aware at how effective they can be. Our outlines below aim to give you some more insight into them, what they’re used for, and how you might be able to benefit from them.
Did you know?
Some useful Excel functions can be used by simply remembering a few handy shortcut keys.
Alt+F1 – Creates embedded chart of data in current range
Shift+F2 – Adds/edits cell comments
F3 – Paste Name dialog box
Shift+F3 – Insert function dialog
F4 – Repeats the last command
F8 – Turns Extend mode on or off
You can find a full list of shortcut keys here.
Flash Fill automatically fills your data when it senses a pattern. You can use Flash Fill to separate two pieces of information from a single column, or combine information from two different columns. For example, if you want to combine first and last names (both of which have been separated into two different columns), you can establish a pattern by typing the full name in the empty column. The Flash Fill feature will fill in the remaining cells with the rest of the information, following the pattern you provide. Flash Fill is usually automatically generated, but you can go to Data – Flash Fill to run it manually.
PivotTables help to sort, total, count or average the data you have sorted in the spreadsheet, and display the data in a new table, customised how you wish. PivotTables let you see comparisons, patterns and trends in your data. You select the cells you want to create the PivotTable out of, verify the cell range, then select the desired location of the PivotTable. You can then add fields to your table in the PivotTable Fields pane that appears afterwards.
By default, cells in Excel have references that are relative. What this means is that the reference is relative to the cell’s location.
An absolute reference is created when you precede the columns and rows with a dollar sign ($) in the reference. Essentially, the dollar sign fixes either the column or row (or both) in the reference. You can mix absolute and relative cell references too. You can select which reference you want for your cells by selecting it, and pressing F4 to view the options.
Think about it like this:
You’re on your way to a housewarming party, having been given instructions of how to get to the address (let’s say it’s 10 Green Drive, but you’re unaware of this). You’re following the instructions of how to get to the house, which involves getting off the bus at Stop C, walking one block down the road, turning left, walking past three houses and your destination is the fourth house on the left.
In reality, you get off at bus Stop D by mistake, and follow the exact same instructions given to you, after stepping off the bus. You walk one block down the road, turn left, walk past three houses and come to the fourth house on the left. You knock on the door, and realise, after someone you have never met before answers, that you have arrived at the wrong house. You check your surroundings and you’re actually at 8 New Lane.
This is because you were given a relative address, as opposed to an absolute address. The instructions you were given were relative to Stop C.
Had the absolute address (10 Green Drive) been provided to you then your destination would have been locked down ahead of arriving at an address relative to the bus stop (8 New Lane).
Paste Special is a handy tool which helps you copy specific amounts of data (or all of it), which includes all formulas, comments, values and formats. You can use the Paste Special feature to copy complex items from an Excel spreadsheet and paste them either in the same spreadsheet or a separate one, using only specific attributes of the data. You can also apply mathematical operations to the copied data by using this feature.
Conditional formatting in Excel brings out patterns in your data. Whether you are applying simple or sophisticated rules, conditional formatting can make all the difference when handling and visualising your data and make it easy to understand. This widely-used feature can save time, and the fact that rules and conditions are customisable to your own parameters make this very handy for learning this valuable feature.
Filters allow you to hide data that you wish to exclude. Not that data isn’t valuable to some degree, but applying a filter can condense data down so the worksheet displays the most relevant and applicable information that you want, and quickly. Filtering is useful if you want to combine content. When you filter data, rows are hidden if values in one or more columns don’t meet the filtering criteria. You have the option to apply Number Filters or Text Filters, and can also specify conditions to create custom filters, allowing you to narrow down the data how you want.
INDEX-MATCH is one of the more powerful functions that many are not aware about. It enables you to look up certain values in a table of data, by which you’ll only receive corresponding values returned to you. An example would be if your company has thousands of employees, but you’re only after information about your team of 15 people, such as their salary, start date, figures etc. INDEX-MATCH will look up the value of your team members, and return the desired information to you. The INDEX-MATCH combination can ultimately prove more valuable than VLOOKUP function.
How can XL Help?
XL Intelligence’s Microsoft Office courses, more specifically our Excel training courses are ideal if you want to get the full experience of learning and maximising your potential with Microsoft Excel. Our trainers bring thorough and extensive knowledge of all Microsoft Office products (including Power BI and Microsoft Access) but also the skills of how to effectively apply the knowledge in real-world situations that you’ll inevitably face.
Our trainers will give you the skills necessary to maximise Excel features, formulas and tools regarding business intelligence and data visualisation to give you greater control over your data. Our Advanced Excel course is ideal for anyone experienced in how to use Excel and who has a solid understanding of Excel features. For anyone wanting to learn how to automate Excel reports, or how to create a Macro in Excel, we hope you’ll consider one of our highly recommended advanced Microsoft Office training courses.