Save a worksheet

When you have multiple worksheets in an Excel workbook, you might want to save only one worksheet as its own workbook. Use the Move or Copy function to save one worksheet in Excel 2013 or Excel 2016.

Save a single worksheet

1. Right-click the worksheet name tab.

   

2. Click select Move or Copy.

   

3. Click on the Move selected sheets to Book drop-down menu. Select (new book).

   

4. Click OK. Your new workbook opens with your moved worksheet.

   Note: This process moves your worksheet from the original workbook. If you want to keep your worksheet in both workbooks, in the Move or Copy dialog box, select the check box for Create a copy.

   

5. Click File > Save in your new workbook.

   Tip: You can also save your Excel workbook with the keyboard shortcut, Ctrl + S.

Change a sum to an average or other aggregate in power view

In the Field List in Power View, some number fields have a Sigma ∑ symbol next to them. They are aggregates, meaning they will be summed or averaged, for example.

Say you have a chart that sums the sales data for different regions, but you'd rather have the average.

• In the Field List click the arrow next to the numeric value and click Average.

Note:  If a field has a calculator icon next to it, then it is a calculated field, and you can't change the aggregate. For example, if it's a sum, it can only be a sum.

In this article

• Aggregate a numeric field

• Convert a text (non-numeric) field to an aggregated field

  • Add a text field to a visualization as an aggregate

• Duplicates and blanks in a field

• Convert an aggregate to a non-aggregated field

• Power View changes how it handles integers

  • Integer behavior when you upgrade a Power View in SharePoint 2010 report to Power View in SharePoint 2013

  • Integers in Power View for SharePoint 2010

  • Integers in Power View in Excel 2013 and SharePoint 2013

Aggregate a numeric field

For example in Power View, you might have a Rating field, with ratings from 1 to 5. You add it to a table in a view and think that therefore you should be able to convert the table to a chart, but all the chart icons are grayed and disabled. You notice the field has no Sigma ∑ symbol next to it, and that there is a row in the table for every rating of every item.

1. In the Table fields or Values box in the layout section of the field list, click the drop-down arrow next to a numeric field.

   Note that Do not summarize is checked.

2. Check one of the aggregation options: Sum, Average, Minimum, Maximum, or Count.

   Let's say you click Average. Here are things that have changed:

   • You now see just one row for each item.

   • The numeric field is an average of the values.

   • In the Values box in the layout section of the field list, the field name has a Sigma ∑ next to it.

   • The chart icons in the Visualizations Gallery are enabled.

Note:  The field in the fields section of the field list is still a non- aggregated field, but the field in the table in the view is an aggregate.

Top of Page

Convert a text (non-numeric) field to an aggregated field

You can convert a text field to an aggregate so you can count it, and then display the count in charts. For example, let's say you want to know how many products are in each product subcategory. You add the Category and Product fields to a table in the view. You see a long list of the products in each category. All the chart types are grayed on the Design tab because the matrix has no aggregates.

1. In the layout (lower) section of the field list, click the drop-down arrow next to a non-numeric field.

   Note that Do not summarize is checked.

2. Click Count.

Add a text field to a visualization as an aggregate

You can also make a field a counted field as you add it to a visualization, rather than adding and then converting it. This can speed up performance, because Power View does not have to fetch all the items in the field.

• For a matrix or chart, drag a field from the fields (upper) section of the field list and drop it in the Values box.

• In the fields section of the field list, click the drop-down arrow next to a non-numeric field.

  • For a matrix or chart, click Add to Values.

  • For a table, click Add to Table as Count.

Doing any of these automatically adds the field as a counted field.

Duplicates and blanks in a field

When you set Power View to count the values in a field, by default it counts all the rows that contain data: It counts duplicate values, but not blanks. You can set it to instead count only unique (distinct) values, including blanks.

• Click the arrow next to the field in the layout section of the field list, and click Count (Non Blank) or Count (Distinct).

Note:  The column label in a visualization for either kind of counted field is the same: Count of <Field Name>. You can tell whether it counts distinct or non-blank values by clicking the dropdown arrow next to the field in the Values box for a matrix or chart or the Fields list for a table.

Top of Page

Convert an aggregate to a non-aggregated field

There may be times when you do want to see every value of a field, rather than aggregating it by summing or averaging, for example.

Note:  You can only change an aggregate to a non-aggregated field in a flat table. The option doesn't exist in any other visualization.

1. In the Table fields or Values box in the layout section of the field list, click the drop-down arrow next to an aggregate.

   Note that an aggregate, such as Sum, is checked.

2. Click Do not summarize.

   Each item may now have multiple rows, each with a different amount.

Top of Page

Power View changes how it handles integers

By default, Power View for SharePoint 2010 and SQL Server 2012 aggregated decimal numbers, but treated integers as categories rather than aggregating them. A data model designer either in Power Pivot or in SQL Server Data Tools could set defaults for integers, but this was the default behavior. In Power View in SharePoint 2013 and SQL Server 2012 SP1, Power View aggregates both decimal numbers and integers by default. A data model designer can still specify other default behavior, but that is the default.

Integer behavior when you upgrade a Power View in SharePoint 2010 report to Power View in SharePoint 2013

When you upgrade a Power View in SharePoint 2010 report to Power View in SharePoint 2013, the default behavior of any integers will change, if they are default fields and the data model creator didn't set a default behavior.

Integers in Power View for SharePoint 2010

In Power View for SharePoint 2010, if a table contains integers:

Category	Item	Quantity
Drink	Apple juice	12
Food	Bread	7
Drink	Tea	5
Food	Crackers	46

When you create a table in Power View with Category and Price fields, it doesn't add the quantities because the numbers are integers:

Category	Quantity
Drink	12
Food	7
Drink	5
Food	46

You can make Power View sum or otherwise aggregate the numbers by clicking the arrow in the Field List and selecting Sum, Average, Count, or another aggregate.

Integers in Power View in Excel 2013 and SharePoint 2013

In Power View in Excel 2013 and SharePoint 2013, when you add a number field, whether decimal or integer, the default is to sum the values. So in the above example of the integers, the table in Power View is:

Category	Quantity
Drink	17
Food	53

Again, you can change that default and make Power View not summarize, but the default behavior has changed.

Top of Page