Sat 30 Aug 2014

Tips & Tricks 47 - Monitor the Value of Cells

By |Saturday, August 30th, 2014|Categories: Tips and Tricks|Tags: , , , |0 Comments

If you are working in a big sheet and want to monitor values in a cell which is far or in other worksheets, you can make use of Watch Window.

1. Go to Formulas Tab and click on Watch Window in Formula Auditing section.

1

2. Now Watch Window will appear.

1

3. Click on Add Watch to add cells which you want to monitor.

4. Now whenever a value changes in cells which you have added in Watch Window, the changed value will appear.

Sat 30 Aug 2014

Tips & Tricks 46 - Microsoft QUERY Error - the data source contains no visible tables

By |Saturday, August 30th, 2014|Categories: Tips and Tricks|Tags: , , |0 Comments

Sometimes, people prefer to use Microsoft Query to extract data from Excel sheets and you encounter this error -

1

The Fix - Query will recognize only those tables whose range has been named. It will not recognize any range which has been made into Tables. Hence, only named ranges are considered as tables for Query purpose.

1. Hence, you just need to select the range and type the range name in Name Box. Now this range name will appear in Query when you select the workbook.

1

OR

You can select the cells who you want to be named and right click. Second item from bottom is "Name a Range"

1

2. If you have already made tables, you can convert the table to ranges. To do this, click a cell in the table and Design menu will get enabled. Press Convert to Range. After this, you will have to execute step 1 to make it Named Range.

1

 

Sat 23 Aug 2014

Tips & Tricks 45 - Lock Cells Containing Formulas

By |Saturday, August 23rd, 2014|Categories: Tips and Tricks|Tags: , , , |0 Comments

1. CTRL+A to select entire Sheet or select top left corner between row 1 and column A.
2. Right Click > Format Cells > Protection = Click Locked Check box to remove tick mark from Locked to make all cells unlocked.

1

3. CTRL + G and click on Special.

1

4. Click on Formulas and OK

1

5. It will highlight all the cells which contain formulas. Right Click one of the highlighted cell > Format Cells > Protection = Click Check box to tick mark Locked to make all selected cells locked.

6. Now protect the sheet from Review tab.

Sat 23 Aug 2014

Tips & Tricks 44 - Locate Hidden Rows and Columns

By |Saturday, August 23rd, 2014|Categories: Tips and Tricks|Tags: , , , |0 Comments

To locate hidden rows and columns, you can have following approaches -

1. Locate them manually. Hence, if after row 4, row 6 comes,row 5 is hidden.

2. ALT+EGSY and Enter. It will select all visible cells and it will mark hidden rows and columns with highlight. But on a white background, it may be difficult to judge this highlight. To overcome this, CTRL+A to select complete sheet and fill in a dark colour on the entire sheet and do ALT+EGSY and Enter. Now, you can make out hidden rows and columns. Don't forget to return back to white background after locating hidden rows and columns.

In below Column D and row 5 are hidden. See, it is difficult to make out highlighted column D and row 5.

1

On a dark background, see column D and row 5 can be clearly made out by dark lines.

1

Sat 23 Aug 2014

Tips & Tricks 43 - Last Day of the Month for a Given Date

By |Saturday, August 23rd, 2014|Categories: Tips and Tricks|Tags: , , , , |0 Comments

Suppose, you are given a date say 10/22/14 (MM/DD/YY) and we want to have the last date of the month for the given date. Hence, you needs an answer of 10/31/14. The formulas to be used in this case -

=EOMONTH(A1,0)

=DATE(YEAR(A1),MONTH(A1)+1,0)

=DATE(YEAR(A1),MONTH(A1)+1,1)-1

Sat 16 Aug 2014

Tips & Tricks 42 - Inputting Numbers Larger than 15 Digits

By |Saturday, August 16th, 2014|Categories: Tips and Tricks|Tags: , , , , |0 Comments

Sometimes, you may have a situation where you need to input numbers having more than 15 digits. For example, Bank Account Nos.

What is 15 Digits Limit - Excel has number precision of 15 digits, it means that if you input numbers > 15 digits, 16 digits onwards, it will be 0s.

Hence 123456789123456789 will be 123456789123456000.

Hence, you will need to enter that in Text format only. There are 2 ways.

1. Precede the number by apostrophe sign ('). Hence '123456789123456789 will be shown as 123456789123456789 in the cell. In Formula Bar, you can notice ' sign not in cell.

2. Take right click>Format Cells>Text in Number tab. It will format your cells as Text. Now you can input larger than 15 digits number in this.

Sat 16 Aug 2014

Tips & Tricks 41 - I have data for many years but I want the sum for only last 12 months

By |Saturday, August 16th, 2014|Categories: Tips and Tricks|Tags: , , , , |0 Comments

Let's assume that your data is aligned like this and you want to have sum only for last 12 months. This should take care of if future entries are done. Hence, if Jun data is entered, sum up has to start from Jun month. -

1

Use this formula -

=IFERROR(SUM(OFFSET(INDIRECT("$B"&COUNTA($A:$A)),0,0,IF(COUNTA($A:$A)-12>0,-12,-COUNTA($A:$A)+1),1)),"")

Note - If you have only one column say B, replace $A with $B. (If only A, replace $B with $A)

Sat 09 Aug 2014

Tips & Tricks 40 - I don't want to Show Horizontal / Vertical Scroll Bar(s)

By |Saturday, August 09th, 2014|Categories: Tips and Tricks|Tags: , , , |0 Comments

1. Go to Office button > Excel Options > Advanced

1

2. Uncheck Show Horizontal Scroll Bar / Show Vertical Scroll Bar as per your need. To reverse, check them.

1

 

Sat 09 Aug 2014

Tips & Tricks 39 - I don't Want to Show Gridlines in the Sheet, how to do..

By |Saturday, August 09th, 2014|Categories: Tips and Tricks|Tags: , |0 Comments

Excel displays gridlines by default. If you don't want to show gridlines -

1. Go to View Tab
2. Un-check Gridlines box.

1

This is specific to sheet. Hence, if you want want to do for entire workbook, you will have to

1. Go to File > Options > Advanced
2. Select your workbook in "Display options for this worksheet". By Default, it would show your current worksheet.
3. Un-check "Show gridlines"

1

Sat 09 Aug 2014

Article 4 - Dynamic Charting in Excel i.e. Create a Chart which Automatically Updates when Data is Added or Removed

By |Saturday, August 09th, 2014|Categories: Articles|Tags: , , , |0 Comments

When you create a chart, you select a range. Now assume, one more row of data is added. But that will not be reflected in your chart as your chart had a fixed range.

-- The Excel for this article can be downloaded from Dynamic Charting --

A work around is to select more rows in your range beyond the existing range. i.e. if your current range is A1:B5, you can select A1:B10 where A6 to B10 are blanks. Hence, when data is added into A6 to B10, chart will automatically be updated. But there are two problems in this approach.

1. Chart shows placements for A6 to B10 also, hence your chart will have 10 data points in this case whereas actual graph will be for A1 to B5.
2. If your data goes beyond A10 that will not reflect in the chart unless you change the range again for the chart.

There are 3 approaches possible to overcome above limitations and create something called Dynamic Chart -

1. Table Approach
2. OFFSET Approach
3. Pivot Table Approach

(more…)