Tag Archives: Excel

Formula AutoComplete in Excel

Would you like to enter an Excel function in a cell without typing the entire function name? It is easy to do if you are familiar with the Formula AutoComplete feature in Excel.

To use this feature, click to select the cell in which you wish to enter the function. Type an equal sign and begin to type the function name. As you begin typing the function name, a list of functions appears below the active cell. The functions listed match the letters you have typed. Continue typing until you see the function you want.

Then, double-click the name of the function you want to use. The function and its arguments appear in a ScreenTip below the cell; you can use the ScreenTip as a guide to enter the necessary function arguments.

Give it a try! FormulaAutocomplete is another way to help you get your spreadsheets completed quickly and accurately.

______________________________________________________________________

For more information on the Business Office Systems and Support department, contact Becky Jones, Associate Dean, bjones@dcccd.edu 972-238-6215.


Want to Quickly Resize all Columns on an Excel Worksheet?

Sometimes when you enter numbers into a cell on your worksheet, Excel returns ####### instead of the number that you typed.   When entering text into a cell, sometimes the text is truncated (not completely visible).  In both instances the problem is that the column is not wide enough to display your entry.

There is a very simple way to correct your entire worksheet (in three clicks) using a feature called Auto Fit.  Click the SELECT ALL Button in the top left corner of the worksheet between A and 1.

Go to the COLUMN BORDER between any of the columns and double-click. All of your columns will be resized instantly.  To be assured that all of your worksheet data will print, you should always perform this simple task before printing a worksheet.  It will take less than 5 seconds!

______________________________________________________________________

For more information on the Business Office Systems and Support (BOSS) department, contact Becky Jones, Associate Dean, bjones@dcccd.edu 972-238-6215.


Leading Zeros in Microsoft Excel

For those of you who use the database features in Excel, leading zeros may present a problem. Let’s say you are entering names and addresses and in the Zip Code field there is a zip code beginning with a zero (05678).

Excel will not by default display a leading zero; when you key 05678 in a cell and press enter, Excel will display the number as 5678. The best way to correct this problem is to create a custom number format for all the cells in which you might be entering numbers beginning with a zero.

To create a custom number format, select all of the cells in which you will be entering numbers that might begin with a zero. Right click on the selection and choose Format Cells. On the Number tab, choose Custom under Category.

In the Type field, clear any displayed information (General, for instance) and enter a zero for every digit – including zeros – you want to show in the selected cell or cells. For example, if you wanted to enter 05678 you would enter 00000 in the Type field. Then all you need to do is click OK—a very simple solution to a one of those frustrating Excel situations!

For additional information or to enroll in an Excel course, contact Becky Jones at 972-238-6215 or bjones@dcccd.edu.


Use Excel 2007/2010’s Table Feature to Help You Quickly Calculate, Format, and Sort Data

Have you ever needed to make an Excel spreadsheet look good in a hurry? Well, Excel’s table feature (introduced in the 2007 version) can help you get that data computed, sorted, and formatted in just five easy steps.

Here’s how the data initially looked on the left. Notice, the data has not been organized on any fields, and the format is very bland. Look at the difference once the table feature has been applied! (Click on the image to enlarge)

Use Excel's Table Feature To Make A Difference!

To give your spreadsheet data a more professional appearance, simply: 

  1. Select the data range and then click the Insert tab. ***IMPORTANT***Be sure there are no blank rows between your column headings (fields) and your actual data.
  2. Click the Table icon from the Table Group to determine your range and then click OK
  3. Select from one of the other 60 pre-designed table styles, if you don’t want to use the default table style.
  4. Click the Total check mark in the Table Styles Options Group and then scroll to the last row in the table and click the drop-down arrow to the right of the column(s) you want summarized (Count, Sum, Average, Min, etc.). In our example, we will include a total (SUM) for 1st Quarter Sales and take a count (COUNT) of the sales reps by last name.
  5. Sort the data on specific fields. In our example, we will sort the data by last name (LName field) by clicking on the arrow for that field and select the Sort A to Z option.

For more information, contact Becky Jones, Associate Dean, bjones@dcccd.edu, 972-238-6215