excel 2013 level 1, part - colorado state university 2013 p1d1.pdf · excel 2013 ‐ level 1, part...

Computer Applications Training © Colorado State University Excel Level 1, Part 1

Excel 2013 ‐ Level 1, Part 1 Excel is a spreadsheet application that provides a variety of analytical tools that allow you to perform both simple and complex calculations, manage and filter lists of data, create and maintain budgets, link to other spreadsheets, databases, or Word files, use built‐in functions for data calculations and analysis and allow you to easily create graphs or charts that are linked to the workbook data.


Workbook: A collection of individual sheets. Sheets can help organize your work. For example, one workbook may contain many related sheets. By default, a new workbook opens with one sheet named Sheet 1. You may add more by clicking on the Add new worksheet icon. Worksheet: The area that is divided into columns and rows and is where you will create your spreadsheet. Rows: Rows run horizontally on the worksheet and are referenced by a number. Columns: Columns run vertically on the worksheet and are referenced by a letter. Cell: The rectangular area at the intersection of a row and column. A cell is referenced by its Cell Address, which is a combination of the column letter followed by its row number (e.g. A3) and can hold data in the form of numbers, text, or formulas. Active Cell: The current, selected cell. It is marked by a heavy green box in the worksheet. Anything you type will be entered into the currently active cell. If a cell is selected, any formatting changes will be applied to that cell only. Range: A block of cells that is selected. Excel uses a colon to denote a range (example: A3:C10). The first cell listed is the top left corner of the block and the second cell is the lower right. Sometimes ranges are used in formulas such as =AVERAGE(A3:A20). [This would find the average of all cells including and between A3 and A20.] Status Bar: Located at the bottom of the workbook. It provides information about the workbook by a status of Ready, Enter, or Edit. It will also provide various information about a range of cells such as Average, Count, and Sum.

Title Bar The Title bar is located at the top of the Excel window. The Title bar displays the name of the active workbook.

Formula Bar The Formula Bar is located directly below the Ribbon. This is where you can enter text, labels, numbers, and formulas. To the left of the formula bar is the Name Box. The Name Box displays the name of the active cell or selected range and can be used to name a cell or as a shortcut to access a named range.


The File Menu (Backstage view) The File Menu, or Backstage view, is where the information about your file is located.

The file menu also has Sections for creating a new workbook, opening an existing workbook, Saving, Save As, Printing and the overall Excel options. To go back to your spreadsheet, hit the Esc key, or click the left arrow on the upper left‐hand side of the page.

Creating a New workbook To create a new workbook, go to File, New. You are able to create a blank workbook by double clicking on the Blank workbook, or you can use some of the prebuilt templates that are within Excel. You are also able to use the search feature to search for a specific type of template. Excel has added a lot of new templates within Excel 2013. It may be worth looking at the templates to see if any of them will meet your needs before having to build from scratch. Examples: Schedules, Loan comparisons, Budget planning, etc. If you find a template that you would like to use, double click on the icon to create it.


Saving a workbook The easy way to save a workbook is to go to File, Save. This will save the current workbook in a default location determined by Excel, typically this is in your Documents folder. If you want to save your file in a new location, or in a new format, you will want to use the File, Save As option.

On the File Save As page, Excel will show the current folder that you are in if you opened an existing file,

it will also recent folders that you saved to. When you click on your current folder, or recent folder

icons, you will be able to change the name or file type of the file.

If none of these options are where you would like to save to, click on the Browse icon on the bottom of

the page. This will bring up a Save As window that will allow you to browse your computer for a location

to save your document.


On the Save As window, you will see your Favorite locations, any internal and external drives, as well as

any network drives that you may be connected to.

On the bottom of the window, you will be able to name your file, as well as change the Save as type. For example, if you are creating a document that you are sending to other people and they are using an older version of Excel, you will want to change the save as type to Excel 97‐2003 file type. To change the file type, click on the dropdown menu for the Save as type to see all of the options that are available.


Navigating in Excel Click with the mouse in a cell to make it the active cell

Use arrow keys to move up, down, left, or right

