# data[].attributes.headline
Accessible Alternatives for Common Excel Tasks

# data[].attributes.subject
Accessible Alternatives for Common Excel Tasks

# data[].attributes.headline
Consolidate multiple worksheets into one PivotTable in Excel 

# data[].attributes.subject
Consolidate multiple worksheets into one PivotTable in Excel 

# data[].attributes.attachment_link.title
Consolidate multiple worksheets into one PivotTable in Excel - Microsoft Support

# data[].attributes.attachment_link.description
Consolidating data is a useful way to combine data from different sources into one report.

# data[].attributes.headline
How to Create Pivot Table From Multiple Worksheets

# data[].attributes.subject
How to Create Pivot Table From Multiple Worksheets

# data[].attributes.attachment_link.title
How to Create Pivot Table From Multiple Worksheets

# data[].attributes.attachment_link.description
It is possible to create Pivot Table from multiple worksheets in case the source data is located on two or more worksheets and cannot be combined.

# data[].attributes.headline
Replace Blanks in a Pivot Table with Zeroes

# data[].attributes.subject
Replace Blanks in a Pivot Table with Zeroes

# data[].attributes.attachment_link.title
Replace Blanks in a Pivot Table with Zeros

# data[].attributes.attachment_link.description
When I have no sales of a particular product in a particular region, Excel leaves those cells in the pivot table blank. This seems like a really bad idea.

# data[].attributes.body
Visit Padlet

Frequently asked questions and support documentation for Padlet

How to Use Padlet: Fantastic Tool

30 Creative Ways to Use Padlet

20 Useful Ways to Use Padlet Now

5 Reasons to Use Padlet Tomorrow

Appsolutely! Create your own online interactive whiteboard with Padlet

32 Interesting Ways to Use Padlet in the Classroom

# data[].attributes.headline
Finally! A Definitive List of What Those F1 – F12 Keys Do

# data[].attributes.subject
Finally! A Definitive List of What Those F1 – F12 Keys Do

# data[].attributes.attachment_link.title
Finally! A Definitive List of What Those F1 – F12 Keys Do

# data[].attributes.attachment_link.description
You can stop wondering about those function keys. We have the answer. The post Finally! A Definitive List of What Those F1 – F12 Keys Do appeared first on Reader’s Digest.

# data[].attributes.headline
How to Find the Median with a Pivot Table in Excel 

# data[].attributes.subject
How to Find the Median with a Pivot Table in Excel 

# data[].attributes.attachment_link.title
How to Find the Median with a Pivot Table in Excel | HR Metrics Example

# data[].attributes.headline
Calculating the median in Excel PivotTables

# data[].attributes.subject
Calculating the median in Excel PivotTables

# data[].attributes.attachment_link.title
Calculating the median in Excel PivotTables

# data[].attributes.attachment_link.description
Learn how to add the median as a calculation in PivotTables.

