Microsoft Office Tutorials: Excel

Showing posts with label Excel. Show all posts

Thursday, March 25, 2021

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

Right-click the worksheet name tab.
Click select Move or Copy.
Click on the Move selected sheets to Book drop-down menu. Select (new book).
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.
Click File > Save in your new workbook.

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

Wednesday, March 24, 2021

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 Calculator icon in Power View 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.

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.

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.
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.

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.
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.

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.
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

Friday, March 19, 2021

Use goal seek to find the result you want by adjusting an input value

If you know the result that you want from a formula, but are not sure what input value the formula needs to get that result, use the Goal Seek feature. For example, suppose that you need to borrow some money. You know how much money you want, how long you want to take to pay off the loan, and how much you can afford to pay each month. You can use Goal Seek to determine what interest rate you will need to secure in order to meet your loan goal.

Windows macOS



Note: Goal Seek works only with one variable input value. If you want to accept more than one input value; for example, both the loan amount and the monthly payment amount for a loan, you use the Solver add-in. For more information, see Define and solve a problem by using Solver.

Step-by-step with an example

Let's look at the preceding example, step-by-step.

Because you want to calculate the loan interest rate needed to meet your goal, you use the PMT function. The PMT function calculates a monthly payment amount. In this example, the monthly payment amount is the goal that you seek.

Prepare the worksheet

Open a new, blank worksheet.
First, add some labels in the first column to make it easier to read the worksheet.
1. In cell A1, type Loan Amount.
2. In cell A2, type Term in Months.
3. In cell A3, type Interest Rate.
4. In cell A4, type Payment.
Next, add the values that you know.
1. In cell B1, type 100000. This is the amount that you want to borrow.
2. In cell B2, type 180. This is the number of months that you want to pay off the loan.
  
  Note: Although you know the payment amount that you want, you do not enter it as a value, because the payment amount is a result of the formula. Instead, you add the formula to the worksheet and specify the payment value at a later step, when you use Goal Seek.
Next, add the formula for which you have a goal. For the example, use the PMT function:
1. In cell B4, type =PMT(B3/12,B2,B1). This formula calculates the payment amount. In this example, you want to pay $900 each month. You don't enter that amount here, because you want to use Goal Seek to determine the interest rate, and Goal Seek requires that you start with a formula.
  
  The formula refers to cells B1 and B2, which contain values that you specified in preceding steps. The formula also refers to cell B3, which is where you will specify that Goal Seek put the interest rate. The formula divides the value in B3 by 12 because you specified a monthly payment, and the PMT function assumes an annual interest rate.
  
  Because there is no value in cell B3, Excel assumes a 0% interest rate and, using the values in the example, returns a payment of $555.56. You can ignore that value for now.

Use Goal Seek to determine the interest rate

On the Data tab, in the Data Tools group, click What-If Analysis, and then click Goal Seek.
In the Set cell box, enter the reference for the cell that contains the formula that you want to resolve. In the example, this reference is cell B4.
In the To value box, type the formula result that you want. In the example, this is -900. Note that this number is negative because it represents a payment.
In the By changing cell box, enter the reference for the cell that contains the value that you want to adjust. In the example, this reference is cell B3.

Note: The cell that Goal Seek changes must be referenced by the formula in the cell that you specified in the Set cell box.
Click OK.

Goal Seek runs and produces a result, as shown in the following illustration.
Finally, format the target cell (B3) so that it displays the result as a percentage.
1. On the Home tab, in the Number group, click Percentage.
2. Click Increase Decimal or Decrease Decimal to set the number of decimal places.

Note: Goal Seek works only with one variable input value. If you want to accept more than one input value, for example, both the loan amount and the monthly payment amount for a loan, use the Solver add-in. For more information, see Define and solve a problem by using Solver.

Step-by-step with an example

Let's look at the preceding example, step-by-step.

Prepare the worksheet

Open a new, blank worksheet.
First, add some labels in the first column to make it easier to read the worksheet.
1. In cell A1, type Loan Amount.
2. In cell A2, type Term in Months.
3. In cell A3, type Interest Rate.
4. In cell A4, type Payment.
Next, add the values that you know.
1. In cell B1, type 100000. This is the amount that you want to borrow.
2. In cell B2, type 180. This is the number of months that you want to pay off the loan.
  
  Note: Although you know the payment amount that you want, you do not enter it as a value, because the payment amount is a result of the formula. Instead, you add the formula to the worksheet and specify the payment value at a later step, when you use Goal Seek.
