Microsoft Excel offers the largest range of functionalities available from a single spreadsheet and workbooks program.
That makes it a necessary investment for big and small businesses alike, across nearly every industry. More and more modern companies are also seeking Excel expert support to better understand key formulas, functions and automation tips. This is the only way to optimise day-to-day processes and tailor Excel dashboards to suit their own individual needs as a business.
We’ve put together this useful guide for companies or general Excel users looking to do just that. We’re also keen to keep any technical jargon to a minimum because this helps people to get a better handle on different Excel functions. Let’s get straight to it.
COUNTIF formula with multiple criteria
The COUNTIF formula is the perfect way to count the number of matching cells within any given list of values. All you need to do is highlight the list you are working with and specify the exact criteria that you want to calculate.
For instance, let’s say you are a business selling computer hardware and have collated all purchase orders within a single spreadsheet. As part of this you have a column titled Product Type, which includes three criteria: Laptop, PC or Tablet. You now need to check the exact number of laptops sold without tallying up each sale one by one.
To do this, highlight the range of cell range that you want to inspect (we’ll use the example of B2 to B30) and then input the following formula into a blank cell:
=COUNTIF(B2:B30,”Laptop”)
This will leave you with a cell containing the total number of laptops sold.
Excel random number function
Excel’s random number function allows users to generate a random number between 0 and 1. To do this, enter the following formula in a blank cell:
=RAND()
There are also several variations of the standard RAND function. For example, the RANDBETWEEN function lets you generate a random number between a specified top and bottom range. In the below example we’re looking to generate a number between 1 and 100.
=RANDBETWEEN(1,100)
Be aware that when you use Excel’s random number function, the random number will change every time the cell is recalculated. If you don’t want the number to change you can enter the formula as normal and then press F9 to swap the formula out for a random number. This will leave you with a cell containing a single value, rather than a formula.
Calculating standard deviation in Excel
Standard deviation allows you to see how spread out a set of values are in relation to the mean average. If most of the values are higher than the mean average, that is known as higher deviation. Likewise, if the majority of values are lower than the mean average then that’s an example of lower deviation.
For example, to calculate standard deviation in Excel for the cell range B2 to B10, input the following formula into a blank cell:
=STDEV (B2:B10)
The STDEV function is an estimate of standard deviation in a sample. If data represents an entire population, use the STDEVP function.
Excel formula bar shortcut
The Excel formula bar is a useful tool that comes in handy when working with long formulas, mainly because it allows the user to see an entire an entire formula without accidentally affecting other cells. It is indicated by the symbol fx and can be used to input a new formula or to create a new one.
In the most up-to-date versions of Excel, you can show formula bar by navigating to View tab > Showgroup and selecting the Formula Bar option.
You may also toggle the formula bar on and off by using the keyboard shortcut Ctrl + Shift + U.
Freeze frames and panes in Excel
Working with large amounts of data can make it challenging to compare information in your spreadsheets. The freeze function makes it far easier to view content from separate sections of your workbook at the same time.
How to freeze rows in Excel:
Highlight the row below the row(s) you wish to freeze. Navigate to View > Freeze Panes and right click. You should now see that the row is frozen as indicated by a grey line.
How to freeze columns in Excel:
If you’re looking to freeze a column, highlight the column next to the column(s) you wish to freeze, then follow the same steps as you would to freeze a row.
We will also see that Excel has several quick freezing options in the main menu navigation, including Freeze First Column, Freeze Top Row etc. You can unfreeze rows or columns at any time by selecting Unfreeze Panes in the View navigation menu.
Insert slicer in Excel
Excel’s slicer function offers the perfect way to filter information in a normal table or a PivotTable. Unlike basic filtering options, slicers make the current filtering state clear to the user so that it’s easy to understand exactly what data is being shown at any given time.
To insert a slicer in a table of your choice, follow the below steps:
- Click any point in the table that you wish to insert a slicer.
- Navigate to Analyse > Insert Slicer. You may have to click the Options menu if you’re using an older edition of Excel.
- Click the tickbox for the columns in which you want to insert a slicer and then select OK.
- A slicer will now appear for each field that you have chosen.
You can also customise slicers to your liking by navigating to Options > Slicer Styles. Excel comes with numerous in-built formatting options, though you may also create and set your own theme with personalised colours and fonts. This often serves a practical purpose for users by quickly giving them the ability to distinguish different parts of your dataset.
