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…
Downloads 11 – Printable & Pin-up Sheet for Excel Functions
You can download this one page pin-up sheet and take the printout and pin up to your desk for quick reference – Excel Functions Quick Reference
Tips & Tricks 154 – Insert a Space after Each Character
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….
Solution – Challenge 58 – Make a Vedic Square
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
Excel Quiz 36
Tips & Tricks 153 – Sum only Visible Columns
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…
Solution – Challenge 57 – Another Word Challenge – Palindrome or Not
Below is a possible solution to the Challenge 57 – Another Word Challenge – Palindrome or Not Use the below formula –
Tips & Tricks 152 – While Printing a Sheet, Don't Print Page Number on First Two Pages and Print 1 on 3rd Page
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…
Downloads 10 – Printable & Pin-up Sheet for Excel Shortcuts
You can download this one page pin-up sheet and take the printout and pin up to your desk for quick reference. – Excel Shortcuts Quick Reference
Solution – Challenge 56 – Cryptography Challenge 5 – Fully Functional Caesar’s Shift Cipher Decrypter
Below is a possible solution to Challenge 56 – Cryptography Challenge 5 – Fully Functional Caesar’s Shift Cipher Decrypter