# data[].attributes.headline
Add or remove items from a drop-down list (Data Validation prior to Excel 2021

# data[].attributes.subject
Add or remove items from a drop-down list (Data Validation prior to Excel 2021

# data[].attributes.attachment_link.readable_text
Edit a drop-down list that's based on an Excel Table

If you set up your list source as an Excel table, then all you need to do is add or remove items from the list, and Excel will automatically update any associated drop-downs for you.

To add an item, go to the end of the list and type the new item.

To remove an item, press Delete.

Tip: If the item you want to delete is somewhere in the middle of your list, right-click its cell, click Delete, and then click OK to shift the cells up.

1. On the worksheet where you applied the drop-down list, select a cell that has the drop-down list.

2. On the Settings tab, click in the Source box, and then change your list items as needed. Each item should be separated by a comma, with no spaces in between like this: Yes,No,Maybe.

3. To update all cells that have the same drop-down list applied, check the Apply these changes to all other cells with the same settings box.

After you update a drop-down list, make sure it works the way you want. For example, check to see if the cell is wide enough to show your updated entries.

If the list of entries for your drop-down list is on another worksheet and you want to prevent users from seeing it or making changes, consider hiding and protecting that worksheet. For more information about how to protect a worksheet, see Lock cells to protect them.

If you want to delete your drop-down list, see Remove a drop-down list.

See Create a drop-down list.

Edit a drop-down list that's based on an Excel Table

If you set up your list source as an Excel table, then all you need to do is add or remove items from the list, and Excel will automatically update any associated drop-downs for you.

To add an item, go to the end of the list and type the new item.

To remove an item, press Delete.

Tip: If the item you want to delete is somewhere in the middle of your list, right-click its cell, click Delete, and then click OK to shift the cells up.

1. On the worksheet where you applied the drop-down list, select a cell that has the drop-down list.

2. On the Settings tab, click in the Source box, and then change your list items as needed. Each item should be separated by a comma, with no spaces in between like this: Yes,No,Maybe.

3. To update all cells that have the same drop-down list applied, check the Apply these changes to all other cells with the same settings box.

In Excel for the web, you can only edit a drop-down list where the source data has been entered manually.

1. Select the cells that have the drop-down list.

2. Go to Data > Data Validation.

3. On the Settings tab, click in the Source box. Then do one of the following:

If the Source box contains drop-down entries separated by commas, then type new entries or remove ones you don't need. When you're done, each entry should be separated by a comma, with no spaces. For example: Fruits,Vegetables,Meat,Deli.

If the Source box contains a reference to a range of cells (for example, =$A$2:$A$5), click Cancel, and then add or remove entries from those cells. In this example, you'd add or remove entries in cells A2 through A5. If the list of entries ends up being longer or shorter than the original range, go back to the Settings tab and delete what's in the Source box. Then click and drag to select the new range containing the entries.

If the Source box contains a named range, like Departments, then you need to change the range itself using a desktop version of Excel.

After you update a drop-down list, make sure it works the way you want. For example, check to see if the cell is wide enough to show your updated entries. If you want to delete your drop-down list, see Remove a drop-down list.

Create a drop-down list

Apply Data Validation to cells

# data[].attributes.attachment_link.title
Add or remove items from a drop-down list

# data[].attributes.attachment_link.description
Add or remove items from an existing drop-down list (also called a drop-down menu or drop-down box).

# data[].attributes.headline
Create a dynamic dependent drop down list in Excel an easy way (Excel 2021 and later)

# data[].attributes.subject
Create a dynamic dependent drop down list in Excel an easy way (Excel 2021 and later)

# data[].attributes.attachment_link.title
Create a dynamic dependent drop down list in Excel an easy way

# data[].attributes.attachment_link.description
See how to make a dependent dropdown list in Excel by using dynamic array functions. Create multiple dropdown, make the list expandable, have the items sorted alphabetically, and more.

# data[].attributes.headline
difference between reg pst and keep src frmt/

# data[].attributes.subject
difference between reg pst and keep src frmt/

# data[].attributes.attachment_link.readable_text
Sometimes we setup Excel worksheets so that they flow logically on the screen. Often times they're set up like you would read a book, top to bottom, left to right, but this isn't always the best setup for printing.

Suppose you have a spreadsheet with detailed information in the first 50 rows, and then a summary in the next 10. There could be times where you don't care to print the details, and only want the summary, or the opposite. Of course, you could set up your spreadsheet so that the details are in one worksheet and the summary is in another, each with their print areas set up correctly. But that would make viewing the spreadsheet on the screen that much more difficult, as you would have to click between the two in order to analyze the data.

This is where custom views help. Read below for a step-by-step example on how to setup custom views.

This example assumes that you have your detailed information (print area # 1) in the first 50 rows in columns A through M, and your summary information (print area # 2) in the next 10 rows in the same columns. You can set more than just two print areas using custom views, but for simplicity's sake, we'll use two for now.

First, select print area # 1 (cells A1:M50)

Click File | Print Area | Set Print Area

Click File | Page Setup and set your print settings for that area (i.e. header/footer, portrait/landscape, letter/legal/other paper size, fit page to 1 by 1 or 100%, etc.)

Click View | Custom Views and then click the "Add" button. In the dialog box, enter a description of the view (in this case, "print_detail" without the quotes would work)

Save your file.

Now that you have your first print area setup, we'll repeat the previous steps to setup the second print area for the summary section.

Select print area # 2 (cells A51:M60)

Click File | Print Area | Set Print Area

Click File | Page Setup and set your print settings for that area (i.e. header/footer, portrait/landscape, letter/legal/other paper size, fit page to 1 by 1 or 100%, etc.)

Click View | Custom Views and then click the "Add" button. In the dialog box, enter a description of the view (in this case, "print_summary" without the quotes would work)

Save your file.

OK, both print areas are setup. Now we need to be able to use them.

Click View | Custom Views

You should see the two custom views we just created in the box, print_details and print_summary

Click on the one you wish to print and then click the "Show" button

You can double check that the print area is set to your liking by clicking File | Print Preview. This will show how your printout will look after printing. If you don't need to make any changes, simply click File | Print.

Added Bonus - Set a Macro to Print Your Custom View

So far we've covered how to set a custom view to make printing easier. We'll lets go one step further and make it even easier.

Insert the following macro code in your Visual Basic Editor to have Excel select the custom view and print it out all in one click!

Note: The macro code will print one custom view (currently set to print the details from the example above). You'll need to copy this macro for as many custom views as you have. For each custom view change print_custom_view() to a unique name and also change the line "print_details" to whatever you have named your custom view.

# data[].attributes.attachment_link.title
Saving Multiple Print Ranges in Excel - Excel Zoom

# data[].attributes.attachment_link.description
Sometimes we setup Excel worksheets so that they flow logically on the screen. Often times they’re set up like you would read a book, top to bottom, left to right, but this isn’t always the best setup for printing. Suppose you have a spreadsheet with detailed information in the first 50 rows, and then a […]

# data[].attributes.attachment_link.readable_text
Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Using Named Formulas Across Workbooks.

Excel allows you to define names that refer to specific cells or ranges of cells in a workbook. In the same manner (using the Define Name tool on the Formulas tab of the ribbon) you can assign a formula to a name, and then use that name in place of the formula throughout the workbook.

A named formula is part of a collection in workbook object. This is why it can be used across different sheets in the same workbook and (in most cases) acts like it is part of the same "sheet" for many functions and routines.

To use a name in another workbook, your workbook must have a link to that name in the other workbook. There are a couple of ways to do this. The first is to link to the named formula with a formula like this:

='C:\Folder\Path\Filename.xls'!NamedFormula

This can be copied in multiple cells. The other way is to create a name in the workbook (it can be the same or different than the name in the other workbook). Just display the New Name dialog box (click the Define Name tool on the Formulas tab of the ribbon) and use the following in the Refers To field:

='C:\Folder\Path\Filename.xls'!NamedFormula

And now the workbook has a name and it refers to the named formula in the other workbook.

Both techniques create a "link" to the original workbook. There is one problem with either of these methods, however. Many simple formulas (the "direct links," like named ranges) will work even if the original file is closed. The more complicated formulas (which act like "indirect links," formulas with offset or other functions) will give a #REF! error if the original workbook is closed. In this latter case, the references will work only if both workbooks are open.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8668) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here:Using Named Formulas Across Workbooks.

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. Learn more about Allen...

Understanding Macros

What is a macro? Ever wonder what these are and how to use them? This tip answers the basics of what a macro is used for, ...

Discover More

Quickly Selecting Cells

Need to quickly select a range of cells? Perhaps the easiest way is to use both the mouse and the keyboard together, as ...

Discover More

Editing a Building Block Entry

Once you've created a Building Block, you may believe that it is "set in stone" and cannot be changed. Not so! You can ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

# data[].attributes.attachment_link.description
Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you.

# data[].attributes.headline
Conditional Formatting Macro For Duplicate Value

# data[].attributes.subject
Conditional Formatting Macro For Duplicate Value

# data[].attributes.attachment_link.title
Conditional Formatting Macro For Duplicate Values in Excel | I'm MS Excel Expert Tutorial | Day 31

# data[].attributes.headline
How To See Circular References in Excel: A Complete Guide

# data[].attributes.subject
How To See Circular References in Excel: A Complete Guide

# data[].attributes.attachment_link.description
Your Ray ID for this request is 8ece8b7fdd5cde9b

# data[].attributes.attachment_link.readable_text
With built-in custom lists, you can sort data-either by days of the week or months of the year. Optionally, you can create your own custom lists to sort by any other characteristic that doesn't sort well alphabetically-such as high, medium, and low-or S, M, L, XL.

With custom lists, you could sort this worksheet either by Delivery month or by Priority.

Sort by days of the week or months of the year with a built-in custom list

Follow these steps:

1. Select the columns to sort.

Note: For the best results, each column should have a heading.

2. In the Sort popup window, in the Sort by drop-down, choose the column on which you need to sort.

For example, if you want to re-sort the previous example by delivery date, under Sort by, choose delivery.

Create your own custom list

Follow these steps to create your own custom list for sorting:

1. In a column of a worksheet, type the values to sort by. Arrange them in the order that you want to define the sort order-from top to bottom. For example:

2. Select all of the cells in that list, and then click File > Options > Advanced.

3. Scroll way down to the General section and click Edit Custom Lists...

Tip: For a short list-such as High, Medium, and Low, it may be faster to enter it directly into the List entries box in the Custom Lists box.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in Communities.

Create or delete a custom list for sorting and filling data

# data[].attributes.attachment_link.description
Sort a list by days of the week or months of the year or sort by priorities in Excel. Or you can create your own custom list for items that don't sort well alphabetically.

# data[].attributes.attachment_link.description
How to calculate fiscal year, fiscal month, fiscal quarter in Excel, based on fiscal year starting date. Get the free workbook…

# data[].attributes.headline
Change the default theme (Excel, Word, PowerPoint)

# data[].attributes.subject
Change the default theme (Excel, Word, PowerPoint)

# data[].attributes.attachment_link.readable_text
Are you facing a repetitive clean up of fifty tables in Word? Do you want a particular document to prompt the user for input when it opens? Are you having difficulty figuring out how to get your contacts from Microsoft Outlook into a Microsoft Excel spreadsheet efficiently?

You can perform these tasks and accomplish a great deal more by using Visual Basic for Applications (VBA) for Office-a simple, but powerful programming language that you can use to extend Office applications.

This article is for experienced Office users who want to learn about VBA and who want some insight into how programming can help them to customize Office.

The Office suite of applications has a rich set of features. There are many different ways to author, format, and manipulate documents, email, databases, forms, spreadsheets, and presentations. The great power of VBA programming in Office is that nearly every operation that you can perform with a mouse, keyboard, or a dialog box can also be done by using VBA. Further, if it can be done once with VBA, it can be done just as easily a hundred times. (In fact, the automation of repetitive tasks is one of the most common uses of VBA in Office.)

Beyond the power of scripting VBA to accelerate every-day tasks, you can use VBA to add new functionality to Office applications or to prompt and interact with the user of your documents in ways that are specific to your business needs. For example, you could write some VBA code that displays a pop up message that reminds users to save a document to a particular network drive the first time they try to save it.

This article explores some of the primary reasons to leverage the power of VBA programming. It explores the VBA language and the out-of-the-box tools that you can use to work with your solutions. Finally, it includes some tips and ways to avoid some common programming frustrations and missteps.

Note

Interested in developing solutions that extend the Office experience across multiple platforms? Check out the new Office Add-ins model. Office Add-ins have a small footprint compared to VSTO Add-ins and solutions, and you can build them by using almost any web programming technology, such as HTML5, JavaScript, CSS3, and XML.

When to use VBA and why

There are several principal reasons to consider VBA programming in Office.

Automation and repetition

VBA is effective and efficient when it comes to repetitive solutions to formatting or correction problems. For example, have you ever changed the style of the paragraph at the top of each page in Word? Have you ever had to reformat multiple tables that were pasted from Excel into a Word document or an Outlook email? Have you ever had to make the same change in multiple Outlook contacts?

If you have a change that you have to make more than ten or twenty times, it may be worth automating it with VBA. If it is a change that you have to do hundreds of times, it certainly is worth considering. Almost any formatting or editing change that you can do by hand, can be done in VBA.

Extensions to user interaction

There are times when you want to encourage or compel users to interact with the Office application or document in a particular way that is not part of the standard application. For example, you might want to prompt users to take some particular action when they open, save, or print a document.

Interaction between Office applications

Do you need to copy all of your contacts from Outlook to Word and then format them in some particular way? Or, do you need to move data from Excel to a set of PowerPoint slides? Sometimes simple copy and paste does not do what you want it to do, or it is too slow. Use VBA programming to interact with the details of two or more Office applications at the same time and then modify the content in one application based on the content in another.

Doing things another way

VBA programming is a powerful solution, but it is not always the optimal approach. Sometimes it makes sense to use other ways to achieve your aims.

The critical question to ask is whether there is an easier way. Before you begin a VBA project, consider the built-in tools and standard functionalities. For example, if you have a time-consuming editing or layout task, consider using styles or accelerator keys to solve the problem. Can you perform the task once and then use CTRL+Y (Redo) to repeat it? Can you create a new document with the correct format or template, and then copy the content into that new document?

Office applications are powerful; the solution that you need may already be there. Take some time to learn more about Office before you jump into programming.

Before you begin a VBA project, ensure that you have the time to work with VBA. Programming requires focus and can be unpredictable. Especially as a beginner, never turn to programming unless you have time to work carefully. Trying to write a "quick script" to solve a problem when a deadline looms can result in a very stressful situation. If you are in a rush, you might want to use conventional methods, even if they are monotonous and repetitive.

VBA Programming 101

Using code to make applications do things

You might think that writing code is mysterious or difficult, but the basic principles use every-day reasoning and are quite accessible. Microsoft Office applications are created in such a way that they expose things called objects that can receive instructions, in much the same way that a phone is designed with buttons that you use to interact with the phone. When you press a button, the phone recognizes the instruction and includes the corresponding number in the sequence that you are dialing. In programming, you interact with the application by sending instructions to various objects in the application. These objects are expansive, but they have their limits. They can only do what they are designed to do, and they will only do what you instruct them to do.

For example, consider the user who opens a document in Word, makes a few changes, saves the document, and then closes it. In the world of VBA programming, Word exposes a Document object. By using VBA code, you can instruct the Document object to do things such as Open, Save, or Close.

The following section discusses how objects are organized and described.

The Object Model

Developers organize programming objects in a hierarchy, and that hierarchy is called the object model of the application. Word, for example, has a top-level Application object that contains a Document object. The Document object contains Paragraph objects and so on. Object models roughly mirror what you see in the user interface. They are a conceptual map of the application and its capabilities.

The definition of an object is called a class, so you might see these two terms used interchangeably. Technically, a class is the description or template that is used to create, or instantiate, an object.

Once an object exists, you can manipulate it by setting its properties and calling its methods. If you think of the object as a noun, the properties are the adjectives that describe the noun and the methods are the verbs that animate the noun. Changing a property changes some quality of appearance or behavior of the object. Calling one of the object methods causes the object to perform some action.

The VBA code in this article runs against an open Office application where many of the objects that the code manipulates are already up and running; for example, the Application itself, the Worksheet in Excel, the Document in Word, the Presentation in PowerPoint, the Explorer and Folder objects in Outlook. Once you know the basic layout of the object model and some key properties of the Application that give access to its current state, you can start to extend and manipulate that Office application with VBA in Office.

Methods

In Word, for example, you can change the properties and invoke the methods of the current Word document by using the ActiveDocument property of the Application object. This ActiveDocument property returns a reference to the Document object that is currently active in the Word application. "Returns a reference to" means "gives you access to."

The following code does exactly what it says; that is, it saves the active document in the application.

Application.ActiveDocument.Save

Read the code from left to right, "In this Application, with the Document referenced by ActiveDocument, invoke the Save method." Be aware that Save is the simplest form of method; it does not require any detailed instructions from you. You instruct a Document object to Save and it does not require any more input from you.

If a method requires more information, those details are called parameters. The following code runs the SaveAs method, which requires a new name for the file.

Application.ActiveDocument.SaveAs ("New Document Name.docx")

Values listed in parentheses after a method name are the parameters. Here, the new name for the file is a parameter for the SaveAs method.

Properties

You use the same syntax to set a property that you use to read a property. The following code executes a method to select cell A1 in Excel and then to set a property to put something in that cell.

 Application.ActiveSheet.Range("A1").Select Application.Selection.Value = "Hello World"

The first challenge in VBA programming is to get a feeling for the object model of each Office application and to read the object, method, and property syntax. The object models are similar in all Office applications, but each is specific to the kind of documents and objects that it manipulates.

In the first line of the code snippet, there is the Application object, Excel this time, and then the ActiveSheet, which provides access to the active worksheet. After that is a term not as familiar, Range, which means "define a range of cells in this way." The code instructs Range to create itself with just A1 as its defined set of cells. In other words, the first line of code defines an object, the Range, and runs a method against it to select it. The result is automatically stored in another property of the Application called Selection.

The second line of code sets the Value property of Selection to the text "Hello World", and that value appears in cell A1.

The simplest VBA code that you write might simply gain access to objects in the Office application that you are working with and set properties. For example, you could get access to the rows in a table in Word and change their formatting in your VBA script.

That sounds simple, but it can be incredibly useful; once you can write that code, you can harness all of the power of programming to make those same changes in several tables or documents, or make them according to some logic or condition. For a computer, making 1000 changes is no different from making 10, so there is an economy of scale here with larger documents and problems, and that is where VBA can really shine and save you time.

Macros and the Visual Basic Editor

Now that you know something about how Office applications expose their object models, you are probably eager to try calling object methods, setting object properties, and responding to object events. To do so, you must write your code in a place and in a way that Office can understand; typically, by using the Visual Basic Editor. Although it is installed by default, many users don't know that it is even available until it is enabled on the ribbon.

All Office applications use the ribbon. One tab on the ribbon is the Developer tab, where you access the Visual Basic Editor and other developer tools. Because Office does not display the Developer tab by default, you must enable it by using the following procedure:

To enable the Developer tab

1. On the File tab, choose Options to open the Options dialog box.

2. Choose Customize Ribbon on the left side of the dialog box.

3. Under Choose commands from on the left side of the dialog box, select Popular Commands.

4. Under Customize the Ribbon on the right side of the dialog box, select Main Tabs in the drop down list box, and then select the Developer checkbox.

Note

In Office 2007, you displayed the Developer tab by choosing the Office button, choosing Options, and then selecting the Show Developer tab in Ribbon check box in the Popular category of the Options dialog box.

After you enable the Developer tab, it is easy to find the Visual Basic and Macros buttons.
Figure 1. Buttons on the Developer tab
Security issues

To protect Office users against viruses and dangerous macro code, you cannot save macro code in a standard Office document that uses a standard file extension. Instead, you must save the code in a file with a special extension. For example you cannot save macros in a standard Word document with a .docx extension; instead, you must use a special Word Macro-Enabled Document with a .docm extension.

When you open a .docm file, Office security might still prevent the macros in the document from running, with or without telling you. Examine the settings and options in the Trust Center on all Office applications. The default setting disables macro from running, but warns you that macros have been disabled and gives you the option to turn them back on for that document.

You can designate specific folders where macros can run by creating Trusted Locations, Trusted Documents, or Trusted Publishers. The most portable option is to use Trusted Publishers, which works with digitally signed documents that you distribute. For more information about the security settings in a particular Office application, open the Options dialog box, choose Trust Center, and then choose Trust Center Settings.

Note

Some Office applications, like Outlook, save macros by default in a master template on your local computer. Although that strategy reduces the local security issues on your own computer when you run your own macros, it requires a deployment strategy if you want to distribute your macro.

Recording a macro

When you choose the Macro button on the Developer tab, it opens the Macros dialog box, which gives you access to VBA subroutines or macros that you can access from a particular document or application. The Visual Basic button opens the Visual Basic Editor, where you create and edit VBA code.

Another button on the Developer tab in Word and Excel is the Record Macro button, which automatically generates VBA code that can reproduce the actions that you perform in the application. Record Macro is a terrific tool that you can use to learn more about VBA. Reading the generated code can give you insight into VBA and provide a stable bridge between your knowledge of Office as a user and your knowledge as a programmer. The only caveat is that the generated code can be confusing because the Macro editor must make some assumptions about your intentions, and those assumptions are not necessarily accurate.

To record a macro

1. Open Excel to a new Workbook and choose the Developer tab in the ribbon. Choose Record Macro and accept all of the default settings in the Record Macro dialog box, including Macro1 as the name of the macro and This Workbook as the location.

2. Choose OK to begin recording the macro. Note how the button text changes to Stop Recording. Choose that button the instant you complete the actions that you want to record.

3. Choose cell B1 and type the programmer's classic first string: Hello World. Stop typing and look at the Stop Recording button; it is grayed out because Excel is waiting for you to finish typing the value in the cell.

4. Choose cell B2 to complete the action in cell B1, and then choose Stop Recording.

5. Choose Macros on the Developer tab, select Macro1 if it is not selected, and then choose Edit to view the code from Macro1 in the Visual Basic Editor.

Figure 2. Macro code in Visual Basic Editor
Looking at the code

The macro that you created should look similar to the following code.

Sub Macro1() ' ' Macro1 Macro ' ' Range("B1").Select ActiveCell.FormulaR1C1 = "Hello World" Range("B2").Select End Sub

Be aware of the similarities to the earlier code snippet that selected text in cell A1, and the differences. In this code, cell B1 is selected, and then the string "Hello World" is applied to the cell that has been made active. The quotes around the text specify a string value as opposed to a numeric value.

Remember how you chose cell B2 to display the Stop Recording button again? That action shows up as a line of code as well. The macro recorder records every keystroke.

The lines of code that start with an apostrophe and colored green by the editor are comments that explain the code or remind you and other programmers the purpose of the code. VBA ignores any line, or portion of a line, that begins with a single quote. Writing clear and appropriate comments in your code is an important topic, but that discussion is out of the scope of this article. Subsequent references to this code in the article don't include those four comment lines.

When the macro recorder generates the code, it uses a complex algorithm to determine the methods and the properties that you intended. If you don't recognize a given property, there are many resources available to help you. For example, in the macro that you recorded, the macro recorder generated code that refers to the FormulaR1C1 property. Not sure what that means?

Note

Be aware that Application object is implied in all VBA macros. The code that you recorded works with Application. at the beginning of each line.

Using Developer Help

Select FormulaR1C1 in the recorded macro and press F1. The Help system runs a quick search, determines that the appropriate subjects are in the Excel Developer section of the Excel Help, and lists the FormulaR1C1 property. You can choose the link to read more about the property, but before you do, be aware of the Excel Object Model Reference link near the bottom of the window. Choose the link to view a long list of objects that Excel uses in its object model to describe the Worksheets and their components.

Choose any one of those to see the properties and methods that apply to that particular object, along with cross references to different related options. Many Help entries also have brief code examples that can help you. For example, you can follow the links in the Borders object to see how to set a border in VBA.

Worksheets(1).Range("A1").Borders.LineStyle = xlDouble

Editing the code

The Borders code looks different from the recorded macro. One thing that can be confusing with an object model is that there is more than one way to address any given object, cell A1 in this example.

Sometimes the best way to learn programming is to make minor changes to some working code and see what happens as a result. Try it now. Open Macro1 in the Visual Basic Editor and change the code to the following.

Sub Macro1() Worksheets(1).Range("A1").Value = "Wow!" Worksheets(1).Range("A1").Borders.LineStyle = xlDouble End Sub

Tip

Use Copy and Paste as much as possible when working with code to avoid typing errors.

You don't need to save the code to try it out, so return to the Excel document, choose Macros on the Developer tab, choose Macro1, and then choose Run. Cell A1 now contains the text Wow! and has a double-line border around it.
Figure 3. Results of your first macro
You just combined macro recording, reading the object model documentation, and simple programming to make a VBA program that does something. Congratulations!

Did not work? Read on for debugging suggestions in VBA.

Programming tips and tricks

Start with examples

The VBA community is very large; a search on the Web can almost always yield an example of VBA code that does something similar to what you want to do. If you cannot find a good example, try to break the task down into smaller units and search on each of those, or try to think of a more common, but similar problem. Starting with an example can save you hours of time.

That does not mean that free and well-thought-out code is on the Web waiting for you to come along. In fact, some of the code that you find might have bugs or mistakes. The idea is that the examples you find online or in VBA documentation give you a head start. Remember that learning programming requires time and thought. Before you get in a big rush to use another solution to solve your problem, ask yourself whether VBA is the right choice for this problem.

Make a simpler problem

Programming can get complex quickly. It's critical, especially as a beginner, that you break the problem down to the smallest possible logical units, then write and test each piece in isolation. If you have too much code in front of you and you get confused or muddled, stop and set the problem aside. When you come back to the problem, copy out a small piece of the problem into a new module, solve that piece, get the code working, and test it to ensure that it works. Then move on to the next part.

Bugs and debugging

There are two main types of programming errors: syntax errors, which violate the grammatical rules of the programming language, and run-time errors, which look syntactically correct, but fail when VBA attempts to execute the code.

Although they can be frustrating to fix, syntax errors are easy to catch; the Visual Basic Editor beeps and flashes at you if you type a syntax error in your code.

For example, string values must be surrounded by double quotes in VBA. To find out what happens when you use single quotes instead, return to the Visual Basic Editor and replace the "Wow!" string in the code example with 'Wow!' (that is, the word Wow enclosed in single quotes). If you choose the next line, the Visual Basic Editor reacts. The error "Compile error: Expected: expression" is not that helpful, but the line that generates the error turns red to tell you that you have a syntax error in that line and as a result, this program will not run.

Choose OK and change the text back to"Wow!".

Runtime errors are harder to catch because the programming syntax looks correct, but the code fails when VBA tries to execute it.

For example, open the Visual Basic Editor and change the Value property name to ValueX in your Macro, deliberately introducing a runtime error since the Range object does not have a property called ValueX. Go back to the Excel document, open the Macros dialog box and run Macro1 again. You should see a Visual Basic message box that explains the run-time error with the text, "Object doesn't support this property of method." Although that text is clear, choose Debug to find out more.

When you return to the Visual Basic Editor, it is in a special debug mode that uses a yellow highlight to show you the line of code that failed. As expected, the line that includes the ValueX property is highlighted.

You can make changes to VBA code that is running, so change ValueX back to Value and choose the little green play button underneath the Debug menu. The program should run normally again.

It's a good idea to learn how to use the debugger more deliberately for longer, more complex programs. At a minimum, learn a how to set break-points to stop execution at a point where you want to take a look at the code, how to add watches to see the values of different variables and properties as the code runs, and how to step through the code line by line. These options are all available in the Debug menu and serious debugger users typically memorize the accompanying keyboard shortcuts.

Using reference materials well

To open the Developer Reference that is built into Office Help, open the Help reference from any Office application by choosing the question mark in the ribbon or by pressing F1. Then, to the right of the Search button, choose the dropdown arrow to filter the contents. Choose Developer Reference. If you don't see the table of contents in the left panel, choose the little book icon to open it, and then expand the Object Model Reference from there.
Figure 5. Filtering on developer Help applies to all Office applications
Time spent browsing the Object Model reference pays off. After you understand the basics of VBA syntax and the object model for the Office application that you are working with, you advance from guesswork to methodical programming.

Of course the Microsoft Office Developer Center is an excellent portal for articles, tips, and community information.

Searching forums and groups

All programmers get stuck sometimes, even after reading every reference article they can find and losing sleep at night thinking about different ways to solve a problem. Fortunately, the Internet has fostered a community of developers who help each other solve programming problems.

Any search on the Web for "office developer forum" reveals several discussion groups. You can search on "office development" or a description of your problem to discover forums, blog posts, and articles as well.

If you have done everything that you can to solve a problem, don't be afraid to post your question to a developers forum. These forums welcome posts from newer programmers and many of the experienced developers are glad to help.

The following are a few points of etiquette to follow when you post to a developer forum:

Before you post, look on the site for an FAQ or for guidelines that members want you to follow. Ensure that you post content that is consistent with those guidelines and in the correct section of the forum.

Include a clear and complete code sample, and consider editing your code to clarify it for others if it is part of a longer section of code.

Describe your problem clearly and concisely, and summarize any steps that you have taken to solve the problem. Take the time to write your post as well as you can, especially if you are flustered or in a hurry. Present the situation in a way that will make sense to readers the first time that they read the problem statement.

Be polite and express your appreciation.

Going further with programming

Although this article is short and only scratches the surface of VBA and programming, it is hopefully enough to get you started.

This section briefly discusses a few more key topics.

Variables

In the simple examples in this article you manipulated objects that the application had already created. You might want to create your own objects to store values or references to other objects for temporary use in your application. These are called variables.

To use a variable in VBA, must tell VBA which type of object the variable represents by using the Dim statement. You then set its value and use it to set other variables or properties.

 Dim MyStringVariable As String MyStringVariable = "Wow!" Worksheets(1).Range("A1").Value = MyStringVariable

Branching and looping

The simple programs in this article execute one line at a time, from the top down. The real power in programming comes from the options that you have to determine which lines of code to execute, based on one or more conditions that you specify. You can extend those capabilities even further when you can repeat an operation many times. For example, the following code extends Macro1.

Sub Macro1() If Worksheets(1).Range("A1").Value = "Yes!" Then Dim i As Integer For i = 2 To 10 Worksheets(1).Range("A" & i).Value = "OK! " & i Next i Else MsgBox "Put Yes! in cell A1" End If End Sub

Type or paste the code into the Visual Basic Editor and then run it. Follow the directions in the message box that appears and change the text in cell A1 from Wow! to Yes! and run it again to see the power of looping. This code snippet demonstrates variables, branching and looping. Read it carefully after you see it in action and try to determine what happens as each line executes.

All of my Office applications: example code

Here are a few scripts to try; each solves a real-world Office problem.

Create an email in Outlook

Sub MakeMessage() Dim OutlookMessage As Outlook.MailItem Set OutlookMessage = Application.CreateItem(olMailItem) OutlookMessage.Subject = "Hello World!" OutlookMessage.Display Set OutlookMessage = Nothing End Sub

Be aware that there are situations in which you might want to automate email in Outlook; you can use templates as well.

Delete empty rows in an Excel worksheet

Sub DeleteEmptyRows() SelectedRange = Selection.Rows.Count ActiveCell.Offset(0, 0).Select For i = 1 To SelectedRange If ActiveCell.Value = "" Then Selection.EntireRow.Delete Else ActiveCell.Offset(1, 0).Select End If Next i End Sub

Be aware that you can select a column of cells and run this macro to delete all rows in the selected column that have a blank cell.

Delete empty text boxes in PowerPoint

Sub RemoveEmptyTextBoxes() Dim SlideObj As Slide Dim ShapeObj As Shape Dim ShapeIndex As Integer For Each SlideObj In ActivePresentation.Slides For ShapeIndex = SlideObj.Shapes.Count To 1 Step -1 Set ShapeObj = SlideObj.Shapes(ShapeIndex) If ShapeObj.Type = msoTextBox Then If Trim(ShapeObj.TextFrame.TextRange.Text) = "" Then ShapeObj.Delete End If End If Next ShapeIndex Next SlideObj End Sub

Be aware that this code loops through all of the slides and deletes all text boxes that don't have any text. The count variable decrements instead of increments because each time the code deletes an object, it removes that object from the collection, which reduces the count.

Sub CopyCurrentContact() Dim OutlookObj As Object Dim InspectorObj As Object Dim ItemObj As Object Set OutlookObj = CreateObject("Outlook.Application") Set InspectorObj = OutlookObj.ActiveInspector Set ItemObj = InspectorObj.CurrentItem Application.ActiveDocument.Range.InsertAfter (ItemObj.FullName & " from " & ItemObj.CompanyName) End Sub

Be aware that this code copies the currently open contact in Outlook into the open Word document. This code only works if there is a contact currently open for inspection in Outlook.

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

# data[].attributes.headline
How to Create a Timeline in Excel: 4 Step-by-Step Methods

# data[].attributes.subject
How to Create a Timeline in Excel: 4 Step-by-Step Methods

# data[].attributes.attachment_link.title
How to Create a Timeline in Excel: 4 Step-by-Step Methods

# data[].attributes.attachment_link.description
A quick tutorial for making project timelines in Excel There are several easy ways to create a timeline in Excel. You can use the built-in SmartArt feature to create a timeline graphic in a variety of styles. Or, select a premade…

# data[].attributes.headline
Creating Macros (including Personal Macro Workbook)

# data[].attributes.subject
Creating Macros (including Personal Macro Workbook)

# data[].attributes.attachment_link.readable_text
4. Select S tore macro in: This Workbook then click the OK button.

Excel is now recording your actions to create a macro.

5. Select Columns A & B and then right-click on the highlighted Column Bar to Hide them.

7. Right-click on the highlighted Column Bar then click on Hide.

Your worksheet should now look like this:

To end the macro recording:

Good job! 👏

You have created your first macro in Excel!

But wait, where is the recorded macro?

To view all of the available Excel macros :

1. Select View Macros.

2. This opens the Macro window. Saved macros will be listed here and you can Run whichever one you need.

You can also click on Edit to view the VBA code window.

3. The VBA code editor opens.

Notice the Hide_Columns Sub procedure. You don't have to write or edit VBA code for the macro.

Excel automatically generated each code line based on the recorded keystrokes and mouse clicks.

The Record Macro feature is powerful enough for general spreadsheet automation needs.

But if you want to customize your own VBA macro, you can learn more about Visual Basic for Applications (VBA) here.

Using the Developer tab

Let's record another macro to Unhide the hidden columns.

This time, you can record the macro from the Developer tab.

The Developer tab gives you access to a lot of useful Microsoft Excel features such as the Visual Basic Editor. It also allows you to quickly insert form controls such as buttons and checkboxes.

However, the Developer tab is not visible in the Excel ribbon by default.

To add it:

1. Right-click on the Excel ribbon.

Select Customize the Ribbon.

2. This opens the Customize Ribbon window.

On the right side, check the Developer tab checkbox.

3. You should now see the Developer tab.

To start recording the Unhide macro:

1. Click on the Record Macro button in the Developer tab.

2. Name this macro Unhide_Columns.

3. Click OK.

The recording has started.

4. Press Ctrl + A twice to select all cells.

5. Right-click anywhere on the Column Bar then click Unhide.

6. Click on the Stop Recording macro button to finish up.

Great work! 👌

Now you have two recorded macros that can be executed.

# data[].attributes.attachment_link.title
How to Create Macros in Excel: Step-by-Step Tutorial (2024)

# data[].attributes.attachment_link.description
How to Create Macros in Excel: Step-by-Step Tutorial (2024) Get ready to have your mind blown! 🤯 Because in this tutorial, you learn how to create your own macros in Excel! That's right! And you don't need to know VBA (Visual Basic for Applications)!

# data[].attributes.headline
How to Use Excel Macros to Save Time and Automate Boring Tasks

# data[].attributes.subject
How to Use Excel Macros to Save Time and Automate Boring Tasks

# data[].attributes.headline
How to Remove #DIV/0! Error in Excel (5 Methods)

# data[].attributes.subject
How to Remove #DIV/0! Error in Excel (5 Methods)

# data[].attributes.attachment_link.readable_text
Get FREE Advanced Excel Exercises with Solutions!
Excel shows the Divided by Zero(#DIV/0!) error when the denominator of any formula is zero or empty. This article shows how to remove the div by 0 or #DIV/0! error in Excel in 5 different ways. The following picture highlights the ultimate results obtained from applying those methods.

I am going to show you the 5 methods on how to remove #DIV/0! error in Excel. We will use the following dataset to illustrate those methods. So, let's begin!

1. Remove #DIV/0! Error With IF Function

In the dataset, the formula in cell D5 gives the quotient of cells B5 & C5 as shown in the following picture. When the formula is copied to the later cells, some cells give the #DIV/0! error. We can remove the #DIV/0! error using the IF function. To do that, please follow the steps below.

1. First of all, replace the formula in cell D5 by entering the following formula in that cell.
2. Then, copy the formula to the other cells using the fill handle tool.
3. Now, we can see that the #DIV/0! error is replaced by the text used in the formula.

2. Fix #DIV/0! Error Using IFERROR Function

An alternative way to remove the #DIV/0! Error is by using the IFERROR function. Please follow the steps below to apply this method.

1. At first, enter the following formula in cell D5:

2. After that, copy the formula to the cells below.

3. Now, we see the #DIV/0 errors are removed as in the earlier method.

3. Hide #DIV/0! Error With ISERROR Function

Another way to remove the #DIV/0! Error is to use the ISERROR function. The steps are as follows.

1. First, apply the following formula in cell D5:

2. This returns FALSE as the result.

3. Then, copy the formula to the cells below.

4. Now, we see the errors are removed. But other quotient values are also gone.

5. To fix this problem, replace the earlier formula in cell D5 with the following formula.

6. After that, apply the formula to the other cells below them.

7. Finally, the #DIV/0! errors are no longer there.

4. Avoid #DIV/0! Error by Changing Cell Value

The #DIV/0! error can be removed by changing the cell values that causing the errors. We can follow the steps below with our dataset to verify this method.

1. One way to avoid the #DIV/0! error is to change the 0 or empty values for the divisors to non-zero values as sown below.

2. Another way to avoid the #DIV/0! error is by replacing the 0 or empty values of divisors with #N/A. This replaces the #DIV/0! error with #N/A as shown in the following picture.

Read More: How to Remove Number Error in Excel

Excel has options to remove the #DIV/0! errors in PivotTable. I have created a Pivot Table to show the sum of quotients from our dataset. But, it shows the #DIV/0! error. Let's fix this by following the steps below.

1. Firstly, click anywhere in the Pivot Table.

2. Then, from the PivotTable Analyze tab, go to PivotTable and then Options.

3. This will open a new dialog box.

4. After that, from the Layout & Format tab in the dialog box, check the checkbox for " For error values show: ".

5. Next, enter the text you want to show for errors in the text box. I have typed 'Unavailable' in this case.

6. Now, hit the OK button.

7. Finally, we see the #DIV/0! error is replaced by the text.

Always make sure that the values for the denominator in your formula are not zero or empty to avoid the #DIV/0! error in Excel.

PivotTable doesn't show the desired text in place of the #DIV/0! error if it is considered as the name of a row or a column.

Download Practice Workbook

You can download the practice workbook from the download button below.

Now, you know 5 different ways to remove the #DIV/0! error in Excel. You can use the comment section below for further queries. If you have other suggestions or shortcuts to remove the #DIV/0! error in Excel, please share them with us.

# data[].attributes.attachment_link.title
How to Remove #DIV/0! Error in Excel (5 Methods) - ExcelDemy

# data[].attributes.attachment_link.description
This article shows 5 different ways to remove div / 0 errors in excel. The methods involve the use of the IF, IFERROR, ISERROR functions.

# data[].attributes.headline
How to sort in Excel by row, column names, horizontally, and in custom order

# data[].attributes.subject
How to sort in Excel by row, column names, horizontally, and in custom order

# data[].attributes.attachment_link.title
Excel: sort by row, by column names and in custom order

# data[].attributes.attachment_link.description
Learn how to sort Excel data by multiple columns, by column names and by values in any row. How to sort data in custom order when sorting alphabetically does not work. The solutions work for Excel 365, 2021, 2019, 2013 and older versions.

# data[].attributes.attachment_link.readable_text
Home > Formulas > Math Trig > Rounding

How to choose functions in Excel that round numbers, up or down. Or format a number to look rounded, but keep the original value in the cell. Fix small differences in column sums

Rounding in Excel

To see a quick overview of 7 ways to round in Excel, you can watch this short slide show. There are details and step by step instructions, below the slide show.

Also, get the 7 Ways to Round sample workbook, so you can follow along with the slide show. The zipped file is in xlsx format, and does not contain any macros.

Round With Formatting

With formatting, a number can appear rounded on the worksheet, but the cell value is not affected. In the screen shot below, the total appears to be incorrect, because 2+3 does not equal 4.

However, if you click on any of the cells, you can see its actual value in the formula bar. For example, the coffee price, in cell B3, is $1.54.

The numbers in cells B3:B5 were formatted as currency, using the Number format drop down on the Ribbon's Home tab.

Then, the Decrease Decimal button was clicked twice, to set the number of decimals to zero.

If the Currency number format is reapplied, it shows the default formatting of 2 decimals, so the total amount looks correct -- 1.54 + 2.54 =4.08

When the numbers were rounded with formatting, each amount displayed as the closest dollar amount:

the coffee price rounded up to $2

the sandwich rounded up to $3

the total rounded down to $4.

Automatic Rounding of Long Numbers

If you leave the default number format -- General -- and enter long numbers on a worksheet, Excel might round some of those numbers automatically.

To see how Excel can automatically round numbers with the General format, watch this short video, and the details are below the video

Maximum 11 Characters

In my tests, a maximum of 11 characters (including a decimal place) are displayed in a cell, even if the column is wide enough to show more numbers.

If a number is longer than 11 characters, the decimals are rounded, to show a maximum of 11 characters.

If a number is longer than 11 characters, and there are no decimals, the number is changed to Scientific format.

To prevent this automatic rounding/ormatting, apply one of the other number formats, such as Number, and set the number of decimals that you want to display.

Column Too Narrow

If you reduce the column width, with General format applied, the numbers will be automatically rounded, in an attempt to fit.

Decimals are rounded, to show only the number that will fit the column

The number will be rounded to an integer, if there is no room for decimals

The number will change to Scientific format, if the rounded integer will not fit

The cell will display number signs, if it can't display in Scientific format

Round With Custom Number Format

In a summary report, with very large numbers, precision to the decimal places is probably not required. It's difficult to get a quick overvier, if the numbers are too detailed, as shown below.

To round large numbers, and make them easier to read, you can use Custom Number Formats, to round to the left of the decimal point.

For example, show 123456.78 in one of the following formats.

Apply a Custom Format

To apply a custom format to cells:

On the Ribbon's Home tab, click the dialog launcher, at the bottom right of the Number group.

On the Number tab, in the list of Categories, click Custom.

Then, select one of the existing custom formats, or clear the box, to create your own.

For example, enter the custom format -- #, -- to show only the rounded thousands in a large number.

Then, click the OK button, to close the dialog box, and apply the format.

Here are a few more custom number formats that you can use:

You can also use a custom number format to line up numbers with varying decimal places. The question mark (?) is a placeholder that adds extra spacing, so the numbers line up at the decimal point.

There are more examples of Custom formats on the Microsoft website: Create a Custom Number Format

ROUND Function

The Excel ROUND function rounds a number to a specified number of digits from the decimal point. The rounding can be to the right of the decimal point, or to the left of the decimal point.

For example:

1. Round a sales amount to the nearest cent

2. Round an age to the nearest decade

3. Total amounts with small differences

ROUND Function Syntax

The ROUND function syntax has the following two arguments in its syntax:
ROUND(number, num_digits)

number: The number that you want to round

num_digits: The number of digits that you want to round to

If greater than zero, number is rounded to the right of the decimal point

If zero, number is rounded to the nearest integer

If less than zero, number is rounded to the left of the decimal point

Sales Tax Example

In the ROUND formula shown below, the sales tax (HST) is rounded to the nearest cent, by using 2 as the number of decimal places to round.
=ROUND(B5*D6,2)
Age by Decade Example

In the screen shot below, the age is rounded to the nearest decade, by using -1 as the number of decimal places to round. Because the number is negative, it rounds to the left of the decimal place.
=ROUND(B4,-1)
Total Amounts with Small Difference

In the worksheet shown below, there is a small difference between the sum of the Total column and the Received column.

In column C, tax is calculated by multiplying sales price times TaxRate (cell F1)

When payment is received, that amount is manually entered in column E
Amount entered always checked, to ensure it matches the Total due.

Why are the totals different, when the amounts are carefully entered, and checked for accuracy?

In column F, a formula finds the difference between the Total and Received amounts.

Even though they look the same on the worksheet, there is a small difference in most rows.

This is caused by hidden decimal places in the Tax column.

Fix SUM Differences With ROUND Function

To fix the problem, use the ROUND function in the Tax calculation column.

With the tax rounded to 2 decimal places, it is an exact match for the amount that's typed in the Received column.

In each row, the Difference calculation shows a result of zero

In the Total row, the sums in the two columns are exactly the same.

ROUNDDOWN Function

The Excel ROUNDDOWN rounds a number down, to a specific number of places from the decimal point. For example:

Round a year down to the nearest decade

In the screen shot below, the publication year is rounded down to the nearest decade, by using -1 as the number of digits in the ROUNDDOWN function. Because the number is negative, it rounds to the left of the decimal place.
=ROUNDDOWN(B4,-1)
ROUNDUP Function

The Excel ROUNDUP function rounds a number up, to a specific number of places from the decimal point. For example:

Round a price up to the nearest dime

In the screen shot below, the price is rounded up to the nearest dime, by using 1 as the number of digits in the ROUNDUP function.
=ROUNDUP(B4,1)
MROUND Function

The MROUND function rounds a number to the closest specified multiple. For example:

Round a sales amount to the nearest nickel

In the screen shot below, the total sales amount is rounded to the nearest nickel, by using 5 as the multiple in the MROUND function. Instead of hard coding the multiple into the formula, it is entered in cell B9, where it can be changed easily.
=MROUND($B$7,$B$9)
FLOOR Function

The Excel FLOOR function rounds numbers down, toward zero, based on the multiple of significance that you specify.
FLOOR(number,significance)
For example:

Round a year down to the nearest decade

In the screen shot below, the year is rounded down to the nearest decade, by using 10 as the significance in the FLOOR function.
=FLOOR(B4,10)
NOTE: In the FLOOR function, if the number is positive, and the significance is negative, the result is the #NUM! error. The FLOOR function works well in the music example above, because the song's year is always a positive number.

FLOOR.PRECISE or FLOOR.MATH Function

For Excel 2010, Microsoft recommends that you use the FLOOR.PRECISE function, instead of FLOOR.

For Excel 2013, use FLOOR.MATH, instead of FLOOR.

Both FLOOR.PRECISE and FLOOR.MATH return a number that is rounded down to the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded down.
FLOOR.PRECISE(number,significance) or FLOOR.MATH(number,significance)
The three functions are compared in the following table. The results from FLOOR.PRECISE and FLOOR.MATH are the same, but FLOOR is different -- it rounds toward zero, and returns an error if the number is positive and the significance is negative.

CEILING Function

The Excel CEILING function rounds numbers up, toward zero, based on the multiple of significance that you specify. For example:

Round a price up to the nearest quarter

In the screen shot below, the price is rounded up to the nearest quarter, by using 0.25 as the multiple in the CEILING function.
=CEILING(B4,0.25)
Sum Rounded Numbers

If a column has decimal numbers, you can use the ROUND function, to round each number individually, BEFORE calculating the total. This might give a different result than rounding the numbers AFTER summing them. .

In this example, there are decimal numbers in cells A1:A5

In cell A6, the formula rounds the total to 0 decimal places, AFTER summing them. It returns a result of 21.0
=ROUND(SUM(A1:A5), 0)
The formula in cell A7 rounds each number BEFORE summing them. It returns a result of 22.0
=SUMPRODUCT(ROUND(A1:A5,0))
Get the Sample Files

1. Rounding Examples: To follow this tutorial, you can get the rounding functions sample file. The zipped file is in xlsx format, and does not contain macros. Some functions will only work in newer versions of Excel.

2. 7 Ways to Round: Get the 7 Ways to Round sample workbook, to follow along with the 7 Ways to Round slide show. The zipped file is in xlsx format, and does not contain any macros.

3. Column Sum Differences: Get this tax calculation column sum difference sample workbook. The zipped file is in xlsx format, and does not contain any macros.

Excel Functions Tutorials
30 Functions in 30 Days Functions List Subtotal Feature

# data[].attributes.attachment_link.description
How to use Excel Rounding functions to round a number up or down. Troubleshoot Excel rounding problems

# data[].attributes.headline
Excel - Basic Reference Guide (CustomGuide)

# data[].attributes.subject
Excel - Basic Reference Guide (CustomGuide)

# data[].attributes.attachment_link.readable_text
Lookup functions in Microsoft Excel are ideal for finding what you need when you have a large amount of data. There are three common ways to do this; INDEX and MATCH, VLOOKUP, and XLOOKUP. But what's the difference?

INDEX and MATCH, VLOOKUP, and XLOOKUP each serve the purpose of looking up data and returning a result. They each work a bit differently and require a specific syntax for the formula. When should you use which? Which is better? Let's take a look so you know the best option for you.

Using INDEX and MATCH

Obviously, the INDEX and MATCH combination is a mixture of the two named functions. You can take a look at our how-tos for the INDEX function and MATCH function for specific details on using them individually.

To use this duo, the syntax for each is INDEX(array, row_number, column_number) and MATCH(value, array, match_type).

When you combine the two, you'll have a syntax like this: INDEX(return_array, MATCH(lookup_value, lookup_array)) in its most basic form. It's easiest to look at some examples.

To find a value in cell G2 in the range A2 through A8 and provide the matching result in the range B2 through B8, you would use this formula:

=INDEX(B2:B8,MATCH(G2,A2:A8))

If you prefer to insert the value you want to find instead of using the cell reference, the formula looks like this where 2B is the lookup value:

=INDEX(B2:B8,MATCH("2B",A2:A8))

Our result is Houston for both formulas.

We also have a tutorial that goes into detail on using INDEX and MATCH should that be your choice.

RELATED: How to Use INDEX and MATCH in Microsoft Excel

Using VLOOKUP

VLOOKUP has been a popular reference function in Excel for some time. The V stands for Vertical, so with VLOOKUP, you're doing a vertical lookup and it's from left to right.

The syntax is VLOOKUP(lookup_value, lookup_array, column_number, range_lookup) with the last argument optional as True (approximate match) or False (exact match).

Using the same data as that for INDEX and MATCH, we'll look up the value in cell G2 in the range A2 through D8 and return the value in the second column that matches. You'd use this formula:

=VLOOKUP(G2,A2:D8,2)

As you can see, the result using VLOOKUP is the same as using INDEX and MATCH, Houston. The difference is that VLOOKUP uses a much simpler formula. For more details on VLOOKUP, check out our how-to.

So why would anyone use INDEX and MATCH instead of VLOOKUP? The answer is because VLOOKUP only works when your lookup value is to the left of the return value you want.

If we did the reverse and wanted to look up a value in the fourth column and return the matching value in the second column, we would not receive the result we want and may even receive an error. As Microsoft writes:

Remember that the lookup value should always be in the first column in the range for VLOOKUP to work correctly. For example, if your lookup value is in cell C2 then your range should start with C.

INDEX and MATCH covers the entire cell range or array making it a more robust lookup option even if the formula is a bit more complicated.

Using XLOOKUP

XLOOKUP is a reference function that arrived in Excel after VLOOKUP and the counterpart HLOOKUP (horizontal lookup). The difference between XLOOKUP and VLOOKUP is that XLOOKUP works no matter where the lookup and return values reside in your cell range or array.

The syntax is XLOOKUP(lookup_value, lookup_array, return_array, not_found, match_mode, search_mode). The first three arguments are required and are similar to that in the VLOOKUP function. XLOOKUP offers three optional arguments at the end for giving a text result if the value isn't found, a mode for the type of match, and a mode for how to perform the search.

For the purpose of this article, we'll concentrate on the first three required arguments.

Back to our cell range from earlier, we'll look up the value in G2 in the range A2 through A8 and return the matching value from the range B2 through B8 with this formula:

=XLOOKUP(G2,A2:A8,B2:B8)

And like with INDEX and MATCH as well as VLOOKUP, our formula returned Houston.

We can also use a value in the fourth column as the lookup value and receive the correct result in the second column:

=XLOOKUP(20745,D2:D8,B2:B8)

With this in mind, you can see that XLOOKUP is a better option than VLOOKUP simply because you can arrange your data any way you like and still receive your desired result. For a full tutorial on XLOOKUP, head over to our how-to.

RELATED: How to Use the XLOOKUP Function in Microsoft Excel

So now you're wondering, should I use XLOOKUP or INDEX and MATCH, right? Here are some things to consider.

Which Is Better?

If you already use the INDEX and MATCH functions separately and have used them together to lookup values, then you may be more familiar with how they work. By all means, if it's not broken, don't fix it, and keep using what makes you comfortable.

And of course, if your data is structured to work with VLOOKUP and you've used that function for years, you can continue using it or make the easy transition to XLOOKUP leaving INDEX and MATCH in the dust.

If you want to a simple, easy-to-build formula in any direction, XLOOKUP is the way to go and can replace INDEX and MATCH. You don't have to worry about combining arguments from two functions into one or rearranging your data.

One last consideration, XLOOKUP does offer those three optional arguments which may come in handy for your needs.

Over to you! Which lookup option will you use in Microsoft Excel? Or maybe, you'll use all three depending on your needs? No matter what, it's nice to have options!

With her B.S. in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She learned how technology can enrich both professional and personal lives by using the right tools. And, she has shared those suggestions and how-tos on many websites over time. With thousands of articles under her belt, Sandy strives to help others use technology to their advantage. Sandy Writtenhouse
Read Full Bio "

# data[].attributes.attachment_link.title
INDEX and MATCH vs. VLOOKUP vs. XLOOKUP in Microsoft Excel

# data[].attributes.attachment_link.description
Lookup functions in Microsoft Excel are ideal for finding what you need when you have a large amount of data. There are three common ways to do this; INDEX and MATCH, VLOOKUP, and XLOOKUP. But what's the difference? INDEX and MATCH, VLOOKUP, and XLOOKUP each serve the purpose of looking up data and returning a result.

# data[].attributes.attachment_link.readable_text
An Excel macro is an abbreviation for macroinstruction-a sequence of commands (or directives) that tell a computer program or a programming language (such as Visual Basic, C, Assembler, PHP, etc.) to perform a series of tasks; generally repetitive tasks, which are prone to errors if repeatedly keyed by hand. Application macros are often created using a macro recorder, which records the user's keystrokes while performing the repetitive task, then replays those keystrokes every time the macro name or shortcut key is executed.

What macros do

Programmers and end-users create and use macros for similar reasons: to simplify a complex procedure, to reduce a sequence of instructions down to a single program statement, or to automate repetitive tasks. For example, instead of manually separating city, state, and ZIP code from one column into three, create a macro; and instead of retyping the same header data on every spreadsheet you create, use a macro.

Macro tips, tricks, and essentials

The three most important macro fundamentals are:

Always begin at the Home position; that is, Ctrl+ Home.

Always use the directional keys to navigate: Up, Down, End, Right, Left.

Always use relative cell addresses, which means the macro's recorded keystrokes are relative to the starting cell's location-A1, the Home position.

Based on these fundamentals, here are three further tips:

Using Absolute cell addresses means the exact cell locations are recorded into the macro. This limits the macro's ability to function if anything changes, new data is added or removed, or the list gets longer.

Keep your macros small and specific. For example, create one macro to sort the data, another macro to extract specific information, and a third macro to print the extracted information. If you combine all these tasks into one long macro, it runs slower (especially if it's required to perform many functions or calculate a lot of formulas on a large spreadsheet). And if it fails, it takes forever to locate the point of failure-you have to step through the entire macro one keystroke at a time to find the errors. If you create small macros and run them separately, you can quickly review the results and verify the accuracy.

Enter fixed values and fixed data in advance. This means any column that has fixed information that rarely or never changes, like product ID numbers, product prices, and/or product descriptions. Macros are for performing repetitive tasks such as entering formulas, sorting, formatting; or the complex tasks such as using a Pivot table's filters to extract specific data, creating detailed Charts, or building complicated Tables.

Macro keyboard essentials

Take advantage of your keyboard to help you make precise macros. The End key (in conjunction with the arrow keys) are the most important keys-they're the only way to navigate through a spreadsheet without hard-coding the cells. For example, to highlight column A, (from the Home position), you press the End key, then Shift+ Down arrow (simultaneously), and the column is highlighted. You can't code or mouse to highlight A1 through A12 (hardcoded cell addresses) in a macro unless you know for certain the selection will never change.

When your cursor is positioned anywhere within your data table, press Ctrl+ A to highlight the "used" portion of your spreadsheet; that is, all the cells that contain data. Press Ctrl+ A twice to highlight the entire spreadsheet.

The macro WILL FAIL if you do not enter the keystrokes EXACTLY as Excel designed them to be used. For example, Ctrl+ A is a combination, simultaneous keystroke, which means you press and hold the Ctrl key, then press the letter A, then release both keys simultaneously (always signified by the plus + sign). End-Down is a combination, consecutive keystroke, which means you press the End key, release; then press the Down-arrow key and release-sequentially, not simultaneously (always signified by the hyphen/dash - symbol).

Before Microsoft cursed us with the Ribbon menu, the shortcut keys were displayed on the dropdown menus beside the commands they represented. The new Ribbon shortcut keys are displayed on the Ribbon Menu as white letters inside black boxes and are accessed through the Alt key. Press the Alt key once to activate Excel's new shortcut keys; then again to turn them off.

How to create, name, and use macros

Macro Name: the first character must be a letter, followed by your choice of letters, numbers, or an underscore. No other characters are accepted.

Shortcut Key: CTRL+ J and CTRL+ M are available. If you choose any other character, your macro will overwrite that key's original function; such as Ctrl+C for Copy and Ctrl+P for print. If you choose to use an already dedicated key, be sure to choose one that you seldom use, like Shift+F2 (add or edit a cell comment).

Save location: Macros saved in "This Workbook" or "New Workbook" function only in those workbooks. To use in all spreadsheets, save macros to the Personal Macro Workbook (PMW).

Description: Describe what the macro does.

If you use a specific macro a lot, consider adding a macro button to the Quick Access Toolbar or to your own personal, customized group.

1. For the Quick Access Toolbar:

b. When the Customize Quick Access Toolbar screen appears, select Macros from the Choose Commands From field box.

c. Select the macro (from the list) that you want displayed on the Quick Access Toolbar.

d. Click the Add button to move the selected macro to the other (right) panel.

f. Notice the new macro button on your Quick Access Toolbar.

2. How to add a macro button to your personal, customized group on the Ribbon menu:

b. When the Customize Ribbon screen appears, check the "Developer" box inside the right panel, then click the plus + sign to open and close the groups under that tab.

c. Click the New Group button (bottom center of right panel), and Excel adds New Group (custom) at the bottom of the list.

e. Next, click Macros in the left panel, and all the macros you have available on your system appear.

f. Selec the macro you want added to the Ribbon menu, then click the Add button. Notice the macro is added to your new custom group.

g. When finished, click OK.

h. Click the Developer tab, and the new Macros group appears at the far right end of this menu with your custom macro inside. Click this macro button to execute your macro.

Let's record an actual macro

Let's walk through recording a simple macro to show how it's done. Imagine that you receive a spreadsheet with a long list of phone numbers that use different formats; for example, some use dashes, some use periods, some use parentheses and dashes, and some use plus signs and letter. We'll record a macro that finds all the periods and parentheses and replaces them with dashes, and format the cells for centered text (alignment left/right and top/bottom), except do not center the names in column A.

1. Select the Developer tab, then click the Record Macro button in the Code group.

3. You are now in Record mode. Everything you do is recorded, so don't stray from the objective.

NOTE: The following macro uses the old Microsoft formatting commands (for example, all caps for commands and buttons) to prevent confusion between the commands/keys/buttons (such as tab and replace) and the dialog/menu tabs, fields, and instructions.

4. Enter the following keystrokes exactly as listed below:

CTRL+ HOME

CTRL+ A. (This highlights the entire database/matrix.)

Alt H F A (displays the Alignment tab of the Format Cells dialog box)

Center Center (in the Horizontal and Vertical field boxes)

OK (button)

CTRL+ HOME

DOWN

SHIFT+ END- DOWN

Alt H F A (displays the Alignment tab of the Format Cells dialog box)

Left Center (in the Horizontal and Vertical field boxes)

OK (button)

CTRL+ HOME

CTRL+ H (opens the Find and Replace dialog window)

Replace (click the Replace tab)

TAB (press the TAB key)

. TAB - REPLACE ALL (button) OK (button) (type a period in the Find field box, press the Tab key, then type a dash in the Replace field box, then press the REPLACE ALL button, then click OK button)

Replace (click the Replace tab to repeat this function)

TAB (press the TAB key to move to the Find What field box)

( (enter a left parenthesis to find that character)

TAB (press the Tab key to move to the Replace With field box)

BACKSPACE (press the BACKSPACE key to erase the previous transaction {the dash}, and to tell Excel to replace the left parentheses with nothing)

REPLACE ALL (button) OK (button) (press the REPLACE All button, then click OK button)

Replace (click the Replace tab to repeat this function)

TAB (press the TAB key to move to the Find What field box)

) _ (enter a right parenthesis and a space {press the space bar once} to find those two characters)

TAB (press the TAB key to move to the Replace With field box)

- (enter a dash in the Replace field box)

REPLACE ALL (button) OK (button) (press the REPLACE All button, then click OK button)

CLOSE (button)

Developer > Macro Sto p (select the Developer tab, then click the Macro Stop icon)

6. Now, undo all the changes you just made again.

7. Then, go back to section 5-How to: create, name, and use macros, number 2-How to add a macro button to your personal, customized group on the Ribbon menu, and add your new FormatPN macro to the Ribbon menu.

8. Select the Developer tab, click the new macro icon, and watch your macro in action.

# data[].attributes.attachment_link.title
Excel macros: Essential rules, tips and tricks

# data[].attributes.attachment_link.description
An Excel macro is an abbreviation for macroinstruction-a sequence of commands (or directives) that tell a computer program or a programming language (such as Visual Basic, C, Assembler, PHP, etc.) to perform a series of tasks; generally repetitive tasks, which are prone to errors if repeatedly keyed by hand.

# data[].attributes.headline
How to move duplicate rows to another sheet in Excel?

# data[].attributes.subject
How to move duplicate rows to another sheet in Excel?

# data[].attributes.attachment_link.readable_text
How to move duplicate rows to another sheet in Excel?

If you have a list of data range which contains some duplicate values in a specific column, now, you want to move the entire rows to another sheet based on the duplicate cells. How could you deal with this task in Excel?
Move entire rows to another sheet based on duplicate cells in a column Move entire rows to another sheet based on duplicate rows
Move entire rows to another sheet based on duplicate cells in a column

If there are duplicate values in a column, then move the entire rows to another sheet, the following VBA code can do you a favor:

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module window.

VBA code: Move entire rows to another sheet based on duplicate cells in a column:

Sub CutDuplicates() 'Updateby Extendoffice Dim xRgS As Range Dim xRgD As Range Dim I As Long, J As Long On Error Resume Next Set xRgS = Application.InputBox("Please select the column:", "KuTools For Excel", Selection.Address, , , , , 8) If xRgS Is Nothing Then Exit Sub Set xRgD = Application.InputBox("Please select a desitination cell:", "KuTools For Excel", , , , , , 8) If xRgD Is Nothing Then Exit Sub xRows = xRgS.Rows.Count J = 0 For I = xRows To 1 Step -1 If Application.WorksheetFunction.CountIf(xRgS, xRgS(I)) > 1 Then xRgS(I).EntireRow.Copy xRgD.Offset(J, 0) xRgS(I).EntireRow.Delete J = J + 1 End If Next End Sub

3. Then press F5 key to run this code, and in the popped out dialog box, select the column which contains the duplicate cells you want to move based on, see screenshot:

4. Then click OK, in another prompt box, please select a cell in another sheet where you want to put the moved rows, see screenshot:

5. And then click OK, the rows which have duplicate values in column A have been moved into a new sheet, see screenshot:

Move entire rows to another sheet based on duplicate rows

If you want to move the duplicate rows from a range of cells, the following VBA code can help you:

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module window.

VBA code: Move entire rows to another sheet based on duplicate rows:

Sub CutDuplicates() 'Updateby Extendoffice Dim xRgD As Range, xRgS As Range Dim I As Long, J As Long, K As Long, KK As Long On Error Resume Next Set xRgS = Application.InputBox("Please select the data range:", "KuTools For Excel", Selection.Address, , , , , 8) If xRgS Is Nothing Then Exit Sub Set xRgD = Application.InputBox("Please select a desitination cell:", "KuTools For Excel", , , , , , 8) If xRgD Is Nothing Then Exit Sub KK = 0 For I = xRgS.Rows.Count To 1 Step -1 For J = 1 To I - 1 For K = 1 To xRgS.Columns.Count Debug.Print xRgS.Rows(I).Cells(, K).Value Debug.Print xRgS.Rows(J).Cells(, K).Value If xRgS.Rows(I).Cells(, K).Value <> xRgS.Rows(J).Cells(, K).Value Then Exit For Next If K = xRgS.Columns.Count + 1 Then xRgS.Rows(I).EntireRow.Copy xRgD.Offset(KK, 0).EntireRow xRgS.Rows(I).EntireRow.Delete KK = KK + 1 End If Next Next End Sub

3. Then press F5 key to run this code, in the popped out dialog box, select the select the data range that you want to move the duplicate rows, see screenshot:

4. Click OK button, and then in another prompt box, select a cell in a new sheet where you want to put the moved supplicate rows, see screenshot:

5. Then click OK button, and now, the duplicate rows have been moved into another sheet you specified at once, see screenshot:

Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...

More than 300 powerful features. Supports Office / Excel 2007-2021 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.

Open and create multiple documents in new tabs of the same window, rather than in new windows.

Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

# data[].attributes.attachment_link.title
How to move duplicate rows to another sheet in Excel?

# data[].attributes.attachment_link.description
If you have a list of data range which contains some duplicate values in a specific column, now, you want to move the entire rows to another sheet based on the duplicate cells. How could you deal with this task in Excel?

# data[].attributes.headline
Following Excel precedents to cells on other sheets

# data[].attributes.subject
Following Excel precedents to cells on other sheets

# data[].attributes.attachment_link.readable_text
Thanks for contributing an answer to Super User!

Please be sure to answer the question. Provide details and share your research!

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

# data[].attributes.attachment_link.title
Following Excel precedents to cells on other sheets

# data[].attributes.attachment_link.description
Thanks for contributing an answer to Super User! Please be sure to answer the question. Provide details and share your research! Asking for help, clarification, or responding to other answers. Making statements based on opinion; back them up with references or personal experience. To learn more, see our tips on writing great answers.

# data[].attributes.headline
Optimizing Charts for Readers with Color Vision Deficiencies

# data[].attributes.subject
Optimizing Charts for Readers with Color Vision Deficiencies

# data[].attributes.headline
Make your Excel documents accessible to people with disabilities

# data[].attributes.subject
Make your Excel documents accessible to people with disabilities

# data[].attributes.attachment_link.readable_text
In this topic

Best practices for making Excel spreadsheets accessible

The following table includes key best practices for creating Excel spreadsheets that are accessible to people with disabilities.

What to fix

How to find it

Why fix it

How to fix it

Avoid common accessibility issues such as missing alternative text (alt text) and low contrast colors.

Use the Accessibility Checker.

Make it easy for everyone to read your spreadsheet.

Check accessibility while you work in Excel

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

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

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.

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

Create accessible tables

Add text to cell A1.

Make sure that you have text in cell A1.

Screen readers start reading any worksheet from cell A1.

Add text to cell A1

Include alt text with all visuals.

To find all instances of missing alt text, use the Accessibility Checker.

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

Add alt text to visuals

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.

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

Give all worksheets unique names, and remove blank worksheets.

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.

Name cells and ranges.

Visually scan your workbook to see which cells and ranges would benefit from having a name.

When you name cells and ranges, screen reader users can quickly identify the purpose of cells and ranges.

Name cells and ranges

Top of Page

Check accessibility while you work in Excel

The Accessibility Checker is a tool that reviews your content and flags accessibility issues it comes across. It explains why each issue might be a potential problem for someone with a disability. The Accessibility Checker also suggests how you can resolve the issues that appear.

In Excel, the Accessibility Checker runs automatically in the background when you're creating a document. If the Accessibility Checker detects accessibility issues, you will get a reminder in the status bar.

To manually launch the Accessibility Checker, select Review > Check Accessibility. The Accessibility pane and the Accessibility ribbon open, and you can now review and fix accessibility issues. The Accessibility ribbon contains all the tools you need to create accessible spreadsheets in one place. For more info, go to Accessibility Ribbon and Improve accessibility with the Accessibility Checker.

Tip: Use the Accessibility Reminder add-in for Office to notify authors and contributors of accessibility issues in their spreadsheets. With the add-in, you can quickly add reminder comments that spread awareness of accessibility issues and encourage the use of the Accessibility Checker. For more info, go to Use the Accessibility Reminder to notify authors of accessibility issues.

Top of Page

Create accessible tables

Tables can help you identify a set of data by name, and you can format the table using styles that make the data stand out. When you carefully name and format your table, you can be sure that everyone can understand your data.

It is also important to specify column header information and use a simple table structure to make sure that screen reader users can navigate the tables easily.

Name a table

By default, Excel names the tables you create as Table1, Table2, Table3, and so on. To make it easier to refer to a table, give each table a descriptive name. A meaningful table name like "EmployeeList" is more helpful than the generic "Table1."

With the descriptive name, you can, for example, quickly jump to the table with the Go To command (Ctrl+G) or the Name Manager dialog box. You can also easily refer to the table in formulas.

Place the cursor anywhere in the table.

Note: Table names must start with a letter, an underscore (_), or a backslash (\) and cannot contain spaces. For more info, see section "Important notes for names" in Rename an Excel table.

Select an accessible table style

Light colored tables with low contrast can be hard to read. To make your table more accessible, select a table style that has colors with a strong contrast. For example, choose a style that alternates between white and a dark color, such as black, dark grey, or dark blue.

Place the cursor anywhere in the table.

Table structures to avoid

Design your tables keeping in mind the following:

Avoid blank cells, columns, and rows. When navigating using the keyboard, a blank cell, column, or row might lead a screen reader user to believe there is nothing more in the table.

If there is no need for a blank cell, column, or row, consider deleting it.

If you cannot avoid a blank cell, column, or row, enter text explaining that it is blank. For example, type N/A or Intentionally Blank.

Avoid splitting or merging cells: 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. Merged or split cells can make navigating Excel tables with assistive technologies very difficult, if not impossible. Always keep your tables straightforward and simple. To ensure that tables don't contain split cells, merged cells, or nested tables, use the Accessibility Checker.

Top of Page

Use an accessible template

Use one of the accessible Excel templates to make sure that your spreadsheet design, colors, contrast, and fonts are accessible for all audiences. The templates are also designed so that screen readers can more easily read the spreadsheet content.

When selecting a template, look for a template that has several features that support accessibility. For example:

Using enough white space makes the spreadsheet easier to read.

Colors with contrast make them easier to tell apart for low vision and colorblind readers.

Larger fonts are easier for low-vision users.

Preset descriptive headings and labels make the spreadsheet easier to understand for users who navigate it with a screen reader.

For step-by-step instructions on how to use accessible templates, go to Video: Start with an accessible Excel template.

Top of Page

Add text to cell A1

A screen reader starts reading any worksheet from cell A1. If you have a table on the worksheet, A1 should preferably be the title of the table.

If the sheet is long or complex, add instructions or an overview of the sheet in cell A1. This will inform people who are blind what's being presented in your worksheet and how to use it. This instructional text can match the background color. This will hide it from people who can see, but allows it to be read by screen readers.

Top of Page

Add alt text to visuals

Alt text helps people who can't see the screen to understand what's important in visual content. Visual content includes pictures, SmartArt graphics, shapes, groups, charts, pivot charts, embedded objects, ink, and videos. In alt text, briefly describe the image and mention its intent. Screen readers read the text to describe the image to users who can't see the image.

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.

Tip: To write a good alt text, make sure to convey the content and the purpose of the image in a concise and unambiguous manner. The alt text shouldn't be longer than a short sentence or two-most of the time a few thoughtfully selected words will do. Do not repeat the surrounding textual content as alt text or use phrases referring to images, such as, "a graphic of" or "an image of." For more info on how to write alt text, go to Everything you need to know to write effective alt text.

For the step-by-step instructions on how to add alt text, go to Add alternative text to a shape, picture, chart, SmartArt graphic, or other object.

To find missing alt text, use the Accessibility Checker.

Note: For audio and video content, in addition to alt text, include closed captioning for people who are deaf or hard of hearing.

Top of Page

Add accessible hyperlink text and ScreenTips

People who use screen readers sometimes scan a list of links. Links should convey clear and accurate information about the destination. For example, avoid using link texts such as "Click here," "See this page," Go here," or "Learn more." Instead include the full title of the destination page. You can also add ScreenTips that appear when your cursor hovers over text or images that include a hyperlink.

Tip: If the title on the hyperlink's destination page gives an accurate summary of what's on the page, use it for the hyperlink text. For example, this hyperlink text matches the title on the destination page: Create more with Microsoft templates.

For the step-by-step instructions on how to create hyperlinks and ScreenTips, go to Work with links in Excel.

Top of Page

Use accessible font format and color

An accessible font doesn't exclude or slow down the reading speed of anyone reading a spreadsheet, including people with low vision or reading disability or people who are blind. The right font improves the legibility and readability of the spreadsheet.

For instructions on how to change the default font, go to Change the font size.

Use accessible font format

Here are some ideas to consider:

To reduce the reading load, select familiar sans serif fonts such as Arial or Calibri. Avoid using all capital letters and excessive italics or underlines.

A person with a vision disability might miss out on the meaning conveyed by particular colors. For example, add an underline to color-coded hyperlink text so that people who are colorblind know that the text is linked even if they can't see the color.

For headings, consider adding bold or using a larger font.

Use accessible font color

The text in your spreadsheet should be readable in a high contrast mode. For example, use bright colors or high-contrast color schemes on opposite ends of the color spectrum. White and black schemes make it easier for people who are colorblind to distinguish text and shapes.

Here are some ideas to consider:

To ensure that text displays well in a high contrast mode, use the Automatic setting for font colors. For instructions on how to change the font color, go to Format text in cells.

Use the Accessibility Checker to analyze the spreadsheet and find insufficient color contrast. The tool now checks the documents for text color against page color, table cell backgrounds, highlight, textbox fill color, paragraph shading, shape and SmartArt fills, headers and footers, and links.

Use an accessible template.

Top of Page

Create accessible charts

Charts help make complex information easier to understand. To make charts accessible, use clear and descriptive language for the chart elements, such as the chart title, axis titles, and data labels. Also make sure their formatting is accessible.

For instructions on how to add chart elements to your chart and make them accessible, go to Video: Create more accessible charts in Excel.

Format a chart element

1. Select the chart element you want to format, for example, the chart title or data labels.

2. Select the Format tab.

3. Select the formatting options that make your chart element accessible, such as a larger font or well-contrasting colors.

Top of Page

Rename worksheets

Screen readers read worksheet names, so make sure those labels are clear and descriptive. Using unique names for worksheets makes it easier to navigate the workbook.

By default, Excel names worksheets as Sheet1, Sheet2, Sheet3, and so on, but you can easily rename them. For instructions on how to rename worksheets, go to Rename a worksheet.

Top of Page

Delete blank worksheets

Screen readers read worksheet names, so blank worksheets might be confusing. Do not include any blank sheets in your workbooks.

For instructions on how to delete worksheets, go to Insert or delete a worksheet.

Top of Page

Name cells and ranges

Name cells and ranges so that screen reader users can quickly identify the purpose of cells and ranges in Excel worksheets. Users can use the Go To command (Ctrl+G) to open up a dialog box which lists all the defined names. By selecting a name, a user can quickly jump to the named location.

Select the cell or range of cells that you want to name.

Note: The name must start with a letter, an underscore (_), or a backslash (\) and cannot contain spaces.

Top of Page

Test the accessibility of your worksheets

When your spreadsheet is ready and you've run the Accessibility Checker to make sure it is inclusive, you can try navigating the spreadsheet using a screen reader, for example, Narrator. Narrator comes with Windows, so there's no need to install anything. This is one additional way to spot issues in the navigation, for example.

1. Start the screen reader. For example, to start Narrator, press Ctrl+Windows logo key+Enter.

2. Press F6 until the focus, the blue rectangle, is on the worksheet table grid.

3. Do the following to test your worksheets:

1. Use the arrow keys to move between the cells in the table grid.

2. To check the worksheet names in your spreadsheet, press F6 until the focus is on the name of the current worksheet, and then use the Left and Right arrow keys to hear the other worksheet names.

3. If your worksheet contains floating shapes such images, press Ctrl+Alt+5. Then, to cycle through the floating shapes, press the Tab key. To return to the normal navigation, press Esc.

4. Fix any accessibility issues you spotted when navigating with a screen reader.

5. Exit the screen reader. For example, to exit Narrator, press Ctrl+Windows logo key+Enter.

Note: Also make sure that your worksheets can be easily read on a mobile phone. This not only benefits people who have low vision and use magnification, but it also benefits a very broad set of mobile phone users.

Top of Page

See also

Rules for the Accessibility Checker

Use the Accessibility Reminder to notify authors of accessibility issues

Everything you need to know to write effective alt text

Make your Word documents accessible to people with disabilities

Make your PowerPoint presentations accessible to people with disabilities

Make your Outlook email accessible to people with disabilities

Make your OneNote notebooks accessible to people with disabilities

Excel help & learning

Mac: Best practices for making Excel spreadsheets accessible

The following table includes key best practices for creating Excel spreadsheets that are accessible to people with disabilities.

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 Microsoft 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 sheets in your 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

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.

Use a simple table structure for data only, 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 alt text to visuals in Microsoft 365

The following procedures describe how to add alt text to visuals in your Excel spreadsheets in Microsoft 365:

Note: For audio and video content, in addition to alt text, include closed captioning for people who are deaf or have limited hearing.

Tip: To write a good alt text, make sure to convey the content and the purpose of the image in a concise and unambiguous manner. The alt text shouldn't be longer than a short sentence or two-most of the time a few thoughtfully selected words will do. Do not repeat the surrounding textual content as alt text or use phrases referring to images, such as, "a graphic of" or "an image of."

Add alt text to images

Add alt text to images, such as pictures and screenshots, so that screen readers can read the text to describe the image to users who can't see the image.

Do one of the following:

The Alt Text pane opens.

Add alt text to shapes

Do one of the following:

The Alt Text pane opens.

Add alt text to SmartArt graphics

Do one of the following:

The Alt Text pane opens.

Add alt text to PivotTable

1. Right-click a PivotTable, and select PivotTable Options....

Tip: You have to right-click somewhere inside the frame that surrounds the entire chart, not inside one of its parts

2. In the Description text box, type 1-2 sentences to describe the table and its context to someone who cannot see it. Once you're done, select OK.

Tip: To spell-check the word you just typed, right-click the word and select an option from the list.

Mark visuals as decorative

If your visuals are purely decorative and add visual interest but aren't informative, you can mark them as such without needing to write any alt text. Examples of objects that should be marked as decorative are stylistic borders. People using screen readers will hear that these objects are decorative so they know they aren't missing any important information.

1. To open the Alt Text pane, do one of the following:

The Alt Text pane opens.

2. Select the Mark as decorative check box. The text entry field becomes grayed out.

Tip: If you have several decorative items, you can edit them in a batch. Select them all, open the Alt Text pane, and click Decorative.

Add alt text to visuals in Office 2019

The following procedures describe how to add alt text to visuals in your Excel spreadsheets in Office 2019:

Note: For audio and video content, in addition to alt text, include closed captioning for people who are deaf or have limited hearing.

Tip: To write a good alt text, make sure to convey the content and the purpose of the image in a concise and unambiguous manner. The alt text shouldn't be longer than a short sentence or two-most of the time a few thoughtfully selected words will do. Do not repeat the surrounding textual content as alt text or use phrases referring to images, such as, "a graphic of" or "an image of."

Add alt text to images

Add alt text to images, such as pictures and screenshots, so that screen readers can read the text to describe the image to users who can't see the image.

Do one of the following:

The Alt Text pane opens.

Add alt text to shapes

Do one of the following:

The Alt Text pane opens.

Add alt text to SmartArt graphics

Do one of the following:

The Alt Text pane opens.

Add alt text to PivotTable

1. Right-click a PivotTable, and select PivotTable Options....

Tip: You have to right-click somewhere inside the frame that surrounds the entire chart, not inside one of its parts.

2. In the Description text box, type 1-2 sentences to describe the table and its context to someone who cannot see it. Once you're done, select OK.

Tip: To spell-check the word you just typed, right-click the word and select an option from the list.

Mark visuals as decorative

If your visuals are purely decorative and add visual interest but aren't informative, you can mark them as such without needing to write any alt text. Examples of objects that should be marked as decorative are stylistic borders. People using screen readers will hear that these objects are decorative so they know they aren't missing any important information.

1. To open the Alt Text pane, do one of the following:

The Alt Text pane opens.

2. Select the Mark as decorative check box. The text entry field becomes grayed out.

Tip: If you have several decorative items, you can edit them in a batch. Select them all, open the Alt Text pane, and click Mark as decorative.

Add alt text to visuals in Office 2016

The following procedures describe how to add alt text to visuals in your Excel spreadsheets in Office 2016:

Note: For audio and video content, in addition to alt text, include closed captioning for people who are deaf or have limited hearing.

Add alt text to images

Add alt text to images, such as pictures, and screenshots, so that screen readers can read the text to describe the image to users who can't see the image.

1. Right-click an image.

2. Select Alt Text.

Add alt text to SmartArt graphics

1. Right-click a SmartArt graphic.

2. Select Alt Text.

Add alt text to shapes

Add alt text to shapes, including shapes within a SmartArt graphic.

1. Right-click a shape.

2. Select Alt Text.

Add alt text to PivotCharts

1. Right-click a PivotChart.

2. Select Alt Text.

Make hyperlinks, tables, and sheet tabs accessible

The following procedures describe how to make the hyperlinks, tables, and sheet tabs in Excel spreadsheets accessible.

Add hyperlink text and ScreenTips

Right-click a cell.

Tip: If the title on the hyperlink's destination page gives an accurate summary of what's on the page, use it for the hyperlink text. For example, this hyperlink text matches the title on the destination page: Templates and Themes for Office Online.

Add headers to a new table

Specify a header row in a new block of cells you are marking as a table.

1. Select the cells you want to include in the table.

2. Select OK.
Excel creates a header row with the default names Column1, Column2, and so on.

3. Type new, descriptive names for each column in the table.

Delete a sheet tab

Right-click a sheet tab.

iOS: Best practices for making Excel spreadsheets accessible

The following table includes key best practices for creating Excel spreadsheets that are accessible to people with disabilities.

What to fix

Why fix it

How to fix it

Give all sheet tabs unique names.

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

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

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.

Use headers in existing tables

Make tables and sheet tabs accessible

The following procedures describe how to make the tables and sheet tabs in your Excel spreadsheets accessible.

Rename sheet tabs

1. Tap the Sheet icon in the bottom left corner of the screen.

2. To replace the selected sheet name, type a brief, unique name for the sheet.

Android: Best practices for making Excel spreadsheets accessible

The following table includes key best practices for creating Excel spreadsheets that are accessible to people with disabilities.

What to fix

Why fix it

How to fix it

Include alternative text with all visuals and tables.

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

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 images

Add alt text to shapes

Add alt text to charts

Give all sheet tabs unique names.

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

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

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 alt text to visuals

The following procedures describe how to add alt text to visuals in your Excel spreadsheets.

Note: For audio and video content, in addition to alt text, include closed captioning for people who are deaf or have limited hearing.

Add alt text to images

Add alt text to images such as pictures and screenshots so that screen readers can read the text to describe the image to users who can't see the image.

1. Select an image.

2. Type a description and a title. Your changes are automatically saved.

Tip: Include the most important information in the first line, and be as concise as possible.

Add alt text to shapes

Add alt text to shapes including shapes within a SmartArt graphic.

1. Select a shape.

2. Type a description and a title. Your changes are automatically saved.

Tip: Include the most important information in the first line, and be as concise as possible.

Add alt text to charts

1. Select a chart.

2. Type a description and a title. Your changes are automatically saved.

Tip: Include the most important information in the first line, and be as concise as possible.

Make tables and sheet tabs accessible

The following procedures describe how to make the tables and sheet tabs in your Excel spreadsheets accessible.

Add headers to new tables

Specify a header row in a new block of cells you are marking as a table.

Highlight the cells you want to include in the table.

Rename sheet tabs

Type a brief, unique name for the sheet.

Office Online: Best practices for making Excel Online spreadsheets accessible

The following table includes key best practices for creating Excel for the web spreadsheets that are accessible to people with disabilities.

What to fix

How to find it

Why fix it

How to fix it

Include alternative text with all visuals and tables.

Use the Accessibility Checker to find instances of missing alternative text in the spreadsheet.

Alt text helps people who can't see the screen to understand what's important in tables.

Avoid using text in tables as the sole method of conveying important information. In alt text, briefly describe the contents of the table and its intent.

Add meaningful hyperlink text.

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.

Add hyperlink text

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

Read the names of the sheet tabs in your workbook, and verify that each sheet has content.

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.

Give the workbook a meaningful name

Rename sheet tabs

Delete sheet tabs

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

Use the Accessibility Checker to ensure that tables don't contain split cells, merged cells, nested tables, or 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 rows and columns 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.

Use headers in a table

Add alt text to images and charts

The following procedures describe how to add alt text to images and charts in your Excel for the web spreadsheets. To add alt text to tables, use a desktop version of Excel.

Note: We recommend only putting text in the description field and leaving the title blank. This will provide the best experience with most major screen readers including Narrator.

Add alt text to images

Add alt text to charts

Make hyperlinks, tables, and sheet tabs accessible

The following procedures describe how to make the hyperlinks, tables, and sheet tabs in Excel for the web spreadsheets accessible.

Add hyperlink text

1. Right-click a cell.

2. In Display Text, type the text that people will click on. Then, depending on the type of link you want to use, do one of the following:

In URL, type or paste the address.

In Place in this document, type the location of the cell you want to link to. For example, A6.

In Email address, type the email address in the format of someone@example.com.

Tip: If the title on the hyperlink's destination page gives an accurate summary of what's on the page, use it for the hyperlink text. For example, this hyperlink text matches the title on the destination page: Templates and Themes for Office Online.

Give the workbook a meaningful name

Note: If you want to rename a file, select Rename, type the name for the file, and select OK.

Rename sheet tabs

Type a brief, unique name for the tab that's descriptive of its contents.

Delete sheet tabs

Right-click a sheet tab.

# data[].attributes.attachment_link.title
Make your Excel documents accessible to people with disabilities

# data[].attributes.attachment_link.description
In this topic The following table includes key best practices for creating Excel spreadsheets that are accessible to people with disabilities. What to fix How to find it Why fix it How to fix it Avoid common accessibility issues such as missing alternative text (alt text) and low contrast colors.

# data[].attributes.headline
Accessible MS Excel Spreadsheets | Digital Accessibility

# data[].attributes.subject
Accessible MS Excel Spreadsheets | Digital Accessibility

# data[].attributes.attachment_link.readable_text
Overview

This reference guide describes how to format your Microsoft Excel documents to be as accessible as possible. It builds upon the accessibility principles explained in the Accessible Document Overview guide; those principles apply to all documents, including Microsoft Excel files, so make sure you are familiar with those guidelines before proceeding here. This resources focuses on how to implement the principles in Excel.

Except where noted, this reference uses the Windows version of Microsoft Office. The Office 365 version does not provide easy access to all of the necessary accessibility features, and is not recommended unless a desktop version is otherwise unavailable.

Guidelines

Workbook and Worksheet Layout

Quick pointers on how to organize sheets within your workbook, and where to place the content within those sheets.

Sheets

Treat cell A1 on every sheet like the title of that sheet - provide a brief description of the contents contained therein. This is where the user of assistive technology will begin navigating, and a description will help the user know what they reviewing.

Begin the content on cell A2 of each sheet.

Don't add extra rows or columns for formatting purposes. If you want to increase the whitespace for layout purposes, widen or otherwise style the current column and/or row, rather than adding empty columns and rows. If users encounter an empty row or column, they may think they have reached the end of the content on the sheet.

Workbooks

Each sheet in your workbook should contain a single dataset or table. Graphs/Charts should be on their own sheets. If you have multiple datasets within a single sheet, break them out into separate sheets.

Sheets names should be descriptive and unique. Do not leave the default Sheet1, Sheet2 names.

Delete unused sheets.

Always save the workbook with the cursor in cell A1 of Sheet 1.

Tables and Datasets

While accessibility guidelines dictate that tables should be avoided whenever possible, Excel is the most appropriate medium to present data in tabular format, especially if the data necessitates complex tables. The following guidelines will help users of assistive technologies more easily understand the structure of tables and datasets.

Identify Table Headers

In order to prevent ambiguity, explicitly identify any headers in your table. By default, tabular data in Excel is not officially organized in a table, it's considered a Dataset. If you do optionally specifically identify it as a table under the Insert -> Table option, make sure the "My table has headers" option is checked. This will help screen readers understand that the first row in the table is data that applies to every other cell in the table.

Define Names for Datasets

This sounds very similar to the previous item, but is different. This applies to Datasets that are not specifically identified as Tables.

The concept of named cells is not widely understood by most Excel users. It provides a shorthand way of referring to certain parts of your dataset. Giving your headings a consistent name allows people who use assistive technology (and everyone else!) to refer to certain parts of your dataset, even without knowing which cells they occupy.

For example, if you had a dataset with a list of people and their ages, you could created a named group "Children" for everyone under the age of 18, and a named group "Adults" for everyone 18 and older. Then you could use a built in Excel function to find the median age of the "Children" group in your list, without needing to specify the range of cells in your sheet.

To define named cells, select the headers (rows and/or columns), right click on the highlighted cells, then choose "Define Name" from the dropdown.

The accessibility community has adopted unofficial standards for naming table headers and columns, so people can easily jump to these parts of the sheet. The conventions are as follows:

If your table has both column and row headers, give the name field a value of Title

If your table has row headers only (Column A only), give the name field a value of RowTitle

If your table has column headers only (Row 2 only), give the name field a value of ColumnTitle

The Scope defines which sheets are affected by this. Excel defaults this value to the entire workbook. Unless you have the same data in the same positions on every sheet in your workbook, change the Scope to apply only to the current sheet.

Once you've done this, users of assistive technology can refer to the headers even without knowing where they are in the sheet.

Avoid Merged Cells

Don't use merged cells. You can use other purely visual stylistic techniques to emulate the appearance of merged cells, especially for elements like table headers, but the Merge Cell functionality can cause users (and their assistive technologies) to lose track of which rows and columns they are focusing on in the table.

Avoid Empty Cells

If you have cells that are missing data, use "n/a" or some other placeholder text to explicitly indicate that the data is missing or unavailable. Simply leaving the cell blank might lead the user to erroneously believe they have reached the end of the table.

Charts

Charts and graphs follow the same accessibility guidelines in Excel than they do in other mediums. The main accessibility concerns with charts in Excel is ensuring that they are sufficiently described for people who are unable to see them, and having means besides color to differentiate between data.

Describing charts requires adding both alt text and a longer plaintext description elsewhere on the document.

To add alt text, right-click on the chart, then select Edit Alt Text. Depending on your version of Excel, either a dialog box will popup, or a sidebar will slide out where you can enter alt text. You also have the option to flag this image as decorative, but charts are never decorative.

The alt text field, as in other document formats, is limited to 1 or 2 sentences. Provide a summary of what the chart represents, often similar to the chart title, versus trying to fully describe the chart within the alt text field.

The other major potential accessibility barrier is with the usage of color. Excel charts default to differentiating data solely by color. One series may be green, one may be blue, one may be red, but there are no additional indicators to tell between the data. This makes the chart inaccessible to some people with colorblindness. As a rule of thumb, if you print the chart out on a black and white printer and are unable to distinguish between the different data series, it is inaccessible.

To resolve this, right click on a data series in the chart and select "Format Data Series". You will see options to style the Fill & Line of that series, in both the chart and the legend. Here, you can choose a variety of ways to visually differentiate the different series of data. Most common is to change the Dash Type. There aren't prescriptive steps here, so long as the different data can be visually distinguished by some means beyond color alone.

How to Test

Accessibility Checks

Like other Microsoft products, Excel has a convenient built-in accessibility checker. This is not an exhaustive tool, but will help you identify any common errors, like missing alt text on media and charts. Under the Review tab, clicking the Check Accessibility button will bring up a sidebar where you can review and revise accessibility errors before publishing.

Additional Resources

Sample files used in the creation of this document, using modified publicly available data from the Alberta provincial government:

# data[].attributes.attachment_link.title
Accessible MS Excel Spreadsheets | Digital Accessibility

# data[].attributes.attachment_link.description
This reference guide describes how to format your Microsoft Excel documents to be as accessible as possible. It builds upon the accessibility principles explained in the Accessible Document Overview guide; those principles apply to all documents, including Microsoft Excel files, so make sure you are…

# data[].attributes.body
If Then Else Statement in Excel VBA (explained with examples)

VBA IF – IF Then Else Statement

Use the If Then statement in Excel VBA

How to use the IF-THEN-ELSE Statement (VBA)

# data[].attributes.headline
Excel- Email only 1 sheet of  a multi-sheet workbook

# data[].attributes.subject
Excel- Email only 1 sheet of  a multi-sheet workbook

# data[].attributes.attachment_link.readable_text
Learn how to calculate the fiscal year and fiscal month for a specific date. The formulas are based on the Fiscal Year starting date or starting month. Also, see how to show fiscal year results in a pivot table.

Author: Debra Dalgleish

Calculate Fiscal Year in Excel

Based on the month number in which the fiscal year starts, you can use the Microsoft Excel IF function to calculate the fiscal year for any date.

In the example shown below, information is entered in the green cells:

fiscal year starting month number is entered in cell C4

a date is entered in cell C6

Fiscal Year Formula

To get the fiscal year for the given date in cell C6, enter the following formula in cell C8:

The above formula result shows the fiscal year for the date. There is an explanation below, for how the formula works.

Number Format

The result in cell C8 might change to a Date format, after you enter the formula, or if you recalculate the worksheet. To fix that, follow these steps:

Select cell C8

To open the Format Cells dialog box, press the keyboard shortcut, Ctrl+1 (one)

Go to the Number tab

In the Category list, click Number

For Decimal places, select 0 (zero)

Remove the check mark for "Use 1000 separator"

Click OK to apply the number format, and to close the dialog box.

How Fiscal Year Formula Works

To figure out the date's fiscal year without an Excel formula, this is the logic we would use:

If the date's month is before the start of the fiscal year:
Fiscal year is the same as the Calendar year

If the month is on or after the start of the fiscal year:
Fiscal year number is Calendar year + 1

To calculate the fiscal year witn an Excel formula, we need 3 pieces of information:

1. Date's year number

2. Date's month number

3. Is month number on or after Fiscal Year start month

1) Get the Year

First, the formula calculates the year for the date entered in cell C6, using the YEAR function.

2) Get the Month

Next, the MONTH function gets the month number for the date in cell C6:

3) Compare Month Numbers

After that, the formula compares the month number for cell C6, to the fiscal year starting month, that was entered in cell C4

Is that comparison TRUE or FALSE

IF Formula for TRUE or FALSE

That month comparison is inside an Excel IF formula, which uses the TRUE or FALSE result of that test, to add 1 or zero to the year number.

How the IF Function Works

IF is one of the Logical functions in Microsoft Excel, and there are 3 parts (arguments) to the IF function syntax:

1. logical_test: TEST something, such as the value in a cell.

2. value_if_true: Specify what should happen if the test result is TRUE.

3. value_if_false: Specify what should happen if the test result is FALSE.

IF Function Video

This short video shows the steps for setting up a simple IF formula.

Note: The written steps for the Fiscal Year continue below the video.

Excel IF Function for Fiscal Year

Here are those 3 arguments, used in our fiscal year IF formula:

And here is the full fiscal year formula that was entered in C8:

Fiscal Year Result Check

Using the date August 1, 2022 as an example, for fiscal year starting in July, here is the arithmetic for the calculation:

Video: CHOOSE Function for Fiscal Calculation

The formulas in the following sections use the CHOOSE function, to select the applicable fiscal month or fiscal quarter.

To see how the CHOOSE function works, watch this short video tutorial. It shows a few examples, starting with a formula for fiscal quarter.

Calculate Fiscal Month - CHOOSE Function

To calculate the fiscal month, you can use the CHOOSE fFunction. The CHOOSE function returns a value from a list, based on an index number.

Based on the month number, the fiscal month number can be returned from a list of numbers.

In this example, the fiscal year starts in July, so January is fiscal month 7. For the months from January to December, the fiscal months are in this order: 7,8,9,10,11,12,1,2,3,4,5,6

Those month numbers can be typed into the CHOOSE formula, or you can refer to the cells that contain the numbers.

Option 1 - List the Numbers

To calculate the Fiscal Month with the CHOOSE function, the date is entered in cell C6.

The following formula is entered in cell C11:
=CHOOSE(MONTH(C6),7,8,9,10,11,12,1,2,3,4,5,6)
If the date in cell C6 is March 22, 2015, the MONTH function will return 3 as the month number.

The formula returns the 3rd number from the list of fiscal months -- 9
7,8,9,10,11,12,1,2,3,4,5,6
Option 2 - Link to Cells

To calculate the Fiscal Month with the CHOOSE function, the date is entered in cell C6.

Fiscal months are entered in the reference table, in cells G6:R6

The following formula is entered in cell K11:
=CHOOSE(MONTH(C6),G6,H6,I6,J6,K6,L6,M6,N6,O6,P6,Q6,R6)
If the date in cell C6 is March 22, 2015, the MONTH function will return 3 as the month number.

The formula returns value from the 3rd reference in the list -- cell I6
G6,H6,I6,J6,K6,L6,M6,N6,O6,P6,Q6,R6
That cell contains the value 9

Calculate Fiscal Quarter - CHOOSE Function

To calculate the fiscal quarter, you can use the CHOOSE function. The CHOOSE function returns a value from a list, based on an index number.

Based on the month number, the fiscal quarter number can be returned from a list of numbers.

In the example shown below, the fiscal year starts in April, so January is fiscal quarter 4.

For the months from January to December, the fiscal quarters are in this order: 4,4,4,1,1,1,2,2,2,3,3,3

Those numbers are listed across row 6, in the Fiscal Qtrs cells

The month numbers are entered in row 7, starting with month 10.

Those quarter numbers can be typed into the CHOOSE formula, to calculate the fiscal quarter for a specific date.

Use the CHOOSE Function

To calculate the Fiscal quarter with the CHOOSE function, the date is entered in cell C6.

The following formula is entered in cell C11:
=CHOOSE(MONTH(C6),4,4,4,1,1,1,2,2,2,3,3,3)
If the date in cell C6 is March 22, 2015, the MONTH function will return 3 as the month number.

The formula returns the 3rd number from the list of fiscal months -- 4
4,4,4,1,1,1,2,2,2,3,3,3
Download the Sample File

Click here to download the Fiscal Year sample file. It is zipped, and in xlsx format, and does not contain any macros.

Related Tutorials
Date Functions Fiscal Year - Pivot Table Fiscal Year Weekdays - Pivot Table Budget Forecast
CHOOSE Function

Group by Fiscal Year

# data[].attributes.attachment_link.description
Learn how to calculate the fiscal year and fiscal month for a specific date. The formulas are based on the Fiscal Year starting date or starting month. Also, see how to show fiscal year results in a pivot table.

# data[].attributes.headline
Remove leading and trailing spaces from text

# data[].attributes.subject
Remove leading and trailing spaces from text

# data[].attributes.attachment_link.title
Remove leading and trailing spaces from text

# data[].attributes.attachment_link.description
The TRIM function is fully automatic. It removes removes both leading and trailing spaces from text, and also "normalizes" multiple spaces between words to one space character only. All you need to do is supply a reference to a cell.

# data[].attributes.headline
Learn How to Highlight Rows in Excel Based on Duplicates

# data[].attributes.subject
Learn How to Highlight Rows in Excel Based on Duplicates

# data[].attributes.attachment_link.title
Learn How to Highlight Rows in Excel Based on Duplicates

# data[].attributes.attachment_link.description
As you may be aware, Excel’s Conditional Formatting feature allows you to identify duplicates in a column. This technique requires some tech savvy in certa

# data[].attributes.headline
Automatically expand the VLOOKUP data range

# data[].attributes.subject
Automatically expand the VLOOKUP data range

# data[].attributes.attachment_link.title
Automatically expand the VLOOKUP data range - Excel Off The Grid

# data[].attributes.attachment_link.description
The 2nd argument in the VLOOKUP function is the table_array. This is the range of cells which contains the data to be used within the VLOOKUP function.

# data[].attributes.headline
Preventing the entry of formulas in cells

# data[].attributes.subject
Preventing the entry of formulas in cells

# data[].attributes.attachment_link.readable_text
I'm using MS Excel 2007. I would like to restrict the data entry of some cells in a worksheet to certain numeric values and also forbid the entry of formulas in those cells.

As an example, given this data

I would like to only allow literal numeric values greater than 100 to be entered in C1.

I tried using Data→ Data Validation→[ Allow= Decimal, Data= Greater than, Minimum= 100] but this also allows certain formulas to be inputted. If you try entering =A1*B1 in cell C1, because it evaluates to more than 100, the data validation allows the formula.

How do I enforce this extra no-formula restriction?

# data[].attributes.attachment_link.title
Preventing the entry of formulas in cells

# data[].attributes.attachment_link.description
I'm using MS Excel 2007. I would like to restrict the data entry of some cells in a worksheet to certain numeric values and also forbid the entry of formulas in those cells. As an example, given this data I would like to only allow literal numeric values greater than 100 to be entered in C1.

# data[].attributes.attachment_link.readable_text
What to Know

To help control the size of an Excel worksheet, you can limit the number of columns and rows that a worksheet displays. In this guide, we show you how to hide (and unhide) rows and columns in Excel 2019, Excel 2016, Excel 2013, and Excel for Microsoft 365, as well as how to limit access to rows and columns using Microsoft Visual Basic for Applications (VBA).

Hide Rows and Columns in Excel

An alternative method for restricting the work area of a worksheet is to hide sections of unused rows and columns; they'll stay hidden even after you close the document. Follow the steps below to hide the rows and columns outside the range A1:Z30.

1. Open your workbook and select the worksheet you want to hide rows and columns in. Click the header for row 31 to select the entire row.

2. Press and hold the Shift and Ctrl keys on the keyboard. At the same time, press the down arrow key on the keyboard to select all rows from row 31 to the bottom of the worksheet. Release all the keys.

3. Right-click one of the row headings to open the contextual menu. Select Hide.

4. The worksheet now shows only the data in rows 1 through 30.

5. Click the header for column AA and repeat steps 2 and 3 (using the right arrow key instead of the down arrow key) to hide all columns after column Z.

6. Save the workbook; the columns and rows outside the range A1 to Z30 will remain hidden until you unhide them.

You can use the same process to hide any rows or columns you want. Just select the header or headers for the row or column, right-click the header, and select Hide.

Unhide Rows and Columns in Excel

When you want to view the data you hid, you can unhide the rows and columns at any time. Follow these steps to unhide the rows and columns you hid in the previous example.

1. Open the worksheet you used to hide row 31 and higher and column AA and higher. Click the headers for row 30 (or the last visible row in the worksheet) and the row below it. Right-click the row headers and, from the menu, select Unhide.

2. The hidden rows are restored.

3. Now click the headers for column Z (or the last visible column) and the column to the right of it. Right-click the selected column headers and, from the menu, choose Unhide. The hidden columns are restored.

Limit Access to Rows and Columns With VBA

You can use Microsoft Visual Basic for Applications (VBA) to temporarily limit the range of usable rows and columns in a worksheet. In this example, you'll change the properties of a worksheet to limit the number of available rows to 30 and the number of columns to 26.

1. Open a blank Excel file. At the bottom of the screen, right-click the Sheet1 sheet tab. From the menu, choose View Code.

2. The Visual Basic for Applications (VBA) editor window opens. In the left rail, locate the Properties section.

3. Under Properties,in the right column of the ScrollArea row, click the empty box and type A1:Z30.

4. Select File > Save and save your workbook as you normally would. Select File > Close and Return to Microsoft Excel.

5. To make sure your change is applied, perform this test. In your worksheet, try to scroll past row 30 or column Z. If the change has been applied, Excel bounces you back to the selected range and you're unable to edit cells outside that range.

6. To remove the restrictions, access VBA again and delete the ScrollArea range.

What is the maximum limit of rows in Excel?

Microsoft says that the maximum size of a single Excel spreadsheet is 1,048,576 rows by 16,384 columns. The maximum row height is 409 points, and the maximum column width is 255 characters.

How do I permanently set row and column limits in Excel?

If you want to set a hard limit to columns or rows in an Excel spreadsheet (i.e. set it up so no additional rows or columns can be added past that set limit), unfortunately you can't. There's currently no menu option to prevent yourself or other users from adding more rows or columns you may not want. However, you can manually delete unwanted rows and columns by right-clicking the selected row(s) or column(s) and selecting Delete.

Thanks for letting us know!

Other Not enough details Hard to understand

# data[].attributes.attachment_link.title
Learn How to Limit Rows and Columns in an Excel Worksheet

# data[].attributes.attachment_link.description
To help control the size of an Excel worksheet, you can limit the number of columns and rows that a worksheet displays. In this guide, we show you how to hide (and unhide) rows and columns in Excel 2019, Excel 2016, Excel 2013, and Excel for Microsoft 365, as well as how to limit access to rows and columns using Microsoft Visual Basic for Applications (VBA).

# data[].attributes.headline
How to Apply Conditional Formatting to Blank Cells

# data[].attributes.subject
How to Apply Conditional Formatting to Blank Cells

# data[].attributes.attachment_link.title
Learn How to Conditionally Format Blank Cells - Excelchat

# data[].attributes.attachment_link.description
We can use the conditional formatting approach for "cells that do not contain any value or for blank cells". This step by step tutorial will assist all levels of Excel users on how to apply conditional formatting to blank cells. The aim of this exercise is apply formatting to the cells in Excel that are blank.

# data[].attributes.headline
PowerPoint Quick Reference Guide (CustomGuide)

# data[].attributes.subject
PowerPoint Quick Reference Guide (CustomGuide)

# data[].attributes.headline
Create External link to another workbook

# data[].attributes.subject
Create External link to another workbook

# data[].attributes.attachment_link.readable_text
1. Open the workbook that will contain the external reference (the destination workbook) and the workbook that contains the data that you want to link to (the source workbook).

2. Select the cell or cells where you want to create the external reference.

3. Type = (equal sign).

If you want to use a function, such as SUM, then type the function name followed by an opening parenthesis. For example, =SUM(.

4. Switch to the source workbook, and then click the worksheet that contains the cells that you want to link.

5. Select the cell or cells that you want to link to and press Enter.

Note: If you select multiple cells, like =[SourceWorkbook.xlsx]Sheet1!$A$1:$A$10, and have a current version of Microsoft 365, then you can simply press ENTER to confirm the formula as a dynamic array formula. Otherwise, the formula must be entered as a legacy array formula by pressing CTRL+SHIFT+ENTER. For more information on array formulas, see Guidelines and examples of array formulas.

6. Excel will return you to the destination workbook and display the values from the source workbook.

7. Note that Excel will return the link with absolute references, so if you want to copy the formula to other cells, you'll need to remove the dollar ($) signs:

=[SourceWorkbook.xlsx]Sheet1!$A$ 1

If you close the source workbook, Excel will automatically append the file path to the formula:

='C:\Reports\[SourceWorkbook.xlsx]Sheet1'!$A$1

1. Open the workbook that will contain the external reference (the destination workbook) and the workbook that contains the data that you want to link to (the source workbook).

2. Select the cell or cells where you want to create the external reference.

3. Type = (equal sign).

4. Switch to the source workbook, and then click the worksheet that contains the cells that you want to link.

5. Press F3, select the name that you want to link to and press Enter.

Note: If the named range references multiple cells, and you have a current version of Microsoft 365, then you can simply press ENTER to confirm the formula as a dynamic array formula. Otherwise, the formula must be entered as a legacy array formula by pressing CTRL+SHIFT+ENTER. For more information on array formulas, see Guidelines and examples of array formulas.

6. Excel will return you to the destination workbook and display the values from the named range in the source workbook.

1. Open the destination workbook and the source workbook.

2. In the destination workbook, Go to Formulas > Defined Names > Define Name.

3. In the New Name dialog box, in the Name box, type a name for the range.

4. In the Refers to box, delete the contents, and then keep the cursor in the box.

If you want the name to use a function, enter the function name, and then position the cursor where you want the external reference. For example, type =SUM(), and then position the cursor between the parentheses.

5. Switch to the source workbook, and then click the worksheet that contains the cells that you want to link.

6. Select the cell or range of cells that you want to link, and click OK.

External references are especially useful when it's not practical to keep large worksheet models together in the same workbook.

Merge data from several workbooks You can link workbooks from several users or departments and then integrate the pertinent data into a summary workbook. That way, when the source workbooks are changed, you won't have to manually change the summary workbook.

Create different views of your data You can enter all of your data into one or more source workbooks, and then create a report workbook that contains external references to only the pertinent data.

Streamline large, complex models By breaking down a complicated model into a series of interdependent workbooks, you can work on the model without opening all of its related sheets. Smaller workbooks are easier to change, don't require as much memory, and are faster to open, save, and calculate.

Formulas with external references to other workbooks are displayed in two ways, depending on whether the source workbook - the one that supplies data to a formula - is open or closed.

When the source is open, the external reference includes the workbook name in square brackets ([ ]), followed by the worksheet name, an exclamation point (!), and the cells that the formula depends on. For example, the following formula adds the cells C10:C25 from the workbook named Budget.xls.

External reference

=SUM([Budget.xlsx]Annual!C10:C25)

When the source is not open, the external reference includes the entire path.

External reference

=SUM('C:\Reports\[Budget.xlsx]Annual'!C10:C25)

Note: If the name of the other worksheet or workbook contains spaces or non-alphabetical characters, you must enclose the name (or the path) within single quotation marks as in the example above. Excel will automatically add these for you when you select the source range.

Formulas that link to a defined name in another workbook use the workbook name followed by an exclamation point (!) and the name. For example, the following formula adds the cells in the range named Sales from the workbook named Budget.xlsx.

External reference

=SUM(Budget.xlsx!Sales)

1. Select the cell or cells where you want to create the external reference.

2. Type = (equal sign).

If you want to use a function, such as SUM, then type the function name followed by an opening parenthesis. For example, =SUM(.

3. Switch to the worksheet that contains the cells that you want to link to.

4. Select the cell or cells that you want to link to and press Enter.

Note: If you select multiple cells (=Sheet1!A1:A10), and have a current version of Microsoft 365, then you can simply press ENTER to confirm the formula as a dynamic array formula. Otherwise, the formula must be entered as a legacy array formula by pressing CTRL+SHIFT+ENTER. For more information on array formulas, see Guidelines and examples of array formulas.

5. Excel will return to the original worksheet and display the values from the source worksheet.

# data[].attributes.attachment_link.title
Create an external reference (link) to a cell range in another workbook

# data[].attributes.attachment_link.description
You can refer to the contents of cells in another workbook by creating an external reference formula. An external reference (also called a link) is a reference to a cell or range on a worksheet in another Excel workbook, or a reference to a defined name in another workbook.

# data[].attributes.attachment_link.readable_text
Published 4/14/2022

1st Edition

Book 978-0-13-756505-4

eBook 978-0-13-756482-8

The quick way to learn Microsoft Project!

This is learning made easy. Get more done quickly with Microsoft Project. Jump in wherever you need answers. Brisk lessons and detailed screenshots show you exactly what to do, step by step.

Quickly start a new plan, build task lists, and assign resources

Share your plan and track your progress

Capture and fine-tune work and cost details

Use Gantt charts and other views and reports to visualize project schedules

Share resources across multiple plans and consolidate projects

Manage Agile plans using the new task boards to create backlogs, plan sprints, and hold scrum meetings

Learn more about integrating Project with Microsoft 365 solutions for collaboration

Master project management best practices as you learn Project

Easily identify the features and lessons you need

Online Sample Chapter

Start a new plan

Table of Contents

Part 1: Get started with Microsoft Project

1: Project, project management, and you

2: Take a guided tour

Part 2: Simple scheduling basics

3: Start a new plan

4: Build a task list

5: Set up resources

6: Assign resources to tasks

7: Format and share your plan

8: Track progress: Basic techniques

Part 3: Advanced scheduling techniques

9: Fine-tune task scheduling

10: Fine-tune task details

11: Fine-tune resource and assignment details

12: Fine-tune the Project plan

13: Organize plan details

14: Track progress: Detailed techniques

15: View and report project status

Part 4: In-depth and special subjects

16: Format and print views: In-depth techniques

17: Format reports: In-depth techniques

18: Customize Project

19: Share information with other programs

20: Consolidate projects and resources

21: Use Agile project management with Project

Part 5: Appendixes

A: A short course in project management

B: Develop your project management skills

C: Integration with Microsoft 365 solutions for collaboration

D: Use this book in a classroom

Glossary

Index

Downloads

Follow the instructions to download this book's lesson files.

1. Click the Download button below to start the download.

2. If prompted, click Save.

3. Locate the .zip file on your computer. Right-click the file, click Extract All, and then follow the instructions.

Download
Features

For the first time, includes instructor resources - PowerPoint slides, a testbank, and an Instructor's Manual

Fully updated for the most recent versions of Microsoft Project

Easy lessons for essential tasks

Detailed screenshots

Skill-building practice files

# data[].attributes.attachment_link.title
Microsoft Project Step by Step (covering Project Online Desktop Client)

# data[].attributes.attachment_link.description
The quick way to learn Microsoft Project!This is learning made easy. Get more done quickly with Microsoft Project.

# data[].attributes.headline
Excel: Conditional Formatting Based on Another Column

# data[].attributes.subject
Excel: Conditional Formatting Based on Another Column

# data[].attributes.attachment_link.title
Conditional formatting based on another column

# data[].attributes.attachment_link.description
In this example, a conditional formatting rule highlights cells in the range D5:D14 when the value is greater than corresponding values in C5:C14. The formula used to create the rule is: =$D5>$C5 The rule is applied to the entire range D5:G14.

# data[].attributes.headline
Project management: A priceless skill - Project Management Simplified Video Tutorial | LinkedIn Learning

# data[].attributes.subject
Project management: A priceless skill - Project Management Simplified Video Tutorial | LinkedIn Learning

# data[].attributes.body
In this video, discover what this course is about. All projects can be planned using the 12 step process explained and is a transferrable skill that you will have for life.

# data[].attributes.attachment_link.readable_text
From the course: Project Management Simplified

Project management: A priceless skill

- Projects are all around us. They're a vital part of any management job. You can't avoid them if you're going to grow or improve your organization. And so this is an important subject to understand. Project management is a transferrable skill. So once you master it, you will have a skill you can use anywhere for the rest of your life if you want to. So if you're planning and running projects of any kind, you'll know how to manage them easily. I'm going to show you everything you need to know to be able to run any project, any size, in any industry so that you can deliver all of your projects on time and on budget. My name's Chris Croft, and I've been teaching project management for 25 years. And over that time, I've boiled it down to just the bits that a normal, busy person needs to know. By the end of this course, you will not only understand project management, but you will believe in it. You'll be thinking, "that's great! "It's easy, and it's very useful. "Why would I not do it?" So I'd like to welcome you to my LinkedIn learning course on the basics of project management. And let's make your projects great.

Practice while you learn with exercise files

Download the files the instructor uses to teach the course. Follow along and learn by watching, listening and practicing.

Download courses and learn on the go

Watch courses on your mobile device without an internet connection. Download courses using your iOS or Android LinkedIn Learning app.

# data[].attributes.attachment_link.title
Project management: A priceless skill - Project Management Simplified Video Tutorial | LinkedIn Learning, formerly Lynda.com

# data[].attributes.attachment_link.description
In this video, discover what this course is about. All projects can be planned using the 12 step process explained and is a transferrable skill that you will have for life.

# data[].attributes.attachment_link.readable_text
Sometimes you might not want zero (0) values showing on your worksheets, sometimes you need them to be seen. Whether your format standards or preferences call for zeroes showing or hidden, there are several ways to make it happen.

Hide or display all zero values on a worksheet

Under Display options for this worksheet, select a worksheet, and then do one of the following:

To display zero (0) values in cells, check the Show a zero in cells that have zero value check box.

To display zero (0) values as blank cells, uncheck the Show a zero in cells that have zero value check box.

Hide zero values in selected cells

These steps hide zero values in selected cells by using a number format. The hidden values appear only in the formula bar and are not printed. If the value in one of these cells changes to a nonzero value, the value will be displayed in the cell, and the format of the value will be similar to the general number format.

1. Select the cells that contain the zero (0) values that you want to hide.

2. You can press Ctrl+1, or on the Home tab, click Format > Format Cells.

To display hidden values:

Hide zero values returned by a formula

Select the cell that contains the zero (0) value.

Display zeros as blanks or dashes

Use the IF function to do this.

Use a formula like this to return a blank cell when the value is zero:

Here's how to read the formula. If 0 is the result of (A2-A3), don't display 0 - display nothing (indicated by double quotes ""). If that's not true, display the result of A2-A3. If you don't want the cells blank but want to display something other than 0, put a dash "-" or other character between the double quotes.

Hide zero values in a PivotTable report

1. Click the PivotTable report.

2. Click the Layout & Format tab, and then do one or more of the following:

Change error display Check the For error values show check box under Format. In the box, type the value that you want to display instead of errors. To display errors as blank cells, delete any characters in the box.

Change empty cell display Check the For empty cells show check box. In the box, type the value that you want to display in empty cells. To display blank cells, delete any characters in the box. To display zeros, clear the check box.

Sometimes you might not want zero (0) values showing on your worksheets, sometimes you need them to be seen. Whether your format standards or preferences call for zeroes showing or hidden, there are several ways to make it happen.

Display or hide all zero values on a worksheet

Under Display options for this worksheet, select a worksheet, and then do one of the following:

To display zero (0) values in cells, select the Show a zero in cells that have zero value check box.

To display zero values as blank cells, clear the Show a zero in cells that have zero value check box.

Use a number format to hide zero values in selected cells

Follow this procedure to hide zero values in selected cells. If the value in one of these cells changes to a nonzero value, the format of the value will be similar to the general number format.

Select the cells that contain the zero (0) values that you want to hide.

Notes:

The hidden values appear only in the formula bar - or in the cell if you edit within the cell - and are not printed.

To display hidden values again, select the cells, and then press Ctrl+1, or on the Home tab, in the Cells group, point to Format, and click Format Cells. In the Category list, click General to apply the default number format. To redisplay a date or a time, select the appropriate date or time format on the Number tab.

Use a conditional format to hide zero values returned by a formula

1. Select the cell that contains the zero (0) value.

2. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, point to Highlight Cells Rules, and then click Equal To.

Use a formula to display zeros as blanks or dashes

To do this task, use the IF function.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1. Select the example in this article. If you are copying the example in Excel Web App, copy and paste one cell at a time.
Important Do not select the row or column headers.

Selecting an example from Help

2. Create a blank workbook or worksheet.

3. Important For the example to work properly, you must paste it into cell A1 of the worksheet.

4. After you copy the example to a blank worksheet, you can adapt it to suit your needs.

For more information about how to use this function, see IF function.

Hide zero values in a PivotTable report

1. Click the PivotTable report.

2. Click the Layout & Format tab, and then do one or more of the following:

Change empty cell display Select the For empty cells, show check box. In the box, type the value that you want to display in empty cells. To display blank cells, delete any characters in the box. To display zeros, clear the check box.

Sometimes you might not want zero (0) values showing on your worksheets, sometimes you need them to be seen. Whether your format standards or preferences call for zeroes showing or hidden, there are several ways to make it happen.

Display or hide all zero values on a worksheet

1. Click the Microsoft Office Button , click Excel Options, and then click the Advanced category.

2. Under Display options for this worksheet, select a worksheet, and then do one of the following:

To display zero (0) values in cells, select the Show a zero in cells that have zero value check box.

To display zero values as blank cells, clear the Show a zero in cells that have zero value check box.

Use a number format to hide zero values in selected cells

Follow this procedure to hide zero values in selected cells. If the value in one of these cells changes to a nonzero value, the format of the value will be similar to the general number format.

Select the cells that contain the zero (0) values that you want to hide.

Notes:

The hidden values appear only in the formula bar - or in the cell if you edit within the cell - and are not printed.

To display hidden values again, select the cells, and then on the Home tab, in the Cells group, point to Format, and click Format Cells. In the Category list, click General to apply the default number format. To redisplay a date or a time, select the appropriate date or time format on the Number tab.

Use a conditional format to hide zero values returned by a formula

Select the cell that contains the zero (0) value.

Use a formula to display zeros as blanks or dashes

To do this task, use the IF function.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

Important: Do not select the row or column headers.

Selecting an example from Help

1. Press CTRL+C.

2. In Excel, create a blank workbook or worksheet.

3. In the worksheet, select cell A1, and press CTRL+V.

Important: For the example to work properly, you must paste it into cell A1 of the worksheet.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.

For more information about how to use this function, see IF function.

Hide zero values in a PivotTable report

1. Click the PivotTable report.

2. Click the Layout & Format tab, and then do one or more of the following:

Change empty cell display Select the For empty cells, show check box. In the box, type the value that you want to display in empty cells. To display blank cells, delete any characters in the box. To display zeros, clear the check box.

# data[].attributes.attachment_link.description
Sometimes you might not want zero (0) values showing on your worksheets, sometimes you need them to be seen. Whether your format standards or preferences call for zeroes showing or hidden, there are several ways to make it happen.

# data[].attributes.attachment_link.readable_text
Goal Seek Example 1 | Goal Seek Example 2 | Goal Seek Precision | More about Goal Seek

If you know the result you want from a formula, use Goal Seek in Excel to find the input value that produces this formula result.

Goal Seek Example 1

Use Goal Seek in Excel to find the grade on the fourth exam that produces a final grade of 70.

1. The formula in cell B7 calculates the final grade.

2. The grade on the fourth exam in cell B5 is the input cell.

3. On the Data tab, in the Forecast group, click What-If Analysis.

4. Click Goal Seek.

The Goal Seek dialog box appears.

5. Select cell B7.

6. Click in the 'To value' box and type 70.

7. Click in the 'By changing cell' box and select cell B5.

8. Click OK.

Result. A grade of 90 on the fourth exam produces a final grade of 70.

Goal Seek Example 2

Use Goal Seek in Excel to find the loan amount that produces a monthly payment of $1500.

1. The formula in cell B5 calculates the monthly payment.

Explanation: the PMT function calculates the payment for a loan. If you've never heard of this function before, that's OK. The higher the loan amount, the higher the monthly payment. Assume, you can only afford $1500 a month. What is your maximum loan amount?

2. The loan amount in cell B3 is the input cell.

3. On the Data tab, in the Forecast group, click What-If Analysis.

4. Click Goal Seek.

The Goal Seek dialog box appears.

5. Select cell B5.

6. Click in the 'To value' box and type -1500 (negative, you are paying out money).

7. Click in the 'By changing cell' box and select cell B3.

8. Click OK.

Result. A loan amount of $250,187 produces a monthly payment of $1500.

Goal Seek Precision

Goal seek returns approximate solutions. You can change the iteration settings in Excel to find a more precise solution.

1. The formula in cell B1 calculates the square of the value in cell A1.

2. Use goal seek to find the input value that produces a formula result of 25.

Result. Excel returns an approximate solution.

3. On the File tab, click Options, Formulas.

4. Under Calculation options, decrease the Maximum Change value by inserting some zeros. The default value is 0.001.

5. Click OK.

6. Use Goal Seek again. Excel returns a more precise solution.

More about Goal Seek

There are many problems Goal Seek can't solve. Goal Seek requires a single input cell and a single output (formula) cell. Use the Solver in Excel to solve problems with multiple input cells. Sometimes you need to start with a different input value to find a solution.

1. The formula in cell B1 below produces a result of -0.25.

2. Use Goal Seek to find the input value that produces a formula result of +0.25.

Result. Excel can't find a solution.

3. Click Cancel.

4. Start with an input value greater than 8.

5. Use Goal Seek again. Excel finds a solution.

Explanation: y = 1 / (x - 8) is discontinuous at x = 8 (dividing by 0 is not possible). In this example, Goal seek cannot reach one side of the x-axis (x>8) if it starts on the other side of the x-axis (x<8) or vice versa.

# data[].attributes.attachment_link.description
Goal Seek Example 1 | Goal Seek Example 2 | Goal Seek Precision | More about Goal Seek If you know the result you want from a formula, use Goal Seek in Excel to find the input value that produces this formula result.

# data[].attributes.headline
Adjust Excel row heights to a specific size

# data[].attributes.subject
Adjust Excel row heights to a specific size

# data[].attributes.body
By default, Excel rows are 12.75 points high, which is the typographical equivalent of about one-sixteenth of an inch.

# data[].attributes.attachment_link.title
How to Adjust the Row Height Measurement in Microsoft Excel

# data[].attributes.attachment_link.description
To get the exact format you want in Excel, it's often necessary to adjust the row heights to a specific size. Row heights can be adjusted singly, in a group, or in an entire document. They can be changed to an exact measurement in pixels, inches, and other units of measure.

# data[].attributes.body
Click here to access

# data[].attributes.headline
The Definitive Guide to Using Dates and Times in Excel

# data[].attributes.subject
The Definitive Guide to Using Dates and Times in Excel

# data[].attributes.attachment_link.readable_text
Dates and times are two of the most common data types people work with in Excel, but they are also possibly the most frustrating to work with, especially if you are new to Excel and still learning. This is because Excel uses a serial number to represent the date instead of a proper month, day, or year, nevermind hours, minutes, or seconds. It's made more complicated by the fact that dates are also days of the week, like Monday or Friday, even though Excel doesn't explicitly store that information in the cells. Here is the definitive guide to working with dates and times in Excel...

How Excel Stores Dates

The source of most of the confusion around dates and times in Excel comes from the way that the program stores the information. You'd expect it to remember the month, the day, and the year for dates, but that's not how it works...

Excel stores dates as a serial number that represents the number of days that have taken place since the beginning of the year 1900. This means that January 1, 1900 is really just a 1. January 2, 1900 is 2. By the time we get all the way to the present decade, the numbers have gotten pretty big... September 10, 2013 is stored as 41527.

Importantly, any date before January 1, 1900 is not recognized as a date in Excel. There are no "negative" date serial numbers on the number line.

How Excel Stores Times

Excel stores times using the exact same serial numbering format as with dates. Days start at midnight (12:00am or 0:00 hours). Since each hour is 1/24 of a day, it is represented as that decimal value: 0.041666...

When a time is specified without a date, Excel stores it as if it occurred on January 0, 1900. In other words, 3:00pm ( 15:00 hours) is stored as 0.625. This can make doing math for time-only values (that have no date) challenging, since subtracting 6 hours ( 6:00) from 3:00am ( 03:00 hours) will become negative and count as an error: 0.125 - 0.25 = -0.125, which is displayed as #########.

Minutes and seconds in Excel work the same way as hours...

A second is 1/60 of an minute, which is 1/60 of an hour, which is 1/24 of a day, or 1/86400 of a day in total, which calculates to 0.00001157407...

Working with Dates and Times

DATE() and TIME()

Serial numbers aren't all that intuitive to use. Fortunately, Excel has a set of functions to make it easier to find and use dates and times, starting with DATE and TIME. The syntax is as follows:

For both functions, specify the year, month, and day, or hours, minutes, and seconds as numbers. For example, September 10, 2013 can be entered as:

=DATE(2013,9,10)

It will be stored as 41527, which means that it is technically storing 12:00am on September 10, 2013.

For times, 6:00pm ( 18:00 hours) can be entered as:

=TIME(18,0,0)

It will be stored as 0.75, which means that it is technically storing 6:00pm on January 0, 1900.

It will be calculate as 41527.75, which means Excel is storing exactly the date we want...

Additional Date and Time Setting Functions

Excel has a few additional functions to make declaring dates easier.

The TODAY function always returns the current date's serial number. The TODAY function is just entered as:

=TODAY()

This article was written at 6:30pm ( 18:30 hours) on September 24, 2013, and the TODAY function calculated to 41541. That means that it is technically storing 12:00am on September 24, 2013.

A similar function called NOW always returns the current date and time's serial number. The NOW function is just entered as:

=NOW()

Again, at 6:30pm ( 18:30 hours) on September 24, 2013, the function calculated to 41541.77081333... NOW stores the exact time and date, down to the second.

EDATE() and EOMONTH()

The EDATE function gives the date the specified number of months away from the input date. The EOMONTH function gives the date of the last day of the month. It can do so for the current month or a number of months in the future or the past. The syntax for each is as follows:

The start_date can be any date-formatted cell reference or date serial number.

The months field can be any number, though only the integer value will be used (e.g. it treats 2.8 as 2).

Occasionally, it may be useful to count ahead based on work-days (Monday-Friday) instead of all 7 days of the week... For that, Excel has provided WORKDAY. The syntax for WORKDAY is as follows:

The start_date is as above.

The days input is the number of workdays ahead (or behind) of the present day you would like to move.

The [holidays] input is optional, but lets you disqualify specific days (like Thanksgiving or Christmas, for example), which might otherwise fall during the work week. These are date serial numbers provided in an array bounded by brackets: { }. To specify multiple holidays, the dates must be held in cells - it is not possible to put multiple DATE functions in an array.

WORKDAY.INTL() (Excel 2010 and newer)

For newer versions of Excel (2010 and later), there is another version of WORKDAY called WORKDAY.INTL. WORKDAY.INTL works just like WORKDAY, but it adds the ability to customize the definition of the "weekend". The syntax for WORKDAY.INTL is as follows:

The start_date, days, and [holidays] inputs work just like the normal WORKDAY function.

The [weekend] input has the following options:

Retrieving Dates in Excel

DAY(), MONTH(), and YEAR()

Now we know how define dates, but we still need to be able to work with them. Serial numbers don't make it easy to extract months, years, and days, nevermind hours, minutes, and seconds. That's why Excel has specific functions for pulling out each of these values. For working with the calendar, there is DAY, MONTH, and YEAR. The syntax is simple:

The serial_number in each can be any date-formatted cell reference. For example, if cell A1 stores September 10, 2013, we can use each of the formulas in turn:

Returns 10 as a numeric value.

Returns 9 as a numeric value.

Returns 2013 as a numeric value.

We could have also given the direct serial number for September 10, 2013:

Returns 10 as a numeric value.

Retrieving Times in Excel

HOUR(), MINUTE(), and SECOND()

The serial_number in each can be any time/date-formatted cell reference. For example, if A1 stores 6:15:30pm ( 18:15 hours, 30 seconds) on September 10, 2013, we can use each of the formulas in turn:

Returns 18 as a numeric value.

Returns 15 as a numeric value.

Returns 30 as a numeric value.

=SECOND(41527.7607638889)

Returns 30 as a numeric value.

Additional Date Retrieving Functions

WEEKDAY() and WEEKNUM()

The serial_number in each can be any date-formatted cell reference. Since [return_type] is optional, each function assumes that each week starts on Sunday. If cell A1 stores September 10, 2013 (a Tuesday), we can use each of the formulas in turn:

The [return_type] allows you to specify a different default week arrangement. You could let the week start on Monday and run until Sunday, or Saturday until Friday, for example. Excel is annoying, however, and makes the entry different for WEEKDAY and WEEKNUM. The full list of options for WEEKDAY is as follows:

Options 2 and 11 are functionally the same - the first is just there for backwards compatibility with earlier versions of Excel.

The full list of options for WEEKNUM is as follows:

Counting and Tracking Dates

Dates can be added and subtracted like normal numbers because they're stored as serial numbers. That lets you count the days between two different dates. Sometimes, though, you need to count by a different metric.

NETWORKDAYS()

Above, we learned about WORKDAY, which lets you move back and forth a set number of workdays, ignoring weekends and holidays. But what if you need to measure the number of workdays between two dates? For that, Excel provides NETWORKDAYS. The formula syntax is as follows:

The start_date and end_date can be any date-formatted cell reference.

The [holidays] input is optional, but lets you disqualify specific days (like Thanksgiving or Christmas, for example), which might otherwise fall during the work week. These are date serial numbers provided in an array bounded by brackets: { }. To specify multiple holidays, the dates must be held in cells - it is not possible to put multiple DATE functions in an array.

For example, if A1 contains 6:00pm ( 18:00 hours) on September 10, 2013 and B1 contains 9:00am ( 9:00 hours) on December 2, 2013, we can use NETWORKDAYS to find the number of workdays between the two dates.

=DATE(2013,10,14)

=DATE(2013,11,11)

=DATE(2013,11,28)

Now, we can combine them in the function:

The function returns 57 as a numeric value.

NETWORKDAYS.INTL() (Excel 2010 and newer)

For newer versions of Excel (2010 and later), there is another version of NETWORKDAYS called NETWORKDAYS.INTL. NETWORKDAYS.INTL works just like NETWORKDAYS, but it adds the ability to customize the definition of the "weekend". The syntax for NETWORKDAYS.INTL is as follows:

The start_date, end_date, and [holidays] inputs work just like the normal WORKDAY function.

The [weekend] input has the following options:

Sometimes it's useful to measure how much time has passed in years, but subtracting the YEAR function will only round down to the nearest full year. YEARFRAC takes two dates and provides the portion of the year between them. The syntax is as follows:

The start_date and end_date can be any date-formatted cell reference.

The [basis] input is optional, but lets you specify the "rules" for measuring the difference. Most of the time, you'll want to use option 1, but here is the full list:

For example, if A1 contains September 10, 2013 and B1 contains December 2, 2013, we can use YEARFRAC to find the decimal portion of a year between the two dates:

Returns 0.227397260273973.

DATEDIF() (Undocumented Function)

The start_date and end_date can be any date-formatted cell reference.

The unit input asks you to specify a string that represents the type of output you want. This is slightly cumbersome, since you must wrap the input in quotes (" ").

For example, if A1 contains September 10, 2012 and B1 contains December 2, 2013, we can use DATEDIF to find the number of full years between the two dates:

Returns 1 as a numeric value.

Using the same start_date and end_date inputs, here are the output possibilities for DATEDIF using different unit parameters:

Every time, the unit must be put in quotes (e.g. "Y" or "MD").

Converting Dates and Times from Text

DATEVALUE() and TIMEVALUE()

The date_text and time_text accept any text string that looks like a date or time.

This is how DATEVALUE responds to various date_text inputs:

This is how TIMEVALUE responds to various time_text inputs:

Converting Dates and Times to Text

The value can be any date or time-formatted cell reference.

The format_text input has a large number of options, summarized here:

A Common Problem

One issue people frequently run into is that Excel occasionally misinterprets text fields as dates. An example is here:

Be careful when entering dates, especially if you are importing from other data sources, to make sure that your "Jan-13" is being stored as January 1, 2013 and not January 13, 2013!

Andrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it.

Join the newsletter to stay on top of the latest articles. Sign up and you'll get a free guide with 10 time-saving keyboard shortcuts!

# data[].attributes.attachment_link.title
The Definitive Guide to Using Dates and Times in Excel - Excel Tactics

# data[].attributes.attachment_link.description
Dates and times are two of the most common data types in Excel, but they can be incredibly frustrating to work with. Learn to master Excel dates and times!

# data[].attributes.headline
Impossible PivotTables 3 – Multiple Data Tables

# data[].attributes.subject
Impossible PivotTables 3 – Multiple Data Tables

# data[].attributes.attachment_link.readable_text
This is the 3rd post in the Impossible PivotTables series. This series is designed to explore Power Pivot. I thought looking at a few limitations of traditional PivotTables would be a fun way to do this. So, in this post, we will look at how traditional PivotTables support a single data table while Power Pivot supports multiple data tables.

When we have multiple data tables and want to build a traditional PT, we end up with some sort of workaround. For example, we may merge the tables together to create one big data table, either by appending them (stacking them one after the other) or using formulas to retrieve related values, such as with VLOOKUP or other functions. The issue with this workaround is that we have to perform the same preparation steps each period. That is, the update isn't a quick click of the Refresh command, it is a series of unfortunate tasks. Well, as you may suspect, this is where Power Pivot can help because it supports building PivotTables from multiple data tables. Let's dig in.

Objective

Let's suppose that our company creates an annual budget. As we go through the year, we also create a monthly forecast which reflects changes and updated business information. Plus, we record actual data which is available each month after our monthly close.

The budget data is stored in our budgeting application, the forecast data is stored in a custom application, and our actual data is stored in our accounting system. We need to build a report that compares the budget to the historical actuals plus projected forecast. That is, we need to use actuals when they are available, and forecast data when actuals are not yet available. For example, if we are in the month of December, we need to compare the annual budget to the Jan-Nov actuals PLUS the Dec forecast. We need to build one report that incorporates values from three different places.

So, we get to work and export the budget data and save it in an Excel table named Bud, as shown below.

We export the monthly forecast data, and save it an another Excel table, named FC, as shown below.

We export the monthly actuals, and save it in another Excel table, named Act, as shown below.

Oh yeah, we also have a chart of accounts, so we export that and save it in a table named Accounts, as shown below.

Now, we have an Excel workbook with 4 tables and we want to summarize them with a PivotTable.

Since a traditional PT supports but a single data table, we prepare to combine four tables into one. We begin by creating helper columns in the data tables that use VLOOKUP to retrieve the account name from the Accounts table. Then, we copy/paste append the three data tables that store budget, forecast, and actuals so they are all stacked up. This proves to be rather time-consuming because the columns don't exactly line up. Plus, we lose track of which rows represent budget, actual, and forecast, so, we add another helper column that identifies the source of each row, as budget, forecast, or actual.

Now that we have combined these tables into one great big table, we begin to summarize it with a traditional PT. But, we soon get stuck. It is December, so, we have actual data through November. Our report needs to include Jan-Nov actuals, and Dec FC. We try to use a calculated field to do this. But, we quickly realize that calculated fields are good for basic math, but, aren't really designed for this type of thing. As you may have guessed, Power Pivot is. Plus, if we use Power Pivot, we won't need to go through the steps of setting up helper columns, using VLOOKUP to get the account names, or aligning and stacking the tables to create one big table. Power Pivot supports multiple data tables. Let's see how we can build this report with Power Pivot.

Overview

We'll create our PT using these steps:

Load the tables into the data model

Get the basic PT working

Write the measures

Let's get started.
Note: The steps below are presented with Excel for Windows 2016. If you are using a different version of Excel, please note that the features presented may not be available or you may need to download and install the Power Pivot Add-in.
Load the tables into the data model

First, we need to load the four tables into the data model. To do so, we select any cell in the desired table and use the Power Pivot > Add to Data Model command. We do this once for each table.

With the tables loaded, we need to create the relationships on the AcctID column. To do so, we use Power Pivot's Home > Diagram View command, and then click-and-drag the AcctID column from each of the data tables into the Accounts lookup table. The resulting diagram view is shown below.

With our tables loaded into the data model and related, it is time to get our basic PT working.

Get the basic PT working

We use Excel's Insert > PivotTable command and opt to Use this workbook's Data Model in the resulting dialog, as shown below.

We then insert the Accounts[AcctName] field into the rows layout area and the Bud[Amt] field into the values area. Our basic PT is shown below.

Now, it is time to write the measures.

Write the measures

First, we'll create a measure that computes the sum of Actual data for Jan-Nov and FC data for Dec. We'll call this measure ActFC. We use the Power Pivot > Measures > New Measure command and write the corresponding formula into the dialog, as shown below.

The formula essentially adds the actual data for all months in the table to the December FC data. Here is how the formula breaks down. The first part, SUM(Act[Amt]) computes the total of the actuals. The second part uses the CALCULATE function to add the forecast data for Dec only. The first argument of CALCULATE, SUM(FC[Amt]), tells the function to add up the forecast table's amount column. The second argument, FC[Mo]="Dec" tells it to only include those rows where the month is equal to December. This is similar to Excel's SUMIFS function here, where we add up a column of numbers, but only include certain rows.

Up next is the variance, which is just the difference between the budget and the ActFC value. So, we write the following measure.

We add the measures to the report and enter our desired report labels. The updated report is shown below.

Now, the only little cosmetic detail we need to address is the sort order. Currently, the report is sorted in AcctName order. But, we'd like it presented in financial statement order, that is, account number order. To do this with a traditional PT, we would need to include the account number field, create a custom list, or manually click-and-drag the account rows. Let's see how we do this with Power Pivot.

In Power Pivot, we select the Accounts table, and then select the AcctName column. We click the Home > Sort by Column command. In the resulting dialog, we tell Excel to sort the AcctName column by the values in the AcctID column, as shown below.

We head back to view our PT, and yes ... it worked!

If you have any other fun Power Pivot tips, please share by posting a comment below.

Also, feel free to check out the sample file below.
Sample file
Get a quick email notice when a new Excel article is available

# data[].attributes.attachment_link.title
Impossible PivotTables 3 - Multiple Data Tables

# data[].attributes.attachment_link.description
This is the 3rd post in the Impossible PivotTables series. This series is designed to explore Power Pivot. I thought looking at a few limitations of traditional PivotTables would be a fun way to do this. So, in this post, we will look at how traditional PivotTables support a single data table while Power Pivot supports multiple data tables.

# data[].attributes.headline
Excel limits (i.e. sheet tab characters)

# data[].attributes.subject
Excel limits (i.e. sheet tab characters)

# data[].attributes.attachment_link.readable_text
Workbooks with the "Allow changes by more than one user..." setting enabled

If the Allow changes by more than one user... setting is on for a workbook, then the following information applies. This setting is accessible by clicking the Review tab > Share Workbook. Note that in newer versions of Excel, the Share Workbook button has been hidden. To unhide it, click File > Options > Quick Access Toolbar. Open the list under Choose commands from and select All Commands. Scroll down that list until you see Share Workbook (Legacy). Select that item and click Add. Click OK. The Share Workbook button is now at the top of the Excel window and looks like this:

# data[].attributes.attachment_link.description
If the Allow changes by more than one user... setting is on for a workbook, then the following information applies. This setting is accessible by clicking the Review tab > Share Workbook. Note that in newer versions of Excel, the Share Workbook button has been hidden.

# data[].attributes.attachment_link.readable_text
Hi All.

We use a simple Excel file to store job numbers, orders, etc. The sheet is locked but has some simple VBA in it to allow users to modify the content. Add row, delete row, edit row, sort rows, etc.

The sort code has recently stopped working for any cells below row 5260. The Sort code sorts a range 15 columns wide by X rows. The number of rows grows as users add new jobs.

So the range is currently 15 columns x 5269 rows. It still sorts the range but not those rows below ~5260.

Any information on this would be greatly appreciated.

Cheers,

Mark.

BTW We all run Office 2016 through Office 365 but the sheet is 10+ years old as is the VBA code within.

It sounds to me that the VBA sort range needs to be edited.
That being the case I'd suggest a small rewrite of that specific code:
eg

Dim LastRow as Long LastRow=cells(rows.count,"A").end(xlup).row

and then change any value in that code from 5260 to LastRow.

It won't matter thereafter how many rows you have to sort, if my understanding is correct.

As an afterthought some of your code may contain something like Range("B2:X5260").
Those instances would need to be rewritten as Range("B2:X" & LastRow), or within the range to which the sort applies.

Thanks Brian.

I have the range set such that when a user adds a row (via a button) it is always within the existing range, so the range just gets bigger.

The range is currently row 12 to 5295 so 5260 is within the range. Its this that makes me think I've hit some limit in Excel or VBA.

Sub Sort_DataRange()
ActiveSheet.Unprotect "syswsr"
Range("All_Data").Sort Key1:=Range("All_Data").Cells(2, 15), Order1:=xlAscending
ActiveSheet.Protect "syswsr"
End Sub

Cheers.

That doesn't make any sense.

The last row of your sheet is miles below your range.
What does Cells(2,15) hold on your sheet? Is that the value of "5295"?
Even that doesn't make sense.

What is the macro block that does the sort?
I suggest therein is what you need to alter.
I assume that you know how that may appear:

With ActiveWorkbook.Worksheets("Sheet1").Sort .SetRange Range("A1:B5") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With

.
It will be within that macro that you will have to alter the "5295" value.

If that is not the case, we will just have to persevere.

I have the range set such that when a user adds a row (via a button) it is always within the existing range, so the range just gets bigger.

I think that this is the code to address.
That said, how many macros run on this sheet?

My apologies for taking so long to reply. Please don't take my slackness for any lack of appreciation!

Turned out the issue was in column 15 which is what the sheet sorts by. Because we use some weird job numbers that can start with a any numeral or character, column 15 uses the left function to return a consistent value to to sort by. All well and good but someone (probably my boss) had been unprotecting the sheet and manually inserting rows instead of using the Insert button (and associated code) at the top of the sheet. In doing this he would occasionally (somehow) not copy the cell with the Left formulae.

Another example of the simplest solution being the correct one.

Again, thanks for your help.

Hi everyone, I'm new to VBA and looking for some guidance. I'd like to filter Column A on three different tabs based on the first value of a list...

I have a form that enters data in Col A, B, C. A macro runs next that groups the data into Col D Here is the macro ( runs fine manually) [code]...

Hi good afternoon, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and...

Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...

Good Morning, I stumbled across this forum through some research and I was hoping you could help me with some VBA code. I have a data set...

Hi everyone, I'm trying to gather information from multiple worksheets into a single preformatted table. I've started with a dialog folder picker...

# data[].attributes.attachment_link.description
Hi All. We use a simple Excel file to store job numbers, orders, etc. The sheet is locked but has some simple VBA in it to allow users to modify the content. Add row, delete row, edit row, sort rows, etc. The sort code has recently stopped working for any cells below row 5260.

# data[].attributes.attachment_link.readable_text
A database is a collection of tables of data that allow logical relationships to each other. You use relationships to connect tables by fields that they have in common. A table can be part of any number of relationships, but each relationship always has exactly two tables. In a query, a relationship is represented by a Join.

What is Join

A join specifies how to combine records from two or more tables in a database. Conceptually, a join is very similar to a table relationship. In fact, joins are to queries what relationships are to tables.

The following are the two basic types of joins that we will be discussing in this chapter −

Both of which can easily be created from a queries design view.

Inner Join

Let us now understand Inner Join −

The most common type of join is an inner join which is also the default join type in Microsoft Access.

Inner Join will display only the rows or records where the joined fields from both tables are equal.

This join type looks at those common fields and the data contained within. It only displays the matches.

Outer Join

Let us now understand what an Outer Join is −

An outer join displays all rows from one table and only those rows or records from the other table where the joined fields are equal.

In other words, an outer join shows all rows from one table and only the corresponding rows from the other table.

There are other Join types too −

Left Outer Join & Right Outer Join

Let us now understand Left Outer Join and Right Outer Join −

You can choose the table that will display all rows.

You can create a Left Outer Join which will include all the rows from the first table.

You can create a Right Outer Join that will include all the rows from the second table.

Let us now go to the Create tab and create a query from the Design View. Select tblProjects and tblTasks and close the Show Table dialog box as in the following screenshot.

Add the ProjectName field from tblProjects, and TaskTitle, StartDate and DueDate from tblTasks table.

Let us now run the query.

We are only displaying records from a few projects. A couple of these projects have a lot of tasks associated with that project and this information is related through ProjectID field.

When we create this query in Microsoft Access, Access is taking this relationship from the relationships we have created.

By default, it is creating what's known as an Inner Join between these two fields, between these two tables, and that is how it is relating this information together from these two tables.

It is showing us only the matches, so when we run this query, there are a lot of other projects listed in tblProjects that do not appear as part of our records set in this query, and that is because of how these two tables are joined together, through that Inner Join, which is again, that default Join for any query.

However, if you want to alter the relationship, Let us say you want to create an outer join, or in other words, show all of the projects from tblProjects, every single record that is in that table, along with all of the tasks from tblTasks - Open join properties; we can do this simply by double-clicking on the relationship line.

Access will display the left table name and the right table name in Join Properties dialog.

The left column name and the right column name and the first radio button is to only include rows where the join fields from both tables are equal and that is the inner join and that is what is selected by default when creating relationships, when creating a join in the query, but, you can change it.

We have two other options as well; we can include all records from tblProjects, and only those records from tblTasks where the joined fields are equal and this one is Left Outer Join.

We have a third option, include all records from tblTasks and only those records from tblProjects where the joined fields are equal and this one is Right Outer Join.

Those are the different types of joins you can create easily from the Design View. Let us select the second option, which is Left Outer Join, and click Ok.

Let us now look into the other steps −

When you look at the relationship line, you will see a little arrow pointing towards ProjectID in tblTasks. When you run this query, you will see the following results.

As you can see that it is showing us every single project name, whether or not it has a related task. You will also see a bunch of null fields. All of this will be blank because there is no related information in tblTasks, where these fields come from. Let us now go to the Design View again and double-click on the relationship line.

In the Join Properties dialog box, select the third option which is for the Right Outer Join and click Ok.

Now look at our relationship line. You will see that a little arrow is now pointing towards ProjectID in tblProjects. When you run this query you will see the following results.

Self-Joins

Self-join is another type of Join. A Self-join relates matching fields from within the same table. For example, look at the employee's table with a supervisor field, which references the same kind of number stored in another field within the same table - the employee ID.

If we wanted to know who Kaitlin Rasmussen's supervisor is, we will have to take the number stored in that supervisor field and look it up within the exact same table in that employee ID field in order to know that Charity Hendricks is the supervisor.

This table is not the ideal structure for a relational database because it's not normalized.

If we have a situation where we want to create a query that just lists the employee names alongside the names of their supervisors, there is no easy way we can query that unless we create a Self-join.

To see a Self-join, create a table with the following fields and enter some data.

Here we want to create a list again with the first name of the employee and then the name of the supervisor. Let us create a query from the query design view.

Now, add tblEmployees tables.

Close this dialog box.

Now, add the first name and last name for our employees.

We now need a way to create a relationship between this table and itself. To do that, we need to open the show table dialog box and add tblEmployees one more time.

We have created another copy of the same table in this query view. Now, we need to create Self-join. To do that, click on Supervisor in tblEmployees table and hold the mouse button and drop it right on top of the EmployeeID in that copied table - tblEmployees_1. Then, add the first name and last name from that copied table.

Let us now run your query and you will see the following results.

It displays the names of the employees along side the names of their supervisors. And, this is how you create a Self-join in Microsoft Access.

# data[].attributes.attachment_link.description
A database is a collection of tables of data that allow logical relationships to each other. You use relationships to connect tables by fields that they have in common. A table can be part of any number of relationships, but each relationship always has exactly two tables.

# data[].attributes.headline
Potential Sources - Data Validation Overwrites via Copy/Paste

# data[].attributes.subject
Potential Sources - Data Validation Overwrites via Copy/Paste

# data[].attributes.body
- Excel Data validation - Copy and Paster can over pass data validation

- Excel cell data validations fails on copy-paste

- Ensuring That Data Validation Is Not Deleted

- Disable Cut, Copy, Paste of Data in Workbook

# data[].attributes.body
Calculate Time Difference in Excel

Use Excel to calculate the hours worked for any shift

How to add & subtract time in Excel to show over 24 hours, 60 minutes, 60 seconds

# data[].attributes.attachment_link.readable_text
The secret is using data validation. Click a cell (or a group of selected cells), and then turn on the validation feature by selecting Data → Validation and clicking the Settings tab. From the Allow drop-down menu, pick the type of validation criteria you want to use, and then specify its parameters from the drop-down menus that appear below. The type of validation you select in the Allow drop down determines what other options appear below. If you select Whole number, for example, you can specify whether the entered values should be between two other values, not between two other values, or less than or greater than a value. You can hardcode the value by picking "equal to" from the Data drop-down menu and typing the value into the Value field. Figure 1-19 shows the types of criteria you can create.

For instance, if he's supposed to be typing in Zip Codes, and you want to make sure he doesn't leave out digits or add extra ones, set the Text length validation rule to require a number between 5 and 5. If he's supposed to enter 94607 and he types 9460 by mistake, he'll receive an error message reading "The value you entered is not valid."

If you want to make sure he doesn't forget to enter data in a critical cell, uncheck the "Ignore blank" box in the Data Validation dialog. That forces him to enter something in the cell. (If you want, you can determine exactly what he needs to enter by selecting the appropriate entry in the Allow drop down.) If the "Ignore blank" box is grayed out, temporarily select anything except "Any value" in the Allow drop down; this makes the "Ignore blank" box accessible. After you uncheck it, you can return to "Any value" if you want, and the "Ignore blank" box, though grayed out, will remain unchecked so that he will have to enter something in the cell.

If you want to keep users from typing data into a cell, you can restrict them to selecting entries from a list. The trick: select List from the Allow drop-down menu. When you do, a field titled Source appears. You can define the list's values by either selecting a range of cells from the worksheet, or typing values into the Source field, with each one separated by a comma. Entries in the Source field can include spaces, semicolons, and almost any other character. The only character you can't use is the one that separates entries, i.e., the comma. For example, your list of categories might look like this: Service Plans, Accessories, Sales, On-Site.

# data[].attributes.attachment_link.description
The secret is using data validation. Click a cell (or a group of selected cells), and then turn on the validation feature by selecting Data → Validation and clicking the Settings tab. From the Allow drop-down menu, pick the type of validation criteria you want to use, and then specify its parameters from the drop-down menus that appear below.

# data[].attributes.headline
Free Microsoft Office 365 for Students [After Registration: portal.office.com]

# data[].attributes.subject
Free Microsoft Office 365 for Students [After Registration: portal.office.com]

# data[].attributes.body
https://www.microsoft.com/en-us/education/products/office

# data[].attributes.attachment_link.readable_text
Get started with Office 365 for free

Students and educators at eligible institutions can sign up for Office 365 Education for free, including Word, Excel, PowerPoint, OneNote, and now Microsoft Teams, plus additional classroom tools. Use your valid school email address to get started today.

Powerful tools for students and educators

Office 365 works like you: everywhere. Familiar applications and always-accessible files update in real time to make building your curriculum seamless and easy on Android, iOS or Windows devices.

One place for organization

The ultimate digital notebook, OneNote helps you organize class materials and easily collaborate with students and colleagues.

Access anytime, anywhere, from any device

Bring conversations, content, and apps together all from a single Office 365 Education experience. Collaborate in real time without boundaries, and without worrying about lost formatting.

Simplify class management

Create classes and groups for Teams, Intune for Education, and more for free with School Data Sync.

Tools to save time and money

An affordable suite of powerful tools built for educators and students, Office 365 is accessible from your favorite devices.

Equip your students for success today and tomorrow

When you use Office 365 Education in the classroom, your students learn a suite of skills and applications that employers value most. Whether it's Outlook, Word, PowerPoint, Access or OneNote, prepare students for their futures today.

Cloud services to create a modern classroom

Create collaborative classrooms, connect in professional learning communities, and communicate with school staff with the power of OneDrive, Teams, and more -- all from a single experience in Office 365 Education.

Get Office 365 Education for your entire school

Give every educator and student the power of Office 365 Education on all their devices, including Word, Excel, PowerPoint, OneNote, Microsoft Teams, and free classroom tools.

Office 365 A1

This free online version of Office includes everything you expect from Office, plus Microsoft Teams.

Office 365 A3

Access to the Office desktop apps including Microsoft Teams, plus robust management and security tools.

Office 365 A5

All the Office desktop apps including Microsoft Teams, with best-in-class security, compliance, and analytics.

Office + Microsoft 365 Education

The best way to buy Office is with Microsoft 365 Education.

Frequently asked questions

How long can I use this plan?

You can use the plan as long as you are working at a qualified school. Your eligibility may be re-verified at any time. When your Office 365 Education plan expires:

The Office applications enter a reduced-functionality mode, which means that you can view documents, but you cannot edit or create new documents.

Online services associated with the school email address-for example, Office Online and OneDrive-will no longer work.

If your plan expires, you can extend your plan by re-verifying your status as an educator, or by moving to an Office 365 personal plan.

Who manages the OneDrive account provided with this offer?

The OneDrive account is assigned to you but associated with the school and should be used for school-appropriate content. Permissions and access can be changed by the school's IT administrator at any time.

How do I migrate my files and settings to Office 365 Personal?

What are the eligibility requirements?

Have a school-specific email address provided by the school (for example, contoso.edu) that can receive external email.

Be of legal age to sign up for an online offer individually.

Have Internet access.

# data[].attributes.attachment_link.title
Free Microsoft Office 365 for Schools & Students | Microsoft Education

# data[].attributes.attachment_link.description
Access to Office 365 Education is free for schools and students with a valid school email address. Access these powerful tools to enable 21st century learning and discovery.

# data[].attributes.headline
How to Print a List of Comments in Microsoft Word 2016

# data[].attributes.subject
How to Print a List of Comments in Microsoft Word 2016

# data[].attributes.attachment_link.title
How to Print a List of Comments in Microsoft Word 2016

# data[].attributes.attachment_link.description
If you're anything like me (read: an obsessed writer), you spend hours crafting the perfect words. The minute you're done, you contact everybody you know and ask for feedback. If you use Word, Microsoft makes it easy for people to share their thoughts on certain passages through the Comments feature.

# data[].attributes.headline
Things to Consider When Designing an MS Access Database - dummies

# data[].attributes.subject
Things to Consider When Designing an MS Access Database - dummies

# data[].attributes.attachment_link.readable_text
Being an Access database designer isn't nearly as glamorous as being a fashion designer, but it has its rewards. If you design your Access database carefully and correctly, it can be very useful to you and others. You can enter information accurately. When the time comes to draw information from the database, you get precisely the information you need. The information below explains everything you need to consider when designing an Access database. The hardest part about designing an Access database is deciding how to distribute information across database tables and how many database tables to have.

Deciding what information you need in your MS Access database
The first question to ask yourself is about the kind of information you want to get out of the database. Customer names and addresses? Sales information? Information for inventory tracking? Interview your coworkers to find out what information could be helpful to them. Give this matter some serious thought. Your goal is to set up the database so that every tidbit of information your organization needs can be recorded.
A good way to find out what kind of information matters to an organization is to examine the forms that the organization uses to solicit or record information. These forms show precisely what the organization deems worthy of tracking in a database.
Check out the information below.

Paper forms also have fields.

This image shows the paper form that players fill out to sign up for a baseball league whose database tables appear below. Compare the images and you can see that the Players, Teams, and Divisions database tables all have fields for entering information from this form.

Plans for database tables and field names.

Separating information into different Access database tables
After you know the information you want to record in the Access database, think about how to separate the information into database tables. Many are tempted to put all the information into a single database table, but because Access is a relational database, you can query more than one table at a time, and in so doing, assemble information from different tables.
To see how Access databases work, consider the simple database. The purpose of this little database and its four tables is to store information about the players, coaches, and teams in a baseball league. The Team Name field appears in three tables. It serves as the link among the tables and permits more than one to be queried. By querying individual tables or combinations of tables in this database, you can assemble team rosters, make a list of coaches and their contact information, list teams by division, put together a mailing list of all players, find out which players have paid their fee, and list players by age group, among other things. This database comprises four tables:

Players: Includes fields for tracking players' names, addresses, birthdays, which teams they're on, and whether they paid their fees

Coaches: Includes fields for tracking coaches' names, addresses, and the names of the teams they coach

Teams: Includes fields for tracking team names and which division each team is in

Divisions: Includes fields for tracking division numbers and names

Deciding how many database tables you need and how to separate data across the different tables is the hardest part of designing a database. To make the task a little easier, do it the old-fashioned way with a pencil and eraser. Here are the basic rules for separating data into different tables:

Restrict a table to one subject only: Each database table should hold information about one subject only - customers, employees, products, and so on. This way, you can maintain data in one table independently from data in another table. Consider what would happen in the Little League database if coach and team data were kept in a single table, and one team's coach was replaced by someone new. You would have to delete the old coach's record, delete information about the team, enter information about the new coach, and reenter information about the team that you just deleted. But by keeping team information separate from coach information, you can update coach information and still maintain the team information.

Avoid duplicate information: Try not to keep duplicate information in the same database table or duplicate information across different tables. By keeping the information in one place, you have to enter it only once, and if you have to update it, you can do so in one database table, not several.

Entire books have been written about Access database design. You can, however, store all your data in a single table if the data you want to store isn't very complex. The time you lose entering all the data in a single table is made up by the time you save not having to design a complex database with more than one table.
Choosing fields for Access database tables
Fields are categories of information. Each database table needs at least one field. If the table itself is a subject, you could say that its fields are facts about the subject. An Address database table needs fields for recording street addresses, cities, states, and ZIP codes. A Products database table needs fields for product ID numbers, product names, and unit prices. Just the facts, ma'am. Within the confines of the subject, the database table needs one field for each piece of information that is useful to your organization.
When you're planning which fields to include in an Access database table, follow these guidelines:

Break up the information into small elements. For example, instead of a Name field, create a First Name field and a Last Name field. This way, you can sort database tables by last name more easily.

Give descriptive names to fields so that you know what they are later. A more descriptive name, such as Serial Number, is clearer than

Think ahead and include a field for each piece of information your organization needs. Adding a field to a database table late in the game is a chore. You have to return to each record, look up the information, and enter it.

Don't include information that can be derived from a calculation. Calculations can be performed as part of a query or be made part of a table. For example, you can total the numbers in two fields in the same record or perform mathematical calculations on values in fields.

Deciding on a primary key field for each Access database table
Each database table must have a primary key field. This field, also known as the primary key, is the field in the database table where unique, one-of-a-kind data is stored. Data entered in this field - an employee ID number, a part number, a bid number - must be different in each record.
If you try to enter the same data in the primary key field of two different records, a dialog box warns you not to do that. Primary key fields prevent you from entering duplicate records. They also make queries more efficient. In a query, you tell Access what to look for in database tables, Access searches through the tables, and the program assembles information that meets the criteria. Primary key fields help Access recognize records and not collect the same information more than once in a query.

Social security numbers make good primary key fields because no two people have the same social security number. Invoice numbers and serial numbers also make excellent primary key fields. Returning to the sample baseball league database, which fields in the Little League database tables are primary key fields? In the Teams table, Team Name can be the primary key field because no two teams have the same name. Division Number can also be a primary key field because divisions in the league are numbered and no two divisions have the same number.

The Players and Coaches database tables, however, present a problem when it comes to choosing a primary key field. Two players might have the same last name, which rules out Last Name as a primary key field. A brother and sister might have the same telephone number, which rules out a Telephone No. field. Because no field holds values that are certain to be different from record to record, fields called Player Number and Coach Number have been introduced. For the purpose of this database, players and coaches are assigned numbers.

Mapping the relationships between tables in Access
If your Access database includes more than one table, you have to map how the tables relate to one another. Usually, relationships are formed between the primary key field in one table and the corresponding field in another, called the foreign key. The image below shows the relationships between the tables in the Little League database. Because these tables are linked by common fields, you can gather information from more than one table in a query or report. For now, when you design your database, consider how to connect the various tables with common fields.

Table relationships.

About This Article

This article is from the book:

About the book author:

Peter Weverka is a veteran tech writer and long-time For Dummies author. Among his credits are Office 2016 All-in-One For Dummies and Windows 10 For Seniors For Dummies, both top sellers in their respective categories.

This article can be found in the category:

Being an Access database designer isn't nearly as glamorous as being a fashion designer, but it has its rewards. If you design your Access database carefully and correctly, it can be very useful to you and others. You can enter information accurately. When the time comes to draw information from the database, you get precisely the information you need. The information below explains everything you need to consider when designing an Access database. The hardest part about designing an Access database is deciding how to distribute information across database tables and how many database tables to have.
Deciding what information you need in your MS Access database
The first question to ask yourself is about the kind of information you want to get out of the database. Customer names and addresses? Sales information? Information for inventory tracking? Interview your coworkers to find out what information could be helpful to them. Give this matter some serious thought. Your goal is to set up the database so that every tidbit of information your organization needs can be recorded.
A good way to find out what kind of information matters to an organization is to examine the forms that the organization uses to solicit or record information. These forms show precisely what the organization deems worthy of tracking in a database.
Check out the information below.

Paper forms also have fields.

This image shows the paper form that players fill out to sign up for a baseball league whose database tables appear below. Compare the images and you can see that the Players, Teams, and Divisions database tables all have fields for entering information from this form.

Plans for database tables and field names.

Separating information into different Access database tables
After you know the information you want to record in the Access database, think about how to separate the information into database tables. Many are tempted to put all the information into a single database table, but because Access is a relational database, you can query more than one table at a time, and in so doing, assemble information from different tables.
To see how Access databases work, consider the simple database. The purpose of this little database and its four tables is to store information about the players, coaches, and teams in a baseball league. The Team Name field appears in three tables. It serves as the link among the tables and permits more than one to be queried. By querying individual tables or combinations of tables in this database, you can assemble team rosters, make a list of coaches and their contact information, list teams by division, put together a mailing list of all players, find out which players have paid their fee, and list players by age group, among other things. This database comprises four tables:

Players: Includes fields for tracking players' names, addresses, birthdays, which teams they're on, and whether they paid their fees

Coaches: Includes fields for tracking coaches' names, addresses, and the names of the teams they coach

Teams: Includes fields for tracking team names and which division each team is in

Divisions: Includes fields for tracking division numbers and names

Deciding how many database tables you need and how to separate data across the different tables is the hardest part of designing a database. To make the task a little easier, do it the old-fashioned way with a pencil and eraser. Here are the basic rules for separating data into different tables:

Restrict a table to one subject only: Each database table should hold information about one subject only - customers, employees, products, and so on. This way, you can maintain data in one table independently from data in another table. Consider what would happen in the Little League database if coach and team data were kept in a single table, and one team's coach was replaced by someone new. You would have to delete the old coach's record, delete information about the team, enter information about the new coach, and reenter information about the team that you just deleted. But by keeping team information separate from coach information, you can update coach information and still maintain the team information.

Avoid duplicate information: Try not to keep duplicate information in the same database table or duplicate information across different tables. By keeping the information in one place, you have to enter it only once, and if you have to update it, you can do so in one database table, not several.

Entire books have been written about Access database design. You can, however, store all your data in a single table if the data you want to store isn't very complex. The time you lose entering all the data in a single table is made up by the time you save not having to design a complex database with more than one table.
Choosing fields for Access database tables
Fields are categories of information. Each database table needs at least one field. If the table itself is a subject, you could say that its fields are facts about the subject. An Address database table needs fields for recording street addresses, cities, states, and ZIP codes. A Products database table needs fields for product ID numbers, product names, and unit prices. Just the facts, ma'am. Within the confines of the subject, the database table needs one field for each piece of information that is useful to your organization.
When you're planning which fields to include in an Access database table, follow these guidelines:

Break up the information into small elements. For example, instead of a Name field, create a First Name field and a Last Name field. This way, you can sort database tables by last name more easily.

Give descriptive names to fields so that you know what they are later. A more descriptive name, such as Serial Number, is clearer than

Think ahead and include a field for each piece of information your organization needs. Adding a field to a database table late in the game is a chore. You have to return to each record, look up the information, and enter it.

Don't include information that can be derived from a calculation. Calculations can be performed as part of a query or be made part of a table. For example, you can total the numbers in two fields in the same record or perform mathematical calculations on values in fields.

Deciding on a primary key field for each Access database table
Each database table must have a primary key field. This field, also known as the primary key, is the field in the database table where unique, one-of-a-kind data is stored. Data entered in this field - an employee ID number, a part number, a bid number - must be different in each record.
If you try to enter the same data in the primary key field of two different records, a dialog box warns you not to do that. Primary key fields prevent you from entering duplicate records. They also make queries more efficient. In a query, you tell Access what to look for in database tables, Access searches through the tables, and the program assembles information that meets the criteria. Primary key fields help Access recognize records and not collect the same information more than once in a query.

Social security numbers make good primary key fields because no two people have the same social security number. Invoice numbers and serial numbers also make excellent primary key fields. Returning to the sample baseball league database, which fields in the Little League database tables are primary key fields? In the Teams table, Team Name can be the primary key field because no two teams have the same name. Division Number can also be a primary key field because divisions in the league are numbered and no two divisions have the same number.

The Players and Coaches database tables, however, present a problem when it comes to choosing a primary key field. Two players might have the same last name, which rules out Last Name as a primary key field. A brother and sister might have the same telephone number, which rules out a Telephone No. field. Because no field holds values that are certain to be different from record to record, fields called Player Number and Coach Number have been introduced. For the purpose of this database, players and coaches are assigned numbers.

Mapping the relationships between tables in Access
If your Access database includes more than one table, you have to map how the tables relate to one another. Usually, relationships are formed between the primary key field in one table and the corresponding field in another, called the foreign key. The image below shows the relationships between the tables in the Little League database. Because these tables are linked by common fields, you can gather information from more than one table in a query or report. For now, when you design your database, consider how to connect the various tables with common fields.

Table relationships.

# data[].attributes.attachment_link.title
Things to Consider When Designing an MS Access Database - dummies

# data[].attributes.attachment_link.description
Being an Access database designer isn't nearly as glamorous as being a fashion designer, but it has its rewards. If you design your Access database carefully and correctly, it can be very useful to you and others. You can enter information accurately.

# data[].attributes.headline
Working with Project Tasks in Your Outlook Calendar

# data[].attributes.subject
Working with Project Tasks in Your Outlook Calendar

# data[].attributes.body
If you use Microsoft Outlook, you can keep track of your assigned tasks in your Outlook Calendar, along with your other appointments. You can update progress on your project assignments in the Calendar and report the status back to Project Server directly from Outlook.

Project tasks can be displayed as free or busy time, just like any other Calendar entries.

Note Outlook integration with Project Web Access allows integration with the Outlook Calendar, but not with Outlook Tasks.

# data[].attributes.attachment_link.title
Working with Project Tasks in Your Outlook Calendar - Microsoft Project Solutions

# data[].attributes.attachment_link.description
If you use Microsoft Outlook, you can keep track of your assigned tasks in your Outlook Calendar, along with your other appointments. You can update progress

# data[].attributes.attachment_link.readable_text
/en/access2016/creating-reports/content/

Introduction

Access offers several advanced options for creating and modifying reports. The Report Wizard is a tool that guides you through the process of creating complex reports. Once you've created a report-whether through the Report Wizard or the Report command-you can then format it to make it look exactly how you want.

In this lesson, you'll learn how to use the Report Wizard to create complex reports. You'll also learn how to use formatting options to format text, change report colors and fonts, and add a logo.

Throughout this tutorial, we will be using a sample database. If you would like to follow along, you'll need to download our Access 2016 sample database. You will need to have Access 2016 installed on your computer in order to open the example.

Watch the video below to learn more about the Report Wizard in Access.

The Report Wizard

While using the Report command is a quick way to create reports from the current object, it's not as helpful if you want to create a report with data from multiple objects. The Report Wizard makes it easy to create reports using fields from multiple tables and queries. It even lets you choose how your data will be organized.

To create a report with the Report Wizard:

Step 1: Select the fields to include in your report

Step 2: Organize the report

The Report Wizard will provide you with options that let you choose how to view and organize your data. These options group similar data within your fields and organize these fields into multiple levels, like in an outline or bulleted list.

If you are building a report from only one table or query, you can skip to Step 3 below.

1. Access will offer a list of several organization options. Select an option from the list to preview it.

2. Click Next when you are satisfied with the basic organization of your data.

3. If necessary, modify the order of your grouped fields by selecting a field and clicking the up or down Priority arrow to move it up or down a level.

4. Once you are satisfied with the organization of your report, click Next.

Step 3: Sort your report data

1. Click the top drop-down arrow, and select the name of the first field you want to sort.

2. Click the button on the right to change the sort to ascending or descending.

3. Add any additional sorts. You can sort up to four fields. The sort will be applied from top to bottom, meaning the sort at the top of the list will be the main sort.

4. When you are satisfied with the way your data is sorted, click Next.

Depending on the grouping you have chosen for your data, your sorting options may be limited.

Step 4: Select a layout and title

1. Select either a portrait (tall) or landscape (wide) orientation for your report.

2. Once you are satisfied with your report layout, click Next.

3. Select the text box, and type the title you want for your report.

4. Your report will be created and saved.

You may have to adjust your field and row size and location to make sure your data looks the way you want it to. To do this, you'll need to switch to Design view. When you're done, switch back to Report view to see your changes.

Formatting reports

One of the strengths of reports is that you can modify their appearance to make them look how you want. You can add headers and footers, apply new colors, and even add a logo. All of these things can help you create visually appealing reports.

Watch the video below to learn more about formatting reports in Access.

Modifying report text

The bulk of the information in your report comes straight from the query or table you built it from, which means you can't edit it within the report. However, you can change, add, or delete label text, headers, and footers to make your report clearer and easier to read. For example, in our report we decided we didn't need the field headings to understand our data, so we simply deleted them.

Just like other Office programs, Access allows you to modify the text color and font, add shapes, and more. If you're not sure how to perform basic text and shape formatting, visit the Formatting Text and Shapes lessons from our Word 2016 tutorial.

Modifying the page header and footer

To view and modify the header and footer that appear on each page of your report, select the View command on the Ribbon and switch to Design view. The header and footer are located in the white space beneath the Page Header and Page Footer bars.

Depending on your report's design, sometimes you may find that there is no white space in the page header and footer, as in the image above. If this is the case, you must resize the header and footer before you can add anything to them. Simply click and drag the bottom border of the header or footer to make it larger.

To add text to a header or footer:

1. Click and drag the mouse inside the white area to create your label. Release the mouse when it is the desired size.

2. Click the text box, and type the desired text.

To add the date and time to a header or footer:

A dialog box will appear. Select the desired formatting options. A preview of the text that will be included in your report will appear.

By default, the date and time appear in the header. If you would like to move them to the footer instead, simply click the date and time boxes and drag them to the desired location.

To add page numbers to a header or footer:

1. Select the Design tab, then locate the Header/Footer group.

2. The Page Numbers dialog box will appear. Under Format, choose Page N to display the number of only the current page, or Page N of M to display the number of the current page and the number of total pages.

3. Under Position, choose Top of Page or Bottom of Page to control where the page numbers appear.

4. Click the drop-down arrow to select the alignment of the page numbers.

Modifying your report's appearance

To add a logo:

1. Locate the Header/Footer group, then click the Logo command.

2. A small version of the image will appear in the header. Click and drag the image border to resize it.

3. If necessary, move your logo to the desired location by clicking and dragging it.

Themes and fonts

A theme is a set of colors and fonts that applies to the entire database to give it a consistent, professional look. By default, databases use the Office theme. When you change the theme, all of the theme fonts and colors in your database change to match the new theme. Designing and modifying reports using theme elements can help you keep the appearance of your reports consistent.

To change the theme:

1. A drop-down menu will appear. Select the desired theme.

2. The theme will be applied to your entire database.

To change the theme fonts:

The fonts will be applied to your entire database.

Challenge!

1. Open our practice database.

2. Open the Cookies Sold report.

3. Add page numbers to the footer.

4. Choose a new theme and theme fonts.

5. Add a logo. You can use an image from your computer or download the Songbird Bakery logo.

/en/access2016/modifying-tables/content/

# data[].attributes.attachment_link.description
en/access2016/creating-reports/content/ Access offers several advanced options for creating and modifying reports. The Report Wizard is a tool that guides you through the process of creating complex reports. Once you've created a report-whether through the Report Wizard or the Report command-you can then format it to make it look exactly how you want.

# data[].attributes.headline
Meet the new Microsoft Planner: Manage all your tasks and plans in one simple, familiar experience

# data[].attributes.subject
Meet the new Microsoft Planner: Manage all your tasks and plans in one simple, familiar experience

# data[].attributes.body
We’re bringing together the simplicity of Microsoft To Do, the collaboration of Microsoft Planner, the power of Microsoft Project for the web and the intelligence of Microsoft Copilot into a simple, familiar experience. Discover a new way to manage tasks for individual plans and team initiatives, as well as larger scale project management, aligned to goals and key strategic objectives.

# data[].attributes.attachment_link.readable_text
We're heading into a new era, where AI is an integral part of the workday. The tools you're using for work management should be intelligent and help keep you focused and productive. However, even the most innovative solutions for getting work done can slow you down when they don't work together.

In fact, the average employee spends the bulk of their time-more than 57%-communicating, when they could be focused on driving outcomes. And 59% of employees say their collaboration tools are not aligned with how their teams prefer to work. Employees are also eager for AI to help lift the burden of mundane daily tasks, with 70% saying they would delegate as much work as possible to AI.

Customers have been asking for the ability to drive more momentum and efficiency in their day. They want a single, intelligent app to track all their work and projects. That's why Microsoft is leveraging our experience in collaborative work management (CWM), enterprise work management (EWM) and frontline task management to deliver a new modern work management solution assisted by next-generation AI.

Meet the new Microsoft Planner: Manage all your tasks and plans in one simple, familiar experience

We're bringing together the simplicity of Microsoft To Do, the collaboration of Microsoft Planner, the power of Microsoft Project for the web and the intelligence of Microsoft Copilot into a simple, familiar experience. Discover a new way to manage tasks for individual plans and team initiatives, as well as larger scale project management, aligned to goals and key strategic objectives.

The new Planner will easily scale from simple task management to enterprise project management, empowering everyone to manage their work in one place and accelerate business outcomes. Starting in Spring 2024, the new Planner app will be available in Microsoft Teams, followed by a web experience later in 2024. Sign up to be the first to know when the new Planner will be available as a preview, and generally available.

See what you can do with the new Planner

The new Planner is simple

Find the tasks you need, right when you need them. Manage all your tasks, to-do lists, plans, and projects across Microsoft 365 applications in one simple, familiar experience.

The new Planner is flexible

Work the way you and your teams want, from individual task management to large team initiatives. Choose from an array of templates, views, and approaches including lists, boards, timelines, and sprints to personalize your plan. Features such as agile sprint planning, team workload, goals alignment, custom fields, and task history that you use and love today in Project for the web will be available in the new Planner experience.

The new Planner is scalable

Get big picture views across initiatives. You can configure, extend, and automate the new Planner (with or without code) to meet your organization's changing needs. Built on Microsoft Power Platform, the new Planner will enable customized reporting through Microsoft Power BI and configurable workflows through Microsoft Power Automate. It also integrates with Microsoft Viva Goals, connecting to your objectives and key results (OKR) to inform plans and provide high-level visibility.

The new Planner is intelligent

Accelerate outcomes with AI. Through simple, natural-language prompts, Copilot in Planner (coming in Spring 2024) will help you generate new plans, set goals, track status, and react to changes as projects evolve. Copilot will provide support for you and your team, making work and project management faster and easier than ever.

Integrations with Microsoft 365 experiences

The new Planner enables you to stay in the flow of work with integrations across other Microsoft 365 experiences such as Microsoft Loop, Microsoft Outlook, Viva Goals, and Teams. You can bring existing Planner plans into a Loop workspace and track your team plan alongside other project content in one place. Emails you flag in Outlook, or action items assigned to you from Teams meetings are also visible in Planner.

Task management in Planner for frontline teams

The new Planner will continue to include task management features for frontline organizations. With features such as task publishing, leaders can define the work to be done across their frontline locations and monitor completion. These features have been part of what was previously the Tasks app in Teams, and we're carrying them forward into the new Planner. Learn more about frontline worker innovations.

New Planner roadmap and Enterprise Work Management capabilities

Rollout timeline for the new Planner:

November 2023: The Tasks by Planner and To Do app in Teams is renamed Planner.

Spring 2024: The new Planner app in Teams will be generally available. Microsoft Project for the web will be renamed to Planner.

Later in 2024: The web experience of the new Planner will be generally available. The features you know and love in Microsoft Project for the web will be available in the new Planner app in Teams and the Planner web app.

Enhanced EWM capabilities coming to the new Planner
Existing capabilities in Project for the web such as sprints, people view, goals and advanced scheduling with dependencies will be available in the new Planner. Additional new EWM capabilities coming to the new Planner include baselines, portfolios and lead-lag capability.
Continued support for our customers
Project desktop, Project Online, and Project Server will continue to be available and supported for both our existing and new customers. There is currently no end of service date for Project Online.

Current users of Microsoft Project for the web, rest assured, capabilities that you use and love today will continue to be available under the new name, Planner. The Planner Power App will also continue to enable customized reporting through Power BI and configurable workflows through Power Automate. You will be able to enjoy premium experiences* in the new Planner based on your Project plan subscriptions at no additional cost.

Get started

Learn more:

Stay up to date with Planner roadmap and general availability:

Get started:

Stay Connected:
*We will share more about pricing and details for Copilot in Planner in the coming months.

# data[].attributes.attachment_link.title
The new Microsoft Planner: A unified experience bringing together to-dos, tasks, plans and projects

# data[].attributes.attachment_link.description
We're heading into a new era, where AI is an integral part of the workday. The tools you're using for work management should be intelligent and help keep

# data[].attributes.body
Sign up and log in so it keeps up with your progress and lessons.

# data[].attributes.attachment_link.title
Learn to Type Free - Student Signup to Typing.com - Typing.com

# data[].attributes.attachment_link.description
Don't have a student account yet? Sign up here.

# data[].attributes.body
"File already in use" error when you try to open an Access database that's located on a server

Access reports that databases are in an 'inconsistent state'

Security Update MS12-060 Impairs Functionality of Access Database

Fixes or workarounds for recent issues in Access

# data[].attributes.headline
Examples of using dates as criteria in Access queries

# data[].attributes.subject
Examples of using dates as criteria in Access queries

# data[].attributes.attachment_link.title
Examples of using dates as criteria in Access queries

# data[].attributes.attachment_link.description
When entering dates as query criteria, enclose them in pound signs (#) to help Access understand what they are.

# data[].attributes.headline
Word - Basic Reference Guide (CustomGuide)

# data[].attributes.subject
Word - Basic Reference Guide (CustomGuide)

# data[].attributes.headline
Word - Intermediate Reference Guide (CustomGuide)

# data[].attributes.subject
Word - Intermediate Reference Guide (CustomGuide)

# data[].attributes.headline
Word - Advanced Reference Guide (CustomGuide)

# data[].attributes.subject
Word - Advanced Reference Guide (CustomGuide)

# data[].attributes.attachment_link.readable_text
Unlock the secrets of Access 2013 and discover how to use your data in creative ways. With this book's easy step-by-step instructions, you'll learn how to build and maintain a full-featured database and even turn it into a web app. You also get tips and practices from the pros for good database design-ideal whether you're using Access for business, school, or at home.

The important stuff you need to know

Build a database with ease. Organize and update lists, documents, catalogs, and other types of information.

Create your own web app. Let your whole team work on a database in the cloud.

Share your database on a network. Link your Access database to SQL Server or SharePoint.

Customize the interface. Make data entry a breeze by building your own templates

Find what you need fast. Search, sort, and summarize huge amounts of data in minutes.

Put your info to use. Turn raw info into well-formatted printed reports.

Dive into Access programming. Automate complex tasks and solve common challenges.

# data[].attributes.attachment_link.title
Access 2013: The Missing Manual (Missing Manuals)

# data[].attributes.attachment_link.description
Access 2013: The Missing Manual (Missing Manuals) [MacDonald, Matthew] on Amazon.com. *FREE* shipping on qualifying offers. Access 2013: The Missing Manual (Missing Manuals)

# data[].attributes.attachment_link.description
This is learning made easy! Get productive fast with Visio 2016, and jump in wherever you need answers: brisk lessons and colorful screen shots show you exactly what to do, step by step - and practice files help you build your skills

# data[].attributes.headline
Protect Your Microsoft Access Applications From Direct Table Editing and Changing Your Design

# data[].attributes.subject
Protect Your Microsoft Access Applications From Direct Table Editing and Changing Your Design

# data[].attributes.attachment_link.readable_text
The design of an Access application can be a fragile thing. More than likely, you've spent considerable time tweaking the design of your forms, tables, queries and reports to work together to create the user experience and functionality you desire. Accidental changes to any of this, could seriously impact the functionality of your application, therefore, we want to keep untrained people out of the design features that are, by default, provided by Access.

In this article we will:

1. Look at how to keep our users from directly editing our tables

2. Prevent our users from modifying the design of our database object

Prevent Direct Table Editing

Allowing a user to directly edit a data table is to invite the potential for additional accidental data edits, or even unplanned mass data deletions. In addition, there is often rules that govern how data elements should be related to one another. One method of managing this is via relationships between tables, data integrity enforced by the database. Another method of managing this is to embed business rules in our data entry forms. This second method makes an additional case against users directly editing tables.

A great way to prevent user access to data is to split your database, thus putting your data tables in one database file (the back-end), and your user interface in another database file (the front-end). Then password protect your back-end database, and hide the Navigation pane in your front-end.

Prevent Design Changes to Your User Interface

Before you make any changes that will lock your users out of the design-time features provided by Access, remember that if you lock them out, you also lock yourself out. So make a copy of your database and only secure the copy. As to your original copy, heed Gandalf the Grey's advice and "keep it secret, keep it safe".

Here are several things we can to restrict access to the design features that Access provides by default. Each of these items can be modified by going to the Office button in version 2007, or the File tab in versions 2010 and 2013. Then choose "Access Options" near the bottom-left, and then choose "Current Database" on the left.

1. Hide the Navigation pane: This will hide access to our database objects such as tables, forms, reports, queries, and code modules.

2. Turn off Full menus: This will remove most of the tabs from your ribbon. It will also remove most of the entries under the File tab.

1. Even though you won't see the "Access Options" button that got you here, in version 2010, there is a button for "Privacy Options". This button will get you and your users back to the same "Access Options", so beware, they are not totally locked out using just this method.

2. If you are going to remove default tabs from the ribbon, consider providing your users a custom ribbon that can provide access to the features that you do want them to access.

3. Turn off default shortcut menus: this will hide the right-click menus that provide design view and delete capability.

4. Turn off Access Special Keys: Some Special Keys are:

1. F11: Shows Navigation Pane

2. ALT+F11: Opens the VBA code editor

3. CTRL+BREAK: Stops the execution of VBA or a query

A view of turning off these options is given below:

Since your users can still get to the "Access Options" via the "Privacy Options" button, even after turning off full menus, I suggest you go one step further and create an executable-only version of your front-end database. Doing this creates a database file with the extension of "accde". This file does not contain any source code (VBA), and does not allow your users access the design view of forms or reports. It is very easy to make an accde database file. Go to the File tab again, but this time, choose the "Save & Publish" button on the left. Then on right side of this screen, choose "Make ACCDE" then press "Save As" and provide a location and new file name if desired:

An additional benefit of making an accde version of your deployed database is that they tend to be smaller, because the source code has been removed.

Bypassing the Bypass Key

There is one last thing we can do to help us secure the designed functioning of our application, and that is to prevent our users from using the Shift key at database startup. When the shift key is pressed at database startup, this prevents the AutoExec macro from executing as well as prevents the startup form from opening. So if you have code you want to run at startup, you need to disable the bypass key. However, that is the subject of my next blog entry. Until then, cheers!

# data[].attributes.attachment_link.title
Protect Your Microsoft Access Applications From Direct Table Editing and Changing Your Design

# data[].attributes.attachment_link.description
The design of an Access application can be a fragile thing. More than likely, you've spent considerable time tweaking the design of your forms, tables, queries and reports to work together to create the user experience and functionality you desire.

# data[].attributes.headline
Troubleshooting creating index in Access

# data[].attributes.subject
Troubleshooting creating index in Access

# data[].attributes.body
- Error- Microsoft Office Access can't create an index or primary key:  https://www.solvusoft.com/en/errors/runtime-errors/microsoft-corporation/microsoft-access/2396-microsoft-office-access-can-t-create-an-index-or-primary-key/
- Access cannot create index: https://stackoverflow.com/questions/70080169/access-cannot-create-index
- Fix MS Access Error “ID is not an index in this table”: https://www.stellarinfo.com/blog/fix-ms-access-error-id-is-not-an-index-in-this-table/

# data[].attributes.attachment_link.readable_text
Error Number:
Error 2396

Error Name:
Microsoft Office Access can't create an index or primary key

Error Description:
Microsoft Office Access can't create an index or primary key.@One or more field names are missing.@Enter or select at least one field in the Field Name column for each index you name.@1@@@1.

Developer:
Microsoft Corporation

Software:
Microsoft Access

Applies to:
Windows XP, Vista, 7, 8, 10, 11

Explanation of Microsoft Office Access can't create an index or primary key

Microsoft Office Access can't create an index or primary key is generally referred to as a form of "runtime error". Developers like Microsoft Corporation usually go through several checkpoints before launching a software program like Microsoft Access. Sadly, some critical issues such as error 2396 can often be overlooked.

Error 2396 might be faced by Microsoft Access users if they are using the program regularly, also seen as "Microsoft Office Access can't create an index or primary key.@One or more field names are missing.@Enter or select at least one field in the Field Name column for each index you name.@1@@@1.". Reporting this error 2396 will allow developers to update their application and rectify any faults that could be causing it. Programmers can then correct these errors in the code and include a hotfix that can be downloaded from their website. Consequently, the developer will use a Microsoft Access update package to resolve error 2396 and any other reported error messages.

What Generates Runtime Error 2396?

Failure during Microsoft Access startup or at runtime is generally when you will encounter Microsoft Office Access can't create an index or primary key. We are going to review at the key causes of error 2396 errors:

Error 2396 Crash - This is a common error 2396 runtime error that results in the program completely terminating. Typically this is the result of Microsoft Access not understanding the input, or not knowing what to output in response.

Microsoft Office Access can't create an index or primary key Memory Leak - If there's a memory leak in Microsoft Access, it may cause the OS to appear sluggish. Potential triggers may be "infinite loop", or when the program performs a "loop" or repetition over and over again.

Error 2396 Logic Error - A logic error triggers the wrong output even when the user has given valid input data. Usual causes of this problem are due to faults in data handling.

Typically, Microsoft Office Access can't create an index or primary key errors are caused by a corrupt or missing Microsoft Access-associated file, sometimes due to malware infection. Replacing your Microsoft Corporation file is generally a solution to fixing these issues. After the problem file is replaced, running a registry scan can help clean up any invalid Microsoft Office Access can't create an index or primary key, file extension, or other file path references which could have been affected from a previous malware infection.

Classic Microsoft Office Access can't create an index or primary key Problems

The most common Microsoft Office Access can't create an index or primary key errors that can appear on a Windows-based computer are:

"Microsoft Office Access can't create an index or primary key Error."

"Win32 Software Error: Microsoft Office Access can't create an index or primary key"

"Microsoft Office Access can't create an index or primary key needs to close."

"Cannot find Microsoft Office Access can't create an index or primary key."

"Microsoft Office Access can't create an index or primary key is missing."

"Error starting program: Microsoft Office Access can't create an index or primary key."

"Microsoft Office Access can't create an index or primary key not working."

"Microsoft Office Access can't create an index or primary key halted."

"Faulty Program Path: Microsoft Office Access can't create an index or primary key."

Microsoft Access Microsoft Office Access can't create an index or primary key issues occur with installation, while Microsoft Office Access can't create an index or primary key-related software runs, during shutdown or startup, or less-likely during operating system updates. When seeing the Microsoft Office Access can't create an index or primary key error, please record occurrences to troubleshoot Microsoft Access, and to help Microsoft Corporation find the cause.

Microsoft Office Access can't create an index or primary key Issue Origins

Microsoft Office Access can't create an index or primary key issues are caused by a corrupt or missing Microsoft Office Access can't create an index or primary key, invalid registry keys associated with Microsoft Access, or a malware infection.

In particular, Microsoft Office Access can't create an index or primary key problems originate through:

Microsoft Office Access can't create an index or primary key entry invalid or corrupt.

Malware has infected Microsoft Office Access can't create an index or primary key, creating corruption.

Malicious deletion (or mistaken) of Microsoft Office Access can't create an index or primary key by another application (not Microsoft Access).

Another software application in conflict with Microsoft Office Access can't create an index or primary key.

Corrupt download or incomplete installation of Microsoft Access software.

Download Now
WinThruster 2023 - Scan your PC for computer errors.
Compatible with Windows 11, 10, 8, 7, Vista, XP and 2000

Optional Offer for WinThruster by Solvusoft | EULA | Privacy Policy | Terms | Uninstall

# data[].attributes.attachment_link.keywords[]
microsoft office access can't create an index or primary key

# data[].attributes.attachment_link.keywords[]
fix microsoft office access can't create an index or primary key

# data[].attributes.attachment_link.description
Error 2396 problems include computer crashes, freezes, and possible virus infection. Learn how to fix these Microsoft Access runtime errors quickly and easily!

# data[].attributes.attachment_link.readable_text
Query criteria help you zero in on specific items in an Access database. If an item matches all the criteria you enter, it appears in the query results.

To add criteria to an Access query, open the query in Design view and identify the fields (columns) you want to specify criteria for. If the field is not in the design grid, double-click the field to add it to the design grid and then enter the criterion in the Criteria row for that field. If you're not sure how to make this happen, see Introduction to queries.

A query criterion is an expression that Access compares to query field values to determine whether to include the record that contains each value. For example, = "Chicago" is an expression that Access can compare to values in a text field in a query. If the value for that field in a given record is "Chicago", Access includes the record in the query results.

Here are some examples of commonly used criteria you can use as a starting point to create your criteria. The examples are grouped by data types.

In this topic

Introduction to query criteria

A criterion is similar to a formula - it is a string that may consist of field references, operators, and constants. Query criteria are also referred to as expressions in Access.

The following tables shows some sample criteria and explains how they work.

Criteria

Description

>25 and <50

This criterion applies to a Number field, such as Price or UnitsInStock. It includes only those records where the Price or UnitsInStock field contains a value greater than 25 and less than 50.

DateDiff ("yyyy", [BirthDate], Date()) > 30

This criterion applies to a Date/Time field, such as BirthDate. Only records where the number of years between a person's birthdate and today's date is greater than 30 are included in the query result.

Is Null

This criterion can be applied to any type of field to show records where the field value is null.

As you can see, criteria can look very different from each other, depending on the data type of the field to which they apply and your specific requirements. Some criteria are simple, and use basic operators and constants. Others are complex, and use functions, special operators, and include field references.

This topic lists several commonly used criteria by data type. If the examples given in this topic do not address your specific needs, you might need to write your own criteria. To do that, you must first familiarize yourself with the full list of functions, operators, special characters, and the syntax for expressions referring to fields and literals.

Here, you will see where and how you add the criteria. To add a criteria to a query, you must open the query in Design view. You then identify the fields for which you want to specify criteria. If the field is not already in the design grid, you add it by either dragging it from the query design window to the field grid, or by double-clicking the field (Double-clicking the field automatically adds it to the next empty column in the field grid.). Finally, you type the criteria in the Criteria row

Criteria that you specify for different fields in the Criteria row are combined by using the AND operator. In other words, the criteria specified in the City and BirthDate fields are interpreted like this:

City = "Chicago" AND BirthDate < DateAdd("yyyy", -40, Date())

1. The City and BirthDate fields include criteria.

2. Only records where the value of the City field is Chicago will satisfy this criterion.

3. Only records of those who are at least 40 years old will satisfy this criterion.

4. Only records that meet both criteria will be included in the result.

What if you want only one of these conditions to be met? In other words, if you have alternate criteria, how do you enter them?

If you have alternate criteria, or two sets of independent criteria where it is sufficient to satisfy one set, you use both the Criteria and the or rows in the design grid.

1. The City criterion is specified in the Criteria row.

2. The BirthDate criterion is specified in the or row.

Criteria specified in the Criteria and or rows are combined using the OR operator, as shown below:

City = "Chicago" OR BirthDate < DateAdd("yyyy", -40, Date())

If you need to specify more alternatives, use the rows below the or row.

Before you continue with the examples, note the following:

If the criteria is temporary or changes often, you can filter the query result instead of frequently modifying the query criteria. A filter is a temporary criterion that changes the query result without altering the design of the query. For more information about filters, see the article Apply a filter to view select records in an Access database.

If the criteria fields don't change, but the values you are interested in do change frequently, you can create a parameter query. A parameter query prompts the user for field values, and then uses those values to create the query criteria. For more information about parameter queries, see the article Use parameters in queries and reports.

Criteria for Text, Memo, and Hyperlink fields

Note: Beginning in Access 2013, Text fields are now named Short Text and Memo fields are now named Long Text.

The following examples are for the CountryRegion field in a query that is based on a table that stores contacts information. The criterion is specified in the Criteria row of the field in the design grid.

A criterion that you specify for a Hyperlink field is, by default, applied to the display text portion of the field value. To specify criteria for the destination Uniform Resource Locator (URL) portion of the value, use the HyperlinkPart expression. The syntax for this expression is as follows: HyperlinkPart([Table1].[Field1],1) = "http://www.microsoft.com/", where Table1 is the name of the table containing the hyperlink field, Field1 is the hyperlink field, and http://www.microsoft.com is the URL you want to match.

To include records that...

Use this criterion

Query result

Begin with the specified string, such as U

Like U*

Returns records for all countries/regions whose names start with "U", such as UK, USA, and so on.

Note: When used in an expression, the asterisk (*) represents any string of characters - it is also called a wildcard character. For a list of such characters, see the article Access wildcard character reference.

Match a specific pattern

Like "Chi??"

Returns records for countries/regions, such as China and Chile, whose names are five characters long and the first three characters are "Chi".

Note: The characters ? and _, when used in an expression, represent a single character - these are also called wildcard characters. The character _ cannot be used in the same expression with the ? character, nor can it be used in an expression with the * wildcard character. You may use the wildcard character _ in an expression that also contains the % wildcard character.

Criteria for Number, Currency, and AutoNumber fields

The following examples are for the UnitPrice field in a query that is based on a table that stores products information. The criterion is specified in the Criteria row of the field in the query design grid.

To include records that...

Use this crit erion

Query Result

Contain a value smaller than a value, such as 100

< 100
<= 100

Returns records where the unit price is less than $100 (<100). The second expression (<=100) displays records where the unit price is less than or equal to $100.

Contain a value larger than a value, such as 99.99

>99.99
>=99.99

Returns records where the unit price is greater than $99.99 (>99.99). The second expression displays records where the unit price is greater than or equal to $99.99.

Contain a value that falls with a range of values

>49.99 and <99.99
-or-
Between 50 and 100

Returns records where the unit price is between (but not including) $49.99 and $99.99.

Contain a value that ends with the specified digits

Like "*4.99"

Returns records where the unit price ends with "4.99", such as $4.99, $14.99, $24.99, and so on.

Note: The characters * and %, when used in an expression, represent any number of characters - these are also called wildcard characters. The character % cannot be used in the same expression with the * character, nor can it be used in an expression with the ? wildcard character. You may use the wildcard character % in an expression that also contains the _ wildcard character.

Criteria for Date/Time fields

The following examples are for the OrderDate field in a query based on a table that stores Orders information. The criterion is specified in the Criteria row of the field in the query design grid.

To include records that ...

Use this criterion

Query result

Contain values that fall before a certain date, such as 2/2/2006

< #2/2/2006#

Returns records of transactions that took place before Feb 2, 2006.

To view transactions that took place on or before this date, use the <= operator instead of the < operator.

Contain values that fall after a certain date, such as 2/2/2006

> #2/2/2006#

Returns records of transactions that took place after Feb 2, 2006.

To view transactions that took place on or after this date, use the >= operator instead of the > operator.

Contain values that fall within a date range

>#2/2/2006# and <#2/4/2006#

Returns records where the transactions took place between Feb 2, 2006 and Feb 4, 2006.

You can also use the Between operator to filter for a range of values, including the end points. For example, Between #2/2/2006# and #2/4/2006# is the same as >=#2/2/2006# and <=#2/4/2006# .

Criteria for Yes/No fields

As an example, your Customers table has a Yes/No field named Active, used to indicate whether a customer's account is currently active. The following table shows how values entered in the Criteria row for a Yes/No field are evaluated.

Field value

Result

Yes, True, 1, or -1

Tested for a Yes value. A value of 1 or -1 is converted to "True" in the Criteria row after you enter it.

No, False, or 0

Tested for a No value. A value of 0 is converted to "False" in the Criteria row after you enter it.

No value (null)

Not tested

Any number other than 1, -1, or 0

No results if it's the only criteria value in the field

Any character string other than Yes, No, True, or False

Query fails to run due to Data type mismatch error

Criteria for other fields

Attachments In the Criteria row, type Is Null to include records that do not contain any attachments. Type Is Not Null to include records that contain attachments.

Lookup fields There are two types of Lookup fields: those that look up values in an existing data source (by using a foreign key), and those that are based on a list of values specified when the Lookup field is created.

Lookup fields that are based on a list of specified values are of the Text data type, and valid criteria are the same as for other text fields.

The criteria you can use in a Lookup field based on values from an existing datasource depend on the data type of the foreign key, rather than the data type of the data being looked up. For example, you may have a Lookup field that displays Employee Name, but uses a foreign key that is of the Number data type. Because the field stores a number instead of text, you use criteria that work for numbers; that is, >2.

If you do not know the data type of the foreign key, you can inspect the source table in Design view to determine the data types of the field. To do this:

1. Locate the source table in the Navigation Pane.

2. Open the table in Design view by either:

Clicking the table, and then pressing CTRL+ENTER

Right-clicking the table, and then clicking Design View.

3. The data type for each field is listed in the Data Type column of the table design grid.

Multivalued fields Data in a multivalued field are stored as rows in a hidden table that Access creates and populates to represent the field. In query Design view, this is represented in the Field List by using an expandable field. To use criteria for a multivalued field, you supply criteria for a single row of the hidden table. To do this:

1. Create a query containing the multivalued field, and open it in Design view.

2. Expand the multivalued field by clicking the plus symbol (+) next to it - if the field is already expanded, this is a minus symbol (-). Just below the name of the field, you will see a field representing a single value of the multivalued field. This field will have the same name as the multivalued field, with the string .Value appended.

3. Drag the multivalued field and its single value field to separate columns in the design grid. If you want to see only the complete multivalue field in your results, clear the Show check box for the single value field.

4. Type your criteria in the Criteria row for the single value field, using criteria that is appropriate for whatever type of data the values represent.

5. Each value in the multivalued field will be individually evaluated using the criteria you supply. For example, you may have a multivalued field that stores a list of numbers. If you supply the criteria >5 AND <3, any record where there is at least one value greater than 5 and one value less than 3 will match.

See Also

Introduction to queries

Create a simple select query

# data[].attributes.attachment_link.description
Query criteria help you zero in on specific items in an Access database. If an item matches all the criteria you enter, it appears in the query results. To add criteria to an Access query, open the query in Design view and identify the fields (columns) you want to specify criteria for.

# data[].attributes.headline
Use wildcards in queries and parameters in Access

# data[].attributes.subject
Use wildcards in queries and parameters in Access

# data[].attributes.attachment_link.readable_text
Match all characters anywhere in your data

1. Open your query in Design view. To do so, in the Navigation pane, under Queries, right-click the query and click Design View.

2. In the Criteria cell under the field you want to use, add an asterisk on either side of your criteria, or on both sides. For example:

"*owner*".

"owner*".

"*owner".

3. On the Design tab, in the Results group, click Run.

Match a character within a pattern

1. Open your query in Design view.

2. In the Criteria cell of the field you want to use, type the operator Like in front of your criteria.

3. Replace one or more characters in the criteria with a wildcard character. For example, Like R?308021 returns RA308021, RB308021, and so on.

4. On the Design tab, in the Results group, click Run.

Retrieve a list of companies from A through H

1. Open your query in Design view.

2. In the Criteria cell of the field you want to use, enter Like, followed by a pair of double quotes. For example: Like "".

3. Within the double quotes, add a pair of square brackets and the range of characters you want to find, like so:

Like "[a-h]"

4. You can use wildcards outside the brackets. For example:

Like "[a-h]*"

Table of wildcard characters

This table lists and describes the wildcard characters you can use in an Access query.

Symbol

Description

Example

Examples of using wildcards

Situation

Example

Your data lists some people as "owner" and others as "owner/operator".

Like "owner*" or
Like "*owner*"

You want to use wildcards with a parameter query.

Like "*" & [ parameter] & "*"

For example:

Like "*" & [fish] & "*"

Returns all recipes containing "fish", such as fish & chips, rockfish, and so on.

Syntax to retrieve wildcard characters from your data

Character

Required Syntax

Understanding which set of wildcards to use

Access database engine (ANSI-89) versus SQL Server (ANSI-92)

ANSI-89 describes the traditional Access SQL syntax, which is the default for Access databases. The wildcard characters conform to the Microsoft Visual Basic® for Applications (VBA) specification, not SQL.

ANSI-92 is used when you want your syntax to be compliant with a Microsoft SQL Server™ database.

It's recommended that you don't mix the two types of wildcards in the same database.

Wildcards for use with the Access database engines (ANSI-89)

Use these wildcard characters in queries created for an Access database.

Symbol

Description

Example

Wildcards for use with SQL Server (ANSI-92)

Use these wildcard characters in queries created for use with a Microsoft SQL Server™ database.

Symbol

Description

Example

See also

# data[].attributes.attachment_link.title
Use wildcards in queries and parameters in Access

# data[].attributes.attachment_link.description
Open your query in Design view. To do so, in the Navigation pane, under Queries, right-click the query and click Design View. In the Criteria cell under the field you want to use, add an asterisk on either side of your criteria, or on both sides. For example: "*owner*". "owner*".