Microsoft Office Tutorials: Excel 2016

Showing posts with label Excel 2016. 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.

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

Fix problems opening documents in sharepoint libraries

If you run into issues opening Office files and documents from SharePoint document libraries, here are some suggestions to help you fix them.

You might consider syncing your document library with your computer to work with documents. Syncing creates a local folder on your desktop machine where you can open and edit files easily. When you save a document, it's automatically uploaded to the SharePoint or OneDrive for work or school. For more info, see Sync SharePoint files with the new OneDrive sync app.

If the issue happens with Open with Explorer or View in File Explorer, see Open in Explorer or View with File Explorer.

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.

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

Wednesday, March 10, 2021

Prepare your excel data source for a word mail merge

Windows newer Office 2013-2010 macOS



You've built a list of contacts and other data that you want to use for a Word mail merge. If your data source is an existing Excel spreadsheet, then you just need to prepare the data for a mail merge. But if your data source is a tab delimited (.txt) or a comma-separated value (.csv) file, you first need to import the data into Excel, and then prepare it for a mail merge.

Step 1: Set up your data source in Excel

If you're using an Excel spreadsheet as your data source for a mail merge in Word, skip this step. If the data source is a .txt or a .csv file, use the Text Import Wizard to set up your data in Excel.

After you successfully import a .txt or .csv file, go to Step 2.

If you're using Excel for Microsoft 365

Open Excel.
Go to Data > From Text/CSV.
Choose the .txt or .csv file you want, and then select Import.
In the preview window, select Transform Data.
Select the ZIP, Postal Code, or other column to format.
Go to Transform > Data Type: and select Text.
Select Replace current.
Repeat steps 5 - 7 as needed.
Select Close & Load.
Go to File > Save as and save as with a different name.

If you're using Excel 2016

Open Excel.
Excel 2016 Go to Data > Get External Data > From Text.
Choose the .txt or .csv file you want, and then select Import.
In the Text Import Wizard, in the Original data type pane, choose Delimited.
Select My data has headers above the Preview pane if the file has headers, and then choose Next.
In the Delimiters pane, select the check box that matches the delimiter (such as a Tab or Comma) the data uses, and then choose Next.
Under Data preview, choose the column that contains ZIP Codes or postal codes, and under Column data format, choose Text.

Note: Each time you apply a data format—General, Text, or Date—to a column, the name of the format appears in the table header for that column.
Repeat step 7 as necessary, choosing the column you want to change and the data format you want to apply.
Choose Finish.
In the Import Data dialog box, accept the default setting of Existing worksheet and the cell address, and then choose OK.

Caution: The cell address in the Import Data dialog box shows the currently selected cell. Data is imported beginning with the named cell address.
Save your spreadsheet with a new file name.

Step 2: Prepare your data source

In your Excel data source that you'll use for a mailing list in a Word mail merge, make sure you format columns of numeric data correctly. Format a column with numbers, for example, to match a specific category such as currency.

If you choose percentage as a category, be aware that the percentage format will multiply the cell value by 100. Format a column of percentages as text if you want to avoid that multiplication factor.

ZIP Codes or postal codes needs to be formatted as text to preserve the data during a mail merge. If not done when imported, format it now. Leading zeros—for example, 00399—in codes are dropped during a mail merge if they're not formatted as text.

Choose the column that contains the ZIP Codes, postal codes, or other data to be formatted.
Go to Home, and in the Number group, select the Number Format box down arrow, and then choose an option in the list (such as Text).

Make sure you add the appropriate symbol before or after a merge field. For example, here's how the currency and percentage values look if you omit symbols.

Mail merge document that reads "your contribution of 50.00" and "offer you a 20 discount."

If you include the symbols, the numbers make more sense.

Mail merge results document reads "your contribution of $50.00" and "offer you a 20% discount."

In your mail merge document, you add the symbols before or after the merge fields like this:

Sample mail merge document where a field named Donation is preceded by a dollar sign and a field named Percent is followed by a percent sgn.

Step 1: Set up and prepare your data source in Excel

