Excel Functions Archives - Your Excel Partners

December 5, 2019January 9, 2020

MS Excel for Windows Vs Mac: Who wins the race?

Excel is one of the widely used spreadsheet applications created by Microsoft for Windows, MacOS, Android and iOS. In this article, we will be focusing on some of the major differences between Excel for Windows & Mac.

Power Pivot & Power Charts

The below table clearly shows the major differences in windows and MacOS versions of Excel.

Static Charts can be depicted in Mac, but they are not interactive like Windows excel charts and they don’t update upon changing the source pivot table. Any pivot table having source data based on Excel data model will be unfilterable if the spreadsheet is opened by a Mac user. The following image shows an error message while opening a file in MacOS.

VBA Editor User Interface

The VBA editor on Excel for Mac 2016 is in a sorry state compared to its windows counterpart.

‘Properties’ window is missing
Developing a ‘User form’ in Excel for Mac by using design mode would be a tedious job compared to Excel for Windows

Importing VBA collections would be an added disadvantage

VBA Workbook and Worksheet events

If you are someone who uses worksheet events of VBA often, then you would be disappointed as there are no Workbook & Worksheet VBA events in Excel MacOS. Thus, you should rely completely on VBA modules only.

Default file location settings and Autosave workbooks

The windows version of excel enables you to set a default location for saving files. This setting is not available in Mac version of Excel
“AutoSave” is one of time saving features which automatically saves your workbook as a draft and enables you to retrieve older version of the file even if you didn’t save your changes. This feature too is no available in Mac version of Excel

Find and Replace formats

In windows, find and replace dialog has an option to find cells based on their formats (Say, background color) and it would replace the found cells of the same background. This is not available in Excel for Mac.

ActiveX Controls

ActiveX controls are intended to be used with VBA programming, but this feature is not available in Mac version of excel. Thus, Excel VBA pros are restricted to use Form controls in excel MacOS.

Relative references

Irrespective of operating system, relative references are very useful in creating dynamic range while recording macro. Windows allows user to record macros in both absolute and relative references, but Mac doesn’t have relative reference feature while recording the macro.

Check out our Tableau pages

November 19, 2019January 9, 2020

How to create multiple dependent drop-downs using array formulas in Excel?

Drop-down lists are a very important part of any dashboard or data entry form. In this article, we’ll learn how to create multiple dependent drop-downs using array formulas. What I mean by multiple dependent drop-downs is: When selecting an item from the first drop-down, the following drop-downs would show only relevant items based on your selection.

Creating dependent drop-down lists in excel could be easy if done using VBA, but it becomes trickier to do it with excel formulas alone. For this, we can use ‘Array formulas’. An array is a collection of one or more items. Likewise, an array formula can return one or more items as result. Array formulas must be entered in a cell by pressing Ctrl + Shift + Enter. In the formula bar, you can see any array formula wrapped inside curly braces {}. For example, {SUM(A1:A5)}

We have sample data as follows:

Using the above data, we want to create dependent drop-downs like shown below. To create these drop-down lists, I will be using two sheets: “Sample Data” & “Temp”.

Creating a list with unique values for first drop-down

In Column A of ‘Sample Data’ , you can see there are repeated values. We need to eliminate them before we can show it in our first drop-down . There are many ways to make a list unique. One option that is often used is ‘Remove duplicates’ feature from ‘Data’ tab of Excel’s menu ribbon. But using that is a manual task and must be repeated, when new data would be added. Hence, it is not preferred. Another option to get the unique list would be to use array formulas. The below formula can be used to get the unique values from column A of ‘Sample Data’.

Write the below formula in the “A2” cell of “Temp” sheet and remember to press Ctrl + Shift + Enter! Drag the formula till “A11” cell of “Temp” sheet.

IFERROR( INDEX( SampleData!$A$3:$A$11,MATCH( 0, COUNTIF( $A$1:A1,SampleData!$A$3:$A$11), 0) ) ,””)

COUNTIF function would return the number of occurrences of the in the sample data range
MATCH function will look-up for”0” in the output of COUNTIF and returns the row number which has the next unique brand name
INDEX function picks up & shows the text of the next unique brand name from the respective row number given by MATCH
If MATCH does find anymore unique names on the sample data range, then it’ll return #N/A error instead of row number, which would be handled by IFERROR function

Thus, unique list of values for column A of Sample Data would be obtained in “Temp” sheet.

First dynamic range drop-down

To populate the obtained unique values into a drop-down list, we’d use the data validation feature from ‘Data’ tab of Excel’s menu ribbon. Since we don’t know how many unique brand names could be there at any point in time, we cannot use a fixed range to populate the drop-down. We must use a dynamic range to populate the drop-down list.

In cell “A16” of “Sample Data” sheet, write the below formula in the Data Validation window and click OK as shown in the below Figure to get the dynamic drop-down list as shown below.

OFFSET(Temp!$A$2,,,COUNTIF(Temp!$A$2:$A$30,”?*”),)

COUNTIF function would return the number of rows with brand names
OFFSET function would select the number of rows as given by COUNTIF from A2 and populate the dynamic drop-down list

Second dependent dynamic range drop-down

Creating the second drop-down involves the same task as we did before. But, the second drop-down should populated on selecting an item from the first drop-down list. Thus, it should check the selected item in the first drop-down (currently in “A16” cell of “Sample Data” sheet) an get only the relevant items. We achieve that by using the “IF” function.

Compared to the previous formula, the change in this array formula is the input array for the INDEX function. In previous formulas, the input was fixed to SampleData!$A$3:$A$11 . Here, the input array in which the unique values must be found should be based on the selection made on the previous drop-down and hence, is dynamically obtained using OFFSET formula.

