As well as using and modifying Excel’s built-in cell styles, you also have the facility of creating your own. One simple way of doing this is to highlight a range of cells and apply all the formatting attributes that you want to add to your style and then turn those attributes into a cell style. When you create your cell style, Excel will pick up all the attributes from the highlighted cells and include them in the style. Let’s take an example; let’s say we want to create a cell style for headings with text angled at 45 degrees.

To set the text angle to 45 degrees, choose “Angle Counter Clockwise” from the Alignment section of the Home Tab of the Excel Ribbon. We might also want to centre the text horizontally and vertically, change the colour and make the text bold and slightly larger. Having chosen the relevant options from the Home Tab of the Excel Ribbon, to create a style which has all these attributes, simply highlight a cell that contains these formats and choose “New Cell Style” from the Cell Styles drop-down menu in the Styles Tab of the Excel Ribbon.  Read more...

The COUNTIF function enables us to count the number of cells in a given range which satisfy a condition. COUNTIFS does exactly the same. However, with COUNTIFS, we can specify multiple criteria. For instance, let’s say we have a worksheet containing four columns: the date, the number of phone calls received, the number of complaints and the percentage of calls which were actually complaints.

We now want to create a summary worksheet to calculate the number of days where the percentage of calls that were complaints is, firstly, under 5%; secondly, between five and 10% and, thirdly, over 10%.

To find those days where there were fewer than 5% or more than 10%, we can use COUNTIF function because we are dealing with a simple condition: less than 5% or greater than 10%. However, to find those days where there were between 5% and 10%, we will need to use multiple criteria; firstly, greater than or equal to five and, secondly, less than or equal to 10. For this reason, we will need the COUNTIFS function.  Read more...

Concatenation is a fancy word for the process of stringing together different pieces of information to form a new piece of data. For example, let’s say we have columns containing title and first name and another containing last name; we could use concatenation to create each person’s full name. All we have to do is to string together the title, first name and last name.

Excel offers two methods of concatenation. The first is to use the ampersand (’&’) which is the concatenation operator: the second is to use the CONCATENATE function. To use the ampersand in our Full Name example, we would first type in “=” to indicate that we want to create a function. Secondly, we would click on the cell containing the title to have Excel add a reference to that cell to our formula.

The next step would be to add a space to separate the title from the first name. To do this, we simply type the concatenation operator followed by a space and since a space is a text character, it has to be enclosed in quotation marks. Thus, we would actually type ‘& ” ” &’. Next, we click on the cell that contains the first name, then type ‘& ” ” &’ once more to add a space separating the first name from the last name.  Read more...