99.1% Of all customers recommend us, we're so confident about our results we publish all reviews and statsView Live Stats View Reviews
How To Identify The Most Critical Data In A Large Excel Spreadsheet
Sun 20th March 2011
But what happens when you need to find a single piece of data, or a number of entries that fit a certain description, out of a spreadsheet that runs to hundreds of thousands of entries? If you've ever had to rummage through a packed stationery cupboard to find just the pack of staples you need, you'll know how time consuming and frustrating it can be - and Excel can store far more records than your stationery cupboard can store staples, pens and paperclips.
If you have no choice but to search through a huge spreadsheet to pluck out one piece of information, looking for a needle in a haystack could rapidly look like a far more pleasant alternative. Yet you still need to find that information; if picking through the data until you see the right bit isn't appropriate (and it isn't, if you still want some sanity left at the end of it). Wouldn't it be nice if Excel had some tools to help you out?
Well, you can relax. It does.
At the top of the Excel 2010 screen are six tabs. If home is selected - and by default, it is when you turn the program on - you'll find there's a little corner in the top right of the screen titled 'editing'. Within the editing box, there's a Sort & Filter icon; click this, and a drop-down menu gives you tools for sorting and, yes, filtering. Let's look at sorting first.
There are three sorting options: Sort A to Z, Sort Z to A, and Custom Sort.
The first two do exactly what the name suggests - if you have only one column of alphabetical data (perhaps you've a list of employees, their pay, hours, deductions and so on), then these options put simply put them in order.
However, if you've more than one column with letters rather than numbers, or if you want to sort your entries in a different way, then custom sort lets you do just that. Simply pick the column that you want the data to be sorted by - and Excel also lets you sort by multiple columns, with a 'sort by x, then by y' model.
So if you want to sort that list of employees by the number of hours worked, and to group those that worked the same hours by their location, just sort by the hours column, click 'add a level' and sort by the location column. However, and this is very important, you must always select everything that you want to sort: if you want to sort records the alphabetical order of employees' names, then you have to select the whole of the record, not just the name (and to select everything, there's a handy button in the top left corner of the spreadsheet). If you just select the column you want to sort by, then just that column will be sorted, leaving the rest of your records in their original - and now wrong - place.
Next in the Sort & Filter menu is, logically enough, filtering.
If you select the whole of your spreadsheet and click filter, you'll find drop-down menus at the top of each column. These menus list every type of entry that's in the column; select the ones you want to see, and the rest will vanish (but don't worry, they're still there - go back to the filter menu and select all, everything will reappear). So if part-time employees aren't needed in the sheet right now, they can be hidden, making the remaining records much easier to work with.
But what if you're not searching for category of record, but for data that is performing better or worse than the average? Excel makes it easy to find that, too. Perhaps the employees on that list are salespeople, and the spreadsheet also contains a record of their selling success. In the styles box at the top of your Excel window (we're still using the home tab as before) you'll find a box with a drop down menu named 'Conditional Formatting'.
This tool allows you to select a number that the entries in the spreadsheet shouldn't go above or below, and highlight all those cells that cross line. Alternatively, conditional formatting also lets you highlight every cell with colours or symbols to represent a high or low figure. No matter how large your spreadsheet, you can be confident that problems and success stories will jump out of the page as soon as they arise.
Being able to locate and work with a particular record or category of data, whether you have a small spreadsheet or one with hundreds of thousands of rows, is key to ensuring that you can always know how your business is progressing and where its future lies. Excel gives you the means to store however much data your business has, to find and organise it, and also to analyse the information that you have - all vital tools for any organisation, and a short training course in using these tools for you or your staff can give your company a significant advantage. With any data you need always at your fingertips, you'll find that your business is never out of sorts and always ahead of the field.
Original article appears here:
London's widest choice in
dates, venues, and prices
On-site / Closed company:
Castle Moat Property Management Limited
Course had good content & was easy to understand. Mark was very patient & explained everything very well.
I really enjoyed Max's way of giving analogies, it helped explain very complicated and confusing topics.
I feel a lot more confident about using excel now and feel this will greatly improve my work.
Thank you very much Max :)