Next, add the formula for which you have a goal. For the example, use the PMT function:
1. In cell B4, type =PMT(B3/12,B2,B1). This formula calculates the payment amount. In this example, you want to pay $900 each month. You don't enter that amount here, because you want to use Goal Seek to determine the interest rate, and Goal Seek requires that you start with a formula.
  
  The formula refers to cells B1 and B2, which contain values that you specified in preceding steps. The formula also refers to cell B3, which is where you will specify that Goal Seek put the interest rate. The formula divides the value in B3 by 12 because you specified a monthly payment, and the PMT function assumes an annual interest rate.
  
  Because there is no value in cell B3, Excel assumes a 0% interest rate and, using the values in the example, returns a payment of $555.56. You can ignore that value for now.

Use Goal Seek to determine the interest rate

Do one of the following:

In Excel 2016 for Mac: On the Data tab, click What-If Analysis, and then click Goal Seek.

In Excel for Mac 2011: On the Data tab, in the Data Tools group, click What-If Analysis, and then click Goal Seek.
In the Set cell box, enter the reference for the cell that contains the formula that you want to resolve. In the example, this reference is cell B4.
In the To value box, type the formula result that you want. In the example, this is -900. Note that this number is negative because it represents a payment.
In the By changing cell box, enter the reference for the cell that contains the value that you want to adjust. In the example, this reference is cell B3.

Note: The cell that Goal Seek changes must be referenced by the formula in the cell that you specified in the Set cell box.
Click OK.

Goal Seek runs and produces a result, as shown in the following illustration.
Finally, format the target cell (B3) so that it displays the result as a percentage. Follow one of these steps:
- In Excel 2016 for Mac: On the Home tab, click Increase Decimal or Decrease Decimal .
- In Excel for Mac 2011: On the Home tab, under Number group, click Increase Decimal or Decrease Decimal to set the number of decimal places.

Thursday, March 18, 2021

Protect a worksheet

To prevent other users from accidentally or deliberately changing, moving, or deleting data in a worksheet, you can lock the cells on your Excel worksheet and then protect the sheet with a password. Say you own the team status report worksheet, where you want team members to add data in specific cells only and not be able to modify anything else. With worksheet protection, you can make only certain parts of the sheet editable and users will not be able to modify data in any other region in the sheet.

Important:

Worksheet level protection is not intended as a security feature. It simply prevents users from modifying locked cells within the worksheet.
Protecting a worksheet is not the same as protecting an Excel file or a workbook with a password. See below for more information:

To lock your file so that other users can't open it, see Protect an Excel file.
To prevent users from adding, modifying, moving, copying, or hiding/unhiding sheets within a workbook, see Protect a workbook.
To know the difference between protecting your Excel file, workbook, or a worksheet see Protection and security in Excel.

Choose what cell elements to lock

Here's what you can lock in an unprotected sheet:

Formulas: If you don't want other users to see your formulas, you can hide them from being seen in cells or the Formula bar. For more information, see Display or hide formulas.
Ranges: You can enable users to work in specific ranges within a protected sheet. For more information, see Lock or unlock specific areas of a protected worksheet.

Note: ActiveX controls, form controls, shapes, charts, SmartArt, Sparklines, Slicers, Timelines, to name a few, are already locked when you add them to a spreadsheet. But the lock will work only when you enable sheet protection. See the subsequent section for more information on how to enable sheet protection.

Enable worksheet protection

Worksheet protection is a two-step process: the first step is to unlock cells that others can edit, and then you can protect the worksheet with or without a password.

Step 1: Unlock any cells that needs to be editable

In your Excel file, select the worksheet tab that you want to protect.
Select the cells that others can edit.

Tip: You can select multiple, non-contiguous cells by pressing Ctrl+Left-Click.
Right-click anywhere in the sheet and select Format Cells (or use Ctrl+1, or Command+1 on the Mac), and then go to the Protection tab and clear Locked.

Step 2: Protect the worksheet

Next, select the actions that users should be allowed to take on the sheet, such as insert or delete columns or rows, edit objects, sort, or use AutoFilter, to name a few. Additionally, you can also specify a password to lock your worksheet. A password prevents other people from removing the worksheet protection—it needs to be entered to unprotect the sheet.

Given below are the steps to protect your sheet.

On the Review tab, click Protect Sheet.

In the Allow all users of this worksheet to list, select the elements you want people to be able to change.

