Excel A Brief Introduction

Excel

Excel is a software programme designed to take data and conduct analysis to extract useful information in tabular or graphic from.  The information can then be used for a variety of purposes such as supporting decisions, identifying trends, keeping records and supporting oversight or governance, to name a few examples.

Data can be imported from a range of sources and in a variety of formats.  Some examples of data are: financial, scientific, manufacturing, marketing.  In fact, if there is a software application then there will be an associated database which can be accessed for information, which can in turn be analysed and then presented to support the needs of the organisation.

Data can also be entered manually by directly keying the information in.
Data can be stored in a range of formats

Some real-life examples that I have used:
·         In a global banking environment importing and analysing the server estate of the organisation to identify operating systems in use, which were subject to change programs to update or retire them.
·         In a military environment importing HR records of service personnel to determine when their personal situation required assignment action to reduce separation and costs to the organisation.
·         In a personal situation when cataloguing books in a collection to keep a record.

Component Parts

An Excel File is referred to as a Workbook, this workbook has a number of component parts:

·         A Worksheet, sometimes referred to as a tab.  There can be a great many worksheets within a Workbook.
·         A column and row.  We refer to columns by letter, A-Z, AA-AZ, etc.  We refer to rows by number 1 – 1,000,000.
·         A Cell, this is a single part of a Worksheet and has a Column then Row reference such as A1.  The cell is the place where individual bits of data can be held.

A good way to think about the cell is that it has an address A1 and part of that address is also the Worksheet and the Workbook names.  It is possible to connect many workbooks together, but that level of complexity is further down the track from here.

Menu Tabs
In Excel, when you open a new workbook, it will open on a blank Worksheet Sheet1.  At the top of the screen you will see a number of tabs, as shown boxed in red in the section below:

In the shot above the Home tab is selected and the various options available under this tab are displayed.

There are nine tabs as standard:

·         File – This is common to all MS Office Products and allows you to carry out actions and adjust options concerning the file (Workbook).

·         Home – This tab mainly concerns formatting and the more frequent actions.  In particular it allows access to change the format of cells, set conditions for cells and filter rows of data.  This will be explored in later lessons.
·         Insert – This tab has a number of specialist actions that can be carried out from inserting tables, including powerful analysis tools such as Pivot Tables, to producing graphs and charts to represent your data in wide range of formats and situations. 
·         Page Layout – This tab is similar to other Office Applications in that it allows the characteristics of the pages to be set for printing and orientation among others.
·         Formulas – This tab is the real power and utility of the software as it gives access to the Function Library, you will learn what a function is, but in all likelihood will only ever use a fraction of the ones available for everyday use.
·         Data – This tab is another powerful part of the programme that allows you to get data, import it and clean it up.  You can also set controls and limitations on the sheet once you get familiar with the options on this tab.
·         Review – This tab allows you to spellcheck the worksheet and protect the contents.  It is important to note that, unlike Word, Excel does not automatically underline spelling mistakes or check for them.
·         View – This tab, as the name suggests, has actions that change the view of the worksheet, but in particular has Freeze Panes, which is useful for large worksheets where you want to keep sight of the top row or left-hand columns
Help – No need to explain this one!

Conditional formatting

What is conditional formatting? 

In literal terms it is getting the format of a cell to change depending upon the content of the cell linked to a rule of some sort.

In layman’s terms or to the person on the street it is a helpful way to point out a specific thing in your table of information.  Examples might include entries on an arrivals board at an airport that are late in arriving, examination results of the class that are above and below average and the lists of competitors in triathlon by country or team.

Wouldn’t it be really handy when scanning those departure boards at the airport if the flights to Newcastle had a black background and white lettering?  You would be able to pick out the flights pretty quickly if that was the case.  Usually red lettering, numbers or background are the only indications we are given about flight status.

So why use conditional formatting?

Well, speaking from my own experiences, I use conditional formatting as a method of highlighting errors, seeking out certain values and cross checking with other data.  We are now talking about potentially hundreds of thousands of lines of data as opposed to a couple of hundred rows of flights.  It is an efficient way to cut down the amount of data you are dealing with if you can assign a conditional rule and then to filter the data based on that rule.  It is starting to get a bit technical, so I will step back a bit and set out an example

Patient Screening

Suppose you want to check to see which patients are due a screening for a medical test that is due on their 50^th Birthday?  The local health trust may well have several hundred thousand rows of data to search for those reaching their 50^th birthday in the coming year.  You can set up a condition that will highlight all rows where the age of the patient has turned 50, let us say a light blue background and black bold text.  Such a set of data would then show at a glance who should have their tests carried out, this allows the data to be filtered and saves the staff time in reading individual rows with the possibility of error.

*Data used is fictitious.

A further condition could be set on a column indicating whether a test has been carried out to indicate where they are overdue, this filters the data further and allows more focus to be applied to priority cases.

So the idea of conditional formatting is to be more efficient in putting attention and energy into those areas that require work, a means of prioritising.

The rules can be based on actual cell content (dates, numbers, text, etc) or they can be based on the content of other cells.  Examples of this are: if the cell equals “Overdue” then make the background red and text bold black, if the cell contains “Pass” then make the background green and text black.  There are so many combinations and conditions or rules that it is incredibly flexible for the user to set their own conditions.

In the case above column E has had the condition applied to highlight any cell equal to or greater than 50.

When you start combining functions into the conditions you can really drive out some efficiency and smart ways of working.

One of my favourite things to do is to have a cell which contains the TODAY function =TODAY().  What this will do is insert the current date into the cell and this will refresh each time the file is opened.  You can then set a conditional format referring to the cell with the TODAY function to say if the cell content is greater than TODAY, then turn the background red.  That way you can see when something is overdue and it will keep current because the condition is based on a changing date rather than fixed.

The conditional format utility sits under the Home tab of the ribbon.  It is worth just going in and doing some playing to see the results that can be generated.  You will see that there are lots of different conditions that can be set out.
 

If this has been useful then do let me know, alternatively if it is not particularly clear then I am happy to go through and update the article based on feedback.