Robin's Website

Robin’s Excel Page

If somebody asked the author of this website if they were allowed one computer with only two applications installed, and no more, then which two they would be? The answer would be simple: A web browser and a copy of Microsoft Excel. Whilst familiar with all of the other Microsoft Office applications, a reasonable knowledge (from previous work) of Adobe programs, such as InDesign, Photoshop and Fireworks, and countless other applications, nothing surely beats Excel for versatility.

Above: Excel launcher icons throughout the years. How far back can you remember? The first versions of Excel came out in 1985 (Mac) and 1987 (Windows).

The purpose of this webpage is not to provide a comprehensive tutorial in Microsoft Excel, rather than to maintain a personal record of some of the tools and tricks the author has found out whilst using Microsoft Excel throughout the years; thus, this webpage is not intended to “reinvent the wheel”. There follows no “how-to” instructions, rather just a selection of task-specific tips which the author found useful along the way.

Creating a Dropdown List

Starting with some basic features, the dropdown list is useful with ensuring data input is consistent with a choice of entries and helps with eliminating typing errors where required input can be pre-defined.

In this example, cells in column C are highlighted, under “Data”, “Data Validation” is selected, then under “Allow:”, “List” is selected, and the Source is selected as cells A1 to A7. Then a custom error alert can be set to stop entry other than one of the listed colours, with a message alerting the user, if their cell input does not match one of them.
The data validation tool can also be used to put certain restrictions on what is entered into specified cell. For example, a date entry within a specific range or after a specific date, or a numerical value only to a specified number of decimal places. This can help with the accuracy of information entered onto a spreadsheet. Validating data input (as well as number formatting) can also help later with ensuring it is fit for calculations and/or presenting in a consistent format.

Rounding Numbers

Rounding numbers using the formula =ROUND(number, num_digits) is important if applying IF formulas to compare a value against a specification. For example, if a value is always reported to one decimal place, there is no use in comparing a value of 8.03 with a specification of 8.0; the IF formula will state that 8.03 is greater than 8.0. However, if the 8.03 is rounded to 8.0, it will not be greater than the specification (in any circumstances where rounding up is always necessary, the ROUND function can be replaced by the ROUNDUP function).

VLOOKUP (& Some Other Basic Formulae)

An extremely useful tool in retrieving data from a table is the VLOOKUP formula. Building on the previous example, the image below lists some vehicles in column B and their colour, as selected from a dropdown list, in column C:

In E1, the name of a vehicle listed in column B is entered. In cell F1, the following formula is entered:


Where E1 is the value to look up, B1:C10 is the range to search, and 2 = the column number of the range to return the value from. The parameter of FALSE means that VLOOKUP is looking for an exact match for the value (TRUE would mean that an “approximate” match would be used with respect to returning a data value). And so here, because E1 contains the text “Car 3”, cell F1 has looked in the appropriate cells and returned its corresponding colour, Green.

Note 1: If, for example, Car Number or Bus Number ha been entered twice in column B, then the VLOOKUP formula would return the first value it finds, as it searches down the list. For this reason, I would set up a table where the vehicle and its number are initially entered, and have a separate list, where duplicate entries are eliminated, next to which the colour would be entered. This can be achieved using the “COUNTIF” function.

Note 2: If there is no entry for colour, then the value zero might be returned. This issue can be eliminated by embedding the VLOOKUP formula into an IF statement, whereby if the VLOOKUP value zero is returned, “Not Determined” is displayed. Also, when using VLOOKUP formulae, sometimes there may be errors returned in the cell, in which case, I “tidy up” the spreadsheet by embedding the VLOOKUP formula into an ISERROR statement in addition, or instead (ISERROR checks if the returning value is an error or not).

Note 3: Once a spreadsheet is created, it is always useful to lock all cells with a password, other than those where data is to be entered, to help protect those all-important formulae!

Examples where I have used a combination of VLOOKUP, IF, COUNTIF and ISERROR formulae, as well as others, such as “NOW”, which returns the current date and time, include creating Laboratory Information Management (LIMS) systems, where data generated is maintained and accessed in a database (Microsoft Excel provides a more versatile means of performing numerical calculations on data than database programs, such as Microsoft Access), maintaining inventories of items which have an expiry date, keeping staff holiday records and ensuring there are no clashes with other members of staff trained in the same procedures who have already booked time off, and maintaining medication stocks of clients/reordering forms within a residential care home setting. These are just a few examples and, of course, the number of possible applications using these formulae is seemingly endless.

