Few weeks back, I came across a post about some useful tips in MS Excel – Excel can be exciting . So, I thought I’ll collate some of the helpful tips and tricks that I’ve come across while handling data in MIS. First a couple of disclaimers, there maybe simpler ways of doing things than the ones I’ve come across, but I don’t know of them. Also, I’ve done all this on MS Excel 2003, so I cannot comment on how the same can be done in later versions.
1) Copying unique data – This is a tip I picked up from my batchmate in my MBA days. Many a time, we come across large log files that have repetitive data, for example take the following list of sales in a super market. When we have a log file like this running into thousands of lines, it is not easy to determine all the categories that have been sold. But, using a unique filter, you can identify the categories.
All you need to do is go to “Data –> Filter –> Advanced Filter”. The following dialog box will appear.
Set the “Action” to “Copy to another location” and check the “Unique records only” box. The “List Range” will be the column containing categories and the “Copy to” will be the cell to which you wan to copy the unique list of categories. Click “OK” and viola.
2) Vlookup & Hlookup – Couple of extremely useful functions that my mountain trekking roommate who works in an IT company doesn’t know, we use the two when we need to get data from a table that corresponds to the information in hand. For example,
Here, we need to find the corresponding data for soap fro the table. Vlookup takes the location of the category and the table as input. Be careful when signifying the location of the table. Vlookups looks for data in the first column of the table. As seen in the figure, though the table starts from column B, the table is selected from column C for vlookup’s purpose. The other two inputs, “2” tells the function to retrieve the data from the second column of the table and “false” signifies that the looked up data should be an exact match of the given data.
Hlookup is the same as vlookup in syntax, except it takes data in rows or horizontally, unlike vlookup which take data from columns or vertically.
3) Composite Key – Now many a time we need to get data based on multiple conditions. For example in the following case we need to select the data that corresponds to both ”Chennai” and “Soap”
Here, we borrow a concept from database management where we combine to fields to form a unique composite key for each row.
I’ve separated the two columns by a comma so that in case they need to be separated at a later stage it can be easily done through “Data –> Text To Columns”
4) Pivot Table – I came across pivot table during my summers and it has been an essential component of any data analysis I’ve given since then. Pivot table is used to aggregate data and becomes useful when you large amounts repetitive data. You can create a pivot table through “Data –> PivotTable and PivotChart Report”. For example, I have a list of transports from a source to location. I need to know the aggregate of the data. So, I have created a pivot for it.
You can select the data you want in rows, columns and pages. Normally, pivot is used for a larger set of data, rather than the one I have used.
5) Calculated Field – One of the issues with a pivot table is that if you need to work on it by formula, it does not directly allow you to do it.
In the first “Cost / Tonne” row, I have clicked on the cells to calculate. However, instead of picking up the cell location, it picks up a formula which cannot be copy pasted across other cells, thereby giving the data across. This can be circumvented by manually typing the cell location, as I have done in the second “Cost / Tonne” row.
However, this is usage becomes redundant when the structure of the pivot table is changed. So, we need a more permanent solution for the problem. This is a “Calculated Field”, where we manually add another field to the pivot table. It is different from adding a similar column in the base data, since the calculated field is derived from the aggregate figure and not each row.
6) Getting data from a matrix – This is one of my favorite usages. Most people tend to show use data in the form of a matrix, when there are limited number of rows, like a source-destination matrix. However, this data is not amenable for the lookup functions because they need data to be horizontal or vertical. So, use a combination of the two. (Please open the following pic in a new tab, since it has not been shown in full)
Here, an index column is introduced to the end of data (or the second column, if we can tamper with the other data). The vlookup function will return the index to the hlookup function, which then takes the value from the corresponding row. I haven’t found a simpler usage to get data out of a matrix. Do tell me if you have one.
Afterthought : There are shortcuts for setting Auto Filter and creating a Pivot Table in the “Standrad” Toolbar. It is generally hidden and have to manually added. The “Auto Filter” key filters one the data on the basis of the current active cell.