Option	Allows users to
Select locked cells	Move the pointer to cells for which the Locked box is checked on the Protection tab of the Format Cells dialog box. By default, users are allowed to select locked cells.
Select unlocked cells	Move the pointer to cells for which the Locked box is unchecked on the Protection tab of the Format Cells dialog box. By default, users can select unlocked cells, and they can press the TAB key to move between the unlocked cells on a protected worksheet.
Format cells	Change any of the options in the Format Cells or Conditional Formatting dialog boxes. If you applied conditional formatting before you protected the worksheet, the formatting continues to change when a user enters a value that satisfies a different condition.
Format columns	Use any of the column formatting commands, including changing column width or hiding columns (Home tab, Cells group, Format button).
Format rows	Use any of the row formatting commands, including changing row height or hiding rows (Home tab, Cells group, Format button).
Insert columns	Insert columns.
Insert rows	Insert rows.
Insert hyperlinks	Insert new hyperlinks, even in unlocked cells.
Delete columns	Delete columns. Note: If Delete columns is protected and Insert columns is not protected, a user can insert columns but cannot delete them.
Delete rows	Delete rows. Note: If Delete rows is protected and Insert rows is not protected, a user can insert rows but cannot delete them.
Sort	Use any commands to sort data (Data tab, Sort & Filter group). Note: Users can't sort ranges that contain locked cells on a protected worksheet, regardless of this setting.
Use AutoFilter	Use the drop-down arrows to change the filter on ranges when AutoFilters are applied. Note: Users cannot apply or remove AutoFilter on a protected worksheet, regardless of this setting.
Use PivotTable reports	Format, change the layout, refresh, or otherwise modify PivotTable reports, or create new reports.
Edit objects	Doing any of the following: Make changes to graphic objects including maps, embedded charts, shapes, text boxes, and controls that you did not unlock before you protected the worksheet. For example, if a worksheet has a button that runs a macro, you can click the button to run the macro, but you cannot delete the button. Make any changes, such as formatting, to an embedded chart. The chart continues to be updated when you change its source data. Add or edit notes.
Edit scenarios	View scenarios that you have hidden, making changes to scenarios that you have prevented changes to, and deleting these scenarios. Users can change the values in the changing cells, if the cells are not protected, and add new scenarios.

Optionally, enter a password in the Password to unprotect sheet box and click OK. Reenter the password in the Confirm Password dialog box and click OK.
Important:
- Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords don't mix these elements. Passwords should be 8 or more characters in length. A passphrase that uses 14 or more characters is better.
- It is critical that you remember your password. If you forget your password, Microsoft cannot retrieve it.

How can I tell if a sheet is protected?

The Protect Sheet option on the ribbon changes to Unprotect Sheet when a sheet is protected. To view this option, click the Review tab on the ribbon, and in Changes, see Unprotect Sheet.

The Changes group on the Review tab

Unprotect an Excel worksheet

To unprotect a sheet, follow these steps:

Go to the worksheet you want to unprotect.
Go to File > Info > Protect > Unprotect Sheet, or from the Review tab > Changes > Unprotect Sheet.
If the sheet is protected with a password, then enter the password in the Unprotect Sheet dialog box, and click OK.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

Download and check files locally

Some problems occur because of differences in versions of SharePoint server and Office. If you can't get it to work, try downloading the file and seeing if you can open it locally.

Depending on the version of SharePoint, do one of the following:

In SharePoint, select the file, and click Download on the command bar, or click the ellipses (...) and then click Download.

In SharePoint Server 2016, select a file, click More on the command bar, and click Download, or right-click the document, and then click Download.

For SharePoint Server 2013, select a file, click the ellipse (...) click the ellipse (...) again in the dialog, and then click Download.

ForSharePoint Server 2010, click the down arrow on a file, click Send to, and then click Download a copy.
In your browser, click Save, and then Open folder to open the folder where the file is located.
Right-click the file in the desktop folder, and then click Open With.
Choose the app you want to use. If the file hasn't been assigned, it will pop up a dialog to select one. If the app isn't on the list, click Choose another app.
Check the Always use this app to open box.
Close the app and try double clicking the file to see if it opens the correct app.

To find the version of SharePoint you're using, see Which version of SharePoint am I using?.

To get the version of Office you're using, see What version of Office am I using? or Find details for other versions of Office.

Note: If you upgraded from an earlier version of Office to Office 2013 or Office 2016 on your computer, you might still have older versions of some programs like Visio and Project. If you use these programs, update them to 2013 or 2016. If you no longer use them, uninstall them.

File corruption

If the file opened previously, but now doesn't, there could be a problem with a corrupt file, or the format is not compatible with any of your apps. Make sure when you save a file, you try to open it to make sure it was saved correctly. If you're sharing a file with other users, be sure they save files completely and using the same file format. For example, if a spreadsheet is opened as a .xls, make sure it's saved as an .xls file.

Corrupt files can also prevent SharePoint from opening. If you suspect a corrupt file, download the document and try one of the methods outlined in these topics:

File formats not supported

Newer versions of Office add new features that may not be available with older versions of Office. New versions can read documents created by older version, but older versions can't read newer documents. For example, Excel 2016 saves files in an .xlsx format, while Excel 2003 only reads .xls format. When sharing files in SharePoint, be sure your users have compatible versions of Office for documents. If you're using Microsoft 365, you can install the latest version to your desktop from the online link.

