Downloads 03 – Template 03 – A Flexible Agile Burndown Chart
The template can be download from – Template 03 – Agile Burndown Chart Template Unprotected version of template – Template 03 – Agile Burndown Chart Template_Unprotected I decided to create a template for a Burndown Chart in Excel which is very highly used in Agile Project Management. Next download template will be about Burnup Chart…
Tips & Tricks 136 – Quickly Copy a Formula or a Value in a Large Number of Cells
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…
Solution – Challenge 40 – Cryptography Challenge 2 – Fully Functional Caesar’s Shift Cipher
Below is a possible solution to the challenge – Challenge 40 – Cryptography Challenge 2 – Fully Functional Caesar’s Shift Cipher 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….
Challenge 42 – Split a Sentence Into Words in Different Cells
This time the challenge before you is to write a formula that if a sentence is given in A1, your formula if dragged to the right should split the sentence into words. Remember, it has to be a single formula which should be put into B1 and dragged to the right. See the examples below…
Tips & Tricks 135 – I input (c) or (C) and it gets converted to Copyright Symbol..I don't want this
There are many ways to overcome this. 1. a. Put following in a cell and press Enter c) b. Go back to the cell and type first bracket to complete (c) 2. a. You can type (c) preceded with some special character and press enter. b. Go back to that cell and delete that special…
Solution – Challenge 39 – Cryptography Challenge 1 – Caesar’s Shift Cipher
Below is a possible solution to the challenge Challenge 39 – Cryptography Challenge 1 – Caesar’s Shift Cipher. Put below formula in G2 and drag down – =IFERROR(CHAR(IF(CODE(LOWER(D2))+$B$3>122,CODE(D2)+$B$3-26, CODE(D2)+$B$3)),"") The workbook containing the solution can be downloaded from Solution – Challenge 39 – Caesar's Shift Cipher.
Challenge 41 – Sum the Maximum Number where duplicates Exist
Suppose, you have been given following and you will need to find duplicates in column A and sum the maximum values from column B. If duplicates don't exist, values will be taken as they are. The values which needs to be summed up are colored. The answer would be 123 in this. The workbook related…
Downloads 02 – Template 02 – A Highly Customizable Perpetual Yearly Calendar Template in Excel
The second template in the series is a calendar. Just download it once and use every year. Following are the features – 1. The calendar is perpetual in nature. It supports all the years from 1900 to 9999. You just need to change the year every year or you can leave the year field blank….
Tips & Tricks 134 – Last Working Day of the Year
If a year is given in A1 say 2016, below formula can be used to know the last working day of the year (format the result as date) =WORKDAY("1JAN"&A1+1,-1) The above formula assumes that your weekends are Saturday and Sunday. But, if your weekends are different (e.g. in gulf countries), you can use following formula…