Excel Quiz 3
By eforexcel20150531T17:26:38+05:30Saturday, November 29th, 2014Categories: QuizzesTags: Exam, Quiz, Test2 Comments
Quizsummary
0 of 10 questions completed
Questions:
 1
 2
 3
 4
 5
 6
 7
 8
 9
 10
Information
The third quiz in the series to test your general knowledge in Excel. After you hit Finish Quiz, you can click on View Questions to compare your answers to correct answers.
You have already completed the quiz before. Hence you can not start it again.
Quiz is loading...
You must sign in or sign up to start the quiz.
You have to finish following quiz, to start this quiz:
Results
0 of 10 questions answered correctly
Your time:
Time has elapsed
You have reached 0 of 0 points, (0)
Categories
 Not categorized 0%
 1
 2
 3
 4
 5
 6
 7
 8
 9
 10
 Answered
 Review

Question 1 of 10
1. Question
What is the significance of XFD in Excel.
Correct
Incorrect

Question 2 of 10
2. Question
Is .XLSX file small than .XLS, in general?
Correct
Incorrect

Question 3 of 10
3. Question
What is the answer for =COLUMN(Z1)?
Correct
Incorrect

Question 4 of 10
4. Question
A1="a" and B1="A". Then what would be the answer for =EXACT(A1,B1)
Correct
Incorrect

Question 5 of 10
5. Question
If A1="abc", then what would be the answer for =T(A1)
Correct
Incorrect

Question 6 of 10
6. Question
Today is 15Nov14, what would be the answer for =TEXT(TODAY(),"mmmm")
Correct
Incorrect

Question 7 of 10
7. Question
What is the answer for =QUOTIENT(45,8)
Correct
Incorrect

Question 8 of 10
8. Question
A1=1, A2=2, A3=3, A4=4, A5=5, then the answer for =SUMS(A1:A5) would be
Correct
Incorrect

Question 9 of 10
9. Question
What is the maximum column width in Excel?
Correct
Incorrect

