Five Quick Solutions!

I am reasonably sure that all of us would like to be more productive, especially if it requires no additional effort. Often, this entails being aware of a Quick Solution to the situation on which you are working.

Whether you are employed in Human Resources, Analysis, Marketing, or another area, we can always use a quick solution at times. The following are 5 of my All-Time General Favorites:

1. Format a Number as SSN: Go to Format Cells / Custom and use the custom format code 000-00-0000 (Timesaver for HR…)

2. Remove Grid Lines from Worksheet: Go to View / Show and Uncheck Gridlines (Great for making a professional report…)

3. Freeze Rows on Top: Select row and go to View / Window / Freeze Panes (Improves functionality of your worksheet…)

4. Transpose Range of Cells: Simply copy the cells, go to the new location, and press Alt+ese (Voila! Rows to Columns or Columns to Rows!)

5. Protect Your Workbook: Go to Review / Changes / Workbook and assign a password (Excellent way for preventing unwanted changes by “creative” colleagues)

Make your life a little easier and take advantage of a Quick Solution whenever you can!

Page Break Control

Although most of us would like to have a truly Paperless world, that day hasn’t come yet, and probably won’t for many years. In the meanwhile, even the savviest Excel Enthusiast may need to Print a worksheet from time to time.

So, let me ask you something: Have you ever printed an Excel worksheet and not gotten the results you wanted? For instance, some of your data on the right side (perhaps just a couple of columns) may print on its own page. If your intent is to create an easily-referenced document to distribute in a meeting, this can be a Real Drag!

Now, by resizing your fonts and row/column widths, you can use Trial and Error to get your print the way you want it. There is, of course, a far better way. Simply go to View / Workbook Views / Page Break Preview. In this mode, you can easily exercise maximum control with a minimum of effort!

In Page Break Preview you get a view of your worksheet with page breaks shown as Blue Lines. Initially, the lines will be Dashed, indicating Automatic page breaks. By hovering you mouse pointer over one of the lines, you can select it and move it wherever you wish. The lines will then become Solid Blue, indicating Manual page breaks (i.e. those that you have set).

This can be particularly useful when you have just a few columns or rows that don’t initially get onto the printed page you want to present. A Small Word Of Caution, however: It may be tempting to manually slide your Page Breaks to include a great deal of columns or rows, so that you can have an Elegant One-Page Handout for your meeting. Whereas you will, indeed, get all of your data printed on one page, your audience may need strong Magnifying Glasses to read it!

Page Break Control; Another tool that enables you to have Excel do things Your Way!

(You may even have time for another cup of coffee before the meeting…)

The Surprising Ease of Database Functions

This week we are going to revisit what is typically referred to as an “Advanced Excel” function. The word “Advanced” can make it sound a bit scary, but it is, in fact, really quite Easy To Use. Database Functions are particularly useful in creating Interactive worksheets. The more commonly used of these special functions are DSUM, DAVERAGE, and DCOUNT.

A good place to start is to look at the DCOUNT database function, since once you know how to use this, the others will be simple additions to your Excel Arsenal. DCOUNT counts the cells that contain numbers in a database that match conditions that you specify, and has the following syntax, (Note that if Field is omitted, DCOUNT counts all records in the database that match the criteria):

DCOUNT(Database, Field, Criteria)

Let’s say that you have a database of 5,000 records and 4 Columns (or “Fields”), and your 4 columns have the following headers in cells A1:D1

Month, SalesPerson, Product, SalesValue

Refering to the Syntax,  DCOUNT(Database, Field, Criteria):

Database is your entire range, with the first row of the database containing labels for each column.

Field refers to which column is used in the function. Enter the column label enclosed between double quotation marks, such as "SalesPerson" or "SalesValue," or a number (without quotation marks) that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so forth.

Criteria is the range of cells that contains the conditions that you specify. You can use any range for the criteria argument, as long as the argument includes a column label.

Now, let’s say that you want to count all sales for Smith which were greater than $1,000. Here is what you do:

Create a Criteria block in cells F1:I2 like the following illustration:

Then go to an open cell (e.g. F4) and put the following formula in place: =DCOUNT(A1:D5001, “SalesValue”, F1:I2)

Bamm! That is all there is to it! Try it out sometime, and you will be amazed at the new Muscle it will give you in Excel.  And not only that, it's Easy!

Working with CSV Files

CSV files are widely used for data storage across many programs, so it is likely that you will need to work with them in Excel occasionally. CSV stands for Comma-Separated Values, and stores data (numbers and text) in Plain-Text form.

There are many programs that support some variation of CSV as an alternative import/export format. You may need to transfer information from a database program that stores data in a proprietary format, to an Excel workbook where it can be analyzed. The good news is that you can use innate tools in Excel that can import this data, and you can then crunch numbers until your heart is content!

So How Do You Do This?

First of all, you need to make sure the source data is a TXT file. As mentioned, most data-gathering software can be exported to this format, so there is no problem there. Even Microsoft Word can be easily save as a Plain Text file which accomplishes your aims precisely.

Then it is on to Excel Where You can do the Following:

1. Open the worksheet you wish to import data into and click on the Data ribbon
2. Click From Text from the Get External Data group
3. Choose the text file that contains the data you wish to import
4. Click the Import button and then check Delimited
5. Noting the Data preview, choose the Delimiters that that apply to your data
6. The Delimiters many be Tab, Semicolon, Comma, or Space
7. Click Next / Finish / OK

Your data will then be imported into your worksheet in a standard format that you can go on to manipulate and analyze.

That’s all there is to it! Knowing how to use this indispensable tool will enable you do use the familiarity of Excel to work on data generated by other programs. How cool is that!