If you're not using an existing Excel data source for your mail merge, you can use a contact list or an address book in a .txt or .csv file. The Text Import Wizard guides you through the steps to get data that's in a .txt or .csv file into Excel.

Open Excel.
On the Data tab, choose From Text/CSV.
Choose the .txt or .csv file you want, and then choose Import.
In the Text Import Wizard, in the Original data type pane, choose Delimited.
Select the My data has headers check box above the Preview pane if the file you're importing has headers, and then choose Next.
In the Delimiters pane, select the check box that matches the delimiter (such as a Tab or Comma) your data uses, and then choose Next.

Tip: The Data preview pane shows what the results will look like in a table format.
Under Data preview, choose the column that contains ZIP Codes or postal codes, and under Column data format, choose Text.
Notes:
- The available formats are General, Text, and Date. Each time you apply a data format to a column, the name of the format appears in the table header for that column.
- A column that contains numerical data such as a percentage or a currency value, for example, can only be formatted after the file is imported. See the "Format numerical data in Excel" subsection that follows for more information.
Repeat step 7 as necessary, choosing the column you want to change and the data format you want to apply.
Choose Finish.
In the Import Data dialog box, accept the default setting of Existing worksheet and the cell address, and then choose OK.

Caution: The cell address in the Import Data dialog box shows the currently selected cell. Data is imported beginning with the named cell address.
When done, save your data source with a new file name.

Format numerical data in Excel

Format any numerical data like percentages or currency values in any new or existing data source in Excel that you intend to use in a Word mail merge. To preserve numeric data you've formatted as a percentage or as currency during a mail merge, follow the instructions in the "Step 2: Use Dynamic Data Exchange (DDE) for a mail merge" section.

Open your Excel data source.
Choose a column that you want to format.
On the Home tab, in the Number group, choose the Number Format (General) box down arrow, and then choose an option in the list.
Repeat steps 2 and 3 as necessary.
When done, choose Save.

Step 2: Use Dynamic Data Exchange (DDE) for a mail merge

When you have an Excel data source that contains formatted numbers like percentages and currency values, some of the numeric data may not retain its formatting during a mail merge. You'll get the mail merge results you want and can preserve the format of your numeric data in Excel by using DDE.

Excel data format compared to Work Merge Field by using or not using Dynamic Data Exchange

Note: If you decide not to use DDE, follow the guidance in Use mail merge to send bulk email messages.

Open Word, choose File > Options > Advanced.
Under General, select the Confirm file format conversion on open check box.
Choose OK.
On the Mailings tab, choose Start Mail Merge > Step by Step Mail Merge Wizard.
In the Mail Merge pane, under Select recipients, choose Use an existing list.
Under Use an existing list, choose Browse, and then open your formatted spreadsheet.
In the Confirm Data Source dialog box, select the Show all check box, and choose MS Excel Worksheets via DDE (*.xls) > OK.
In the Microsoft Excel dialog box, choose Entire Spreadsheet > OK.
When the Mail Merge Recipients dialog box opens, choose OK.
Continue with your letter, email message, label, or envelope merge.

Tip: You can prevent being prompted by Word every time you open a data file. After you connect to your mailing list, go to File > Options > Advanced. Under General, clear the Confirm file format conversion on open check box.

If you've built a contact list in an Excel spreadsheet, it's important to format any zip codes or postal codes as text to avoid losing data. If you're importing into a new spreadsheet any contacts from either a text (.txt) or a comma-separated value (.csv) file, the Text Import Wizard can help you import and format your data.

Step 1: Set up your data source in Excel

If you're already using an Excel spreadsheet as your data source for a mail merge in Word, go to Step 2 in this topic. If the data source is a .txt or a .csv file that contains your Gmail contacts, for example, use the Text Import Wizard to set up your data inExcel.

Open Excel, and on the Data tab, choose From Text.
Choose the .csv or .txt file you want, and then choose Get Data.
In the Text Import Wizard, choose Next.
In Delimiters, check the box that matches the delimiter that separates each element of your data (such as a tab or comma). Then choose Next.