Question 10 of 10
10. Question
Correct
Incorrect
Article 8  Calculating Percentage Change between New and Old Value
By eforexcel20150531T17:48:23+05:30Saturday, November 29th, 2014Categories: ArticlesTags: ABS, Cost, Infinity, Percentage Growth, Profit, Revenue2 Comments
Many times, I get inspiration to write about articles when I post responses to questions on Microsoft Community. The inspiration to write about this has come from following post  Percentages
You will also have many occasions particularly when you are asked to compute percentage change between two values. This is very much encountered in Finance industry where you have to report percentage growth for many parameters like revenue, cost etc. Economics has demand growth, supply growth and every other industry has their own growth metric. Telecom will have subscriber growth, ARPU growth and so on.
Note  The article is not about growth between percentage values i.e. it is not about growth between 20% and 30% but between absolute values.
Challenge 4  SUM of Multiplication of Preceding Digits by Succeeding Digits
By eforexcel20150531T17:33:19+05:30Saturday, November 29th, 2014Categories: ChallengesTags: Challenge, Exam, Multiplication, Quiz, sum, Test1 Comment
Let's say you have a number 64934 in a cell A1. Now, the challenge before you is to work out the following SUM through a formula which should be flexible enough to capture any number of digits (Don't limit this to 15 digits maximum as allowed by Excel precision but to any number of digits as numbers can be entered in text also bypassing 15 digits limit of Excel)
= First Digit x Second Digit + Second Digit x Third Digit +...........+Last Digit x 0 (Last digit wouldn't be multiplied by something else as there is no succeeding digit)
The samples cases are given below for better clarity
Note  You may choose to post the response in comments section.
Tips & Tricks 66  Show the Complete Area of a Worksheet as only Limited Area is being shown
By eforexcel20150531T16:41:34+05:30Saturday, November 22nd, 2014Categories: Tips and TricksTags: Hide, Protect, Unhide, Unprotect, Worksheet0 Comments
If you can see only limited area of a worksheet
1. Check if the sheet is protected. This you check by going to Review tab and see if Unprotect Sheet Button is appearing.
 If Unprotect Sheet Button is appearing sheet is protected. You need to click it and if password is asked, you need to give the correct password to make the sheet unprotected.
 If Protect Sheet Button is appearing sheet is not protected. Then nothing needs to be done.
2. Press CTRL+A, Right Click on a Column Name A, B, C, D.....and Click Unhide. All columns will be unhidden.
3. Press CTRL+A, Right Click on a Row Number 1, 2, 3, 4.....and Click Unhide. All rows will be unhidden.
Tips & Tricks 65  Show a Limited Area of Worksheet
By eforexcel20150531T16:41:06+05:30Saturday, November 15th, 2014Categories: Tips and TricksTags: Area, CTRL+SHIFT+Arrow, Hide, Scroll, Worksheet0 Comments
You have a worksheet and you want to show only a limited area to users. Say you want them to see only A1:G50 and remaining you don't want to show them.
Use following steps 
1. Click column H so that entire column is selected. Now CTRL+SHIFT+Right Arrow key so that all columns will get selected right of column H. Now right click and hit Hide. Now all columns right of G will be hidden.
2. Similarly click row 51 so that entire row 51 is selected. Now CTRL+SHIFT+Down Arrow key so that all rows starting 50 are selected. Now right click and hit Hide. Now all rows starting 51 will be hidden.
Using above technique, I have hidden entire area except A1:G20 in below screen shot.
3. If you want to make sure that nobody ever is able to unhide these hidden rows / columns, protect the sheet with password after making the visible area not locked. To make visible area unlocked, select the visible area > Right Click > Format Cells > Protection, here uncheck the Locked Box.
Excel Quiz 2
By eforexcel20150531T17:26:08+05:30Saturday, November 15th, 2014Categories: QuizzesTags: Exam, Knowledge, Quiz, Test0 Comments
Quizsummary
0 of 10 questions completed
Questions:
 1
 2
 3
 4
 5
 6
 7
 8
 9
 10
Information
The second quiz in the series to test your general knowledge in Excel. After you hit Finish Quiz, you can click on View Questions to compare your answers to correct answers.
You have already completed the quiz before. Hence you can not start it again.
Quiz is loading...
You must sign in or sign up to start the quiz.
You have to finish following quiz, to start this quiz:
Results
0 of 10 questions answered correctly
Your time:
Time has elapsed
You have reached 0 of 0 points, (0)
Categories
 Not categorized 0%
 1
 2
 3
 4
 5
 6
 7
 8
 9
 10
 Answered
 Review

Question 1 of 10
1. Question
The largest date allowed for calculation in Excel is
Correct
Incorrect

Question 2 of 10
2. Question
The default file format in Excel 2007 / 2010 / 2013 is
Correct
Incorrect

Question 3 of 10
3. Question
What is the answer for the formula =ROWS(2:10)
Correct
Incorrect

Question 4 of 10
4. Question
The cell A1 contains First Name Last Name (for exmaple Vijay Verma), then what does following formula do =LEFT(A1,FIND(" ",A1)1)
Correct
Incorrect

Question 5 of 10
5. Question
Correct
Incorrect

Question 6 of 10
6. Question
What does shortcut CTRL+U do?
Correct
Incorrect

Question 7 of 10
7. Question
Which of the following is Absolute Address?
Correct
Incorrect

Question 8 of 10
8. Question
In Excel, when do you get #Div/0! error?
Correct
Incorrect

Question 9 of 10
9. Question
You can't create header / footer in Excel.
Correct
Incorrect

Question 10 of 10
10. Question
Answer for =ROUNDDOWN(15.486,1) is
Correct
Incorrect
Challenge 3  Generate a Chessboard in Excel
By eforexcel20150531T17:33:07+05:30Saturday, November 15th, 2014Categories: ChallengesTags: Chess, conditional formatting, Matrix, Quiz1 Comment
Below chessboard has been generated in Excel. The challenge before you is to give me steps which will generate this chessboard of 8x8. The approach should be flexible to generate any NxN chessboard (Colouring should not be done manually).
Note  You may choose to post the response in comments section.
Tips & Tricks 64  Show a 0 in place of Blanks in Pivot Table
By eforexcel20150531T16:40:27+05:30Saturday, November 08th, 2014Categories: Tips and TricksTags: Blanks, Options, Pivot, Table, Zero0 Comments
When you create a pivot table and if a source cell has no value (i.e. cell is blank), in pivot table, it will show a blank. All other results in pivot table are carrying numeric values and you see blanks. It is odd though it doesn't impact the results.
But you still prefer to show this as 0.
1. Click anywhere in the pivot table and choose PivotTable Options.
2. Make "For empty cells show" box 0.
Article 7  Generate a Sequence of Numbers
By eforexcel20150531T17:44:25+05:30Saturday, November 08th, 2014Categories: ArticlesTags: auto, fill, Numbers, sequence, sequential, series1 Comment
Many times, we have need when we want to generate a sequence of numbers for various purpose. When I started Excel, I simply used to put 1 in A1 and =A1+1 in A2 and dragged down to required number of rows. I, sometimes, still do it. Just old habits die hard. Over a period of time, I did learn many other ways which I would like to share here. Here, I will be talking about filling in a column i.e. vertically. Utilizing same line of logic, horizontal sequential numbers can be generated. Also, if your starting number is not 1 but some other number, you will have to utilize same line of logic to do it.
Once again, I would like to tackle this topic in 4 ways.
1. Formula Way
2. Manual Way
3. Hybrid Way (Combining both Formula and Manual ways)
4. VBA