Enter ‐ Complete a cell entry and select the cell below.

Esc ‐ Cancel a cell edit or entry.

Tab ‐ Complete a cell entry and select the next cell to the right.

Shift‐Tab ‐ Complete a cell entry and select the previous cell to the left.

Home ‐ Navigates to the first cell in a row, Column A

Ctrl‐Home ‐ navigates to cell A1

Ctrl‐End ‐ Moves cursor to the last cell containing data in the sheet.

Ctrl‐Arrow ‐ Moves the active cell in the direction of the arrow until the edge of a block of data is reached.

Page Up and Page Down ‐ navigates a screen up or down

Ctrl‐Page Up ‐ Switch between worksheet tabs

Ctrl‐Page Down ‐ Switch between worksheet tabs

Alt‐Page Up and Alt‐Page Down ‐ navigates a full screen to the right or left

Scroll Lock Key – use arrow keys to move position within the spreadsheet without moving active cell – moving view without moving cell

Ctrl‐F ‐ Searches the sheet for a specific value, text entry, or item in a formula; also accessible from Find in the Edit menu. [Note: Shift‐F4 will continue the search even if the Find box is closed.]

Additional Shortcuts Ctrl‐F1 ‐ Expand/Collapse Ribbon

Alt ‐ display shortcut keys in ribbon

Ctrl‐1 ‐ Format dialog box

Alt= will load the AutoSum feature

Ctrl single quote ‘ ‐ repeat value from cell above

Data that can be typed in a Cell 1) Text or Labels: These are usually alphabetic characters. Any text entered into a cell will be left‐

aligned by default. 2) Values: A value would consist of numeric characters. By default, values are right‐aligned in a

cell. Values may be formatted to display commas, $, & or other characters. These formatting characters should not be typed into the cell, but should be added by selecting the cell(s) and applying the desired format.

3) Formulas: A cell may contain a formula. A formula, an expression used to calculate data on a worksheet, will always start with an = (equal sign).

Entering/Editing Data To enter data (or activate a cell), click once in the cell. This makes that cell active and you can now enter data. To edit a cell’s contents, double click in a cell and edit the content’s or click with the mouse in the formula bar and edit from there.


Once you have entered your data, be sure to complete the entry by exiting the cell, hitting enter, tab, or your arrow keys, or clicking on the check mark on the formula bar. To cancel an edit or an entry, press Esc on the keyboard or click the X on the formula bar.

Click with the mouse in a cell to make it the active cell

Use arrow keys to move up, down, left, or right. This will also complete a cell with data in it.

Enter ‐ Complete a cell entry and select the cell below.

o This can be changed through the File Tab, Options, Advanced

Esc ‐ Cancel a cell edit or entry.

Tab ‐ Complete a cell entry and select the next cell to the right.

Shift‐Tab ‐ Complete a cell entry and select the previous cell to the left.

Tip: Excel remembers the active cell when you save the file. When you open the file at a later time, the cursor will display in the active cell from the previous file use.

Insert the Same Data in a Range o Select the Range of cells o Type the data o Press Ctrl‐Enter at the same time to repeat the data in the selected cells

Date/Time Formats Excel has many formats for date/time. Internally, Excel stores dates and times as sequential numbers so that mathematical calculations can be performed. For example, you can calculate the number of days between two date entries by subtracting one from the other. Because the date is stored as a numeric value, if cell’s format is numeric or currency, you will see the date as a serial number. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. To change the date/time format for a cell, select the cell and choose the dialog box for Number. From the Format Cells window, select Date. Note: with the advent of the year 2000, it is a good idea to format all your dates with 4 digit years, such as 2000, 2015, etc. Years entered as 2 digits will be converted as follows: 00‐29 will convert to 2000‐2029; 30‐99 will convert to 1930‐1999. Note: If you want to enter a date as a header (09‐10), you will find that Excel will alter the format into a date, showing 10‐Sep. If you want to make sure the data appears as you typed it, enter a single quote before your text, ’09‐10. The single quote will not appear in the cell, it will display as 09‐10. Entering Current Date/Time Below are shortcuts to entering the current date and/or time.

