Indiana University: IT Training Tips Blog

A question arose in a recent Access Forms workshop about a form that was being designed to capture information supplied by library patrons. To that point in the workshop, we had created several forms; but in each case when the form was opened we saw record 1 of the data set. We saw that we could use the New Record icon to set the form fields to blank when we wanted to enter new data. But in this case, the database owner wanted the form to open so that what the user sees immediately is a form with blank fields ready for a new record to be entered. The solution simply involves setting properties of the form as follows:

1. With the Form in Design view or Layout view, open the Property Sheet and be sure you are looking at the properties of the Form
2. To most easily find the properties you need, choose the Data tab at the top of the Property Sheet
3. Find the Data Entry property and set it to Yes
4. Also be sure the Allow Additions property is set to Yes

(more…)

Sometimes a column heading in Excel seems about a mile wider than any of the entries below it in the column. In the graphic below, the Column D heading, Years With Company is 18 characters wide while all the entries below it will be only one or two characters. In many such cases the goal is to keep the column as narrow as possible. Let’s look at some techniques for positioning the text – using word wrap and alignment.

The text in cell D1 is much wider than that in other cells in column D

(more…)

When you import large data sets into Excel, some columns of text may not display in the case you would prefer. For example, you could have a column of names that are all upper case where you want only the first letter of the name to be upper case. Or you might have a list of model IDs that are lower case where you expected upper case. There are three functions for working with text that might be just the tools you need to improve the appearance of your data.

The functions are UPPER, LOWER and PROPER. This function, =UPPER(A1) will convert the text in cell A1 to all upper case. Similarly, the function =LOWER(A1) converts all the text in cell A1 to lower case. The PROPER function converts just the first letter of the text in the designated cell to upper case.

The best way to convert a column of data is to:

1)      Back up the spreadsheet

2)      Insert a blank column to the right of the column holding the text you would like to change.

3)      In the first row of the new column place the function you need and referencing the cell where the text sits assuming here that my original text is in cell A1: =PROPER(A1)

Copy the function down the rest of the column

Have you ever inherited an Excel workbook that is so complex it seems just a maze of text and numbers? If so, the tools in the Formula Auditing group of the Formulas tab on the Excel Ribbon might be the getting-acquainted “social” you need to make that workbook your new best friend.

One first approach might be to use the Show Formulas tool. It will tell which cells contain values that were entered and which are the result of some calculation. Below, on the left, is a sheet in the default view; and, on the right, is the same sheet after clicking the Show Formulas button:

Formulas displayed in cells

(more…)

Excel makes it easy to track mountains of complex data. But just because the data is organized neatly in a spreadsheet, this doesn’t necessarily mean that the user can quickly and easily identify the intended meaning of the data set. Conditional Formatting is one of Excel’s tools that can flag specific cells, based on certain criteria, by applying various formatting techniques to help the user visualize data.
In the example I will use here, the spreadsheet lists reservations that have been received for a banquet. Tables in the banquet hall seat six persons each. Some guests have indicated that they are coming alone, and some reservations are for large groups that will have to be split up. In my data set I want to be notified when any reservation is for a single person or for a group of more than six persons so I can take extra care in accommodating those guests.

Reservation numbers

  (more…)

In a recent post on this blog, I wrote about customizing the Ribbon in Excel 2010; and I used the Camera Tool as an example of an item you might add as part of that customization. The Camera Tool can be used to watch in one workbook or worksheet values in another workbook or worksheet.

For my simple example here I will use my calculation of the number of days until the next Super Bowl as the information I want to keep watching. In my AllThingsFootball.xlsx workbook I have placed the date of the big game in cell A1: 2/5/2012 and the formula for the current date in cell B1: =today(). Then I can place the formula to subtract the two: =A1-B1 in cell C1. With the formatting for cell C1 set to Number, I can see the number of days from now to the end of the season. Each day the value in B1 will update to the current date, and the difference will automatically update in cell C1.

Now, I need to get busy and do some work in my workbook called ImportantStuff.xlsx, but I also want to continue monitoring the number of days until the championship game. I can use the Camera Tool to capture a live picture of the calculation and place it in ImportantStuff.xlsx. It creates a link to the original cells.

(more…)

Recently I decided to investigate the Camera command in Excel 2010. It provides one possibility for capturing data from various spreadsheets and viewing it all in a single spreadsheet. But before I could play with the camera, I had to find it and display it. In my installation of Excel it was hidden by default.

Customizing the Ribbon is possible from the Options window. Go to the Excel (or other Office 2010 application) Options window by choosing the File tab and clicking Options near the bottom of its menu. At the left side of the Options window, choose the “Customize the Ribbon” category. In this window proceed as follows: (more…)

The Web can be a wonderful source of information. But you can easily drag information from a web site table into Excel and squeeze even more meaning from it. I recently stumbled on a table of highest grossing movies of all time, and I was curious to know which of them had been released in 2010. Instead of scrolling through the list to search for them (quite a task since there were almost 500 titles in the list), I swooped the list into Excel so I could analyze from there. Here’s how.

First I copied the URL of the web site holding the data. Then I switched to Excel. On the Data tab of the Ribbon, I clicked the From Web icon in the Get External Data group. The New Web Query window opened providing an address field where I pasted the URL of my movie information source.

Importing Data From the Web

After I pressed Enter the web page displayed, but Excel still didn’t know which content on the page I wanted to import. The dialog box showed black and yellow arrow boxes as indicators for various elements on the page. I clicked the indicator next to my table and clicked Import at the bottom of the box. In the next dialog box I had to confirm cell A1 as my intended destination for the first cell of the table.

There was a slight pause for data retrieval, but in a few seconds the list of movies was sitting in my Excel spreadsheet ready for me to analyze. A favorite at our house from 2010, Toy Story 3, sits at No. 9 in the list!

Sometimes you encounter data that is arranged in a certain way, but you realize an alternate arrangement of rows and columns better suits the way you want to view the information. For instance, you may want to switch this arrangement:

to this:

(more…)

Excel Charts are prime examples of the old saying “A picture is worth a thousand words,” and PowerPoint presentations provide a great vehicle for passing a chart’s message on to an audience. So the combination of an Excel chart on a PowerPoint slide is a powerful way to get a point across. But what if the chart data change? How do we update the slide?

The first part of the updating answer lies in how the chart was added to the slide. Assuming the chart was created in Excel and then copied in preparation for adding it to the slide, there are basically three different ways to paste the chart – embedding the chart, linking the chart, and pasting the chart as a picture. Each of those impacts your ability to update the chart. These choices are available by clicking the drop-down arrow next to the paste icon at the lower right corner of the pasted chart:

Paste Options

(more…)