As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013 and later. Much of the content will be a result of my daily interactions with business users and other BI devs. In order to not forget what I learn or discover, I write it down … here. I hope you too will discover something new you can use. Enjoy!
Formatting In Cells with the TEXT Function
This is a shorter tip. Sometimes the data you use in a cell needs to be formatted. This is particularly helpful when coupled with the CUBEVALUE function from the previous tip. The TEXT function allows you to format values to the pattern you want. Here are some of examples of using the TEXT function.
- Number formatting – 1000 to 1,000
- =TEXT(1000,”#,#”)
- Number formatting with a decimal – 10.1 to 10.10
- =TEXT(10.1,”#,#.00″)
- Date formatting – 10/1/2014 to 2014-Oct
- =TEXT(“10/01/2014″,”YYYY-MMM”)
As you can see, the standard Microsoft formatting can be applied to values. Here is what it would look like when used with the CUBEVALUE function.
=TEXT(CUBEVALUE(“ThisWorkbookDataModel”,$B$4,$B8,G$5,Slicer_Poll_Has_Submissions), “#,#.0#”)
This function will not work in the pivot tables themselves because cell formulas cannot be created in pivot tables. However, when you decompose a pivot table, you can use the CUBEVALUE and CUBEMEMBER functions as values to be formatted.
One caveat is that the TEXT function returns the value as a string or text data type in Excel. That means this function is best used to create headers and text with values in it. If you wanted to create a label plus a value in a field for a list of various metrics, you can concatenate the text with the resulting TEXT function.
=”Average Poll Count for Men: ” & TEXT(CUBEVALUE(“ThisWorkbookDataModel”,$B$4,$B8,G$5,Slicer_Poll_Has_Submissions), “#,#.0#”)
This would return a value such as “Average Poll Count for Men: 8.0” in the cell.
While a simple tip, it can add significantly to the look and feel of your Excel based dashboards.
