- How To Do A Pivot Chart In Excel For Mac Osx
- How To Do A Pivot Chart In Excel For Mac Shortcut
- How To Create A Pivot Chart In Excel 2016 Mac
Secondly, Microsoft developed a feature for Excel that Lotus didn’t provide in 1-2-3, namely PivotTables. The PivotTables feature, exclusive to Excel, was deemed so staggeringly useful that people were willing to learn an entire new software package (Excel) rather than stick with a program (1-2-3) that didn’t have it. WATCH PART 2: file used in the video: this video s. Filtering pivot charts. When you graph the data in a pivot table using a typical chart type, such as column, bar, or line, that uses both an x– and y-axis, the Row labels in the pivot table appear along the x– (or category) axis at the bottom of the chart and the Column labels in the pivot table become the data series that are delineated in the chart’s legend. However, Excel for the Mac has an additional bug that renders any pivot chart created on a PC that includes a report filter utterly useless. You can see the options in the drop-down filter and even select them. In fact, the pivot table (that’s created with every pivot chart) updates. However, your pivot chart. Pivot Table with Multiple Sheets (Table of Content) Pivot Table with Multiple Sheets; How to Create Pivot Table from Multiple Sheets in Excel? Pivot Table with Multiple Sheets. Most of you know about the pivot tables, it is a very useful tool to get all your data consolidated in one table and get the figures for particular things as required.
A reader emailed to ask whether you could make a dynamic chart using OFFSET-function-based Names in Excel 2016 for Mac. Good question, and I wondered if he’d encountered some unexpected problem, perhaps a bug, in Mac Excel. So I dusted off my MacBook Pro and tried it out.
Bottom line: There are several ways to make dynamic charts in Excel, and there seems to be no difference other than cosmetic in how they work between different versions of Excel, and between operating system. The protocols are the same for Mac Excel and Windows Excel, and perhaps it’s time for a quick review. This exercise was done completely in Mac Excel 2016, and other than not knowing a few of the shortcuts I use everyday, it was not very different from working in Windows Excel 2016.
Dynamic Charts in Excel
It’s pretty easy to set up data and create a chart in Excel. But once you’ve created a chart, it keeps plotting data from the same cells. If the data in the cells changes, so does the chart, but if the data extends to more cells (or shrinks to fewer cells), the chart doesn’t seem to notice.
There are a couple ways to create charts that will grow with your data. The easiest way is to use Tables as the chart source data. A bit more complicated is to use Excel’s Names to define the series data for your chart. Using Names can lead to more flexibility in defining the data in your charts. I’ll describe how to make dynamic charts using Tables, using Names, and using Names in a more flexible way.
Dynamic Charts Using Tables
The easiest way to make a chart’s contents reflect the size of a range of data is to put the data into a Table.
Tables made their appearance in Excel 2003, and were called “Lists”. These lists were a more structured container for your data, with a database structure of fields (columns) and records (rows), field headers (column headers) and filtering tools. You could sort and filter your data range easily, and any formula that used a whole column of your List updated to automatically keep using that whole column of the list. Lists became the favored source data for charts and also for pivot tables.
In Excel 2007, Lists became known as “Tables”, and their capabilities have been expanded in every version since.
The screenshot below shows the same data and chart as above, but the data is now in a Table.
To get your data into a table, you select it (or select one cell and let Excel figure out how far it stretches), and on the Insert tab of the ribbon, click Table. Excel asks if your table has headers, then it applies a Table style (the yellow style is shown below), it adds AutoFilter dropdown arrows to the field headers, and it puts a small backwards “L” bracket at the bottom right corner of the table.
You can change the size of the Table by clicking and dragging the bracket at the bottom left corner of the Table. If you type or paste data directly below the Table, the Table will automatically expand to include this new data. And a chart that uses all rows of the existing Table will expand accordingly.
If you type or paste data directly to the right of the Table, the Table will also automatically expand to include this new data. A chart that uses all of the existing Table will expand accordingly.
This little trick of adding a new series if the data expands accordingly is nice, but it requires that the chart already contain all of the Table’s data.
Names (a/k/a “Defined Names”, “Named Ranges”, etc.)
A Name is what Excel calls a variable that resides in a worksheet or a workbook.
Names are often assigned to cells or ranges; for example, you might place a sales tax rate into a cell and name the cell SalesTax, and subsequently use the cell’s name rather than its address in a formula. Because of this Names have been nicknamed “Named Ranges”.
However, the definition of the name includes a formula. If my sales tax rate was stored in cell A1, then my Name SalesTax would have a definition of “=A1”. Because of this, John Walkenbach proposed that Names should be called “Named Formulas”, but he’s smarter than the rest of us, so his suggestion didn’t stick.
We can use Names in our charts, but we need a distinct name for each dynamic range that the chart will need. We’ll need one Name for the X values if the series use the same X values range, and we’ll need one Name for the Y values of each series. In our sample, we will need three Names. I’ll call them XValues, Y1Values, and Y2Values, and I will define them as follows:
This OFFSET formula uses cell A1 of worksheet Names as a starting point, offsets the range down by one row and right by zero rows, then makes it as many rows tall as the number of alphanumeric cells in column A minus one (we don’t want to include the “Category” label), and one column wide.
So starting with cell A1, our range begins in cell B1, and is 6 rows tall and one column wide; our final range is A2:A7. It’s easy to see that adding another value into cell A8 will expand this range to A2:A8. However, if we add a value in cell A57, it will also expand our range to A2:A8, so we need to make sure the rows below our data are kept blank.
The other two definitions are easier:
We’ve already figured out how large each range needs to be, since the X and Y values have the same number of cells, so both of these OFFSET formulas start with the first name XValues as an anchor, and offset no rows down but one or two columns to the right. If we don’t specify sizes, then the new Names will define ranges the same size as the anchor.
Okay, that’s how to build a formula definition for a Name. Let’s actually create a Name.
On the Formulas tab of the ribbon, click the Define Name dropdown, and select Define Name… This pops up the Define Name dialog, shown below for the Mac. The Windows dialog is a bit more extensive, and Windows Excel has a much better Names Manager (this dialog happens to serve as the Mac’s Names Manager). For a truly powerful Name Manager, you should try out the free Name Manager add-in at the website of my colleague, Excel MVP Jan Karel Pieterse.
If you’ve selected data before opening the dialog, Excel tries to guess how you want to name data based on labels in the top row and left column of the selection. But I’ve cleared all of this so we’re starting fresh.
Here I’ve typed the name of the Name. Note that I’ve included the worksheet name and exclamation point, which means the Name will be “in scope” (i.e., available) for the worksheet “Names”. Otherwise it would be “in scope” for the entire workbook.
Then enter the formula where it says “Select the range of cells”. You can enter any formula that refers to cells, or a formula that calculates a value, or a constant value. I don’t capitalize my function names when I enter them; that way, if there’s an error, Excel won’t capitalize a bad function name. A misspelled keyword is easier to recognize if it is not capitalized (“offfset” vs. “OFFFSET”).
Click the OK button to add the Name and exit the dialog, or click the Plus icon to add the name and keep the dialog open.
The name is listed in the box at the left; the worksheet name is listed as well to remind us that the scope of the Name is limited to that worksheet. The sheet name is removed from the name in the top right box.
You can make sure the name refers to the intended range if you click in the box with the formula defining the Name. With the cursor in the formula, the range A2:A7 is highlighted in the worksheet. Perfect.
Now enter the name and formula for the next Name, and don’t forget to include the worksheet name. Cctv software for mac free.
Click the plus icon to add the name, and click in the formula to make sure that the Name references the desired range, B2:B7. Check.
Enter the name and formula for the last Name, remembering to include the worksheet name. Click the plus icon, and check that the formula refers to C2:C7.
Whew! Now we’re finally ready to make our dynamic chart.
Dynamic Charts Using Names
Every chart series has a formula that defines the data in the chart. The blue series in the static chart below is
This means it uses cell B1 of the sheet Names for the series name (“Alpha”), A2:A7 for the X values, B2:B7 for the Y values, and it’s the first series in the chart. The formula for the orange series is
We can use the Select Data dialog to modify these, but it’s easiest to simply edit the formula directly.
Select the blue series of the dynamic chart, and observe the formula in the Formula Bar. It probably looks like the first SERIES formula above (I invariably start with a static chart of the data I want to plot dynamically). Edit the formula to read as follows, and press Enter.
If Excel doesn’t like the new formula, make sure you’ve spelled the Names correctly.
Similarly edit the formula for the orange series to read
At first the two charts look the same.
When we select the static chart, we can see the chart’s source data highlighted in the worksheet.
We see the same data highlighted when the dynamic chart is selected. It’s convenient that Excel is smart enough to highlight the chart data even if it is defined by dynamic Names. I’ll select the dynamic charts in the rest of this tutorial to show the range included in these charts.
Now let’s extend the data by a couple of rows. The static chart isn’t clever enough to notice, but the dynamic chart keeps up nicely, illustrated by the highlighted data in the worksheet.
If we extend the data by a new column, the static chart doesn’t change, and the dynamic chart doesn’t add a series to represent the new data.
I’ve added a third chart which shows the new data. I had to add an extra Name to the worksheet:
and then I had to add a third series to the chart with the following SERIES formula:
My new chart contains all the data, though I had to include it in the chart manually.
Before Excel 2003, the only way to get a dynamic chart was by using Names. It’s a lot of work, and hardly seems worthwhile if using Tables is so easy. But if we know how to make a chart using Names, we can make a dynamic chart that’s more complicated than just expanding to add a row.
Dynamic Charts Using Complicated Names
How about a chart that doesn’t show all the data, but only the last several points. This might be useful if you want to show the last six months of sales, or high temperature for the previous week.
We’ll make a dynamic chart that plots the last five values. The setup is almost identical to the previous dynamic chart, but our definition of XValues will change.
This OFFSET formula uses cell A1 of worksheet Names as a starting point, offsets the range down by the number of alphanumeric cells in column A minus one, and right by zero rows. This means now we’re starting at cell A7 instead of A2. Then we make the range -5 rows high, meaning we count upward, and one column wide. Our new X values range should be A3:A7. After entering the new name as before, check to make sure the correct range is highlighted.
Edit the SERIES formulas of your dynamic chart as we did above. We see that our static chart shows all six points of the data, but the dynamic chart shows only the last five points, categories B through F.
Let’s add a couple rows of data. The static chart is, well, static, but our dynamic chart shows the new last five points, categories D through H.
Summary
How To Do A Pivot Chart In Excel For Mac Osx
Dynamic charts can easily be created in Excel using data ranges from Tables.
With a bit more work dynamic charts can be created using skillfully defined Names. These charts can be more flexible than Table-based dynamic charts, depending on the formula skills of the Excel user. There are a few gotchas that I didn’t mention: some Name definitions seem like they should work, for example, but Excel charts won’t recognize certain functions. Also, some Name names may cause problems, especially those beginning with the letter “c”; you can’t enter them into the SERIES formula, but you can use them in the Select Data dialog.
July 10, 2018 - by Bill Jelen
Can you build a pivot table with text in the values area? Susan from Melbourne Florida has a text field and wants to see the before and after of that text.
Traditionally, you can not move a text field in to the values area of a pivot table.
However, if you use the Data Model, you can write a new calculated field in the DAX language that will show text as the result.
- Make sure your data is Formatted as Table by choosing one cell in the data and pressing Ctrl + T. Make a note of the table name as shown on the Table Tools tab of the ribbon.
Insert, Pivot Table. Choose 'Add This Data to the Data Model' while creating the pivot table.
Drag fields to the Rows and Columns of the pivot table.
To add the text to the values area, you have to create a new special kind of calculated field called a Measure. Look at the top of the Pivot Table Fields list for the table name. Right-click the table name and choose Add Measure.
Note
If you do not have this option, then you did not choose Add This Data To The Data Model in step 2.
- Type a field name of ListOfCodes
- The formula is
=CONCATENATEX(Table1,Table1[Code],', ') - Leave the format as General
Click Check DAX Formula to make sure there are no typos
Click OK. The new measure will appear in the field list.
When you drag ListOfCodes to the Values area, you will see a list of codes for each cell in the values area.
Note
It is probably important to remove grand totals from this pivot table. Otherwise, the intersection of the Grand Total Row and Grand Total Column will list all of the codes in the table separated by columns. You can go to PivotTable Tools Design, Grand Totals, Off for Rows and Columns.
Amazingly, as you re-arrange the fields in Rows & Columns, the CONCATENATEX updates.
Bottom line: There are several ways to make dynamic charts in Excel, and there seems to be no difference other than cosmetic in how they work between different versions of Excel, and between operating system. The protocols are the same for Mac Excel and Windows Excel, and perhaps it’s time for a quick review. This exercise was done completely in Mac Excel 2016, and other than not knowing a few of the shortcuts I use everyday, it was not very different from working in Windows Excel 2016.
Dynamic Charts in Excel
It’s pretty easy to set up data and create a chart in Excel. But once you’ve created a chart, it keeps plotting data from the same cells. If the data in the cells changes, so does the chart, but if the data extends to more cells (or shrinks to fewer cells), the chart doesn’t seem to notice.
There are a couple ways to create charts that will grow with your data. The easiest way is to use Tables as the chart source data. A bit more complicated is to use Excel’s Names to define the series data for your chart. Using Names can lead to more flexibility in defining the data in your charts. I’ll describe how to make dynamic charts using Tables, using Names, and using Names in a more flexible way.
Dynamic Charts Using Tables
The easiest way to make a chart’s contents reflect the size of a range of data is to put the data into a Table.
Tables made their appearance in Excel 2003, and were called “Lists”. These lists were a more structured container for your data, with a database structure of fields (columns) and records (rows), field headers (column headers) and filtering tools. You could sort and filter your data range easily, and any formula that used a whole column of your List updated to automatically keep using that whole column of the list. Lists became the favored source data for charts and also for pivot tables.
In Excel 2007, Lists became known as “Tables”, and their capabilities have been expanded in every version since.
The screenshot below shows the same data and chart as above, but the data is now in a Table.
To get your data into a table, you select it (or select one cell and let Excel figure out how far it stretches), and on the Insert tab of the ribbon, click Table. Excel asks if your table has headers, then it applies a Table style (the yellow style is shown below), it adds AutoFilter dropdown arrows to the field headers, and it puts a small backwards “L” bracket at the bottom right corner of the table.
You can change the size of the Table by clicking and dragging the bracket at the bottom left corner of the Table. If you type or paste data directly below the Table, the Table will automatically expand to include this new data. And a chart that uses all rows of the existing Table will expand accordingly.
If you type or paste data directly to the right of the Table, the Table will also automatically expand to include this new data. A chart that uses all of the existing Table will expand accordingly.
This little trick of adding a new series if the data expands accordingly is nice, but it requires that the chart already contain all of the Table’s data.
Names (a/k/a “Defined Names”, “Named Ranges”, etc.)
A Name is what Excel calls a variable that resides in a worksheet or a workbook.
Names are often assigned to cells or ranges; for example, you might place a sales tax rate into a cell and name the cell SalesTax, and subsequently use the cell’s name rather than its address in a formula. Because of this Names have been nicknamed “Named Ranges”.
However, the definition of the name includes a formula. If my sales tax rate was stored in cell A1, then my Name SalesTax would have a definition of “=A1”. Because of this, John Walkenbach proposed that Names should be called “Named Formulas”, but he’s smarter than the rest of us, so his suggestion didn’t stick.
We can use Names in our charts, but we need a distinct name for each dynamic range that the chart will need. We’ll need one Name for the X values if the series use the same X values range, and we’ll need one Name for the Y values of each series. In our sample, we will need three Names. I’ll call them XValues, Y1Values, and Y2Values, and I will define them as follows:
This OFFSET formula uses cell A1 of worksheet Names as a starting point, offsets the range down by one row and right by zero rows, then makes it as many rows tall as the number of alphanumeric cells in column A minus one (we don’t want to include the “Category” label), and one column wide.
So starting with cell A1, our range begins in cell B1, and is 6 rows tall and one column wide; our final range is A2:A7. It’s easy to see that adding another value into cell A8 will expand this range to A2:A8. However, if we add a value in cell A57, it will also expand our range to A2:A8, so we need to make sure the rows below our data are kept blank.
The other two definitions are easier:
We’ve already figured out how large each range needs to be, since the X and Y values have the same number of cells, so both of these OFFSET formulas start with the first name XValues as an anchor, and offset no rows down but one or two columns to the right. If we don’t specify sizes, then the new Names will define ranges the same size as the anchor.
Okay, that’s how to build a formula definition for a Name. Let’s actually create a Name.
On the Formulas tab of the ribbon, click the Define Name dropdown, and select Define Name… This pops up the Define Name dialog, shown below for the Mac. The Windows dialog is a bit more extensive, and Windows Excel has a much better Names Manager (this dialog happens to serve as the Mac’s Names Manager). For a truly powerful Name Manager, you should try out the free Name Manager add-in at the website of my colleague, Excel MVP Jan Karel Pieterse.
If you’ve selected data before opening the dialog, Excel tries to guess how you want to name data based on labels in the top row and left column of the selection. But I’ve cleared all of this so we’re starting fresh.
Here I’ve typed the name of the Name. Note that I’ve included the worksheet name and exclamation point, which means the Name will be “in scope” (i.e., available) for the worksheet “Names”. Otherwise it would be “in scope” for the entire workbook.
Then enter the formula where it says “Select the range of cells”. You can enter any formula that refers to cells, or a formula that calculates a value, or a constant value. I don’t capitalize my function names when I enter them; that way, if there’s an error, Excel won’t capitalize a bad function name. A misspelled keyword is easier to recognize if it is not capitalized (“offfset” vs. “OFFFSET”).
Click the OK button to add the Name and exit the dialog, or click the Plus icon to add the name and keep the dialog open.
The name is listed in the box at the left; the worksheet name is listed as well to remind us that the scope of the Name is limited to that worksheet. The sheet name is removed from the name in the top right box.
You can make sure the name refers to the intended range if you click in the box with the formula defining the Name. With the cursor in the formula, the range A2:A7 is highlighted in the worksheet. Perfect.
Now enter the name and formula for the next Name, and don’t forget to include the worksheet name. Cctv software for mac free.
Click the plus icon to add the name, and click in the formula to make sure that the Name references the desired range, B2:B7. Check.
Enter the name and formula for the last Name, remembering to include the worksheet name. Click the plus icon, and check that the formula refers to C2:C7.
Whew! Now we’re finally ready to make our dynamic chart.
Dynamic Charts Using Names
Every chart series has a formula that defines the data in the chart. The blue series in the static chart below is
This means it uses cell B1 of the sheet Names for the series name (“Alpha”), A2:A7 for the X values, B2:B7 for the Y values, and it’s the first series in the chart. The formula for the orange series is
We can use the Select Data dialog to modify these, but it’s easiest to simply edit the formula directly.
Select the blue series of the dynamic chart, and observe the formula in the Formula Bar. It probably looks like the first SERIES formula above (I invariably start with a static chart of the data I want to plot dynamically). Edit the formula to read as follows, and press Enter.
If Excel doesn’t like the new formula, make sure you’ve spelled the Names correctly.
Similarly edit the formula for the orange series to read
At first the two charts look the same.
When we select the static chart, we can see the chart’s source data highlighted in the worksheet.
We see the same data highlighted when the dynamic chart is selected. It’s convenient that Excel is smart enough to highlight the chart data even if it is defined by dynamic Names. I’ll select the dynamic charts in the rest of this tutorial to show the range included in these charts.
Now let’s extend the data by a couple of rows. The static chart isn’t clever enough to notice, but the dynamic chart keeps up nicely, illustrated by the highlighted data in the worksheet.
If we extend the data by a new column, the static chart doesn’t change, and the dynamic chart doesn’t add a series to represent the new data.
I’ve added a third chart which shows the new data. I had to add an extra Name to the worksheet:
and then I had to add a third series to the chart with the following SERIES formula:
My new chart contains all the data, though I had to include it in the chart manually.
Before Excel 2003, the only way to get a dynamic chart was by using Names. It’s a lot of work, and hardly seems worthwhile if using Tables is so easy. But if we know how to make a chart using Names, we can make a dynamic chart that’s more complicated than just expanding to add a row.
Dynamic Charts Using Complicated Names
How about a chart that doesn’t show all the data, but only the last several points. This might be useful if you want to show the last six months of sales, or high temperature for the previous week.
We’ll make a dynamic chart that plots the last five values. The setup is almost identical to the previous dynamic chart, but our definition of XValues will change.
This OFFSET formula uses cell A1 of worksheet Names as a starting point, offsets the range down by the number of alphanumeric cells in column A minus one, and right by zero rows. This means now we’re starting at cell A7 instead of A2. Then we make the range -5 rows high, meaning we count upward, and one column wide. Our new X values range should be A3:A7. After entering the new name as before, check to make sure the correct range is highlighted.
Edit the SERIES formulas of your dynamic chart as we did above. We see that our static chart shows all six points of the data, but the dynamic chart shows only the last five points, categories B through F.
Let’s add a couple rows of data. The static chart is, well, static, but our dynamic chart shows the new last five points, categories D through H.
Summary
How To Do A Pivot Chart In Excel For Mac Osx
Dynamic charts can easily be created in Excel using data ranges from Tables.
With a bit more work dynamic charts can be created using skillfully defined Names. These charts can be more flexible than Table-based dynamic charts, depending on the formula skills of the Excel user. There are a few gotchas that I didn’t mention: some Name definitions seem like they should work, for example, but Excel charts won’t recognize certain functions. Also, some Name names may cause problems, especially those beginning with the letter “c”; you can’t enter them into the SERIES formula, but you can use them in the Select Data dialog.
July 10, 2018 - by Bill Jelen
Can you build a pivot table with text in the values area? Susan from Melbourne Florida has a text field and wants to see the before and after of that text.
Traditionally, you can not move a text field in to the values area of a pivot table.
However, if you use the Data Model, you can write a new calculated field in the DAX language that will show text as the result.
- Make sure your data is Formatted as Table by choosing one cell in the data and pressing Ctrl + T. Make a note of the table name as shown on the Table Tools tab of the ribbon.
Insert, Pivot Table. Choose 'Add This Data to the Data Model' while creating the pivot table.
Drag fields to the Rows and Columns of the pivot table.
To add the text to the values area, you have to create a new special kind of calculated field called a Measure. Look at the top of the Pivot Table Fields list for the table name. Right-click the table name and choose Add Measure.
Note
If you do not have this option, then you did not choose Add This Data To The Data Model in step 2.
- Type a field name of ListOfCodes
- The formula is
=CONCATENATEX(Table1,Table1[Code],', ') - Leave the format as General
Click Check DAX Formula to make sure there are no typos
Click OK. The new measure will appear in the field list.
When you drag ListOfCodes to the Values area, you will see a list of codes for each cell in the values area.
Note
It is probably important to remove grand totals from this pivot table. Otherwise, the intersection of the Grand Total Row and Grand Total Column will list all of the codes in the table separated by columns. You can go to PivotTable Tools Design, Grand Totals, Off for Rows and Columns.
Amazingly, as you re-arrange the fields in Rows & Columns, the CONCATENATEX updates.
After using this method for a few weeks, I and others noticed that in some data sets, the concatenated values would contain duplicates, such as the Fig, Fig data shown in the East region above. Thanks to Rob Collie at PowerPivotPro.com, you can remove the duplicates by changing
=CONCATENATEX(Table1, Table1[Code], ”, “)
to
=CONCATENATEX(Values(Table1[Code]), Table1[Code], ', ')
The VALUES function returns a new table with the unique values found in a column.
Watch Video
Download Excel File
To download the excel file: pivot-table-with-text-in-values-area.xlsx
The DAX formula language allows many new calculations in a pivot table.
How To Do A Pivot Chart In Excel For Mac Shortcut
Excel Thought Of the Day
I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:
How To Create A Pivot Chart In Excel 2016 Mac
'Excel conquers all'
