Video – Conditional Formatting in Excel

Hello all. Here is the video tutorial about conditional formatting in excel.

I hope you like this video. Please help in sharing the content. Thanks for watching.

Video – Lookup functions in Excel covering Vlookup, Hlookup and Xlookup

Hello all. In this video tutorial, we will see the lookup functions in excel.

Thanks for watching. Please help by subscribing and sharing the content.

Video – How to import & interpret .CSV file with Power Query

Hello all. In this video, we shall see the use of power query to import and interpret a .csv file.

I hope you like this video. Please help by sharing our content. Thanks for watching!

Video – Count function in Excel

Hello all. In this video tutorial, we will be looking at the count function. We will be covering count, counta, countblank, countif and countifs functions.

I hope you liked this video. Please help by sharing our content. Thanks for watching.

GETPIVOTDATA function in Excel

Hello all. Another short section regarding getpivotdata function in excel. Getpivotdata function uses / queries the existing Pivot table and returns data based on specified conditions as arguments.

Syntax

=getpivotdata(data_field, pivot_table, [field1,item1],..)

Here, data_field is the name of the value field to be queried. Pivot_table is the reference to any cell in the pivot table. Field1&Item1 has to be provided as pair which is the condition for the data that has to be queried.

Working Example

In the below example, we have sales data for different cars and created a simple pivot table. Let’s query the pivot table to identify North (K3) region sales for Ciaz (I6)

The above example retrieves from the field “Test” which is I2, then looks for Region as K3 (North) and looks for Product as I6 (Ciaz). The result is 315255.

Let’s use the same dataset and use the formula, but this time without any optional argument.

Here, the datafield can be any value within the pivot table. pivot_table has to be either the row header (car names) or the region header(north,south,west,east). The function would return the total of the pivot_table value.

I hope you like this short tutorial on getpivotdata function. Thanks for reading!

FormulaText function in Excel

Hello all. In this short section, let’s cover the formulatext function in excel. Formulatext field would return the formula used in the reference cell as a text value.

Syntax

=Formulatext(ref)

Here, ref is any cell reference.

Working Example

In the below example, I have used an offset function to return the ops metrics for Feb month. The formula was used in I4.

If we need to return the formula used as text value, we use the =formulatext(I4) formula.

You may ask a better work example to understand why we use this. let’s look at a more comprehensive example. In the below example, we have a set of values in H2:H18. We don’t know what these values are as they don’t have any description.

Let’s use the formula text to understand what formula is used which will help us understand what these values are

With the formula used in the cell, we can determine what the values are. For example, H6 uses xlookup function to return the month name which has the maximum ticket count. We wouldn’t have known this without seeing the formula when the description of the value was not available.

Did you notice, we have “#NA” error though we had values? Please note the formula used in H7 – the if function will check all the values in B2:B13 to check if they are above or below the yearly average and return the appropriate text result. The function would return 11 values as we are checking a range – only H7 has the formula but values in H8:H18 are the output. When there is no formula but just an output, the formulatext function would just return “#NA” error.

You can trap the “#NA” error with the use of IFERROR function and have a custom message. Like below:

I hope you like this short tutorial. Please help us with your valuable feedback. Thanks for reading!