Microsoft products are truly amazing tools for management consultants who need to do data analysis and make a presentation before clients. And Microsoft Excel is no doubt one of the most important tools for management consultants. Today I’d like to share with you the top Excel functions or features for those who work in consult industry.
Generally, there are two parts in this article. The first part will focus on Excel function while the second part is mainly for useful features.
Top 70 Excel Functions
DATE Function (8 functions)
The DATE function is very useful for business data management. Before exploring further, I’d like to make a brief summary of Date functions.
Returns the day of a date. The day is given as an integer, ranging from 1 to 31.
Returns the month of a date represented by a serial number. The month is given as an integer, ranging from 1 (January) to 12 (December).
Returns the year corresponding to a date. The year is returned as an integer, ranging from 1900 to 9999.
Returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday) (by default). For details of return_type, please refer to Figure 1.2.
Returns the week number of a specific date. For details of return_type, please refer to Figure 1.2.
|NETWORKDAYS||NETWORKDAYS(start_date, end_date, [holidays])
Returns the number of whole working days between start_date and end_date. Working days do not include weekends and any dates identified as holidays.
Returns the sequential serial number that represents a particular date.
Return the serial number of the current date.
Figure 1.1 shows examples of the above functions. Let’s assume a week begins on Monday and workdays include Monday through Friday. You can see from cells E14 through F14 that 15 Oct 2016 is not a workday. Therefore, it should not be removed from network days from cell C14. And this is the reason why the value returned in cell C15 is 22 instead of 21.
Figure 1.2 shows return_type for WEEKDAY and WEEKNUM. Suppose that a week begins on Monday, the return_type should be 2 or 11. Both formula “=WEEKDAY($B$2,2)” in cell H3 and formula “=WEEKDAY($B$5)” in cell H5 returns 7. It tells that 2 Oct 16 is Sunday. This is consistent with the right panel of Figure 1.2. You can choose return_type based on the common practice from your own country.
Now let’s use a simple example to elaborate the role of Date function in business. Figure 1.3 shows how to track task completion for a project. There are three sections in total. For each subtask, the start date was entered into Column B while the end date was entered into Column C. And Reminder Date on which we need to fill % complete was put into Column E. Formula “=IF(TODAY())=E2,”Fill % Complete”,””)” was copied from H2 into range H3:H15.
When you open the workbook each day, “Fill % Complete” as shown in Figure 1.3 will appear if the date is equal to the Reminder Date. For example, “Fill % Complete” will be displayed in cell H3 if you open the workbook on 24 Apr 16. Since today is 5 Nov 16, “Fill % Complete” prompted in cell H14. Here is where you can find details for IF function.
I also use Conditional Formatting to highlight “Fill % Complete”. The red color can enable you to see the notification easily and thus remind you to fill in the completion status.
TEXT Function (11 functions)
Text functions allow you to manipulate strings in order to retrieve information or make beautiful reports. Besides functions in below table, there are still other functions such as FINDB, SEARCHB, LEFB, RIGHTB, MIDB. Those functions are based on bytes and will not be discussed in this article. TEXT function will also not be covered and you can find details in this article.
|FIND||FIND(find_text, within_text, [start_num])
Locates find_text string within within_text and returns the number of the starting position of the find_text from the first character of the second wthin_text. Start_num is optional. It is the number in the within_text argument at which you want to start searching. FIND function is case-sensitive.
|SEARCH||SEARCH(find_text, within_text, [start_num])
Locates find_text string within within_text and returns the number of the starting position of the find_text from the first character of the second wthin_text. Start_num is optional. It is the number in the within_text argument at which you want to start searching. SEARCH function is not case-sensitive.
Returns first num_chars characters in text string.
Returns last num_chars characters in text string.
|MID||MID( string, start [, length ])
Returns a string containing a specified number of characters from the starting position of a string.
Returns the number of characters in the text string
Converts a text string (that represents a number) to a number
Converts a value to text with a specific format
Removes all spaces from a text except for single spaces between words.
Remove all nonprintable characters from text.
|CONCATENATE||CONCATENATE(text1, [text2], …)
Join two or more text string into one string.
Figure 2 shows you how to use the above functions to manipulate the string in cell B1. You can see that FIND function will return 23 once the third argument is greater than 4. FIND function has to start from the 5th letter to locate “in” when the third argument equals to 5. Therefore, it will return the position of the second “in”. SEARCH function can do almost the same thing as FIND function except that SEARCH function is not case-sensitive.LEN function returns the total number of characters for that string. Left function returns “Stri” when the second argument is 4. 30 minus 4 equals to 26. If we use 26 as the second argument of RIGHT function, we can get a string of “ng manipulation in Excel”. If we combine these two strings together using CONCATENATE function in G14, we will get the same string as that in cell B1. This is already checked by the IF function in cell J14.
By comparing cell G14 against G12, you can see that two blank spaces have been removed and there is only one blank space between the words “String” and “manipulation”.
LOGICAL Function (8 functions)
Logical functions allow you to make logical comparisons between a value and what you expect.
Returns the logic value of TRUE.
Returns the logic value of FALSE.
|AND||AND(argument1, argument2, …)
Returns TRUE if all of its arguments are TRUE.
|OR||OR(argument1, argument2, …)
Returns TRUE if any of its arguments are TRUE.
Reverses the logic of its argument
|SWITCH||SWITCH(value to switch, Value1_match, Value1_return, value2_match, value2_return, …, Optional default value)
Evaluates a value against a list of values and returns the corresponding results. The optional default value will be returned if there is no match.
|IF||IF(argument1, argument2, argument3)
If argument1 is TRUE, then do as argument2 dictates. Otherwise, do as argument3 dictates.
Returns value_if_error if the first argument is evaluated to an error. Otherwise, returns the result of the first argument.
TRUE function and FALSE function are useless unless you apply them together with IF function in a way similar to the formula in cell F7. IFERROR is used often especially for large data and it can help discern errors quickly.
LOOKUP and Reference function (13 functions)
Lookup functions are usually used when mapping records to categories, extending data tables, or retrieving data.
|CHOOSE||CHOOSE(index_num, value1, [value2], …)
Select one of up to 254 values from the value list based on index_num.
Returns the row number of the given cell reference.
Returns the column number of the given cell reference.
Returns the number of rows in a reference or array.
Returns the number of columns in a reference or array.
|VLOOKUP||VLOOKUP(lookup_value, lookup_range, offset_column, TRUE/FALSE)
Looks in the first column of an array and returns the value of offset_column
|HLOOKUP||HLOOKUP(lookup_value, lookup_range, offset_row, TRUE/FALSE)
Looks in the top row of an array and returns the value of the offset_row
|LOOKUP||LOOKUP(lookup_value, lookup_vector, [result_vector])
Looks in a single row or column and find the value from the same position in a second row or column
|MATCH||MATCH (lookup_value, lookup_array, [match_type])
Get the position of an item in an array.
|INDEX||INDEX(array, row_num, [column_num])
Returns the value of an element in a table or an array based on the row_num and column_num.
|OFFSET||OFFSET(reference, rows, cols, [height], [width])
Returns values in a cell or a range that is rows and columns away from a reference cell.
Returns the reference specified by a text string.
|GETPIVOTDATA||GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], …)
Returns data stored in a PivotTable report
In range F7:G10, I listed 4 ways to retrieve 24 in cell C2. And from range F12:G15, you can see that the combination of INDEX and MATCH functions can do lookup too.
In fact, this combination is more flexible than HLOOKUP or VLOOKUP. It allows you to do a case-sensitive lookup. The combination of OFFSET and MATCH functions can also do a case-sensitive lookup. Figure 4.2 demonstrates these points. It also allows you to look up from right to left. I will not discuss in details here. You can read my previous articles: Excel Offset Function with 3 Real Life Uses and Practice Excel Indirect Function with 3 Case Studies.
MATH Function (15 functions)
Big firms usually have dedicated groups who can analyze data and conduct detailed statistical analysis for consultants. But management consultants need to use Excel functions to do basic math and basic statistics almost every day.
Returns an evenly distributed random real number greater than or equal to 0 and less than 1. RAND()*(b-a)+a can return number between a and b.
Generates a random integer number between the bottom number and top number.
Rounds a number down to the nearest integer.
Rounds a number to a specified number of digits.
Rounds number down, toward zero, to the nearest multiple of significance.
Rounds number up, away from zero, to the nearest multiple of significance.
|SUM||SUM(number1, number 2, …)
Add together all the numbers given as arguments and returns total number.
|SUMIF||SUMIF(range_evaluate, criteria, [range_sum])
Add together cells in range_sum if corresponding cells in range_evaluate meets criteria. If range_sum is omitted, Excel will add cells in range_evaluate.
|SUMIFS||SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Add all cells meeting all of those criteria
|PRODUCT||PRODUCT(number1, number 2, …)
Multiplies all the numbers given as arguments and returns the product.
Returns the result of a number raised to a power.
Returns a positive square root
Returns the remainder after number is divided by divisor
Returns a subtotal in a list or database. The function_ number includes 1-11 or 101-111. It specifies the function to be used for the subtotal. 1-11 includes manually hidden rows while 101-111 excludes them. Filtered-out cells are always excluded. For details of function_num, please refer to Figure …..
|SUMPRODUCT||SUMPRODUCT(array1, [array2], [array3], …)
Multiplies the corresponding items in given arrays and then returns the sum of the results.
There are no SUBTRACT or DIVIDE equivalents to SUM or PRODUCT functions. Adding a negative number is the same as subtracting. Use SUM function and convert any numbers that you want to subtract to their negative values. Similarly, you can use PRODUCT to do division.
Please be careful with negative values when using INT, ROUND, FLOOR or CEILING functions. Figure 5.1 shows that “=INT(-2.235)” returns -3 while “=INT(2.235)” returns 2. And both SUM and PRODUCT functions treat arguments “B17:B18, C17:C18”, “B17:C18”, “B17:C17, B18:C18” as the same things. The results returned by Excel are the same.
Here shows you how SUMPRODUCT function works. By copying formula “=H2*I2” from cell L2 into cells from L3 through L5, we can get the multiplication of each pair of items. Then by adding them together using the formula “=SUM(K2:K5)”, we got $59,000. This is the way SUMPRODUCT function works. You can see that formula “=SUMPRODUCT(H2:H5, I2:I5)” also returns $59,000.
Figure 5.3 shows you function_num and its corresponding function. For example, 1 is for Average function and 9 is SUM function. Hidden values will also be computed if you use function numbers in column H. Hided values will be excluded from computing if you use function numbers in column G.
The formula in cell D8 is “=SUBTOTAL($G$3,D3:D7) “ while the formula in cell E8 is “=SUBTOTAL($H$3,D3:D7)”. You can see that by changing values in G3 and H3, we can compute different statistics. Formula “=VLOOKUP($G$3,$G$18:$I$28,3)” was entered into D1 to tell viewer what the function is used for column D. Similarly, formula “=VLOOKUP($H$3,$H$18:$I$28,2)” in cell E1 will tell you which function is applied for column E. You can see that we get total sales in column D and average sales in column E.
The left part of Figure 5.4 shows you that function number 109 and 9 return the same value when no records are hidden. But if you hide some records, you will get different results as shown in the right part of Figure 5.4Finally, it is always better for you to apply drop-down list when using the SUBTOTAL function. You don’t have to remember all the possible numbers and don’t have to worry about if you choose the right number for your expected function.
Let’s take cell G3 as an example. Following Figure shows you how to make a drop-down list . First of all, click on G3 and then click on Data -> Data Validation -> Data Validation. Data Validation dialog box will be prompted. In the Settings tab, choose List in Allow field and type” 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111” in Source field. Click on Ok to save your changes. Then in Input Message tab, type things shown in Input message field and finally click on Ok. So far, you have successfully created a drop down list for cell G3. If you click on cell G3 now, you will see a yellow box is shown in Figure 5.3 which tells you which number is the right one. And by clicking on the drop-down arrow to the right of cell G3, you can choose any number as you wish.
STATISTICAL Function (15 functions)
|COUNT||COUNT(value1, [value2], …)
Count total number of cells that contain numbers.
|COUNTA||COUNTA(value1, [value2], …)
Count The number of non-blank cells.
|COUNTIF||COUNTIF (range_evaluate, criteria, [range_sum])
Counts the number of cells within a range that meet the given criteria
|COUNTIFS||COUNTIF S(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Counts the number of cells within a range that meet multiple criteria
|AVERAGE||AVERAGE(number1, number2, ..)
Returns the average/mean of the arguments.
|AVERAGEIF||AVERAGEIF (range_evaluate, criteria, [range_average])
Get average of cells in range_ average if corresponding cells in range_evaluate meets criteria. If range_average is omitted, Excel will take the average on cells in range_evaluate.
|AVERAGEIFS||AVERAGEIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Get average of cells meeting all of those criteria
|MIN||MIN(number1, number2, …)
Returns the minimum value in the arguments.
|MAX||MAX(number1, number2, …)
Returns the maximum value in the arguments.
|MEDIAN||MEDIAN(number1, number2, …)
Calculate the median of a sequence of numbers.
|STDEV||STDEV(number1, number2, …)
Calculates standard deviation based on arguments.
|VAR||VAR(number1, number2, …)
Estimates variance based on a sample.
Returns the rank of a number in a list of numbers. Order is omitted by default. If it is 0 or omitted, Excel ranks number as if ref were a list sorted in descending order. If the order is any non-zero value, Excel ranks number as if ref were a list sorted in ascending order.
Returns the k-th largest value in an array.
Returns the k-th smallest value in an array.
COUNT function only counts number while COUNTA also counts cells filled with characters. And this is why Excel returns 8 in cell C5 and 9 in cell G5. There is a relationship between MIN function, SMALL function, and LARGE function. The formula in cells highlighted in yellow all return 12. Similarly, you can look into cells highlighted in purple or blue to figure out the relationship among other functions such as MAX, MEDIAN, SMALL, and LARGE.
There are only 5 numbers in range C1:G1 and the median value returned in cell C12 tells that 25 is ranked 3 in these 5 numbers. This is in consistent with what we got from RANK function in cell C17 and G18.
Figure 6.2 shows you how to use such functions as SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS. Please pay attention to the range_ average or range_ sum. For example, range_sum is placed at different positions for SUMIF and SUMIFS. Another point is that you will get 0 in cell Q1 if you apply COUNT function instead of COUNTA function.
Top 7 Features
Named ranges can allow you to simplify your formula. You don’t have to write range reference. And readers can understand easily. It also allows you to refresh formulas automatically when data is changing.
Figure 7.1 shows you how to define a name for range E2:E20. Select the range first and then right click on the range. Click Define Name menu and New Name Dialogue box will be prompted. Enter “WEIGHT” into Name field. Range E2:E20 will be given a name after you click on OK.
Now click on Formulas tab and then click on Name Manager in Defined Names group to open Name Manager dialogue box, you will see that the named range (WEIGHT) that we just created. By clicking on the buttons such as New, Edit, or Delete, you can create a new named range or manipulate an already exist named range.
Figure 7.3 shows that you can apply the name as the argument of functions (SUM in this case) directly. And by comparing with the right part of the left panel, you can see the SUM function can refresh automatically when you delete some records.
Conditional formatting is a useful feature which can enable you to highlight certain cells with a certain color. It can also allow you to display simple icons, to compare values, find discrepancies, find the smallest duplicate, etc. Following three simple examples will show you how to use this wonderful feature.
Create alternating bands to improve readability
Let’s take range B1:B20 in Figure 8.1 as an example. Click on the range and then click on Conditional Formatting drop down arrow in Styles group. In the drop-down list, select New Rule.
In the prompted New Formatting Rule dialogue box, select Use a formula to determine which cells to format and type “=EVEN(ROW())=ROW()” into Format values where this formula is true filed. Then click on Format button to open Format Cells dialogue box, click on the color that you want and click on OK to close this dialog box.
After clicking on OK in New Formatting Rule dialogue box, you can see that even rows are highlighted in light green. You can also “=ODD(ROW())=ROW()” to format odd rows.
Finding duplicate or unique values
By selecting Format only unique or duplicate values in New Formatting Rule dialogue box and duplicates in Format all, we can highlight cells having duplicates in seconds.Here shows you how to find duplicates that cross multiple columns. The key here is to concatenate all columns together and then apply conditional formatting on this concatenated column.
Formatting cells containing specific value
Look back at Figure 1.3 in Date Function part. Here shows how to highlight notification.
Pivot tables and charts
Pivot tables allow you to quickly analyze datasets. For example, it can allow you to split sales by region, to average revenue per customer, per country and so on. With pivot tables, you can also calculate slice, and dice the data along any dimension as you wish. This is very important as it can give you an idea of what the data looks like and where you need to dig deeper.
Suppose that we want to create a pivot table which can return statistics per sex per age. Figure 9.1 and Figure 9.2 shows you how to do this. First of all, select the data range and then click on Insert tab. In Tables group, select PivotTable -> PivotTable. In the prompted Create PivotTable dialogue box, select range as shown in Figure 9.1.
After clicking on OK button in Create PivotTable dialogue box, a PivotTable Field List dialogue box appears. Drag SEX, AGE, and WEIGHT to Row Labels or Values field as shown in Table 9.2. A pivot table similar to the middle panel of Figure 9.2 will be created.
By dragging SEX into Column Labels field, we can create another pivot table as shown in the middle panel of Figure 9.3.
Right-click on any cell in the pivot table and then select Value Field Settings to open Value Field Settings dialogue box. In the dialog box, select any function as you wish. And in our case, selecting AVERAGE will return us a pivot table display average statistics (bottom panel in Figure 9.4). It is quite flexible, right? In fact, you can also choose Format Cells, Number Format to format your pivot table.
If you select PivotTable -> PivotChart after clicking on Insert tab, you can create a Pivot Chart together with a Pivot table. The subsequent process is similar to that for Pivot table. I will not discuss in details here. Figure 9.5 shows you the pivot table and pivot chart created through this way.If you look at data closely, you will find that there are duplicate records for source data. We should remove duplicate records, right? Therefore, please remember to check data using conditional formatting before doing further analysis.
RSQ is a quick way for you to check the correlation between 2 datasets. And you can plot RSQ on a scatter plot. Figure 10.1 shows you how to compute RSQ using RSQ function. It shows the correlation between sales and advertising cost.
Commonly, we’d like to make a scatter plot to have an intuitive impression. Select range B2:C8 and click on Insert tab. In the Charts group, select Scatter and Excel will return you a plot shown in Figure 10.2.
Right click on any dot in the plot and select Add Trendline to open Format Trendline dialog box. Check boxes as shown in Figure 10.3.After clicking on Close, you will get a plot like that in Figure 10.4. You can see that RSQ returned by RSQ function equals to that plotted in scatter plot. Usually, higher RSQ means stronger relationship. And the plot also gives you an equation to describe the relationship between sales and advertising cost.
You might want to use this equation to predict sales. But I have to remind you that R-squared cannot tell you whether the coefficient estimates and predictions are biased. From this viewpoint, you need advanced tools.
The most common use of regression is to make a prediction and optimize business process. For example, regression can tell how many products consumers will purchase based on their previous behaviors and other data. And as a factory manager, you can build a model to find the relationship between oven temperature and the shelf life of the cookies.
Figure 11.1 illustrates how to load Data Analysis ToolPak which will be used to do regression analysis. Click on File tab and select Options to open Excel Options dialogue box. Click on Add-Ins and then click on Go button at the bottom to open Add-Ins dialog box. Check the box before Analysis ToolPak before clicking on the OK button. Now you will be able to see Data Analysis in Analysis group under Data tab.Now let’s use the same sample data for RSQ to illustrate how to do the regression using Excel. Right click on source data -> Click on Data tab -> Click on Data Analysis in Analysis group -> Select Regression in Data Analysis dialogue box and click on OK -> Fill Regression dialog box as shown in Figure 11.2. After you click on OK in Regression dialog box, Excel will return a report shown in Figure 11.3. The R-square in cell F5 is 0.9847. It is the same with that for RSQ. Both Regression analysis and RSQ tells that this is a good fit. The difference is that Regression also allows you to check if the results are reliable or statistically significant. Look at cell J12. The value is less than 0.05. It implies that the results are reliable. Coefficients in cells F17 and F18 gives you an equation Sales = 408.19 + 3.7901 * Advertising cost. This is also in consistent with that for RSQ. The residual output tells you how far away the actual data points (range C2:C8) are from the predicted data points (range F25:F31).
Excel solver is one of most useful feature which can allow you to find the optimal solution for a problem. I have written 9 articles on this topic recently. These articles show you how to solve different kinds of real-life problems using a lot of examples. Below table can direct you to those article after you click on titles. They are arranged in ascending order regarding the extent of difficult. Therefore, it’s better for you to read them in sequence if you are not familiar with Excel Solver Feature. And the first article will tell you how to activate Excel Solver.
VBA programming is the most important feature in Excel. Management consultants can use VBA to control Printer and print more than 100 files automatically. They can also use VBA to make slides so that slides will change as source data in Excel changes. And VBA can even be applied to download source data for analysis from the internet. In summary, VBA is a very important skill that mastering it can save you a lot of time and improve your efficiency.
Download working file
Download the working file from the link below.