Posted on

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.

Excel Windows and MAC differences

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.

Pivot table error message 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.

VBA Events in MAC pop error message

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

Posted on

XLOOKUP: A Brand-new formula in Office365

The wait is over!! Finally, Microsoft has released an update to Office365 with a brand-new formula “XLOOKUP”.

XLOOKUP has the following advantages, compared to its predecessors.

  • Can lookup on both left and right directions.
  • Exact match is default.
  • Can return FIRST or LAST match.
  • XLOOKUP also replaces VLOOKUP & HLOOKUP.

Earlier, INDEX-MATCH formula combination was used to lookup values where HLOOKUP & VLOOKUP fell short. Now, XLOOKUP can replace all other functions. It is a robust and easy to use formula which can lookup values from right to left and vice versa.

Syntax:

=XLOOKUP(lookup_value,lookup_array,return_array,[match_mode],[search_mode])

Explanation

Let us consider the below example to get deep insight of the formula. Our agenda is to fill column D of Table1 (Salary) using XLOOKUP

Two distinct tables for Xlookup

Lookup_value: Exactly same as VLOOKUP. In this scenario, let us consider Column B of Table1 as our lookup value.

Lookup_Array: Typically, the destination range/column where XLOOKUP will look for the match. Column H of Table2 would be our lookup array.

Return_Array: This argument is asking for the return value. In this case, we want salary column to be filled so we would consider Column G of Table2 as the return value.

[match_mode]: This is an optional argument in XLOOKUP formula. It can be any one of the following. By default, it is “Exact Match”.

  • 0 – Exact match
  • -1 – Exact match or next smaller item
  • 1 – Exact match or next larger item
  • 2 – Wildcard character match.

[Search_mode]: It can be either 1 or -1. “1” is to search from first to last and “-1” is from last to first.

Let us write the below formula in cell “D3” of table1 and the output looks as below.

XLOOKUP($B$3:$B$9,$H$3:$H$9,$G$3:$G$9,0,1)

Xlookup output