Removing Repeat Entries from a Single Column (e.g. Batch or Lot Numbers)

If, for example, information is to be recorded for items with unique reference numbers (e.g. data pertaining to a batch in this example, or lot number), it may be essential that a row of batch information is not repeated in the spreadsheet. Simple formulae may be used to ensure that each batch number is entered only once. For this example, formulae may be altered where applicable, including adjustments if the data is presented in a transposed format. Some of the description below may seem a bit tricky at first, but once the formulae have been entered into a spreadsheet, things should hopefully make a bit more sense if they didn’t first time.

In the example above, batch numbers are recorded in column A, and column B highlights repeated entries by returning the word “Repeat”. Cell B2 is left blank as the first entry under Batch in column A cannot be a repeat. Cell B3 contains the formula =IF(COUNTIF(A$2:A3,A3)>1,"Repeat","") . You will see that there are two formulae being used here. The COUNTIF is looking at the range A2:A3 and counting the number of instances where the entry in cell A3 may be found. The IF statement is then returning a true value (=”Repeat”) if the number of instances of the entry in column A is greater than 1 (i.e. if it is a repeated batch number entered). The purpose of the $ in the formula is to keep the value 2 in the range A$2:A3 fixed when dragging the formula down to extend the table. And so, dragging down the formula in cell B2 will change the range of cells being looked at to A$2:A4, A$2:A5, A$2:A6… and so on. The value being checked to see it is being repeated will always be that in the cell to the left of the cell the formula is entered into.

Following on, we may now wish to have a neat list of all the entries without the repeat values and without blank rows (as seen in column H, above). In this example, the left area is set aside just to enter batch numbers in. On the right hand side (Column H,I,J etc.), batch numbers, minus repeats, can be displayed (preferably in locked cells), ready for Batch information to be entered horizontally (as mentioned above, all of this can be transposed if desired so that batched go across and data down. However, it is worth noting that Excel spreadsheets have many more rows than columns).

And so, for sorting, the following technique may be used (there may be other ways). First, I have inserted another column to the left of the batch number entry column and entered incremental numbers for each row.
Cell D2 contains the formula =IF(C2="",A2,"") . Here, it will display a number from column A, only if the Batch entered is not a repeat. The cell D2 formula can be dragged down.
Cell E2 contains the formula =SMALL(D$2:D8,A2). This will return the smallest value in column D (A2 equalling 1). The Cell E2 formula can be dragged down. Cell E3 contains the formula =SMALL(D$2:D9,A3). As A3=2, it will show the second smallest value in column D. Subsequent rows in column E will return the third smallest value from column D, then the fourth smallest and so on.
Cell F2 contains the formula =VLOOKUP(E2,A$2:B8,2,FALSE) . Here, the value in cell E2 is searched for in the range A2:B8, returning the value in the second column of the row where it is found. You will see, we now have a list of the batches entered, without repeated batch numbers and with no spaces between them. Again, this formula can be dragged down. Note that the B8 may be extended and amended to B$1000, or however large the spreadsheet is anticipated to be.
Finally, cell H2 contains the formula =IF(ISERROR(F2)=TRUE,"",F2). This is also dragged down and in essence returns the same values as those in column F, minus any error values which may be displayed where there is no batch number to return. Note: If using a spreadsheet which is transposed w.r.t. this example, the HLOOKUP formula may be used in place of VLOOKUP.

Returning the nth (or nth to nth) Value(s) Using the MID Function

The Mid formula is in the form =MID(text, start_num, num_chars)
This can be useful if it is required to extract characters from a cell entry, where the position of those characters will be fixed. For example, if we wanted to return A or B from the following data:

In cell B2, we could type:
This will return “A” in cell B1. The formula can be dragged down the column for the rest of the entries in the spreadsheet.
By typing =MID(A1,5,2) in cell B1, the values “Av” will be returned.

Playing with Numbers and Letters

Sometimes it may be necessary to tidy up data. For example, if routinely importing .csv files from an external source into a pre-formatted Excel template. Some of the following formulae may be of help:

Removing the nth characters from a cell entry:

=REPLACE(old_text, start_num, num_chars, new_text)
From the previously shown example, by entering the following into cell B1:
The value “Av22” will be returned in cell B1
Note: this formula can be used to replace the characters with something else, by substituting the “” in the formula with what it is to be substituted for.

Replacing specific text from a cell entry:

This can be achieved using the formula =SUBSTITUTE(text_string,old_text,new_text)
So, entering =SUBSTITUTE(A1,"A","X",1) into cell B2 would return”2022Xv22” (from “2022Av22”)
Note: this formula can be used to remove specific characters, by substituting the “new_text” in the formula with “”.

