GETPIVOTDATA vs CUBE functions | Extracting data from a PivotTable | Excel Off The Grid

Подписаться 27 тыс.

Просмотров 31 тыс.

50% 1

★ Want to automate Excel? Check out our training academy ★
exceloffthegrid.com/academy
★ Download the example file★
exceloffthegrid.com/getpivotd...
★ About this video ★
We’ve been able to create PivotTables in Excel since the mid-1990s. Ever since then, people have been asking how to extract data from PivotTables using formulas.
In this video I will show you 3 options for this:
1) Standard cell references
2) GETPIVOTDATA
3) CUBE functions
This is not an in-depth look into any one approach but intended to highlight the differences so you can make the best decisions for your scenario.
0:00 Introduction
1:03 Example Data
2:36 Option #1: Standard cell references
4:24 Option #2: GETPIVOTDATA
8:20 Option #3: CUBE functions
16:20 Which is the best option to use?
★ Useful links ★
[1] Tips & Tricks for Writing CUBEVALUE Formulas - Excel Campus:
www.excelcampus.com/cubevalue...
[2] Cube Formulas - The best Excel formulas you're not using - Macrodinary: macrordinary.ca/2020/08/19/cu...
[3] An intro to Cube functions for PowerPivot - BI Gorilla: gorilla.bi/excel/cube-functions/
[4] How to use PivotTable - GetPivotData - Contextures: www.contextures.com/xlpivot06...
[5] Excel GETPIVOTDATA function: Excel Jet: exceljet.net/excel-functions/...
★ Download 30 most useful Excel VBA Macros ebook for FREE ★
exceloffthegrid.com/
★ Where to find Excel Off The Grid ★
Blog: exceloffthegrid.com
Twitter: / exceloffthegrid
#MsExcel

Опубликовано:

2 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 54

@adesojijoshua1758 Год назад

The fact that you explained the three main options available for retrieving information from a PivotTable, made the CUBE functions much easier to understand. Thanks so much Mark.

@ExcelOffTheGrid Год назад

It can get a little complicated, so I’m glad it’s all a bit clearer.

@subbu_ca Год назад

The cubevalue was an eye opener for me. Thanks a lot

@ExcelOffTheGrid Год назад

You are welcome!

@MLFranklin 11 месяцев назад

Very helpful comments on the syntax and making it more independent from the table format. It's something practical that I can use right now.

@ExcelOffTheGrid 11 месяцев назад

Thanks, I’m glad it was helpful.

@wayneedmondson1065 2 года назад

Hi Mark. Very helpful! GETPIVOTDATA and CUBE functions seem complicated at first, but with a little effort, they become more understandable. Thanks for demonstrating. Thumbs up!!

@ExcelOffTheGrid 2 года назад

Very true Wayne. A bit of practice, it starts to make sense.

@kebincui Год назад

Thanks Mark. That Cube function is new to me and really mind-blowing. Your videos are all excellent and well explained. Thank you.👍👍

@ExcelOffTheGrid Год назад

CUBE functions are amazing. They even let you get calculated values out of a Power BI data model.

@teoxengineer 2 года назад

Cube function isn't known and very magic formula to evaluate data. Thank you so much this good video

@ExcelOffTheGrid 2 года назад

Cube functions are some of my favourites. But it normally takes a lots of work (i.e. a data model + DAX) before they can be put to good use.

@davidferrick Год назад

I think Cube is easier than GetPivot. You can get started by converting pivot to OLAP and then the syntax is quite simple from there.

@ankitchopra1286 5 месяцев назад

Awesome Mark

@ExcelOffTheGrid 5 месяцев назад

Glad you enjoyed it 👍

@Muuip 2 года назад

Great presentation! Much appreciated!👍

@ExcelOffTheGrid 2 года назад

Thanks Muuip.

@superyngo 3 месяца назад

Thanks I finally get what cubemember is for. Can you do how slicer works with cubevalue? I got it working but not understand how and why.

@tahirhanif9669 7 месяцев назад

Excellent, thank you

@bbotzong 2 года назад

Nicely done. Thanks!

@ExcelOffTheGrid 2 года назад

Thanks Bill 😊

@arokiarajan1230 2 года назад

Awesome👍 need more automate excel vedio

@JuanNadal Год назад

This was super helpful.. Thank you!!

@ExcelOffTheGrid Год назад

