Making the Most of Excel

Practical applications to make you more effective

Microsoft Excel has a host of practical uses to make life easier and they are all available for you to use now. Some of these uses are well known and used by people daily others though common sense are not that well known. As you read each of these tips you will be amazed how they will make you much more effective in your use of Excel.

“One font to rule them all, one font to find them, one font to bring them all and in the darkness bind them" with apologies to JRR Tolkien

There is nothing worse than opening up a spreadsheet and seeing a vast assortment of different fonts thrown together. Unless you are developing a file to display all the different fonts Excel has to offer try to keep it to just one font. This is good spreadsheet management and your boss will thank you for not having to wade through a jungle of fonts to try to decipher what you are trying to say.

Filters are the way to go

Imagine you have a large spreadsheet with loads of information on it. Using AutoFilter to filter data is a quick way to find and work with a subset of data in a range of cells and it lets you choose what you want to see and what you want to exclude. Click the cell in the top left corner, highlighting the whole spreadsheet. Click Data > Filter > AutoFilter and you will see there are now little boxes in each column that help narrow your search. You can also use a Custom filter and just type in the information you want to locate

Creating Call Logs

One of the most useful features about Excel is the ability to organise data. A lot of us make and receive a lot of telephone calls, so being able to organise names or customers is useful. It is easy to create a call log using Excel and easier than trying to use Word. Create a worksheet with three simple headings: name, time of call and reason for call.

Colour Coding for Emphasis

Why not jazz up your spreadsheets. There are many times when a spreadsheet can be improved if you use colours to separate sections. Coloured backgrounds work well with borders to help distinguish headings from data such as subtotals, totals, months, etc. However be careful not to use colours combinations that aren't professional such as a green background with a purple font, backgrounds should be a pale shade. Don’t be too daring as you will have the opposite effect and leave people with a headache.

Calculating Discounts

Whether you are determining your profits or calculating a discount, you can set up Excel to help you determine the percentage discounts. Enter the original price in cell A1, percent discount in cell A2 and discounted price in cell A3. In cells B1 through B3, you will enter the original price of the item and the percentage discounted. In cell B3, enter the formula (B2-A2)/A2 then click the % icon to format the number into a percentage

Use conditional formatting

Excel users can format their spreadsheets using different colour shades, bolds and italics, to differentiate between columns and bring the most important data to the fore. This function could be useful when presenting accounting information, such as the pre-tax profit and the balance being carried forward by the company. Users can select an appropriate colouring scheme via the Quick Analysis button, and then by utilising the Formatting tab.

Help identify trends

When presenting data in the form of charts or graphs, it can be helpful to include average lines, which explicitly detail the key trends emerging from the information. This may help demonstrate the key points to other users in a straightforward manner, for example an executive from a different department during a board meeting. Excel allows trend lines to be extended beyond the graph, to offer predictions of future activity and such forecasts can help businesses develop their future strategy.

Bringing data together

Excel can be used to bring information from various files and documents together, so that it exists in a single location. As well as raw data and information from other spreadsheets, it is possible to import text and images. Other objects can be added using the Insert tab, or additional spreadsheets can be added to the file.

Online access (BYOD)

Excel is available as part of Office 365 Business and Office 365 Business Premium. This means senior managers and employees have access to the program from a range of devices from almost any location as long as they have a web-enabled PC, laptop, Smartphone or tablet it should be possible to access Excel, making remote and mobile working viable.

Importing data from a Website

If you come across a site with loads of data that is useful for a project you are working on, you can convert this into a spreadsheet. Click File > Open then change the file format to All Web Pages. This covers all .htm, .html, .mht and .mhtml files. Load the web address and click Open. Once you've done this, you may need to alter the formatting, but this is much easier than creating a new workbook.

Sending a Read-Only (PDF) File

Occasionally you may need to create a report that can't be manipulated or edited. This is where PDFs come in handy. But if you don't have Adobe Acrobat, you can use Excel to create your own PDF file. When you click File > Send you can Email as PDF Attachment. You can also save the file in PDF format. This enables you to create PDF-like files that aren't editable. If your current version of Excel doesn't have this option, you can download the PDF add-in from the Microsoft website

Get many more practical ideas on one of our Microsoft Excel Training Course.

Excel Links

Microsft Excel Lifehacks