Posts Tagged ‘Office’

Excel Tip: Table’s Row-Column Shortcuts July 30th, 2013

Vinod Kumar

I work with tons of tables inside Excel and sometimes there are requirements to do some simple but repetitive activities on a table that I have always wanted to automate. These tasks include inserting a row, inserting a column, deleting a row, deleting a column, hiding a column or row etc. So this blog post will be all about these simple tasks. Our dataset looks like one below.

Inserting a Row and Column

This is one of the easiest shortcut to remember.

To insert a row at current location: ALT + I + R (like (I)nsert + (R)ow)

To insert a column at current location: ALT + I + C (like (I)nsert + (C)olumn)

Our excel will show an output like below.

Deleting a Row or Column

If adding was this easy. Now we might want to delete rows ad columns too right? There are two mode for me. Here is the lengthy route. If you press ALT+ E + D, you will get the following dialog.

Now, using the dialog box can be easy. We can still use some of the hotkeys on the dialog. Check the row and column. So the lengthy route to:

Delete row: Alt + E + D + R + Enter

Delete column: Alt + E + D + C + Enter

I have already calling this as lengthy approach. So there must be something simpler right. You guessed it correct. Use the below shortcuts.

Delete row: Shift + Spacebar + Ctrl + -

Delete column: Ctrl + Spacebar + -

That seems to be slightly lesser keystrokes right? Found it interesting? Let us move to the next shortcut.

Hiding Row or Column

What we want to achieve is to hide column D and Row 12.

So here are the shortcuts for the same.

Hide a Row: CRTL + 0

Hide a Column: CTRL + 9

The last part of this tips would be, to unhide the row or column – select the rows and right click to Unhide. Do you know of a shortcut to unhide columns or rows? Drop a line and let us learn from each other. Hope you enjoyed this tip today.

Continue reading...


Excel Tip: Compare two lists (Method 3) July 19th, 2013

Vinod Kumar

This is in continuation to our series where we want to compare two lists and find the difference. In previous posts we discussed the use of MATCH() and then VLOOKUP() functions. In this post, I will use the other popular method of PIVOT TABLES. Just to reiterate, we have two lists “List A” and “List B” and we want to know how many values in List B is not available in “List A”.

Method 1: Using MATCH() function.

Method 2: Using VLOOKUP() function.

Method 3: Using PIVOT TABLE.

For PIVOT Table, I am going to prepare the above two lists into one mega list. I have added a column to denote Year and merged both these lists like below.

Now that our data is prepared, our next step is to initialize the PivotTable. Goto INSERT Tabe –> PivotTable.

The Create PivotTable dialog comes up. Under Select a table or Range, select our complete table. Under PivotTable Placement-> Select a cell in our same worksheet. I have shown a typical example below.

To aggregate Drag the corresponding columns to get the result I have shown.

Columns –> Year

Rows –> Company

Values –> Revenues

Voila, our task is complete. Now in one shot we have got, the red empty boxes are the new values in 2012 that are not available in 2011. While the blue box below represents, values in 2011 that are missing in 2012 year.

Thanks for reading this far. I hope this was useful and you learnt something new. Do let me know if you want me to write some other topic.

Continue reading...


Excel Tip: Compare two lists (Method 2) July 18th, 2013

Vinod Kumar

This is in continuation to my previous post. Just to recap the problem statement – we want to find from our “List B” which is not in “List A”. The initial dataset can be found below first.

Method 1: Using MATCH() function.

Method 2: Using VLOOKUP() function.

Method 3: Using PivotTable.

