# Month: October 2016

## Different count functions in Excel

COUNT returns the amount of cells in the range which contain a number. COUNTA (count all) returns the amount of cells containing any value. COUNTBLANK returns the amount of cells containing no value. COUNTIF returns the amount of cells in the range which meet your chosen criteria.

## Random Number Generation tool in Excel

This tool can be used to rapidly populate large amounts of random numbers that adhere to various statistical rules and conditions which you can place on them. This is helpful to prove or disprove your hypothesis or to forecast how something may perform based on tried-and-tested statistical methods.   You will need to have Data Read More …

## Descriptive Statistics in Excel

When you have a lot of numeric data which you need to make sense of, the Descriptive Statistics tool can help.   To demonstrate using a simple example, my dataset is the number of each ball in the UK National Lottery (numbers 1 to 49).   Data Analysis tools must be enabled in order to Read More …

## Enabling the Data Analysis tools in Excel

Open up Excel and go to the File menu. Select Options and then Add-Ins from the left hand panel.   From the Manage drop-down list at the bottom choose Excel Add-Ins and click Go.     Click to select the Analysis ToolPak. Once you’ve done it, it will appear permanently on the Data tab in Read More …

Excel has a built-in function allowing you to add hyperlinks to a cell. You can also give the hyperlink a user-friendly name rather than just displaying the file path.   E.g. =HYPERLINK(“E:\Documents\Finance\Financial Calendar.xlsx”,”Finance calendar”)

## Manipulating text in Excel

There are lots of cases where manipulating text in different ways can be quite challenging in Excel. Learning a few formulas and techniques can help you to overcome these challenges.   TYPE function A common problem is distinguishing whether a cell value is in number or text format. You may have values that look like Read More …

## Using the SUMPRODUCT function in Excel

The SUMPRODUCT function can be used to multiply an array of cells and then sum the total of those calculations.   To demonstrate, firstly here is an example of the long way of doing it, without SUMPRODUCT:     To calculate total number of chocolate bars sold, each row is calculated one-by-one and then all Read More …

## Rotate an auto shape

This is a quick and easy one, but essential if you use a lot of auto shapes on your dashboard.     To make some space along the top you want to rotate the text heading 90 degrees so it is displayed to the side of the table. Click on the auto shape containing the Read More …

## Hide gridlines in Excel

A dashboard or summary in Excel often looks smoother without the gridlines in the background, here’s how to remove them.   The quickest way to toggle between showing and hiding them is the following shortcut: Alt, W, V, G.   The ribbon equivalent is to go to the View tab and de-select Gridlines.