Sat 18 Feb 2017

Tips & Tricks 156 - Get Workbook's Directory from Formula

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

If your workbook is located in say C:\Excel\MyDocs, the formula to retrieve the directory for this would be

=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-2)

Note - For this formula to work, you workbook must be saved at least once.

 

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 04 Feb 2017

Challenge 59 - Clean the Problem Workbook Data

By |Saturday, February 04th, 2017|Categories: Challenges|Tags: , , , , , |1 Comment

Download the the workbook from the below link. The Challenge before you is to write a formula to clean the data. If you perform any mathematical operation on the data, it will return #VALUE error. You need to clean the data through a formula.

Challenge 59 - Problem Workbook

The solution to the above problem will be posted after a month i.e. on 4-Mar-17.

 

Sun 29 Jan 2017

Excel Quiz 49

By |Sunday, January 29th, 2017|Categories: Quizzes|Tags: , , , |0 Comments

Excel Quiz 49

A general quiz on excel

Sat 21 Jan 2017

Tips & Tricks 154 - Insert a Space after Each Character

By |Saturday, January 21st, 2017|Categories: Tips and Tricks|Tags: , , , , , , , |0 Comments

You have a sheet and you want to put a space after each character. For example, A1 contains 12345, you want to make it 1 2 3 4 5. B1 contains Mango12 and you want to make it M a n g o 1 2. The same need to be accomplished by a simple macro. (Flash Fill also works in most of the cases. But Flash Fill will copy in a different range which you will have to copy back and if data is scattered throughout the sheet, it may not be convenient)

1. Make a backup of your workbook.
2. Open your workbook and ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Right click on your workbook name > Insert > Module
5. Copy paste the Macro code given and change "Sheet1" as per your requirement.
6. Go back to your Workbook and ALT+F8 to display Macro Window
7. Run your Macro from here
8. Delete you Macro if the Macro was needed to be run only once.
9. Otherwise save your file as .xlsm if you intend to reuse Macro again.

 

Tue 17 Jan 2017

Solution - Challenge 58 - Make a Vedic Square

By |Tuesday, January 17th, 2017|Categories: Solutions|Tags: , , , , , , |0 Comments

Below is a possible solution to Challenge 58 - Make a Vedic Square

Put the following formula in C3 and drag right and down

=MOD(C$2*$B3-1,9)+1

The Excel sheet having this solution can be downloaded from Solution - Challenge 58 - Make a Vedic Square

 

Sat 14 Jan 2017

Downloads 15 - Excel Formulas Bible

By |Saturday, January 14th, 2017|Categories: Downloads|Tags: , , , , , , , , , , , , |0 Comments

This is one single document which contains close to 100 formulas dealing with various situations. Useful for Intermediate and Advanced users.

Download it from Excel - Formulas Bible

Sat 07 Jan 2017

Tips & Tricks 153 - Sum only Visible Columns

By |Saturday, January 07th, 2017|Categories: Tips and Tricks|Tags: , , , , , , , , |0 Comments

You can use SUBTOTAL or AGGREGATE function to sum visible rows but Excel doesn't provide the facility to sum only visible columns. Look below. Columns B and D are hidden. Hence, our function should sum up only A, C, E and F. G2 has the formula =SUM(A2:F2)

If you hide the columns, the sum stays the same whether columns are hidden or visible.

1

Unfortunately, Excel doesn't provide any native functionality to accomplish this. This will have to be done through a VBA function. We will write a VBA function "SumVisCols" which can be called like =SumVisCols(A2:F2)

(You can give any range not only A2:F2)

1. Make a backup of your workbook.
2. Open your workbook and ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Right click on your workbook name > Insert > Module
5. Copy paste the Macro code given and change the bold lines as per your requirement
6. Go back to your Workbook and ALT+F8 to display Macro Window
7. Run your Macro from here
8. Delete you Macro if the Macro was needed to be run only once.
9. Otherwise save your file as .xlsm if you intend to reuse Macro again.

Tue 03 Jan 2017

Solution - Challenge 57 - Another Word Challenge - Palindrome or Not

By |Tuesday, January 03rd, 2017|Categories: Solutions|Tags: , , , , , , , , , , |0 Comments

Below is a possible solution to the Challenge 57 - Another Word Challenge - Palindrome or Not

Use the below formula -

(more…)

Sat 24 Dec 2016

Tips & Tricks 152 - While Printing a Sheet, Don't Print Page Number on First Two Pages and Print 1 on 3rd Page

By |Saturday, December 24th, 2016|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

One user had a specific requirement. His first two pages were cover page and Table of Contents. Hence, he wanted to start his page number from 3rd page. He wanted to display page X of Y. Hence, if the worksheet had 10 pages, his 3rd page should say 1 of 8......Last Page should say 8 of 8.

The above problem will have to be solved through VBA.

1. Make a backup of your workbook.
2. Open your workbook and ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Double click on ThisWorkbook
5. Copy paste the Macro code given
7. Save your file as .xlsm
8. Create a button and attach this macro to that button