Skip to main content
It looks like you're using Internet Explorer 11 or older. This website works best with modern browsers such as the latest versions of Chrome, Firefox, Safari, and Edge. If you continue with this browser, you may see unexpected results.

Office 365

About Excel

Excel logoOne of the primary programs within the Microsoft suite is Excel. This spreadsheet was developed by Microsoft and released for Mac in 1985. The first Windows version was launched two years later in 1987. This program features calculation, tables, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications.

FSU Resources

Downloading Office 365

All active students enrolled in classes are eligible for free downloads of Office 365 ProPlus—including Word, Excel, PowerPoint, and more—on up to fifteen personally-owned devices: five computers, five tablets and five phones. 

For more details on how to download, visit the FSU ITS Office 365 Resource Page.

A Note About File Formats

Beginning with Excel 2007, the file extension for the program changed from .xls to .xlsx. While all versions of Excel will be able to open files with a .xls format (they are backward compatible), older editions will not be able to display all of the tools and features included in a .xlsx file. These glitches may cause errors either in the program functionality or within the file itself.

In Microsoft 365, you can follow this path to save a file in an older format: 

File → Save As → Browse → Save as type

Quick Guides

For a list of articles and tutorials relating to accessible features, click here.

From Microsoft's official support site:

What to fix

How to find it

Why fix it

How to fix it

Include alternative text with all visuals.

Visual content includes pictures, SmartArt graphics, shapes, groups, charts, embedded objects, ink, and videos.

To find all instances of missing alternative text in the spreadsheet, use the Accessibility Checker.

Alt text helps people who can’t see the screen to understand what’s important in images and other visuals.

Avoid using text in images as the sole method of conveying important information. If you must use an image with text in it, repeat that text in the document. In alt text, briefly describe the image and mention the existence of the text and its intent.

Add alt text to visuals in Office 365

Add alt text to visuals in Office 2019

Add alt text to visuals in Office 2016

Add meaningful hyperlink text and ScreenTips.

To determine whether hyperlink text makes sense as standalone information and whether it gives readers accurate information about the destination target, visually scan the workbook.

People who use screen readers sometimes scan a list of links. Links should convey clear and accurate information about the destination. For example, instead of linking to the text Click here, include the full title of the destination page.

Tip: You can also add ScreenTips that appear when your cursor hovers over a cell that includes a hyperlink.

Add hyperlink text and ScreenTips

Use sufficient contrast for text and background colors.

To find insufficient color contrast, use the Accessibility Checker.

You can also look for text in your spreadsheet that’s hard to read or to distinguish from the background.

If your spreadsheet has a high level of contrast between text and background, more people can see and use the content.

Use accessible text color

Give all sheet tabs unique names, and remove blank sheets.

To find out whether all sheets that contain content in a workbook have descriptive names and whether there are any blank sheets, use the Accessibility Checker.

Screen readers read sheet names, which provide information about what is found on the worksheet, making it easier to understand the contents of a workbook and to navigate through it.

Rename sheet tabs

Delete sheet tabs

Use a simple table structure, and specify column header information.

To ensure that tables don’t contain split cells, merged cells, or nested tables, use the Accessibility Checker.

You can also visually scan your tables to check that they don't have any completely blank rows or columns.

Screen readers keep track of their location in a table by counting table cells. If a table is nested within another table or if a cell is merged or split, the screen reader loses count and can’t provide helpful information about the table after that point. Blank cells in a table could also mislead someone using a screen reader into thinking that there is nothing more in the table.

Screen readers also use header information to identify rows and columns.

Add headers to a new table

Use headers in an existing table

 

 

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

"Data Validation" under "Data" tab

  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

Selecting a group of tabs

  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.
    "Insert" tab
  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. "PivotTable Fields" optionsTo 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

 

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.

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

DATEIF function and result

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

DATEIF function and result

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.

Extension

Format Name

Description

.csv 

CSV (Comma delimited) 

Saves a workbook as a comma-delimited text file for use on another Windows operating system, and makes sure that tab characters, line breaks, and other characters are interpreted correctly. Saves only the active sheet. 

.csv 

CSV (Macintosh) 

Saves a workbook as a comma-delimited text file for use on the Macintosh operating system, and makes sure that tab characters, line breaks, and other characters are interpreted correctly. Saves only the active sheet. 

.csv 

CSV (MS-DOS) 

Saves a workbook as a comma-delimited text file for use on the MS-DOS operating system, and makes sure that tab characters, line breaks, and other characters are interpreted correctly. Saves only the active sheet. 

.dbf 

DBF 3, DBF 4 

dBase III and IV. Users can open these files formats in Excel 2019, Excel 2016, and Excel 2013, but, users can't save an Excel file to dBase format. 

.dif 

DIF (Data Interchange Format) 

Saves only the active sheet. 

.htm, .html

Web Page 

A web page that is saved as a folder that contains an .htm file and all supporting files, such as images, sound files, scripts, and more. 

.mht, .mhtml 

Single File Web Page 

A web page that is saved as a single file that includes an .htm file and all supporting files, such as images, sound files, scripts, and more. 

