Excel Tips for Handling Lots of Data
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!
|