Office 365

To download an example of a workbook with data validation, click here.

Applying Data Validation to Cells

For a video displaying these features, click here. 

1. Select the cell(s) you want to create a rule for.
2. Select Data > Data Validation.
3. On the Settings tab, under Allow, select an option:
   • Whole Number - to restrict the cell to accept only whole numbers.
   • Decimal - to restrict the cell to accept only decimal numbers.
   • List - to pick data from the drop-down list.
   • Date - to restrict the cell to accept only date.
   • Time - to restrict the cell to accept only time.
   • Text Length - to restrict the length of the text.
   • Custom – for custom formula.
4.  Under Data, select a condition:
   • between
   • not between
   • equal to
   • not equal to
   • greater than
   • less than
   • greater than or equal to
   • less than or equal to
5. On the Settings tab, under Allow, select an option:
6. Set the other required values, based on what you chose for Allow and Data. For example, if you select between, then select the Minimum: and Maximum: values for the cell(s).
7. Select the Ignore blank checkbox if you want to ignore blank spaces.
8. If you want to add a Title and message for your rule, select the Input Message tab, and then type a title and input message.
9. Select the Show input message when cell is selected checkbox to display the message when the user selects or hovers over the selected cell(s).
10. Select OK. Now, if the user tries to enter a value that is not valid, a pop-up appears with the message, “This value doesn’t match the data validation restrictions for this cell.”

Create a Chart

For a video on how to create a chart, click here. 

1. Select data for the chart.
2. Select Insert > Recommended Charts.
3. Select a chart on the Recommended Charts tab, to preview the chart.
   • Note: You can select the data you want in the chart and press ALT + F1 to create a chart immediately, but it might not be the best chart for the data. If you don’t see a chart you like, select the All Charts tab to see all chart types.
4. Select a chart.
5. Select OK.

Add a Trendline

1. Select a chart.
2. Select Design > Add Chart Element.
3. Select Trendline and then select the type of trendline you want, such as Linear, Exponential, Linear Forecast, or Moving Average.

Instant Analysis

For a video on instant analysis, click here. 

1. Select a range of cells.
2. Select the Quick Analysis button that appears at the bottom right corner of the selected data. Or, press Ctrl + Q.
3. Select Charts.
4. Hover over the chart types to preview a chart, and then select the chart you want.

For additional details on quick analysis tools, click here.

Create a Table

For a video on creating tables, click here. To learn more about the basic elements of a table, click here.

Please note: Excel tables are not the same as applying a What-If analysis. A What-If analysis changes the values in cells to see how those changes will affect the outcome of formulas on the worksheet. For more details on this type of analysis, click here. 

To create a basic table:

1. Select the cell or the range in the data.
2. Select Home > Format as Table.
3. Pick a table style.
4. In the Format as Table dialog box, select the checkbox next to My table has headers if you want the first row of the range to be the header row.
5. Select OK.

Excel has some features that enable you to work efficiently with your table data:

• Using structured references -- Instead of using cell references, such as A1 and R1C1, you can use structured references that reference table names in a formula. For more information, see this page on the official Microsoft support site: Using structured references with Excel tables.
• Ensuring data integrity -- You can use the built-in data validation feature in Excel. For example, you may choose to allow only numbers or dates in a column of a table. For more information on how to ensure data integrity, see the tab about applying data validation to cells.

For additional details on tables, including sorting, filtering, totaling, and slicers, click here. 

Create a PivotTable

A PivotTable is a powerful tool to calculate, summarize, and analyze data that lets you see comparisons, patterns, and trends in your data. For a video about basic pivot tables, click here. 

1. Select the cells you want to create a PivotTable from.
   • Note:  Your data shouldn't have any empty rows or columns. It must have only a single-row heading.
2. Select Insert > PivotTable.
   
3. Under Choose the data that you want to analyze, select Select a table or range. 
4. In Table/Range, verify the cell range.
5. Under Choose where you want the PivotTable report to be placed, select New worksheet to place the PivotTable in a new worksheet or Existing worksheet and then select the location you want the PivotTable to appear.
6. Select OK.

Building Out Your PivotTable

1. To add a field to your PivotTable, select the field name checkbox in the PivotTables Fields pane.
2. Note: Selected fields are added to their default areas: non-numeric fields are added to Rows, date and time hierarchies are added to Columns, and numeric fields are added to Values.
3. To move a field from one area to another, drag the field to the target area.

Using the Field List in PivotTables

The Field List should appear when you click anywhere in the PivotTable. If you click inside the PivotTable but don't see the Field List, open it by clicking anywhere in the PivotTable. Then, show the PivotTable Tools on the ribbon and click Analyze > Field List.

The Field List has a field section in which you pick the fields you want to show in your PivotTable, and the Areas section (at the bottom) in which you can arrange those fields the way you want.