Tip: The Preview of selected data pane shows you what the results will look like in a table format.
In Preview of selected data, choose the column that containing the zip codes or postal codes. Then, in Column data format, choose Text.
Repeat step 5 as necessary, choosing the column you want to change and the data format you want to apply.

Note: Each time you apply a data format to a column, the name of the format appears in the table header for that column.
Click Finish.
In the Import Data dialog box, choose where you want Excel to put your data, and then click OK.

Note: Save your spreadsheet with a new file name.

Step 2: Format numerical data in Excel spreadsheet

To ensure your zip code or postal code pass through a mail merge without losing any zeros, format the column containing those codes as text.

Open your spreadsheet and select the column that contains the zip codes or postal codes.
On the Home tab, in the Format box, choose Text.

Now, you can use this data for a mail merge.

Monday, March 8, 2021

Format a map chart

Once you've created a Map chart, you might want to take advantage of some of its powerful formatting features.

Note: Map charts are only available in Excel 2016 if you have a Microsoft 365 subscription. If you are a Microsoft 365 subscriber, make sure you have the latest version of Office.

Series options

Note: There are several Map chart specific Series options, however they are not supported in Android devices or Excel Mobile. If you need some of the Map Series options, then you can build your chart in Excel for Windows and view it on an Android device or Excel Mobile.

Depending on the map chart type, you can change the following Series options:

Map projection - Change the cartographic projection of the map. The available projections are Mercator, Miller, Albers and Robinson. Map projections are methods of displaying a spherical object (the Earth) on a flat plane.
Map area - Change the zoom level of the map, ranging from a state/province view all the way to the world view.
Map labels - Show geographic names for your regions. Choose to show names based on fit, or show all labels.

Formatting Guidelines

Following are some guidelines for formatting a Map chart's Series Options. To display the Series Options for your map chart you can right-click on the outer portion of the map and select Format Chart Area in the right-click menu, or double-click on the outer portion of the map. You should see the Format Object Task Pane on the right-hand side of the Excel window. If the Series Options aren't already displayed, then click the Series Option expander button on the right side and select the Series "value" option that corresponds with your data.

Next, select the Series Options button to display the Series Options and Color choices:

Excel Map Chart Format Object Task Pane Series options

Note: The Map projection, Map area and Map labels options are the same for either type of map chart, but the Series Color options are only available for value charts.

Map projection

Excel will automatically select the Map projection option that it thinks is best, but you can choose from any of the available selections (not all options will be available depending on the scope of your chart).
Map area

Excel will automatically select the Map area option that it thinks is best, but you can choose from any of the available selections (not all options will be available depending on the scope of your chart).
Map labels

You have the option to not display country or region names, or display where possible based on the Best fit only, or Show all options. Label display depends greatly on the actual size of your chart - the larger the chart, the more labels can be displayed.
Series Color

Series Colors are only available for Map charts that display values. You can choose from the Sequential (2-color) option, which is the default color setting, or the Diverging (3-color) option. You can experiment with changing the Minimum, Midpoint and Maximum values, as well as the colors assigned to each.
Color formatting a category map chart

Although there are no Series Colors options for a map chart based on a category, you can adjust the individual category colors. Select the data point of interest in the chart legend or on the chart itself, and in the Ribbon > Chart Tools > Format, change the Shape Fill, or change it from the Format Object Task Pane > Format Data Point > Fill dialog, and select from the Color Pallette:

Other chart formatting

You can apply the same charting styles and elements to map charts that you can with other Excel charts. See more at: Format elements of a chart.

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.

Thursday, March 25, 2021

Save a single worksheet

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

Download and check files locally

File corruption

File formats not supported

Thursday, March 11, 2021

I want to

Insert an Index Column

Insert a Custom Column

To insert a custom column

Example formulas

Wednesday, March 10, 2021

Step 1: Set up your data source in Excel

Step 2: Prepare your data source

See also

Step 1: Set up your data source in Excel

Step 2: Format numerical data in Excel spreadsheet

Monday, March 8, 2021

Series options

Formatting Guidelines

Other chart formatting

Need more help?

See Also