SUBSTITUTE replaces one or more instances of a given character or a text string, whilst REPLACE changes characters in a specified position of a text string.

Extracting left (or right) characters:

The LEFT function or the RIGHT function extract a substring from the left or right side of a text string respectively:
=LEFT(text,[num_chars])   or   =RIGHT(text,[num_chars])
So, for example, using the example shown in the previous image, if =RIGHT(A1,3) were entered into cell B1, the value “v22” would be returned in cell B1

Reporting to Significant Figures

In some situations, values may be reported to a number of significant figures, as opposed to a number of decimal places. Values may be reported this way as per the following example:


Where the value in cell A1 is to be reported to (as an example) 3 significant figures. (3 can be replaced by another value or by a cell reference, if the number of significant figures to report to varies)

Hiding Formulae Where the Returned Value is for Calculation Purposes Only

In a number of instances, in order to simplify things and prevent the creation of very long formulae that can appear confusing to anyone else editing the spreadsheet formulae, a calculation may run over multiple cells using different formulae in each step of an overall calculation. In this situation, it may be desirable to hide intermediate calculation steps from an end user, so as to make the spreadsheet look more user-friendly, tidier, and also more presentable, especially if printing. One common way is to simply hide a column (or row) using the Hide option in Excel. Other than recolouring text to white (or whatever the cell fill selected is), a much more convenient way is to simply set a custom format of the cell(s) containing the formula(e) to be hidden to three semicolons (Format Cells, Number Tab, Category: Custom, Type: ;;;).

Returning the Sheet Name in a Cell

This can be achieved using the following formula (in a workbook that has already been saved somewhere):


Presenting Tables of Read-Only Data on a Webpage

This is another use I have found for Excel. A workbook containing information (only) in tables in Excel is saved as a webpage. The first worksheet is then added to a webpage using an iframe in HTML and the other worksheets can be viewed in the webpage as displayed in a browser, by clicking on the different worksheet tabs, as one would do in Excel itself.

Protecting / Unprotecting all Worksheets

For protecting all worksheets in a workbook, run the following in Visual Basic for Applications (VBA):

Sub ProtectAllWorksheets()

    Dim ws As Worksheet

    Dim Pwd As String

    Pwd = InputBox("Enter your password to protect all worksheets", "Protect Worksheets")

    For Each ws In ActiveWorkbook.Worksheets

        ws.Protect Password:=Pwd

    Next ws

End Sub

For unprotecting multiple worksheets in a workbook, run the following in Visual Basic for Applications (VBA):

Sub UnProtectAllWorksheets ()

    Dim ws As Worksheet

    Dim Pwd As String

    Pwd = InputBox("Enter the password to unprotect all worksheets", "Unprotect Worksheets")

    On Error Resume Next

    For Each ws In Worksheets

        ws.Unprotect Password:=Pwd

    Next ws

    If Err <> 0 Then

        MsgBox "Incorrect password entered. All worksheets could not " & _

"be unprotected.", vbCritical, "Incorect Password"

    End If

    On Error GoTo 0

End Sub

Using Excel for Drawing

For all of its power in handling numerical data, Excel can be surprisingly useful when it comes to drawing. Not only for flow-charts and the like, but as seen here, it is a useful tool for scaled drawings. The use here, came in the form of creating diagrams of Orreries (models showing the rotation of planets around a star, or moons around a planet) which were constructed out of Meccano. An example is shown here:

Prior to creating the drawings, which involved using cell borders for the horizontal and vertical lines, for this purpose it was necessary to make the cells on the worksheets square. This can be achieved by selecting “Page Layout” under the “View” tab. Column widths and row heights will now be in ruler units (inches, centimetres or millimetres, depending on how your version of Excel is set up). Then, adjust the row heights and column widths to an identical value as seen fit. By changing the view setting from “Page Layout” back to “Normal”, row heights and column widths will be displayed in points again.

Note: To change the ruler units visible in Page Layout (e.g. from mm to inches or vice versa), then under “File”, select “Options”, then “Advanced” and under the heading “Display”, change the Ruler units using the dropdown menu and click on “OK” to apply.

How to Get Directory/Folder Names and File Names from File Manager/Windows Explorer into Excel

In a situation where there are a large quantity of files and folders stored on a computer and the names of them all need to be recorded, I have written a guide on how to do this (in Windows). Note – for the filenames, see Note 2 on page 4. The guide can be viewed on the link Here.

Back to Top