Write the below formula in cell “B2” of “Temp” sheet, press Ctrl+Shift+Enter & drag the formula till “B11”.

IFERROR(

INDEX(OFFSET(SampleData!$B$2,MATCH(SampleData!$A$16,SampleData!$A$3:$A$11,0),0,COUNTIF(SampleData!$A$3:$A$11,SampleData!$A$16),1),

IF(AND( ISERROR( MATCH( 0, COUNTIF( $B$1:B1, OFFSET( SampleData!$B$2, MATCH( SampleData!$A$16, SampleData!$A$3:$A$11, 0), 0, COUNTIF( SampleData!$A$3:$A$11, SampleData!$A$16), 1) ), 0) )=TRUE, $B1=”HeaderName”), 1,MATCH(0,COUNTIF($B$1:B1, OFFSET( SampleData!$B$2, MATCH( SampleData!$A$16, SampleData!$A$3:$A$11, 0), 0, COUNTIF( SampleData!$A$3:$A$11, SampleData!$A$16), 1) ), 0) )),””)

In Cell “B16” of “Sample Data” sheet, using data validation, enter the below formula and click OK to get the dependent dynamic second drop-down list. Eventually, the output would look as shown below.

OFFSET(Temp!$B$2,,,COUNTIF(Temp!$B$2:$B$30,”?*”),)

Creating dependent drop-down using data validation and array formulas

Multiple dependent dynamic range drop-downs

Any number of further dependent drop-downs we create would follow the same procedure, but for more than two drop-down selections, the above formulas would become very long & difficult to handle. Instead of IF condition, we can use logical AND concept to get the same output. So, we can use the below formula from third drop-down to all further ones.

Write the below formula in cell “C2” of “Temp” sheet to get the unique values for third drop-down.

IFERROR(INDEX(SampleData!$C$3:$C$11,AGGREGATE(15,3,((SampleData!$A$16=SampleData!$A$3:$A$11)* (SampleData!$B$16=SampleData!$B$3:$B$11))/((SampleData!$A$16=SampleData!$A$3:$A$11)* (SampleData!$B$16=SampleData!$B$3:$B$11))*(ROW(SampleData!$B$3:$B$11) – ROW(SampleData!$B$2)), ROWS($B$1:$B1))),””)

AGGREGATE function is used here to get the smallest number in the range. The * operator performs logic AND operation.

In Cell “C16” of “Sample Data” sheet, using data validation, enter the below formula and click OK to get the dependent dynamic third drop-down list.

OFFSET(Temp!$C$2,,,COUNTIF(Temp!$C$2:$C$30,”?*”),)

The final output after we repeat the process for fourth and fifth drop-downs, would look as shown in the below Figure.

Conclusion

Creating drop-down with array formulas is fun. I hope this article would help you in creating more sophisticated dashboards with multiple dependent dynamic drop-downs.

Check out our Tableau Pages

May 20, 2011May 2, 2013

How to Create Dynamic Drop Downs?

Suppose we have items of different criteria like Fruits, Vegetables, Flowers, Animals, and Birds. Each criterion has a list of items as above:

Here is a process to create a drop down:

Drop Down No: 1

First select a cell to add the drop down, for the list of criteria.
Select ‘Data’ from the ribbon.
Select ‘Data validation’ from the menu.

4. Click on the ‘Data validation’ In the validation criteria, select ‘list’ in ‘allow’ box and type in the following formulae in ‘source’:

=$C$2:$G$2

The above formula indicates the range of cells in which the list of criteria is available.

5. When we click on the drop down, we will be able to see the list of criteria.

Drop Down No: 2

Now suppose we want to retrieve ‘Apple’ from criterion ‘Fruits’.

Select the items of ‘Fruits’ and click in the ‘Name box’ which is at the top, left hand side of the sheet.
Type the name of the criterion, ‘Fruits’ which we had selected. Then press enter key.

3. Select a cell to add the drop down, for the list of items.

4. Select ‘Data’ from the ribbon.

5. Select ‘Data validation’ from the menu.

6. In the validation criteria, select ‘list’ in ‘allow’ and type in the following formulae in ‘source’:

=INDIRECT ($D$9)

What this does is this:

=INDIRECT (ref-text, [a1])

Ref_text is a reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string. If ref_text is not a valid cell reference, INDIRECT returns the #REF! Error value.

A1 is a logical value that specifies what type of reference is contained in the cell ref_text.

Here, we are using the formula in drop down. So we need to give the reference cell number (D9).

Now the drop down for list of items is added. Now the required item

can be selected from the list.

In the similar way we can do for all the criteria, and select the required item. In this way we can create dynamic drop downs in excel.

February 27, 2011May 2, 2013

How to Change Text Like YYYYMMDD into YYYY/MM/DD format?

Often, you get text like 19950607 and you want to be able to convert it into text like 1995/06/07. Here’s a formula you can use to convert the text into

a formula. (this assumes the text is in A1)

=DATE(MID(A1,1,4), MID(A1,5,2), MID(A1,7,2))

What this does is this:

Date formula has the following parameters – DATE (Year, Month, Day)

The formula I wrote about simply splits the text

into Year, Month and Day and uses it in the DATE function.

MID function allows you to split text and pick up parts of it. Here’s the syntax

MID(text, start number of digit you want to start from, number of digits)

MID(A1,7,2) – picks up the text 19950607 (i.e. A1) starts from 7 i.e. 0 and gives you two digits from there – 07

—————————————-

| Excel/Financial Modeling | Profitability/Cost Analysis | Dashboard Reporting | Custom Invoice