Sat 24 Jun 2017

Excel Quiz 53

By |Saturday, June 24th, 2017|Categories: Quizzes|Tags: , , , , , , , , , , , , , , , , , , |0 Comments

Excel Quiz 53 - A Quiz on Data Tab in Excel

This quiz checks your knowledge about Data tab in Excel.

Sat 07 Jan 2017

Tips & Tricks 153 - Sum only Visible Columns

By |Saturday, January 07th, 2017|Categories: Tips and Tricks, VBA|Tags: , , , , , , , , |2 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.