By: Christine Harrell
Microsoft Excel offers extremely powerful numerical and statistical data manipulation. However, such a powerful program can seem a little intimidating when all you need are some simple statistical results.
If you are new to Excel, why not take time out to fully explore your Excel window? If you are familiar with Microsoft Word, you will be pleasantly surprised by the similar ‘look and feel’.
You will also recognise the menu bar, formatting toolbar and the help box environment. Slightly less familiar features will include column headings, row headings, cell ranges and worksheet selection tabs. These helpful additions provide much of the back bone to Excel’s success.
Each Excel workbook is divided into worksheets. By default there are three sheets, with sheet one initially active. These sheets can be selected by clicking on the name of the sheet located at the bottom of the booklet.
Each sheet consists of many rows horizontally and columns vertically. Columns are named as alphabetical headings, while rows are numerical. Each row or column consists of a range of cells where data is stored and/or manipulated.
Once you are happy with the Excel layout, we can then delve into the features most useful to the novice user:
1) Basic Formatting
Rows and rows of data can be pretty bland if nothing really stands out. The presentation of data can be as important as the results themselves. An inventory report submitted to a new customer or potential client may only be reviewed initially for a matter of seconds. With a little thought, basic formatting can be used to highlight important areas and automate simple tasks.
If you are familiar with other Microsoft Office programs, you will have seen the formatting toolbar usually found at the top of the application’s screen. You can use this to select a cell range and apply whatever formatting you like.
Simple formatting allows the alteration of cell content. This includes applying different styles and sizes to data. You can also apply default Excel styles such as adding pound (£) and percentage (%) to cell areas.
The content of the cells can also have shading and borders applied to allow important elements to stand out. Rows and columns can be resized or cells can be automatically adjusted according to their contents. Cell contents can be justified - often good for making headings and content stand out. It doesn’t end here! There are many more formatting features available for you to experiment with.
2) Sorting & Filtering Information
With so many cells it can be a hard job finding and analysing the many rows of data you require - much like finding a needle in a haystack!! Fear not, Excel has prepared for all this and more!
Sorting: Excel allows you to sort data for one or more columns. A basic sort using the Ascending sort (A-Z) or Descending sort (Z-A) would allow data in a column to be reordered alphabetically and numerically, using the appropriate buttons on the standard toolbar.
More advanced sorting tools would allow you to sort by multiple columns. An example of this could be where an estate agent needs to view available houses that have only been on the market in the last week, are located in Surrey and have a garage. This could be done in a painfully manual way by searching and checking each row. Or simply, just sorting by date, then by location and finally by garage for each column heading.
Filtering: Filtering allows you to quickly and easily extract certain data that you do not wish to see. Where as sorting involves the re-arrangement of columns of data. The filtering process allows you to keep all the data in one sheet, but provides views of only the data that you are interested in - sounds great!
For example, a manager may have an excel sheet with hundreds of rows of invoice information. By applying a simple filter to the ‘invoice paid’ column, the manager will be able to see only the invoices that have been paid.
3) Simple Graphs:
You know the old saying ‘A picture tells a thousand words’ - well this is true for graphs too and Excel can be used to create just about any type of chart! These include bar, line, pie, scatter or you can even create your own custom graph to meet your specific needs. In terms of presenting data and delivering a quick, clear and meaningful message - a simple graph wins hands down!
Be careful when selecting the graph type. Think about which graph will best represent that data you are capturing and the message you wish to portray. Excel provides a ‘Chart Wizard’ that makes the whole process of creating a graph relatively easy with step-by-step guides. The Chart Wizard can be found in the standard toolbar.
4) Formulas & Functions:
They may sound quite scary at first, but they really aren’t once you see a simple example. It is true that formulas are extremely powerful, but you have the choice of the complexity that you wish to use. So why not start simple?
One of the most simple formulas makes use of the add function whereby two cells are added together e.g. ‘=sum(A1+A2)’. You could extend this further to add a whole row ‘=sum(A1:J1)’ or a whole column ‘=sum(A1:A10)’. This is a really handy tool which can be used to summarise totals of rows or columns of data.
Addition, subtraction, multiplication, division and averages are all examples of simple math functions that are built into Excel. There are many more functions available to explore.
We have just gone through the most useful basic features that Excel has to offer, and this is just the tip of the iceberg in terms of what Excel has to offer! I hope this has given a good insight as to how Microsoft Excel can be most efficiently used. However, reading this article alone will not make you a better Excel user. Training and practice is required to push your Excel skills to the next level.
These days it is often quite cost effective to get quality instructor led training from qualified trainers. There are many public scheduled training courses available or trainers can visit you on-site. Why not see what Excel training courses can do for you?
