A lot of people prefer a form entry view to a table view for data entry. Forms for data entry are something we are all used to seeing in popular programs like Filemaker and Access, but also for something as basic as filling out an online form… that is data entry too, after all.

There are ways to accomplish this task with macros, but there is an even simpler way to activate a new view involving Tables.

Exercise File

If you would like to follow along, download the exercise file here:

ExerciseFormEntry

This looks familiar if you have attended Advanced Formulas training. We use this same data to calculate age from birthday and grade from a grade percent. Notice that columns E, G and H all contain formulas, while the other columns are free entry.

Important: Format as a Table

This data has been formatted as a Table. We talk about tables at great length in Excel Essentials. Remember how I said there are endlessly cool things that will happen in your life when you format data as a table? Here is another one!

Creating a Form Button

Form entry is not a command you can find in the ribbon, but there is a work around for this. You might recall that the Quick Access Toolbar (QAT) contains all the commands you can find on the ribbon and also commands not in the ribbon… let’s find this one.

1. Find the Quick Access Toolbar (QAT) above the ribbon.
2. Select the dropdown arrow at the right side of the QAT.
3. Select More Commands.
4. On the Choose Commands From dropdown, select Commands not in the Ribbon.
5. On the left pane, scroll down and select Form (features are listed alphabetically).
6. Press the Add button between the two panes.
7. Form will appear on the right pane.
8. Press OK

A new button will appear in your QAT for Form entry.

Using the New Button

Let’s test out the button.

1. Place your cursor anywhere inside the table.
2. Press the Form button you just created in your QAT.
   
3. A form entry box will appear.

New: Adding Entries

1. On the right side of the form view, press New
   
2. Enter in your own information (give yourself a good grade!)
3. Notice how the columns that contain formulas (E, G and H) are not enterable.
4. Tab to move between fields
5. Press enter once data is entered.
6. Scroll down to look at the new entry in the table.

 

Criteria:  Search for a Specific Field

1. On the right side of the form view, press Criteria.
2. Place your cursor in the Last Name field and try searching for Last Name: Green
3. Press Find Next to be taken to the next entry.

Possible Pitfalls

• If you are sharing a workbook with a group of people who would like to do form entry on your table, they will need to add the Form tool to their QAT too. They will still be able to type into the table without the form view, of course.

Thoughts?

What do you think? Do you have any old tables lying around that could use this handy feature?

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

• Hannah Bates

Every once in a while, you find yourself in a situation where you need to perform a basic arithmetic function on a series of cells… maybe you need to take a group of cells and increase or decrease their number by 1, or by a percentage.

You could create a helper column, write a formula into it, auto fill down, then copy and paste the values back into their original spots, but that is a lot of steps for simple addition! You might be tempted to change the values by hand; adding one to each cell is not so hard, right? Well, don’t! There is an even faster way.

I have mentioned before that there is more than meets the eye in Paste Special options. Here is another great example.

Exercise File

To follow along, download today’s exercise here: ExercisePasteSpecialAddition

Scenario

This workbook contains a sheet called Attendance. In this scenario, my supervisor has asked that I add myself and him/her to the staff headcount at the January meetings. So, I would like to quickly add 2 to all the numbers in column B.

Paste Special: Operations

Since we will be using copy and paste for this, we need to have the number we want to add somewhere on the sheet so it can be copied. You can always delete it later: I populated this number in cell F1 for us.

1. Select F1
2. Either press Ctrl C on your keyboard or right click, copy
3. Select all the cells that need this figure added: so B2 to B28
4. In the Clipboard group of the Home tab, select the dropdown under Paste
5. Select Paste Special
6. In the popup screen, under Operation, select Add.
   • By doing this, we are telling Excel that, instead of copying the number 2 to all these cells, we would like to add 2 to the existing contents.
7. All the numbers in the selected cells have now increased by 2.Once you have finished this task, you could theoretically delete the contents of E1 and F1. We did not create formulas in column B, we just added to the existing number, so we are no longer relying on F1.
   

   More Arithmetic Options

   Did you spy the other options in the Operations section of Paste Special? You could also:

   • Multiply
   • Divide
   • Subtract

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

• Lana Anthis
  
• Cindy Sharp
  
• Susie Steinbach