Add and Rearrange Fields

For a video on using Fields, click here.

NOTE: Typically, nonnumeric fields are added to the Rows area, numeric fields are added to the Values area, and Online Analytical Processing (OLAP) date and time hierarchies are added to the Columns area.

Fields that you place in different areas are shown in the PivotTable as follows:

• Filters area fields are shown as top-level report filters above the PivotTable.
• Columns area fields are shown as Column Labels at the top of the PivotTable. Depending on the hierarchy of the fields, columns may be nested inside columns that are higher in position.
• Rows area fields are shown as Row Labels on the left side of the PivotTable. Depending on the hierarchy of the fields, rows may be nested inside rows that are higher in position.
• Values area fields are shown as summarized numeric values in the PivotTable. 

If you have more than one field in an area, you can rearrange the order by dragging the fields into the precise position you want. To delete a field from the PivotTable, drag the field out of its areas section.

Additional PivotTable Topics

• Group or Ungroup Data
• Filter Data
• Create a PivotChart

For a list of over 300 Excel formulas and functions, click here. 

Basic Formulas

For a video overview of basic formulas, click here.

To download the Microsoft Formulas workbook that will provide examples of various formulas and functions, click here.

Create a Formula that refers to values in other cells

1. Select a cell.
2. Type the equal sign =.
   • Note: Formulas in Excel always begin with the equal sign.
3. Select a cell or type its address in the selected cell.
4. Enter an operator. For example, – for subtraction.
5. Select the next cell, or type its address in the selected cell.
6. Press Enter. The result of the calculation appears in the cell with the formula.

See a Formula

1. When a formula is entered into a cell, it also appears in the Formula bar.
2. To see a formula, select a cell, and it will appear in the formula bar.

Define and Use Names in Formulas 

By using names, you can make your formulas much easier to understand and maintain. You can define a name for a cell range, function, constant, or table. Once you adopt the practice of using names in your workbook, you can easily update, audit, and manage these names.

For a video about these functions, click here.

Name a Cell

1. Select a cell.
2. In the Name Box, type a name.
3. Press Enter.

Define Names from Selected Range

1. Select the range you want to name, including the row or column labels.
2. Select Formulas > Create from Selection.
3. In the Create Names from Selection dialog box, designate the location that contains the labels by selecting the Top row,Left column, Bottom row, or Right column check box.
4. Select OK.
   • Please Note: Excel names the cells based on the labels in the range you designated.

Use Names in Formulas

1. Select a cell and enter a formula.
2. Place the cursor where you want to use the name in that formula.
3. Type the first letter of the name, and select the name from the list that appears.
4. Or, select Formulas > Use in Formula and select the name you want to use.
5. Press Enter.

Additional Formulas and Functions

VLOOKUP

For a video about VLOOKUP, click here.

In its simplest form, the VLOOKUP function says:

=VLOOKUP(lookup value, range containing the lookup value, the column number in the range containing the return value, optionally specify TRUE for approximate match or FALSE for an exact match).

For example: =VLOOKUP(A7, A2:B5, 2, FALSE).

To set up VLOOKUP:

1. Select a cell.
2. Type =VLOOKUP( and then select the value to lookup.
3. Type a comma (,) and select the range or table to look for the value.
4. Type a comma (,) and the number of the column where the lookup value is located.
5. Type ,FALSE) to find an exact match.
6. Press Enter.

SUM Function

The SUM function, one of the math and trig functions, adds values. You can add individual values, cell references or ranges or a mix of all three.For example:

=SUM(A2:A10)

=SUM(A2:A10, C2:C10)

COUNTIF Function

Use COUNTIF, one of the statistical functions, to count the number of cells that meet a criterion; for example, to count the number of times a particular city appears in a customer list.

In its simplest form, COUNTIF says:

=COUNTIF(Where do you want to look?, What do you want to look for?)

For example:

=COUNTIF(A2:A5,"London")

=COUNTIF(A2:A5,A4)

For examples and a video about this function, click here.

DATEIF Function

Use the DATEDIF function when you want to calculate the difference between two dates. First put a start date in a cell, and an end date in another. Then type a formula like one of the following.

Warning: If the Start_date is greater than the End_date, the result will be #NUM!.

In this example, the start date is in cell D9, and the end date is in E9. The formula is in F9. The “d” returns the number of full days between the two dates.

For more examples of date calculation functions, click here

DATEDIF Function

You can also calculate age or someone’s time of service. The result can be something like “2 years, 4 months, 5 days.”

In this example, the start date is in cell D17, and the end date is in E17. In the formula, the “y” returns the number of full years between the two days.

For more examples of date calculation functions, click here.

The following tables describe the file formats that are supported in Excel.