How To Identify The Most Critical Data In A Large Excel Spreadsheet

Excel can store hundreds, thousands, even millions of records, the perfect repository for your company's key data. But if you need to identify a single record or a category of data, wading through that much information could take forever. Fortunately, Excel gives you a range of tools for picking out whatever kind of data you need from your spreadsheets.

Microsoft Excel 2010/2013/2016 can hold a lot of data. And when I say a lot, I really do mean a lot: more than a million rows, more than 16,000 columns. It's safe to say that however many records you need to put into Excel, and however many different pieces of information about each record that you have, there's room to put it in there.

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 organize it, and also to analyze 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.

Revolutionize the way you learn Excel by attending our Super Excel courses and become Excel Superhero in your office.

You can can reach us via the following;

Tel: 08062468296 | 08108054165

Email: [email protected]