As mentioned, in this blog post we will use VLOOKUP() function instead of MATCH() function. Now in F3 cell, let us start typing “=VLOOKUP(“ and press CTRL+A. This will bring the function arguments dialog.

Lookup_value: D3

Table_array: $A$3:$A$10 (this is the range to make comparison, remember F4 key after selecting the range, it will automatically lock the range using the $ synbols)

Col_index_num: 1 (the column ordinal based on range, we have just one column and hence it is 1)

Range_Lookup: FALSE (for exact match)

Press Enter. Now if a match is found you will see the value of column repeated. Next is to find the cross arrow from right corner cell and double click.

Now whereever we get an “#N/A” Error, it means we couldn’t find a match from other list.

We saw in our previous post how to get rid of the Error. We will use the “=IFERROR” function to add an value “Missing” and we will double click our right cross arrow.

Voila, now that is the second method of finding missing values in comparison to another range.

Continue reading...


Excel Tip: Compare two lists (Method 1) July 17th, 2013

Vinod Kumar

One of my friend recently had this requirement where he wanted to compare two lists and find out which were the values from second list that are not in first list. This was an interesting requirement and we kept playing with Excel to come up with close to 3 different methods. After that task was complete, I thought it is worth every bit to bring all these methods over my blog for future reference. So have fun !!!

Method 1: Using MATCH() function.

Method 2: Using VLOOKUP() function

Method 3: Using PIVOTTABLE.

Let us look at the dataset in hand. It is a list of companies with some random revenue numbers listed for two years. Interestingly there are differences in both the dataset. Now the task is simple. We want to check companies in “List B” which are not in “List A”. So let us get started.

As mentioned earlier, the Method 1 is to use MATCH() function. Here is a neat trick, when you type “=MATCH(“ –> Press “CTRL+A”.

Voila, this is the magic dialog we get. This will guide us for various parameters for that given function. This trick can be done with any function which has parameters values.

Lookup Value => in our data set it is the first cell D3 from List B.

Lookup array => in our data set it will be A3:A10. Please note that we will use the same range and as we move through the list of companies. Hence press F4 to get $A$3:$A$10. This is an very important step.

Match_Type => In our example we will have 0 for exact match.

Press “OK” now.

Now we will use the cross arrow trick we used before. Move to right corner of this cell till you get the cross arrow as shown below. Now double click. This will fill the rest of the rows till the end is reached.

I didn’t mention the significance of the numbers earlier. Match() function is returning the relative position of the values based on comparision to first list. So here you can see “Johnson n Johnson” is in the 1st ordinal in our array. So is the case for others too.

The irritating factor here is the ERROR “#N/A”. The simple method is to prefix our function with the IFERROR() function. In our case, if we get n error we are going to print the text “Missing”.

Now use the cross arrow trick again. And now the final output looks like below.

So I plan to write the next two methods tomorrow. Want to guess them? If they are correct, I will approve the comments after making the posts :) … Do let me know if you want me to write on anything.

Continue reading...


Excel Tip: Remove All blanks from a Excel table July 16th, 2013

Vinod Kumar

This is one of the simplest tips among those I have shared over this blog till date. Let me explain you the scenario. We have the following table / range / data set inside Excel and the problem with these dataset is that, we have spaces in between and this is causing us problems for filter, moving between cells and many more. So our objective is to remove the rows having space. I have seen people use “CTRL+Down Arrow” to goto a row with space, delete that row – press CRTL+Down Arrow and repeat this process till you reach the last row.

This for sure is manual and intensive repetitive activity. In this blog post we will do the same operation efficiently in few clicks. Quite an handy option to know.

When you are at in this screen, Select the range on “B column” just like shown below. And now hit the “CTRL+G”. This bring the dialog as below. Select “Special…

In the following dialog select “Blanks” and press “OK”.

Voila, now you can see that from our dataset the Blank rows are highlighted as shown below.

Just right Click and select “Delete” –> “Table Rows”. Now that is as simple as it gets.

We have successfully deleted all the rows with blanks and our final dataset looks like:

Excel is an awesome tool and there are many such functions that make it interesting. Hope you will use this shortcut in your day-today work and hope you found this trick useful. Do let me know if you want me to write on anything specific and I would love to explore and show you few tricks.

Continue reading...