Friday, March 12, 2021

Video use autofill and flash fill

Overview Transcript



Watch this video for a quick introduction to AutoFill and Flash Fill, two helpful time savers that we'll cover in more detail in this course.

Want more?

Fill data automatically in worksheet cells

Split a column of data based on what you type

Change the case of text

Sometimes you need to enter a lot of repetitive information in Excel, such as dates, and it can be really tedious.

If you think there might be an easier, automated way to do this, you'd be right.

Type the first date in the series.

Put the mouse pointer over the bottom right-hand corner of the cell until it's a black plus sign.

Click and hold the left mouse button, and drag the plus sign over the cells you want to fill.

And the series is filled in for you automatically using the AutoFill feature.

Or, say you have information in Excel that isn't formatted the way you need it to be, such as this list of names.

Going through the entire list manually to correct them is daunting.

You'd think there'd be an easier automated way to do this, too, and you'd be right again.

Type the first name the way you want it.

Start to type the next name, and, as if by magic, Excel provides a preview of the names formatted the way you want.

Press Enter, and the names are all filled in for you using the Flash Fill feature, which is new in Excel 2013.

AutoFill and Flash Fill are tremendous time savers, and they can do much more than we have covered in this introduction.

In the next videos, we'll cover them in more detail.

Thursday, March 11, 2021

Insert a custom column into a table power query

Note: Power Query is known as Get & Transform in Excel 2016. Information provided here applies to both. To learn more, see Get & Transform in Excel 2016.

Note: Query Editor only appears when you load, edit, or create a new query. For a quick video on how to display Query Editor, see the end of this article.

With Power Query, you can add an Index or Custom (you define the formula) column to your current query. When you create a column formula, Power Query will validate the formula syntax. This validation experience is consistent with how the Advanced Query Editing dialog validates formulas provided by you.

Insert an Index Column

Using the Query Editor ribbon

In the Query Editor ribbon, click Insert Index Column.

Using the table icon ( ) in the preview grid

Click the table icon ( ).
From the context menu, select Insert Index Column. An Index column is added in the query table. Rename the column name as required.

Top of Page

Insert a Custom Column

A custom column is a column where the value in a cell is computed using a formula that you create. For more information about the Power Query Formula Language, see Learn about Power Query formulas.

Using the Query Editor ribbon

In the Query Editor ribbon, click Insert Custom Column.

Using the table icon ( ) in the preview grid

Click the table icon ( ), and click Insert Custom Column.

To insert a custom column

In the Insert Custom Column dialog box, enter a column name, and insert a column into the Custom Column Formula box:
1. Double click a column from the Available Columns list or
2. Click a column from the Available Columns list, and click Insert.
  
  Note: You can use multiple reference columns as long as you separate them with some operator. For example, to calculate a TotalSales column, you add Total and SalesTax using the formula = each [Total] + [SalesTax].
Click OK.

Example formulas

Formula	Description
`"abc"`	Creates a column with the text abc in all rows.
`1+1`	Creates a column with the result of 1 + 1 (2) in all rows.
`[UnitPrice] * [Quantity]`	Creates a column with the result of multiplying two table columns.
[UnitPrice] * (1 – [Discount]) * [Quantity]	Calculates the total price, considering the Discount column.
"Hello" & [Name]	Combines Hello with the contents of the Name column in a new column.

Note: You can use custom column to merge values from two or more columns into a single custom column. For more information, see Merge columns.

With Power Query, Custom Columns, conditional formatting in Excel, and other customizations of worksheet tables are preserved after you refresh a query. Power Query will preserve worksheet customizations such as Data Bars, Color Scales, Icon Sets or other value-based rules across refresh operations and after query edits.

Top of Page

Note: The Query Editor only appears when you load, edit, or create a new query using Power Query. The following video shows the Query Editor window appearing after editing a query from an Excel workbook. To view the Query Editor without loading or editing an existing workbook query, from the Get External Data section in the Power Query ribbon tab, select From Other Sources > Blank Query. The following video shows one way to display the Query Editor.

How to see Query Editor in Excel

Thursday, March 25, 2021

Save a single worksheet

Wednesday, March 24, 2021

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

Friday, March 19, 2021

Step-by-step with an example

Prepare the worksheet

Use Goal Seek to determine the interest rate

Step-by-step with an example

Prepare the worksheet

Use Goal Seek to determine the interest rate

Thursday, March 18, 2021

Choose what cell elements to lock

Enable worksheet protection

Step 1: Unlock any cells that needs to be editable

Step 2: Protect the worksheet

Need more help?

See Also

Download and check files locally

File corruption

File formats not supported

Friday, March 12, 2021

Want more?

Thursday, March 11, 2021

I want to

Insert an Index Column

Insert a Custom Column

To insert a custom column

Example formulas