Thank you 👍

@KhurrumIqbal1 9 месяцев назад

You are good

@ExcelOffTheGrid 9 месяцев назад

Thanks 😊

@MohammadRahdar 2 года назад

Well explained, thanks!

@ExcelOffTheGrid 2 года назад

Thanks, I’m glad you found it useful.

@srizki 2 года назад

Wow, Thank you so much, I learnt a lot.

@ExcelOffTheGrid 2 года назад

Good stuff… those CUBE functions are super useful.

@FRANKWHITE1996 Год назад

Great content. Thanks for sharing.

@ExcelOffTheGrid Год назад

You’re welcome. 😀

@pancholitachiu23 Год назад

Gotta love the “it depends”

@ExcelOffTheGrid Год назад

Yes, it always depends. 😀 You can’t provide a solution unless you know the problem.

@database_tips_tricks Год назад

Best excel videos seen

@ExcelOffTheGrid Год назад

Thanks Nigel 👍

@iliesboukhatem7803 Год назад

very useful, thank you

@ExcelOffTheGrid Год назад

You are welcome!

@maximumaverage 11 месяцев назад

The question I had was best option between GetPivotData and Cube tables. For a large FP&A workbook, I can either create a data model / power pivot table reference and link to it via formulas or try implement a cube table instead. What are the pros and cons to each approach? I can make my static outputs dynamic with drop downs/getpivotdata without needing slicers so what advantages do cube functions give me? Will cube tables affect performance speed?

@ExcelOffTheGrid 10 месяцев назад

Power Pivot and CUBE formulas are superior to standard PivotTables. Only use a standard PivotTable for simple one-off analysis. But anything you care about you want to use Power Pivot. The file sizes will be smaller and the calculation engine is more flexible as you can use DAX formulas. However CUBE formulas are single threaded, so might become slower if you use a huge number of them. CUBE formulas can connect directly to a published Power BI dataset, so data doesn’t need to be in Excel. You can use a Slicer directly in a CUBE formula, so there is no issues with that.

@paulmfti Год назад

Good stuff man

@ExcelOffTheGrid Год назад

You're welcome.

@graemegourlay2850 11 месяцев назад

I often use GETPIVOTDATA and from pivot tables generated from the data model. The syntax for referencing fields within the GETPIVOTDATA function then changes. Recommend covering in a future video the format of the argument referencing in this scenario.

@chandramohan1418 2 года назад

PL videos on DAX functions.thank yu

@moribatraore377 7 дней назад

Hello, how I can get the excel sheet please?

@frederic_millenial Год назад

Can we define a name pointing to a column of a table existing in a data model ? Like we would refer a column table existing in a worksheet : “My_Table[Column_A]”

@GeertDelmulle Год назад

Hi Mark, Can you show us how to use CUBE functions to extract from the data model a list of unique row headers like we need in a pivot table? Thanks.

@ExcelOffTheGrid Год назад

This has been on my list for a long time. Maybe I’ll bump it up the list a bit.

@GeertDelmulle Год назад

@@ExcelOffTheGrid Mark, if you know of a PQ connector that can extract data from the data model inside an Excel file, then make that a much higher priority. (I don’t think it exists, though) Anyhow, that’s the wider context to that question. Also, check out EMT 1435 by excelisfun. It gives another perspective on how to access the data model…

@davidferrick Год назад

Pivot AutoFit should be a Regional Setting you can turn off. Very frustrating to have to do this every single time I create a pivot.

@ExcelOffTheGrid Год назад

You can set it as a default for your PC on Excel 2021 and Excel 365. File > Options > Data > Edit Default Layout... > PivotTable Options... Then uncheck AutoFit column widths on update

@brianleewilliams 2 года назад

If I have a product table, can cube functions pull the product description from the same table using the product number as a lookup?

@raymorin.tu01 4 месяца назад

I have the same question as Brian. I my case I want to look up a person's phone number and email address in a table in the 'data model' in Excel (referencing off a customer number). The =CUBE functions only seem capable of counting phone numbers the customer has. (I get a "1" returned instead of the phone number itself.) There appears to be no =CUBE function capable of this. So instead I must load the data to an ordinary table (instead of to the data model) and use =VLOOKUP. Adding a table to a new sheet is suboptimal. If =CUBE formulas cannot pull a data item from the data model in Excel, what formula should Brian and I use?