Check the corners of your data set

Ctrl + A and Ctrl + * are common ways to select the data.

The key difference in using Ctrl+A and Ctrl+ * is that in Ctrl+A you are thrown to the last corner of the dataset where as in Ctrl+ * you stay where you are.

I have found that in-spite of this arrogance of Ctrl+A, it is more widely used, probably because of the proximity of keys.

Suppose on huge database you use Ctrl+A and want to come back to original cell, Ctrl+[.] (Ctrl plus period) shortcut comes to your rescue. pressing it repeatedly takes you to the corners of the selected data. It helps you make sure that all the data is selected. Very nifty shortcut, I use it often.


Number in text format to number format

Recently I was going through a huge projects related data file in excel. One of the column has project numbers but was in text format.  There was green triangle on the left side of the cells. The data was downloaded from database management of a company.

Due to this I was unable to apply formulas on these numbers.

My first instinct was to select the column, click on the error message and select the option of convert to numbers. Which I did as well and it took long time i mean really long time to happen.


One of the faster alternative have found is to select the column and go to Data tab -> Data tools -> Text to columns with default setting press next twice and then finish. Your data is converted to numbers, ready to be used in formulas. The default setting do not push or overlap your data in other columns.


Draw a shape number of times

In excel if you want to you draw a shape like rectangle more than once, you will have to either repeat the process of drawing the shape or copy paste the previous shape and resize it as per your need

One trick I learnt recently is to right click on the shape you want to draw and then select the Lock Drawing Mode option. With this you can repeatedly draw the shape number of time of different sizes. To return to excel mode press escape.


Update : After selecting the shape if you just click where you want to put the shape, the shape is drawn automatically mostly of 2.54cm x 2.54cm size.