Excel tips for handling rows and rows of data

Managing huge amounts of data in Excel can be painful if you don’t know how to handle it properly. These tips* will boost your effectiveness and make you feel more confident with hundreds of rows and columns.

1. PivotTables
PivotTables are probably the most overlooked yet most powerful feature in Excel. They are great if you need to quickly analyse, sort and summarise large amounts of data in a worksheet or database file. And all this with simple drag and drop.

Let’s say you have a list of products and categories in an unsorted table where you want to know the total price of all the products in one category. Sure you could do the usual stuff and sort the column by category and then sum up the price of that specific category. But if you face thousands of unsorted lines, you’ll end up getting more grey hair faster than a Tibetan goat before winter.

To avoid that from happening, Excel has this powerful and nifty feature: PivotTables.

1. As an example: create a simple list with headers, for example, Product, Category, Price.
2. Select the range for the data you want to use. The cells must contain a header.
3. Insert a new PivotTable: go to Insert ribbon and click on new PivotTable.
4. Select the target cell where you want to place the PivotTable.
5. After you’ve created the table, the empty field list pops up.
6. To create the report, check the fields you want to use in the report and drag and drop them in the areas you want them to have. For example, drop Category and Product in Row Labels and Price in Values. Don’t worry if you’ve deleted or modified the wrong field; a PivotTable is just a representation of your data and won’t change your data in any way.
7. You will now see the aggregated data in your Excel sheet.

Congratulations, you have your first PivotTable! If you want to update it, just right click on it and hit “refresh”.

That’s it, basically. The more data you feed Excel, the prettier and more interesting the PivotTable gets. Although one quick side note: a PivotTable only makes sense when there are at least two columns to choose from.

2. Web data queries
If you have a lot of data that you need to extract from a website, you could always opt for the normal copy paste adventure. But a smarter way would be to let Excel do all the work.

1. Specify the cell where you want your data to appear.
2. Click the “From Web” command button in the Data tab.
3. In the URL, type in the web address where you want to copy the data from.
4. Click the yellow box next to the table you want to import, and click on “Import”.
5. Excel will now import the data into the cell you specified.

A nice feature is also that you can tell Excel when and how often it should run the web query. This comes in handy if you need to import constantly changing data like stock quotes.

3. Defining names for cells
Ever wondered whether there’s an easy way to reference cells without having to remember the sometimes complex cell numbers? Was it C338 or C339? It gets worse if you try to remember the reference to another datasheet like “‘Products’!$C338”. So we suggest you try using “Defined Names”.

There are multiple ways to define a name but the most basic and simple are:

• Use the name box on the formula bar.
• Use the New Name dialogue box.

A new window pops up where you can specify the name and the cell reference. For example, you could now use “‘Products’!Price” instead of “‘Products’!$C$338”. You can reference this cell value anywhere in your Excel sheet or indeed on any sheet within the open Excel file.

To delete cell names, go to the “Formulas” menu and click on “Name Manager”, and then click delete. In the Name Manager you’ll find an overview of all custom names you specified for your Excel sheet.

We hope you found these tips useful!

*All these tips are based on Microsoft Office Excel 2010 (Windows) but other Excel versions offer the same or similar functionality. (Menus and performance may vary.)

Microsoft and Excel are trademarks of the Microsoft group of companies.