Date ‐ Hold Ctrl and press ; to insert the current date. (This inserts a static date.)

Time ‐ Hold Ctrl +Shift and press ; to insert the current time. Tip: To enter a date code that will update any time the workbook is opened, type the function =Today() into a cell.


Add Worksheets By default, each new workbook is set up to display with one worksheet. The default setting for the number of worksheets can be changed in the Excel Options menu, which is located in the File, Options, General menu. To add additional worksheets to your workbook, click on the New Worksheet icon that is located on the right side of the worksheets on the bottom of the page. This will insert a new worksheet to the right of the active worksheet. (The active worksheet is highlighted in green) You may also insert a new worksheet by right‐clicking the mouse on a worksheet tab and choosing Insert. On the Insert window, click on the Worksheet icon and click OK, or double click on the Worksheet icon. This will insert a new worksheet to the left of the active worksheet.

Renaming Tabs Each worksheet tab in Excel can be renamed. The easiest way to rename a tab is to

double click on the tab and type the new name. You may also right‐click the mouse on the tab and choose Rename from the menu.

Delete a Worksheet Right click the mouse on a worksheet tab and select “Delete” to remove it from the workbook.

Worksheet Navigation Shortcuts Ctrl‐Page Up ‐ Switch between worksheet tabs, moving from Right to Left

Ctrl‐Page Down ‐ Switch between worksheet tabs, moving from Left to Right

Right click on the Navigator (located to the left of the worksheets) to display a list of all worksheets in the workbook

Click the left or right arrow on the Navigator to move the sheets to the left or the right. o This will not change the current active worksheet.

Ctrl‐Left click – scroll to the last sheet

Changing Tab Colors You are able to change the color of any worksheet tabs. This may be helpful for organizing data on separate tabs. To change the color, right click on the worksheet, navigate to Tab color, and pick the color you want. If you need more color options, select the More Colors option on the bottom of with menu.


Excel Icons When the mouse is over any of the cells in a worksheet, the cursor is a hollow plus sign. A Hollow Plus sign indicates selection of a cell or range of cells.

If you double click in a cell, a single flashing cursor line will appear. This cursor indicates you are in editing mode. Any characters you type will be entered into the cell in the location of the cursor.

Click on a cell to make it active and position the cursor in the lower right corner of the cell. The cursor will change to a dark plus sign which indicates Copy or Auto fill. If there is text in the cell, dragging the “+” sign, will copy the contents to all other cells. If there is a formula in this cell, dragging this “+” sign downward or to the right will “auto fill” all adjacent cells. (“Auto fill” will be explained later in this handout.) Click on a cell to make it active and position the cursor on a cell border. A cross‐hair cursor indicates you will move the cell content when you left click the mouse and drag and drop in a new location. Position the cursor in a column or row label, when the cursor is a solid arrow, left click to select the entire row or column, or multiple rows or columns.

Copy and Paste Copy and paste will copy a cell or selection of cells and paste a copy in a new location.

Select the Data and Copy by o Ctrl‐C o Edit, Copy o Right‐click with mouse and choose Copy o Select Copy icon from Standard Toolbar

When pasting cells, make sure you have enough space to paste them in. o For example, if I have four cells selected then I must have four cells or four containers

available to accept the pasted data.

Click in the new location for your data o Press Ctrl‐V to Paste o Choose Edit, Paste o Right‐click the mouse and choose Paste from the Context Sensitive menu.

Tip: The marquee selection remains displayed around selected items. To remove the selection marquee, press the Esc key. Tip: The MS Office clipboard can hold up to 24 items at a time and works across all MS products.

Cut and Paste Cut and paste will “cut” a cell or selection of cells data and move it to a new location.

Select the Data and Cut by o Ctrl‐X o Edit, Cut o Right‐click with mouse and choose Cut o Select Cut icon (scissors) from Standard Toolbar


Paste Special Paste Special is a feature that gives you more control on how copied content is pasted in a new location. To use the Paste Special, copy a set of data that has been formatted, or used in a calculation. To access the Paste Special Window, click on the dropdown menu under the Paste button, Click Ctrl‐Alt‐V, or right click in the new location, go to Paste, then Paste Special. The icons above Paste special are the most commonly used Paste Special options.

