Excel: Form Entry for Tables

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.QAT Dropdown
  3. Select More Commands.QAT More Commands
  4. On the Choose Commands From dropdown, select Commands not in the Ribbon.Commands Not in 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 OKSteps 5 through 8 illustated

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

New button in QAT

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.
    New button in QAT
  3. A form entry box will appear.

Form Entry box

New: Adding Entries
  1. On the right side of the form view, press New
    New Button
  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.Criteria Button
  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.Find Next Button

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 BatesHannah Bates

PowerPoint: Animation Painter

We frequently go to a lot of trouble to create the perfect animation in PowerPoint. In PowerPoint Essentials, we discuss options for adjusting duration, delaying animations, triggers for animations, and other various tweaks you can make when selecting an animation. Once you have spent a lot of time making these adjustments, you may want to apply the same effect to another object in your presentation without recreating the wheel every time. Animation Painter is the perfect tool for this!

Exercise File

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

This file has the beginnings of a presentation on Wichita State University. Slide 2, titled Shocker Hall, has an image with a couple animations applied to it; both animations have been tweaked for timing. Slide 3, Culture and Activities, has an image with no animations applied.

Your Challenge (should you choose to accept it) is to apply the animation from Slide 2 to the image in Slide 3.

Important: Animation Pane

For this exercise, and really any time you are working with animations in PowerPoint, toggle on your Animation Pane. There are many ways this will make your life easier when working with animations, and working with the Animation Painter is no exception.

  • Go to: Animations tab, Advanced Animation group, select Animation Pane.
    Animations Tab, Animation Pane button

Animation Painter

Let’s paint some animations!

Slide two selected, click on element, go to animation painter.

  1. Select Slide 2 (Shocker Hall) from the preview pane on the left side of the screen.
  2. Select the Shocker Hall image.
  3. Note the animations applied to the image in the Animation Pane on the right.
    • To preview the animations, press the Preview button in the left side of the Animations tab.
      Preview Button
  4. With the Shocker Hall image selected, go to the Animation tab, Advanced Animation group, and select Animation Painter.
    Animation Painter
  • Note that your cursor turns into a paintbrush (just like the Format Painter we explore in Word and Excel Essentials)

Cursor is a paintbrush

5. Select Slide 3 (Culture and Activities) in the preview pane on the left side of the screen.

6. Select the Performance image in the slide; this will “paint” the animation settings onto this picture.

Click on image

  • A preview of the newly-applied animation should occur immediately, but can also be replayed with the Preview button in the Animations

Possible Pitfall

  • Mouse Click Folly: Be careful where you click with this feature: be sure you have selected what you would like to copy, then immediately click onto the object to be altered. It is very easy to “paint” animation onto the wrong item (don’t forget, ctrl Z can be your best friend).

Thoughts?

How will you use this feature on your presentations?

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

  • Jesse Koza
    (channeling Clippy!)Jesse Koza

Outlook: Delay Delivery

There are a variety of reasons that you may want to delay delivery of an email in Outlook. Perhaps you are waiting until a specific time to send a reminder, or maybe you need to delay sending emails to specific people until 4:59 pm (or 7:59 am). Outlook’s Delay Delivery feature may be just what you are looking for.

Mac Users

It looks like this feature will be coming in the future to Mac users (365 and 2019 versions of Outlook), so stay tuned! It will be called “Send Later,” and will appear as a dropdown next to the Send button.
Mac Send Later screen

PC Users: Outlook Options

To start, try testing this out on a test email, not an important one.

  1. Create a New Email.
    New email button
  2. Select the Options tab in the ribbon
  3. In the More Options group, press Delay Delivery
    Options tab, delay delivery button
  4. In the pop up screen, Delivery Options section, make sure the box next to Do not Deliver before is checked. Set the date and time for delivery and pressIf you want to see the feature in action, try delaying your test email for just a few minutes.
    Do not deliver before selected, date and time entered
  5. When you are ready to send the message, press Send, just like usual.
    Send Button
  6. The email will be in your Outbox until the delivery time. To make a change or delete the message, go to your outbox. Double click on the message to reopen and access the Options tab on the ribbon.
    Outlook folder,s Outbox circled

Important Caveats

Closing Outlook

  • When you use this feature in Outlook, your Outlook program must be up and running for the delivery to occur. So, if the delivery time occurs while your Outlook is closed, the delivery will not occur until you reopen Outlook.
  • If you Delay Delivery and close your Outlook, you will receive a reminder message that there are items in your Outbox.Warning message that there are messages in outbox

Button Defaults

  • As soon as you press the Delay Delivery button, the email will be delayed until a default time until you uncheck Do Not Deliver Before In other words, if you press this button to look at the feature but do not want to delay delivery, be sure to uncheck the box next to Do Not Deliver Before.Do Not Deliver Before box

Thoughts?

What do you think? Do you have great ideas for using this cool feature in Outlook?

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

  • Susie Jacques
    Susie Jacques
  • Heather Merchant
    (as Flash Phil!)Heather Merchant

Excel: Paste Special Operations: Quick Arithmetic with a Range Cells

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, copyF1 Selected, 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 SpecialPaste dropdown, Paste Special selected
  6. In the popup screen, under Operation, select Add.Operations section, Add selected
    • 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.New list, with numbers increased by 2Once 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

Operations section of Paste Special

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
    Lansa Anthis
  • Cindy Sharp
    Cindy Sharp
  • Susie Steinbach

Susie Steinbach