Skip to Main Content

Indiana University Indiana University


Veronica Mount's Archive

Access – Collecting Data by Email

Access provides great tools for organizing, searching and analyzing data. But before you can use those great tools, you must first enter the data. In Access 2007, a wizard with the monster-name, “Collect Data Through Email Messages Wizard,” for collecting information by email was introduced.  Two aspects of this process make it particularly attractive:

1)      You can set the wizard to automatically deliver data from the email replies directly into the appropriate fields of  your database, and

2)      The data is being entered by the primary source of the information – from their keyboard to your table.

This wizard lives – as you might guess – on the External Data tab of the Ribbon in the Collect Data group. You can start the wizard by first selecting a table or query that holds the fields you want to send out and then clicking the Create E-mail icon.

Access Ribbon (more…)

“Saving” time in Word, PowerPoint and Excel

“Just Browsing” might be considered worthwhile when we are strolling through the mall with no particular must-haves on a shopping list. But when we’re working in Microsoft Office and frequently saving files, taking time to browse for the preferred folder can be about as productive as hunting for a mall parking spot at Christmas!

Your installations of Microsoft Office applications come with default locations defined for saving files. If, for every file you save, you navigate from that location to another, you could be losing lots of time browsing. And if you absently click the save button without specifying the location, you lose more time later searching for the file and/or moving it to the preferred spot. So how do you tell the application that you would like to head to a different location when you start to save a file? (more…)

Access: Querying for “Preferred” Data Given Multiple Choices

A friend once presented a challenge with his Access database. A table called tblContacts held information about contacts and included the fields WorkEmail and HomeEmail. Some records had values in both fields, others had values in just one or the other, and some had no values in either field. The user wanted to query the table for a list with a name and only one email address for each record. His preference was the work email, but he would use the home email if no work email existed. The table looked something like this:

FName

LName

WorkEmail

HomeEmail

Mary

Jones

someone@examle.com

me@example.com

Jim

Smith

js@example.com

Nancy

Olson

no@example.com

Lee

Brown

(more…)

Excel: Trimming Cell Entries

A friend recently complained to me about mail merge results she was getting from her Excel data. Her spreadsheet held lists of important donors to her organization, but the data had been entered in various ways from many different sources. As a result, cells in the data held random spaces before or after the information causing her address labels and letter components to look less than professional. Excel’s Trim function came to her rescue. The Trim function removes spaces from text.

My friend’s original data looked something like this:

Data with unwanted spaces

(more…)

Printing Graph Paper from Excel

Graph paper can be handy for a number of purposes when you need it – art projects, numeric comparisons, floor plans, and crafts to name a few. But unless you use a lot of it, graph paper isn’t something most of us are likely to keep on hand. Excel can serve up a quick grid to print out by following a few simple steps. (more…)

Extending STEPS with lynda.com: Office for Mac 2008

Users of Microsoft Office can take advantage of STEPS workshops on Excel, Word, PowerPoint, Outlook and Access.  However, these workshops are all taught in Office 2007 for Windows; and Office for the Macintosh (both the 2004 and 2008 versions) have a significantly different interface. Don’t worry though – users of Office 2008 for the Mac can find the training they need in Office 2008 through lynda.com. From now until December 20, 2009, students, faculty and staff in the IU community receive FREE access to lynda.com. (For more information and a link to lynda.com training, visit http://ittraining.iu.edu)

For users already familiar with Office for Mac, one tutorial on Lynda gives a comprehensive look at just the new features in the 2008 suite. Other topics of training include essential training for Word, PowerPoint and Excel as well as Entourage, the personal information manager. For a more detailed listing of titles included in the Word, Excel and PowerPoint tutorials in the lynda.com site read the rest of this article.

(more…)

Numbering and Counting Records in an Access 2007 Report

Access reports provide many options for formatting the data in the report so that it is easy for readers to understand. One formatting element you may want to include is numbering of the records in the report. Adding numbers allows readers to see a rank order of the items listed or to easily reference a particular record in a discussion.

 Numbered List
(more…)

Using Pattern Fills in Excel 2007 Charts

When preparing articles for publication, you may encounter a journal requirement specifying that chart data markers be filled with different patterns such as dots, slanted lines, etc. These types of fills make data that is being displayed in black and white easier to discern than those formatted in various colors. Unfortunately, while Excel 2007 includes a plethora of attractive choices for formatting chart data markers, pattern fills are not among them. Here are links to two articles that show how to apply pattern fills to your chart elements.

The first article from Microsoft Excel Team Blog guides you to the Visual Basic Immediate Window to apply the patterns. Then the author also describes the process for accessing and installing an Add-in to modify the gallery to include pattern fills. The blog is at:

http://blogs.msdn.com/excel/archive/2007/11/16/chart-pattern-fills.aspx

Here is a link to another article by Andy Pope that addresses the way to fix the problem with an add-in.

http://andypope.info/charts/patternfills.htm

If you scroll way to the bottom of the article, you will see two links. One takes you to the free download for the add-in; the other has instructions for installing the add-in.

Using Excel’s Text to Speech Feature

Everyone who has used a computer for analytical purposes has at one time heard the expression “Garbage In – Garbage Out”.  It might have been stated, “The analysis is only as accurate as the data.”  In either case it emphasizes that the calculations in Excel are only as reliable as the data that was entered in the first place. The data entry process is vulnerable to errors caused by tedium, repetition and perhaps similarity of entries. 

When it is necessary to enter large amounts of data, it is good practice to have the data checked for accuracy.  This can be done immediately by the person entering the data, or entries can be verified by a different person.  Wouldn’t it be convenient to have a tool that could provide immediate feedback of the data entered? 

Excel provides just such a tool that enables a quick and easy double-check of entries before you continue working with the data. It is the Text to Speech feature. Through this tool, Excel can read back to you what you have typed while you check that audio against your original data. (more…)

Grouping Worksheets in Excel

In a recent training session on Excel basics, one panicked participant reported that she was suddenly unable to access or use any tools on the Insert tab of the ribbon. The majority of the icons there were gray. When the workshop assistant arrived on the scene, the participant and the assistant discovered the reason for the problem by looking at the top of the Excel window. The status of the worksheet displayed in the title bar:

ExcelGroupedSheets
 
(more…)

Get Training Tips via RSS Subscribe to Entries feed or Read Comments via RSS Subscribe to Comments feed.