You can specify which parts of the current cell selection to use by selecting the appropriate Paste Special options:

All: Paste all the data in the cell selection (formulas, formatting). This is a Normal Paste.

Formulas: Paste all the text, numbers, and formulas in the current cell without their formatting.

Values: Excel will convert any formulas in the current cell selection to their calculated values.

Formats to paste only the formatting from the current cell selection, without the cell entries.

Comments: Paste only the comments that you attach to any cells

Validation: Paste only the data validation rules into the cell range that you set up.

All Using Source Theme: Paste all the data plus the cell formatting applied to the cells.

All Except Borders: Paste all the data in the cell selection without copying any borders styles around the data.

Column Widths: Apply the column widths of the cells copied to the columns where the cells are pasted.

Formulas and Number Formats: Include the number formats assigned to the pasted values and formulas.

Values and Number Formats: Convert formulas to their calculated values and include the number formats you assigned to all the copied or cut values.

All Merging Conditional Formats: Paste conditional formatting into the cell range

Paste Link: Click this button when you want to establish a link between the copies you're pasting and the original entries. That way, changes to the original cells automatically update in the pasted copies.

o Note: This will not keep any formatting applied to cells. Some of the most commonly used Paste Special Functions are available if you use the Right click feature, such as Paste Values, Formatting, and Paste Link. Because of Live Preview, which is enabled by default, you are able to see what you data will look like before you actually paste it in the new location.


Selecting Cells, Rows and Columns Before you can format a cell or group of cells, you will need to select them first. Also, to add or delete columns or rows, you will need to select the number of columns or rows you wish to add or delete, first. Below are the selection techniques that you will use in Excel.

To select a single cell: Click on the cell.

To select multiple cells (range): Click on the first cell (right furthermost cell) and as you hold the

Shift key down, click on the last cell (left farthest cell) in the range.

Select a row or column: Click the row or column label.

Select multiple rows/columns: Hold the left mouse button as you drag horizontally across column

headings or vertically down row headings.

