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…
Author: Vijay A. Verma
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…
Article 38 – 10 Features I would like to see in Excel
Like all of you, I am dependent upon day to day work in Excel. Excel is a software which is like ocean, unlimited in bounds. It has almost all features but it doesn't have few which everybody cherishes. All of us want something or the other. Below is a list of features which I would…
Solution – Challenge 38 – Formula for Top 5
Below is a possible solution to the challenge Challenge 38 – Formula for Top 5. Enter following formula in F2 and drag down – =IF(COUNTIF($B$2:$B$100,">="&LARGE(IF(FREQUENCY($B$2:$B$100,$B$2:$B$100)<>0, $B$2:$B$100),5))>=ROWS($1:1),INDEX($A$2:$A$100,MATCH(1,INDEX(($B$2:$B$100= LARGE($B$2:$B$100,ROWS(F$1:F1)))*(COUNTIF(F$1:F1,$A$2:$A$100)=0),),0)),"") Enter following formula in G2 and drag down – =IF(COUNTIF($B$2:$B$100,">="&LARGE(IF(FREQUENCY($B$2:$B$100,$B$2:$B$100)<>0, $B$2:$B$100),5))>=ROWS($1:1),INDEX($B$2:$B$100,MATCH(1,INDEX(($B$2:$B$100= LARGE($B$2:$B$100,ROWS(F$1:F1)))*(COUNTIF(F$1:F1,$A$2:$A$100)=0),),0)),"") The workbook containing the solution can be downloaded from Solution – Challenge 38 – Find Top…