MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Text in the Values of a Pivot Table


June 24, 2019 - by Bill Jelen

Excel Text in the Values of a Pivot Table. Photo Credit: rawpixel at Unsplash.com

Another amazing use for a measure in a Data Model pivot table is to use the CONCATENATEX function to move text into the values area of a pivot table.

In this data set, there is an original and revised value for each sales rep.

The source data has Region, Market, Rep, Version and Code. The code is text. The version is either Original or Revised.

Insert a pivot table and check the box for Add This Data To The Data Model. Drag Rep to the Rows and Version to Columns.

continue reading »

More Reading


Excel Portable Formulas. Photo Credit: Sheldon Nunes at Unsplash.com

Excel 2019: Portable Formulas »

June 20, 2019 - by Bill Jelen


If you have the full version of Power Pivot, you can use the DAX formula language to create new calculated fields. From the Power Pivot tab in the Ribbon, choose Measures, New Measure.

Compare Budget Versus Actual via Power Pivot. Photo Credit: Michael Longmire at Unsplash.com

Excel 2019: Compare Budget Versus Actual via Power Pivot »

June 19, 2019 - by Bill Jelen


Budgets are done at the top level – revenue by product line by region by month. Actuals accumulate slowly over time – invoice by invoice, line item by line item. Comparing the small Budget file to the voluminous Actual data has been a pain forever.

read more articles »


IMA Data Analytics Leveraging Excel

Featured Products


Power Excel With MrExcel - 2017 Edition

Power Excel With MrExcel - 2017 Edition »


This is the print book edition of "Power Excel with MrExcel - 2017 Edition" - by Bill Jelen. Master Pivot Tables, Subtotals, Visualizations, VLOOKUP, Power BI and Data Analysis.

Excel Subtotals Straight to the Point

Excel Subtotals Straight to the Point »


I used to use the Subtotals feature daily after downloading mainframe data. This book covers every tip and trick for using Subtotals.

The Absolute Guide to Dashboarding & Reporting with Power BI

The Absolute Guide to Dashboarding & Reporting with Power BI »


Learn Power BI Dashboarding Techniques by Watching A Pro!

Excel JavaScript UDFs Straight to the Point

Excel JavaScript UDFs Straight to the Point »


JavaScript custom functions - UDFs can be used like any other native functions or UDFs in Excel. This book shows the process of creating JavaScript UDFs in Excel Developer Preview for Office Insider program subscribers.

MrExcel LIVe

MrExcel LIVe »


A book for people who use Excel 40+ hours per week. Illustrated in full color.

MrExcel LX – The Holy Grail of Excel Tips

MrExcel LX – The Holy Grail of Excel Tips »


A book for people who use Excel 40+ hours per week. Illustrated in full color.

see more products »