All cells in the worksheet: Press Ctrl‐A, or click the Select All button (where column and row headings intersect.

Non‐contiguous cells/rows/columns: Click the first item to be selected; hold down the Ctrl key as

you select additional items.

Insert/Delete a Row or Column To insert a row or column, navigate your mouse into the row or column headings. When the cursor turns into a dark arrow, highlight and select the same number of rows or columns you wish to insert. Right‐click on the selection to display the menu and choose Insert. The new columns will be inserted to the right of the selection, whereas new rows will be inserted on top of your selection. If you prefer, you can use the Insert icon on the Cells Group of the Home tab to add Rows, Columns, or Cells. To delete a row(s) or column(s), select the row/column to delete. Right‐click on the selection and choose Delete.

Sizing Columns and Rows An easy way to size a column or row is to position the mouse pointer on the border between columns. When you get the double headed arrow, drag the border of the cell to the desired position. Size to Fit: You can also use the double‐headed cursor arrow to adjust a column or row automatically to the widest/narrowest value. Position the cursor on the column or row border and double click the mouse.


Deleting Cell versus Cell content Use the delete key on the keyboard to remove cell content

Select Cells and right‐click mouse and choose Clear Contents

Press the space key when in a cell to remove it contents o CAUTION!!! Adds an empty space to the cell

Right‐click with the mouse and select Delete from the Contextual menu. o CAUTION: deleting a cell will shift other cells to a new location and this can change data

records. If you delete this way, make sure you are allowing for the cell shift as this can create flaws in your data and effect formulas.

AutoFill Auto fill is a tool that will fill cells with data that follows a pattern or that is based on data in other cells. To use the Auto fill feature, make your selection, move your mouse to the lower right side of the selection until your cursor turns into a dark plus sign. Click, Hold and drag the dark plus to Auto fill. To use Auto fill, you need to have least two cells with data so Excel can determine a pattern.

There are a couple exceptions to this rule. If you type in a day of the week, a Month of the Year, or a date, Excel will automatically select the next day, month, or date. When using days, months, or dates, you can type in any one and excel will auto fill with the next piece of data. For example, if you have Wednesday in one cell and you want to complete the week, excel will automatically put Thursday in the next cell, and then Friday, etc.


Customized Autofill List If you have a set of data that you are using a lot that the Auto fill feature doesn’t catch, you are able to

create your own Customized Auto fill list.

To create a customized list, go to File, Options. In the Excel Options window, select Advanced on the left

side, then scroll about 80% of the way to the bottom. Under the General section, you will see a button

called Edit Custom Lists…

On the Custom Lists window, type

your new list in the List entries text

box. Separate each new entry by a

comma or by hitting the Enter key

to get a new line. When you have

entered in your new list, click on the

Add button to Add the list to the

Custom List box. Click OK. You are

now able to type in one of the

pieces of data within your new list

and Excel will auto fill based on the

list you created.

You may also Import a list by selecting the Import button from the custom list dialog box. Click in the text box to the left of the import and then navigate to and highlight your list within Excel. , and select the list from the actual spread sheet. When you have made your selection, click the Import button to add your list to the Custom lists.


Autocomplete With Autocomplete you can type a few letters or digits of a cell entry and Excel will automatically scan other values in the columns for a similar entry. If it finds one, it will try to complete the entry for you. This feature is very beneficial to keep data integrity. For Example, if you have a list of city names that you want to be written the same way every time. When you start typing the F for Fort Collins, Excel scans the current column and sees that there is a string of test starting with an F, Fort Collins. Excel will Autocomplete the cell for you. If the text appearing in the cell is correct, you may hit Enter, Tab, etc. to accept the text and move to the next cell. There are times when Autocomplete can pose a problem. If you have a string of text that you are typing in that is shorter than what is already in the column, you can type in the new string of text and hit Enter, Tab, etc. and Excel will autocomplete the cell with the preexisting text, not what you typed in.

For example. If you want to type in the last name of Berg, as you type in Berg and hit Tab, Excel sees that there is text that is similar to what you are typing and will autocomplete the cell to Bergeron. If you are not watching as you type, you may have bad

data within your spreadsheet. If you do not want this feature active, it can be turned off by choosing File, Options, Advanced and remove the check box for Enable Autocomplete for Cell Values.

Pick from Drop‐down List Pick from a drop‐down list is similar to Autocomplete, but you will see all of the options that are available within the column. Pick from a Drop‐down list is a good way to make sure all of your data is entered in the exact same way. To use Pick from a drop‐down list, right click on the cell that you want to insert you data and select Pick from a drop‐down list. Excel will produce a popup box that will show the unique values within the column. To make a selection, double click with your mouse, or highlight the selection and Hit Enter.

Note: This only works with text, not with numbers or formulas.


Understanding Formulas A formula is an equation that performs calculations using worksheet data. Formulas use cell addresses to perform calculations, rather than actual values which is one of the primary reasons for using and electronic spreadsheet. This is what enables a formula to automatically update whenever the data within a cell is changed. Formulas are used to perform mathematical operations, such as addition, subtraction, division and multiplication, or to compare worksheet values or join text. Formulas can refer to other cells on the same worksheet, cells on other sheets in the same workbook, or cells on sheets in other Excel workbooks. To create a formula: 1) Select the cell in which you want to enter the formula.

2) Type an equal sign (=) to activate the formula bar. 3) Type the formula which may include cell addresses, arithmetic operators and numeric values.

Examples: =gG13*.05 =sum(B1:B24) =A3‐B7 =(A1+B1)/C3 =436+1231+333+A2 Valid arithmetic operators for use in a formula are

Addition ‐ (+)

Subtraction ‐ (‐)

Multiplication ‐ (*)

Division ‐ (/)

Exponents ‐ (^)

Order of Operations When producing arithmetic formulas, Excel will perform the calculations based on the order of operations, from left to right

1. Calculate in Parenthesis 2. Exponents 3. Multiplication and/or Division 4. Addition and/or Subtraction

