Most powerful method of data analysis with Excel?

[excelos]

Hello

I am looking to learn to use Excel for data analytics such as data modelling, aggregate calculations, filtering, etc.

Would you suggest to learn cube formulas, MDX formulas, DAX formulas or anything else?

Any insight on the pros and cons of each?

Thank you!

 

[jdellasala]

• Excel 365 Formulas- especially Dynamic Array / one cell report Formulas:
  • PRO: Instantly Updates
  • PRO: Works well with Power BI
  • Sometimes: Difficult formulas, especially if attempting a one cell report (but it keeps getting easier)
  • CON: Manual Updates
  • CON: Cannot handle more then 1,048,576 rows or more than 16,384 columns of data
• Power Query:
  • PRO: Easy to update external data, combine files, output to Tables/Pivot Tables with or without Data Model
  • PRO: Can easily handle millions of records, can be used for/with Power BI
  • Sometimes: Can be tricky to set up a query to be dynamic - that is to anticipate changes to source data
  • CON: Complex input data can be tricky to get into shape for output needs
  • CON: As opposed to Excel which works in Row, Column, and Array contexts, Power Query is ALMOST exclusively Column based.
  • CON: Requires a REFRESH to update data
• DAX
  • PRO: Very powerful periodic calculations can be done lightning fast
  • PRO: Easy to set up Relationships and Measures
  • PRO: Can easily handle millions of records, can be used for/with Power BI
  • CON: DAX is not as friendly as Excel Formulas or the Power Query editor, but not bad either.
  • CON: Coming from Excel, it's hard to change gears and understand that formulas depend on CONTEXT, not rows or columns.
  • CON: Requires a REFRESH to update data

I'm sure there's lots of other thoughts out there, but these are the basic differences between the three methods to work with data. Which you chose is more dependent on the data source, update/change frequency, amount of data, and output.

 

[excelos]

Thank you! What about CUBE and MDX? Also can we use SQL on Excel cell ranges?

 

[jdellasala]

Thank you! What about CUBE and MDX? Also can we use SQL on Excel cell ranges?

CUBE formulas are what are used when converting a Pivot Table to formulas. I don't know what MDX is unless you mean the M formula language which is the language Power Query uses. You can pull data in from SQL databases - it's listed in Data -> Get Data - From Databases.