used with permission from Technology at Work

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 will make you feel more confident with hundreds of rows and columns.

1. Freeze Panes

Have you ever dived into the unexplored depths of Excel spreadsheet in search of a specific cell or value? The common issue in this case is that it becomes very complicated to say to which category the value belongs (or in other words what is the name of the row and column which contain your cell). So this might be the right time to start using an Excel option called “Freeze Panes”. It freezes the defined row or column on the screen, so you can still scroll data in any direction. This is how it works:

  • If you have data names nested in the first row and you want them to be constantly on the screen, use the Excel command “Freeze Top Row”. For this go to View –> then head to the area called “Window” –> Press “Freeze Panes” and choose “Freeze Top Row”. From now you can scroll down to eternity but your top row with data labels would be halted on top.
  • If you have data labels indicated in your first column and you want to scroll to the right to see more data, repeat the previous steps but choose “Freeze Top Column” in the end. Now you will be able to scroll your data to the right, but row names in the first column will always be available.
  • And what if you want to simultaneously freeze the fifth row and the second column for example? This is also possible with Excel! First, find the intersection of the defined row and column. In this example it is cell B5 (B is always the second column in Excel). From this cell, take one step down and then one step to right. You will get to cell C6 which is what we need. Go to “Freeze Panes” and choose “Freeze Panes” again. Now you have a rectangle frozen, which consists of 2 columns and 5 rows. You can scroll down or to the right, but the rectangle will stay in its place.

The “Freeze Panes” option is also very useful for inputting data since you will always see the correct cell where you need to nest a value.

Once you have finished working with your data, how can you turn off the “Freeze Panes” option? Simply click on “Freeze Panes” and then choose “Unfreeze Panes”. That’s it!

2. Sorting of data in tables by row and by column

Let’s imagine you have created a table in Excel with 6 rows and 8 columns, packed it with data, given names to rows and columns, and now you want to sort your data from smallest to largest, or vice versa. Moreover, you want to be able to sort both by row and by column! In this case use an Excel “Sort” option. It will allow you to sort your values ascending or descending; horizontally and vertically.

  • First, choose your whole table with data by dragging a mouse from the top left cell top bottom right
  • Go to Data –> Find Sort&Filter area –> And press “Sort”.
  • A new window for sorting will open. Now imagine you want to sort your data ascending in the first column. For this, first check “My data has headers”. This will separate data labels from the data itself. Then press Options and choose “Sort Top to Bottom”, because you are going to sort data in your column (i.e. vertically). In the field Order choose “A to Z” which stands for ascending (Z to A is descending). Once this has been done, press “Sort by” and choose the name of the column where you want to sort. Press OK. Now you’ll see that the values in your column are sorted from lowest to highest. Notice that the rows have changed their order according to the values in your sorted column.
  • Next, you want to sort your data horizontally in a given row, say in the 4th. To do this, once again choose your table and once again go to Data –> Find Sort&Filter area –> and press “Sort”.
  • Here click on “Options” and choose “Sort left to right”. After this, choose the order of sorting (say it’s Z to A) and choose a row 4 in a field “Sort by”. Then press OK. Now you’ll see that all the values stored in a table in row 4 (note that 4 is a number of a row as Excel understands it – it’s not your row label) are descending from left to right.

With this simple trick, you can quickly sort data both horizontally and vertically.

3. Subtotals

The beautiful side of Excel is that it carries out a lot of calculations without your even noticing it. The following tip will be useful if you have at least two columns of data and a few repeated categories for your rows. This tip would can be easily understood in the following example. Imagine you have a table in Excel with raw data on car sales. You have three columns here: City, Car Brand and Price of a sold car. You want to see the sales results first based on the city level (which we’ll assume is the first level for you) and you also want to see sales for each car brand within each city (which we assume is a second level of your analysis). Weird? With Excel, you can handle it easily.

  • First of all, use your skills acquired from previous tip and sort data in the column where your potential groups of the first level are stored. We said that it is city for us:
  • Now that you have sorted data by city, highlight the whole table (including headers) and go to Data –> Find area called “Outline” –> And press “Subtotals”. A new window will open:
  • In the field called “At each change in” choose the grouping category City. In the field “Use function” choose “Sum” since you want to see the total sales. In the field “Add subtotals to” check the “Sales” field because you want to see the sums of sales. Then press OK.
  • Now you’ll see that your table has new rows, each representing a total of sales within a city. At the bottom of the table, you also have a Grand Total row that shows a sum of all sales.
  • Nice! How about showing sales in a city by car brand? For this, choose your whole table again and go to Data –> Find area called “Outline” –> And press “Subtotals”. A new window will open.
  • You don’t need to change anything here except for a field called “At each change in”. Choose this time “Car”. Press OK. Note: the field called “Replace current subtotals” should be unchecked!
  • Now your table has changed even more. You have sales subtotals not only for each city, but also for each car brand within your cities.

Try to change values and see how Excel proactively reacts to them in the Subtotals cells. Now your sales managers can update data virtually every minute: you’ll always stay on top with this useful tip!

If you want to withdraw the subtotals from your table, do the following: go to Data –> Find the area called “Outline” –> And press “Subtotals”. In the new window, press “Remove all”. That’s it!

*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.)

We hope you found these tips useful!

Posted in Articles, News and tagged , ,