AutoSum The AutoSum function is a shortcut to typing =Sum(cell1:cell2). Click on the cell where the sum is to be placed and press the AutoSum button located in the Editing Group of the Home tab. Excel types the sum function and suggests the range of cells you want to add. If the suggested range is incorrect, select the correct range with your mouse and press the Enter key to accept the completed formula. To sum both rows and columns, select a range that includes empty cells on the side and bottom to receive the total values.

Tip: to see what cells are being used in a particular formula, click on the cell and hit F2


Other Built‐In Functions You will be able to perform other built‐in functions easily by clicking on the drop‐down arrow to the right of the Auto‐Sum button. From here you can choose Average, Count, Maximum, or Minimum using the same method used in AutoSum above. For functions that do not reside on this list, click on More Functions or the Formulas Tab.

Copying a Formula Once you have entered a formula into a cell, you may choose to copy that formula to a new location. There are two methods for copying formulas: copy/paste and Auto fill. Note: Auto fill only works when copying to adjacent cells. If you wish to copy the contents of a cell from a location to a non‐adjacent cell, you must use copy/paste. Copy/Paste

1. Select the cells to copy;

2. Click on the Copy button (or right‐click to choose Copy);

3. Position the cursor in any new cell (this worksheet/workbook or another);

4. Click on the Paste button (or right‐click to choose Paste).

Relative versus Absolute Cell Addresses It is important to understand how Excel identifies cells in formulas. By default, all cell references are relative references. As a formula is copied across multiple cells, they change based on the relative position of rows and columns. Look at this sample of a Relative Reference.

Excel reads the formula in cell C3 as “take the number located two cells to the left and add that to the number in the cell located one column to the left”. Therefore, when you copy this formula (which appears as =A2+B2) to the cell below, it performs the calculation using the same pattern, but updates the formula to reflect the appropriate cell addresses. This is called a Relative Reference and is the feature that enables you to copy the same formula to a different location in a worksheet. There will be times when you don’t want a formula to update, which is called an Absolute Reference. Unlike relative references, absolute references don’t change when copied or filled. You can use an absolute reference to keep a cell, a row or a column constant. For example, you might want to use a single cell to calculate the percent of sales by category. Rather than doing a separate calculation for every employee, you could include a reference to that specific cell in the formula for each category. This is called an Absolute Reference. To make a cell reference absolute so that it will not adjust when a formula is copied, insert a dollar sign ($) in the appropriate position. By navigating into the formula in the Formula bar, you can cycle through the references by hitting the F4 key on your keyboard.


Press the F4 key one time

Two times

Three times

Example 1: $B$12 Both the Row and the Column are held Constant Example 2: B$12 The Row is held constant, the Column will move Example3: $B12 The Column is constant, the Row will move.

In this example, we are dividing by our total sales, which is cell B12. We create our formula in Cell C5, go into the formula bar to put our cursor within the cell reference of B12 and hit our F4 key one time to get our reference to B12. Now auto fill to the remaining cells.

Class exercise. Entering Data and Formulas Use the Excel Features to complete this worksheet. We are projecting Sales to be $100 in January, then we are expecting a 10% increase for each month thereafter. Expenses are projected to be 60% of each month’s sales. Tools to use;

AutoFill

Copying formula

Absolute Referencing

AutoSum


Excel 2013 Worksheet and workbook specifications and limits

FEATURE MAXIMUM LIMIT

Open workbooks Limited by available memory and system resources

Worksheet size 1,048,576 rows by 16,384 columns

Column width 255 characters

Row height 409 points

Page breaks 1,026 horizontal and vertical

Total number of characters that a cell can contain

32,767 characters

Characters in a header or footer

255

Sheets in a workbook Limited by available memory (default is 1 sheet)

Colors in a workbook 16 million colors (32 bit with full access to 24 bit color spectrum)

Named views in a workbook Limited by available memory

Unique cell formats/cell styles

64,000

Fill styles 256

Line weight and styles 256

Unique font types 1,024 global fonts available for use; 512 per workbook

Number formats in a workbook

Between 200 and 250, depending on the language version of Excel that you have installed

