Home » Archive

Articles in the Featured Category

Featured, General, Tips and Tricks »

Useful Excel shortcuts – episode 2
[28 Feb 2010 | 8 Comments | ]
Useful Excel shortcuts – episode 2

This post is in continuation to my previous post on Excel Keyboard Shortcut. This time I have chosen shortcuts for ‘Formatting’ commands.

  • Activate the Formatting dialog box: The shortcut key for this command is CTRL+1. This command will activate the Formatting dialog box which can then be used for applying any kind of formatting to the selected cells.

Featured, Lookup Functions »

Lookup Functions – New list of functions released
[8 Sep 2009 | No Comment | ]
Lookup Functions – New list of functions released

Today I am starting to write about Lookup functions, that include VLookUp(), HLookUp(), InDirect(), etc. Lookup functions are one of the most useful and in-demand functions.

These functions are used in lot of applications, their demand in today’s market is very high, one of which is financial industry. And not just financial research, these functions are very famous in other industries also, at all those places where there is high use of Excel spreadsheets.

Advanced Topics, Featured, Queries, Tips and Tricks »

Working out with Data Validation list
[6 Aug 2009 | No Comment | ]
Working out with Data Validation list

Recently one of the readers sent me an email with a question on data validation issue that most of us would have faced. I’ll call this person as Tom as this person has asked for anonymity of his identity.

As you remember, a cell can be validated for numerous things, text entries, numerical ranges, pre-defined lists. etc. [Refresh your data validation concepts here].

Featured, Queries »

Email query – populating values based on max value
[16 Jul 2009 | 7 Comments | ]
Email query – populating values based on max value
I recently received a query from one our readers – Pedro Costa. Here is the query that I received through email:
Email:
Hi Mohit,
The purpose is to copy the values from beginning in AA19 till the end of the column to the column painted in yellow (G19 till …). The column to copy is determined by the Maximum value between Cell J2 to cell DD2.

I hope You understand the problem. Thanks in advance for your attention and help.

Pedro
Explanation of the query:
Here is the attachment – PedroCosta_query.xls (I have cleaned up the columns which are not required and have pasted the formulas as values to maintain confidentiality of Pedro’s data).
In this spreadsheet Pedro has some data in a table spread across columns ‘B’ to ‘I’. And there is some data through columns – ‘K’ to ‘DE’ and ‘DG’ to ‘HA’. There are a few rows above this data which does some calculations on the given table.

What is required – In the sheet attached, Pedro needs to populate the column ‘G’ with that column for which row ’2′ gives the highest value. For example, if ‘T2′ cell has the maximum value, then range ‘T19′ to ‘T166′ should be populated in the corresponding range in the column ‘G’.
So, guys I leave this query open to all of you. Help Pedro in solving this problem. You can either email me the solution or can also post your replies as comment to this post. For those who will send me an email, I will post them as comment myself.

Excel Basics, Featured »

A re-visit to Math Functions
[11 Jul 2009 | One Comment | ]
A re-visit to Math Functions
By now we have discussed a good number of Math functions, Text functions and some advanced concepts. Now let us take some of the math functions that we have discussed so far.

[Before I move ahead and talk about various functions, I would like to touch upon an issue which we generally face while using Excel. Many a times we tend to forget how a particular function is written, i.e. the syntax of functions, though we know how to use the function well. Microsoft understands this problem well and has provided a very useful, but not so popular, tool for this. You can recall the syntax of any function by pressing CTRL+A while in the middle of writing that particular function and WHOAA..you now know how to write that function. Read more about this topic here.]
In our initial days we talked about some basic, but useful, math functions. The list includes basic functions such as Sum(), Average(), Product(), Count(), Counta() etc.

We also talked about certain advanced level math functions. These functions are like – Sumif(), ABS(), SumSQ(), etc.. The Sumif() functions perform sum with some conditions applied to them.
For example, if I want to add a number of entries in range A1 to A10 (containing only numbers), for only those cells that have ‘3’, then I would need to use Sumif(). The formula for this would be – =Sumif(A1:A10,3).
If you wanted to convert a given number into an even or an odd number, you would need Even() and Odd() functions.
All of the above functions were such which can be used for performing some or the other function. There are two functions which are used to fetch random numbers – R

