Microsoft Excel is packed with useful functions that can help to speed up day to day jobs in the workplace, however for a lot of us the program is a total mystery and completing the simplest of tasks can leave us in a meltdown.
If you’re not quite as clued up with Excel as you’d like to be, this handy guide from Best STL may be of some help. Master these 7 easy to learn tricks and you’re sure to impress your boss!
1) Flash fill
One thing that makes Excel great, is that it’s pretty good at picking up on patterns in what you enter and how you edit data. If you’re always having to split up first and last names in your database, or switching between UPPER and lowercase, Excel can pick up on this and make the corrections for you automatically.
How: You can do this by clicking in the column next to the data you want to fix and typing how you would like it to look. Then select the correction and all of the other cells you would like to correct and hit CTRL+E.
2) Pivot tables
Pivot tables allows you to quickly summarise large amounts of data in lists or tables without having to use formulas – what a life-send!
How: All you have to do is click in your table of data and go to “Insert>Pivot table”. Once you’ve highlighted all of the data you want to analyse, hit OK. You can use the right hand bar to to create your table by dragging the fields in the top half to their respective slots in the lower four boxes.
3) Goal seek
If you know what answer you want to get from your data, but you’re not sure will inputs will produce that answer, Goal Seek can help you. By letting it know what you want answer you’re after, it will tell you what figures you need to put into your formulas to produce the result you are looking for. This is a useful tool to use for forecasting and setting goals.
How: You can do this by going to “Data>Data Tools>What-If Analysis>Goal Seek”. In the “Set Cell” box, select the cell with the formula you want to determine. On the “Top Value” box enter the answer you need. Lastly use “By changing cell” to pick the input value that Excel can adjust, then click OK.
4) Conditional formatting
So that results can be read at a glance when reviewing spreadsheets, conditional formatting can be used to highlight key information. This is great for reporting and analysis.
How: Highlight the cell range by going “Home>Conditional Formatting” and choose from the range of formats available. For example, the to highlight all values lower than 100, choose “Highlight Cell Rules>Less than” and type in 100. Before clicking with OK you can also choose the format that will apply to any matching values.
5) Index and Match
If you’re looking to search through data in your table you can use Index and Match, which offers much more flexibility, speed and ease than VLOOKUP. This is really handy for retrieving data from a complex sets of data.
How: Ensure that your data is formatted in a grid with column and row labels. Use Match to return the Column that contains your search target and another Match to find the row that will contain your answer. Feed these two answers into Index and Excel can find the value where the two intersect.
6) Waterfall charts
Waterfall charts are on the most popular options for visualisation of data. They break down net values into flow and contributions to a total. This is particularly useful for reporting performance.
How: Select “Insert>Insert Waterfall Chart or Stock chart>Waterfall”.
7) Forecasting
Forecasting allows you to predict future outcomes from historical datasets. This is great for modelling performance expectations.
How: You can do this by selecting the corresponding sets of data and go to “Data>Forecast Sheet”. In “Create forecast Worksheet” box choose either a line chart or a column chart, pick and end date and then select “Create”.