Names in a workbook Limited by available memory

Windows in a workbook Limited by available memory

Hyperlinks in a worksheet 66,530 hyperlinks

Panes in a window 4

Linked sheets Limited by available memory

Scenarios Limited by available memory; a summary report shows only the first 251 scenarios

Changing cells in a scenario 32

Adjustable cells in Solver 200

Custom functions Limited by available memory

Zoom range 10 percent to 400 percent

Reports Limited by available memory

Sort references 64 in a single sort; unlimited when using sequential sorts

Undo levels 100

Fields in a data form 32


Workbook parameters 255 parameters per workbook

Items displayed in filter drop‐down lists

10,000

Noncontiguous cells that can be selected

2,147,483,648 cells

Maximum limits of memory storage and file size for Data Model workbooks

32‐bit environment is subject to 2 gigabytes (GB) of virtual address space, shared by Excel, the workbook, and add‐ins that run in the same process. A data model’s share of the address space might run up to 500 – 700 megabytes (MB), but could be less if other data models and add‐ins are loaded. 64‐bit environment imposes no hard limits on file size. Workbook size is limited only by available memory and system resources. NOTE Adding tables to the Data Model increases the file size. If you don’t plan to create complex Data Model relationships using many data sources and data types in your workbook, uncheck the Add this data to the Data Model box when you import or create tables, pivot tables, or data connections. For more information, see Data Model specification and limits.


Calculation specifications and limits


Number precision 15 digits

Smallest allowed negative number ‐2.2251E‐308

Smallest allowed positive number 2.2251E‐308

Largest allowed positive number 9.99999999999999E+307

Largest allowed negative number ‐9.99999999999999E+307

Largest allowed positive number via formula 1.7976931348623158e+308

Largest allowed negative number via formula

‐1.7976931348623158e+308

Length of formula contents 8,192 characters

Internal length of formula 16,384 bytes

Iterations 32,767

Worksheet arrays Limited by available memory

Selected ranges 2,048

Arguments in a function 255

Nested levels of functions 64

User defined function categories 255

Number of available worksheet functions 341

Size of the operand stack 1,024

Cross‐worksheet dependency 64,000 worksheets that can refer to other sheets

Cross‐worksheet array formula dependency Limited by available memory

Area dependency Limited by available memory

Area dependency per worksheet Limited by available memory

Dependency on a single cell 4 billion formulas that can depend on a single cell

Linked cell content length from closed workbooks

32,767

Earliest date allowed for calculation January 1, 1900 (January 1, 1904, if 1904 date system is used)

Latest date allowed for calculation December 31, 9999

Largest amount of time that can be entered 9999:59:59


Charting specifications and limits


Charts linked to a worksheet Limited by available memory

Worksheets referred to by a chart 255

Data series in one chart 255

Data points in a data series for 2‐D charts Limited by available memory

Data points in a data series for 3‐D charts Limited by available memory

Data points for all data series in one chart Limited by available memory

PivotTable and PivotChart report specifications and limits


PivotTable reports on a sheet Limited by available memory

Unique items per field 1,048,576

Row or column fields in a PivotTable report Limited by available memory

Report filters in a PivotTable report 256 (may be limited by available memory)

Value fields in a PivotTable report 256

Calculated item formulas in a PivotTable report Limited by available memory

Report filters in a PivotChart report 256 (may be limited by available memory)

Value fields in a PivotChart report 256

Calculated item formulas in a PivotChart report Limited by available memory

Length of the MDX name for a PivotTable item 32,767

Length for a relational PivotTable string 32,767

Items displayed in filter drop‐down lists 10,000

Shared workbook specifications and limits


Users who can open and share a shared workbook at the same time

256

Personal views in a shared workbook Limited by available memory

Days that change history is maintained 32,767 (default is 30 days)

Workbooks that can be merged at one time Limited by available memory

Cells that can be highlighted in a shared workbook 32,767

Colors used to identify changes made by different users when change highlighting is turned on

32 (each user is identified by a separate color; changes made by the current user are highlighted with navy blue)

Excel tables in a shared workbook 0 (zero)