and() and Randbetween().


Randbetween() functions is available in Excel 2007 (and is not there in Excel 2003). Rand() is used to generate a random number between the range of 0 to1 and is a decimal number. Whereas, Randbetween() returns random integers between the range provided by the user.

So this wraps the list of Math Functions which we have discussed since the beginning of the blog. I will also be summarizing all the text functions that we have discussed here in our earlier posts.
In case you would like me to include any other specific function in this list, please feel free to send e a note, or make a comment on any of the blog entries. I will include that function/topic in my list of publications.
In my next post, I will talk about various Text Functions that we have discussed till date.

Advanced Topics, Featured »

Concept of Data Validation
[4 Jul 2009 | 5 Comments | ]
Concept of Data Validation

Concept of Data Validation?
Data Validation is a tool provided by MS Excel that allows you to validate data that is entered by a user. The validation happens once the user enters data and hits the return key (enter key).


Why should I use data validation?


Data validation, though a simple tool, can be of lots of help. It’s very core function (of validating data) can be applied to many other tasks. Here are a few things that can be done using data validation tool:

Advanced Topics, Featured »

How to calculate subtotals using Array functions
[1 Jul 2009 | One Comment | ]
How to calculate subtotals using Array functions

I have discussed basic concept of array functions, its advantages and disadvantages in my previous post. Now, let us now talk about an example where we generally get to use Array Functions.

Example:
In our example, we have some data related to sales of 3 products – Product A, Product B and Product C. The sales force is active in all 4 geographic regions – East, West, North and South. Now we need create a small report (just 4 cells of data) that will summarize the whole data. This report will have subtotals by Region and Product. Follow the below steps for this:

  • Create an Excel spreadsheet with some sample data (alternatively you can download this spreadsheet) which has these columns – Region in column A, Product in column B and Sales in column C. Also, fill some sample data for these 3 columns
  • Create a small matrix table with columns titled as each product and rows titled as each region. The matrix should look something like this:

Let us now try to formulate one single formula for subtotals that can be used with all the cells in the matrix. Here is the formula that I used for calculating the totals for each cell in the above matrix:


=SUM(IF($A$2:$A$29=$E6,IF($B$2:$B$29=F$5,$C$2:$C$29,0),0))


..and i entered this function as an array function, i.e. using CTRL+Shift+Enter


Let us understand what does each part of the formula does.

  1. =SUM(IF($A$2:$A$29=$E6,IF($B$2:$B$29=F$5,$C$2:$C$29,0),0))This portion of function gets executed first. This part checks if the Product column has ‘Product A’ in it. If yes, it lets the first cell go for next level of validation (next step of calculation), If the cell does not have ‘Product A’, then 0 value is passed on to next level
    Note: I hope you remember I discussed in my previous post that array functions process each cell at a time. To refresh your concept read more about Array Functions here and here.

  2. =SUM(IF($A$2:$A$29=$E6,IF($B$2:$B$29=F$5,$C$2:$C$29,0),0))
    Similar to previous part of the formula, this part check the cell for Region. If the cell has region as ‘East’. If true, then the cell is passed on to next level of formula, i.e. Sum() function. If the cell does not have ‘East’, then 0 value is passed on to next level

  3. =SUM(IF($A$2:$A$29=$E6,IF($B$2:$B$29=F$5,$C$2:$C$29,0),0))

    Here, the value passed from previous 2 steps gets accumulated and is added up. (Learn more about Sum() Function and other Math Functions)

Now, you copy and paste this formula to other cells of the matrix and you will get the required results.

Note: The formula will take required cells from the column and row heads for validation by itself. This has been done through using ‘Relative Reference’ technique of writing formula in Excel. I will take this up in my later posts.