.ods 

OpenDocument Spreadsheet 

A file format for saving Excel 2019, Excel 2016, and Excel 2013 files so that they can be opened in spreadsheet applications that use the OpenDocument Spreadsheet format, such as Google Docs and OpenOffice.org Calc. Users can also open spreadsheets in the .ods format in Excel 2019, Excel 2016, and Excel 2013. When users save and open .ods files, they might lose formatting. 

.pdf 

PDF 

Portable Document Format, a format that preserves document formatting and enables file sharing. When the PDF format file is viewed online or printed, it keeps the format that users intended. Data in the file cannot be easily changed. The PDF format is also useful for documents that will be reproduced by using commercial printing methods. 

.prn 

Formatted Text (Space delimited) 

Lotus space-delimited format. Saves only the active sheet. 

.slk 

SYLK (Symbolic Link Format) 

Saves only the active sheet. 

.txt 

Text (Tab delimited) 

Saves a workbook as a tab-delimited text file for use on another Windows operating system, and makes sure that tab characters, line breaks, and other characters are interpreted correctly. Saves only the active sheet. 

.txt 

Text (Macintosh) 

Saves a workbook as a tab-delimited text file for use on the Macintosh operating system, and makes sure that tab characters, line breaks, and other characters are interpreted correctly. Saves only the active sheet. 

.txt 

Text (MS-DOS) 

Saves a workbook as a tab-delimited text file for use on the MS-DOS operating system, and makes sure that tab characters, line breaks, and other characters are interpreted correctly. Saves only the active sheet. 

.txt 

Unicode Text 

Saves a workbook as Unicode text, a character encoding standard that was developed by the Unicode Consortium. Saves only the active sheet. 

.xla 

Excel 97-2003 Add-In 

The Excel 97-2003 Add-In, a supplemental program that runs additional code and supports VBA projects. 

.xlam 

Excel Add-In 

The XML-based and macro-enabled Add-In format for Excel 2019, Excel 2016, Excel 2013, and Office Excel 2007. An Add-In is a supplemental program that runs additional code. Supports VBA projects and Excel 4.0 macro sheets (.xlm). 

.xls 

Excel 97-Excel 2003 Workbook

The Excel 97-Excel 2003 Binary file format. 

.xls 

Microsoft Excel 5.0/95 Workbook 

The Excel 5.0/95 Binary file format. 

.xlsb 

Excel Binary Workbook 

The binary file format for Excel 2019, Excel 2016, Excel 2013, and Excel 2010 and Office Excel 2007. This is a fast load-and-save file format for users who need the fastest way possible to load a data file. Supports VBA projects, Excel 4.0 macro sheets, and all the new features that are used in Excel. But, this is not an XML file format and is therefore not optimal for accessing and manipulating content without using Excel 2019, Excel 2016, Excel 2013, Excel 2010 or Excel 2007 and the object model. 

.xlsm 

Excel Macro-Enabled Workbook 

The XML-based and macro-enabled file format for Excel 2019, Excel 2016, Excel 2013, Excel 2010 and Office Excel 2007. Stores VBA macro code or Excel 4.0 macro sheets (.xlm). 

.xlsx 

Excel Workbook

The default XML-based file format for Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Office Excel 2007. Can't store VBA macro code or Microsoft Excel 4.0 macro sheets (.xlm). 

.xlsx 

Strict Open XML Spreadsheet 

An ISO strict version of the Excel Workbook file format (.xlsx). 

.xlt 

Excel 97 - Excel 2003 Template 

The Excel 97 - Excel 2003 Binary file format for an Excel template. 

.xltm 

Excel Macro-Enabled Template 

The macro-enabled file format for an Excel template for Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Office Excel 2007. Stores VBA macro code or Excel 4.0 macro sheets (.xlm). 

.xltx 

Excel Template 

The default file format for an Excel template for Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Office Excel 2007. Can't store VBA macro code or Excel 4.0 macro sheets (.xlm). 

.xlw 

Excel 4.0 Workbook 

An Excel 4.0 file format that saves only worksheets, chart sheets, and macro sheets. Users can open a workbook in this file format in Excel 2019, Excel 2016, and Excel 2013. But, users can't save an Excel file to this file format. 

.xml 

XML Spreadsheet 2003 

XML Spreadsheet 2003 file format. 

.xml 

XML Data 

XML Data format. 

.xps 

XPS Document 

XML Paper Specification, a file format that preserves document formatting and enables file sharing. When the XPS file is viewed online or printed, it maintains exactly the format that users intended, and the data in the file cannot be easily changed. 

Tutorials

  • Microsoft Excel Tutorials
    This collection of videos and articles covers a wide array of topics and trainings on Excel tools and features. 
  • LinkedIn Learning 
    Search through hundreds of videos from beginner to expert level. These extensive courses and tutorials can be accessed for free through your FSU account under Resources

Further Reading

The Florida State University Libraries
Library Hours | Maps and Directions | Employment | Giving to the Libraries | The FSULib Blog | Library Homepage

Florida State University Libraries | 116 Honors Way | Tallahassee, FL 32306 | (850) 644-2706