Sat 11 Feb 2017

Tips & Tricks 155 - Gridlines not Visible, Help!!

By |Saturday, February 11th, 2017|Categories: Tips and Tricks|Tags: , , , , , , |0 Comments

You may try following one by one and verify your results

1. Update your Printer Driver to latest from internet (Or change your default printer driver to PDF/XPS/One Note)

2. Select the triangle between row 1 and column A to select entire sheet (or CTRL+A, 3 times).

Home tab > Go to Paint Bucket and select No Fill (or any other color other than white).

1

3. View tab > Check Gridlines if not already checked.

2

4. File > Options > Advanced

Make sure Gridline color is set Automatic (or any other color other than white)

3

5. Select the triangle between row 1 and column A to select entire sheet (or CTRL+A, 3 times) > Right Click > Format Cells > Border Color should be Automatic or any other color other than white.

4

Sat 14 May 2016

Tips & Tricks 136 - Quickly Copy a Formula or a Value in a Large Number of Cells

By |Saturday, May 14th, 2016|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

Say, you have written a formula (or put a value) in cell B1 and you want to fill in this formula till B10000. You can drag the formula but higher the number of cells, more tedious it becomes. Fortunately, we have a way to do this very fast.

1. Put formula in B1.
2. CTRL+C of B1
3. CTRL+G and put the range say B2:B10000 in Reference: box (or even B1:B10000 as copying B1 in B1 will not cause any problem)
4. Press Enter

1

Sat 05 Sep 2015

Tips & Tricks 107 - Autofill on a Filtered List

By |Saturday, September 05th, 2015|Categories: Tips and Tricks|Tags: , , , , , , , |0 Comments

Everybody is quite aware about Autofill. There are various ways to Autofill. Refer to following article for Autofill -

Article 7 – Generate a Sequence of Numbers

Now, apply a filter on your range and all the techniques fail. If you drag, all cells are filled with 1 and no other techniques also work. The reason is that Autofill works only on a contiguous range. Once, you apply filter, the range becomes non-contiguous.

Now, here comes the trick -

1. Apply the filter.
2. Let's assume that first row is 3 and you wanted to fill it in column B.
3. Put following formula in B3 and drag down
=COUNTIF($B$1:B2,"<>"&"")

If you don't want to drag down -
3.1 Put the above formula in B3.
3.2 Select all the cells including B3.
3.2 Press F2
3.3 CTRL+Enter

Above steps will fill the filtered list with 1, 2, 3.....

Sat 08 Nov 2014

Article 7 - Generate a Sequence of Numbers

By |Saturday, November 08th, 2014|Categories: Articles|Tags: , , , , , |1 Comment

Many times, we have need when we want to generate a sequence of numbers for various purpose. When I started Excel, I simply used to put 1 in A1 and =A1+1 in A2 and dragged down to required number of rows. I, sometimes, still do it. Just old habits die hard. Over a period of time, I did learn many other ways which I would like to share here. Here, I will be talking about filling in a column i.e. vertically. Utilizing same line of logic, horizontal sequential numbers can be generated. Also, if your starting number is not 1 but some other number, you will have to utilize same line of logic to do it.

Once again, I would like to tackle this topic in 4 ways.

1. Formula Way
2. Manual Way
3. Hybrid Way (Combining both Formula and Manual ways)
4. VBA

(more…)

Sat 27 Sep 2014

Tips & Tricks 55 - Quickly Fill in Cells with Dates from M to N

By |Saturday, September 27th, 2014|Categories: Tips and Tricks|Tags: , , , , |0 Comments

1. Put the start date in the cell where you want your first date to be. Let's put 6/1/14 as an example.

2. Select that Cell and in Home Tab, go to Fill and Select Series

1

3. Select Series in Columns if you want Column to be populated (Most likely case). Put End Date in in stop value as we want to generate dates till that end date and OK to generate list of numbers. Step value consists of increments by which dates have to be incremented. (If you want to space out dates with one week interval, put 7 in step value)

1

Sat 28 Jun 2014

Tips & Tricks 26 - Fill in the data from non blank cells to blank cells down

By |Saturday, June 28th, 2014|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

Suppose you have data like below and you want to fill in blank rows with data with non blank row up. Here, in range A3:A5, you want value A to appear, in range B7:B9, you want value B to appear and so on.

You need this kind of situation, mostly, when you are making pivot which demands that all rows should have the data.

1

You need to follow following steps -

1. ALT+EGSK and Enter. This will select all your blank cells.

2. Type "=" (without double quotes) and press up Arrow.

3. CTRL+Enter

Now you will have cells filled as you wanted. You will notice that those blank cells are carrying the formula = A2, =A3 etc.

4. To convert them to static values, select you column A, Copy and Paste Special Values.