• Home
• Blog
• Downloads
• Info
• Reviews
• Virus Warnings
• Search Site

Conditional Formatting in Excel

Microsoft Excel has a "conditional formatting" feature that allows one to change the formatting of cells based on their contents. E.g., you can specify that the background color for a cell or the font color be changed based on the current value of a cell.

[ More Info ]

[/os/windows/office/excel] permanent link

Calculating the Number of Days Between Two Dates in Excel

The datedif function can be used in Microsoft Excel to calculate the number of days between 2 different dates.

The syntax for the DATEDIF function is as follows:

=DATEDIF(Date1, Date2, Interval)

Where:

Date1 is the first date,
Date2 is the second date,
Interval is the interval type to return.

If Date1 is later than Date2, DATEDIF will return a #NUM! error. If either Date1 or Date2 is not a valid date, DATEDIF will return a #VALUE error.
The Interval value should be one of the following:

Interval	Meaning	Description
m	Months	Complete calendar months between the dates.
d	Days	Number of days between the dates.
y	Years	Complete calendar years between the dates.
ym	Months Excluding Years	Complete calendar months between the dates as if they were of the same year.
yd	Days Excluding Years	Complete calendar days between the dates as if they were of the same year.
md	Days Excluding Years And Months	Complete calendar days between the dates as if they were of the same month and same year.

If Interval is not one of the items listed in above, DATEDIF will return a #NUM error.

Examples of datedif usage

[/os/windows/office/excel] permanent link

Importing Data from an Access Database in Microsoft Excel

You can import data from a Microsoft Access database in Microsoft Excel 2007, even if you don't have Microsoft Access on the system. To do so, take the steps below:

1. Open Excel.
2. Create a new blank workbook or open an existing one that you wish to use.
3. Click on Data.
4. Select Import External Data.
5. Select Import Data.
6. Browse to the location of the Access .mdb file
7. You will then be prompted to select the table in the Access database that you wish to import into an Excel spreadsheet. Select the table you wish to use by clicking on it and then clicking on OK.
8. An Import Data window will open asking you where you wish to place the data in the Excel spreadsheet. The default location will be column A row 1. If that is where you want the data to go, click on OK, otherwise specify the location you wish to use.

   

References:

1. Import Microsoft Access Data Into Excel
   Mysticgeek's Realm :: Your Guide Through the Cyber Galaxy!

[/os/windows/office/excel] permanent link

Excel Password Protection

For encrypting Excel workbooks with a password, use the following technique. Note: these steps apply to Office 2003 and 2007; for other versions the exact steps may vary.

1. From the Excel menu select "File" or, if you are using Office 2007, click on the Office Button at the top left corner of the Excel window.
2. Select "Save As"
3. From the "Save As" window, select "Tools"
4. Select "General Options"
5. Specify a "Password to open"
6. Click on "OK" to save the file.

A password is now required to open and view the file.

[/os/windows/office/excel] permanent link

Capitalizing the First Letter of Names with Excel

I needed to take a Comma-Separated Values (CSV) file that contained a column of names, with all of the letters in upper case, e.g. "JOHN SMITH", and convert the names to a form where only the first letter of the first name and the first letter of the last name remained in upper case.

I opened the .csv file in Excel and used the proper() function to perform the conversion. Excel provides 3 functions, upper, lower, and proper to change the case of text.

Example: JOHN Smith is in cell A1 of an Excel worksheet

=UPPER(A1) will change the text to all uppercase, i.e. JOHN SMITH

=LOWER(A1) will change the text to all lowercase, i.e. john smith

=PROPER(A1) will change the case to suit a proper name, i.e. John Smith

There were about 1,500 entries in the worksheet, so I didn't want to type a formula in each cell. Instead, I created another column immediately to the right of the one containing the name by clicking on Insert then Columns in Excel. The first cell containing a name was B2. In C2, I put the formula =proper(b2). Then I clicked in the C2 cell to select it. I then held the left mouse button down and extended the highligted area down to the last row containing a name. Since the last such row was row 1482, I highlighted cells C2 through C1482. When I had all of the cells highlighted, I released the mouse button. I then hit Ctrl-D to copy the formula down through all of the higlighted cells (the formula is automatically incremented as it is copied). That resulted in the following formulas in column C.

Cell	Formula
B2	=PROPER(B2)
B3	=PROPER(B3)
...	...
B1481	=PROPER(B1481)
B1482	=PROPER(B1482)

I then resaved the file in csv format and closed it. I then reopened it in Excel. Since it was saved in CSV format, column C had the names with the correct capitalization now without any formula attached to them. I then deleted column B, which had the names in all capital letters. I then resaved the file.

Note: If you have a name in the form "JOHN S SMITH III", proper will convert the name to "John S Smith Iii", so you may need to scan through the list of names for such instances.

References:

1. Properly Capitalize Text with "Proper"
   Lega Andrew - law for the rest of us
2. text case conversion in excel
   Excel Lesson (97 and 2002)
   CastleJB.com

[/os/windows/office/excel] permanent link

Counting Non-Blank Cells in a Spreadsheet and Using Multiple Criteria for Counting

Either the counta or countif functions can be used to count the number of cells that are not blank in a spreadsheet.

E.g., suppose you have the following information in a spreadsheet

	A	B
1	apple	maple
2		oak
3	plum	birch
4		birch
5	apricot

The function counta(A1:A5) would count those cells that aren't empty in cells A1 to A5 yielding 3. The function countif(B1:B5,"*") would also count those cells that contain text in cells B1 to B5, yielding 4 as well.

Note: counta will count cells that have formulas in them even though those formulas may amount to nothing appearing in the cell, i.e. they evaluate to "".

If you wanted to count the number of cells that contained specific text, e.g. birch, you could use countif(B1:B5,"birch"), which would yield 2. The count is case insensitive, i.e., if one of the cells contained "BIRCH", instead of "birch", the count would still be equal to two.

If you wanted to count the number of cells that contained "birch" in column B and "plum" in column A, then countif won't work, since you can only specify one criteria with it. You could use sum instead. E.g. sum((B1:B5="birch")*(A1:A5="plum")), which yields 1. The * in this case "ands" the two conditions. Note: You have to hit the Ctrl, Shift, and Enter keys simultaneously when you've typed the formula in order to enter it. If you don't, you will see a #value appear in the cell. This type of formula is considered to be an "array" formula or CSE formula, since it requires the Ctrl-Shift-Enter keys to be hit to enter it. When it is entered, you will see {} appear around the formula.

References:

1. Excel -- Worksheet Functions -- Count Cells
   Last updated: November 11, 2006
   Contextures -- Microsoft Office Consulting
2. Excel Developer Tip: Summing and Counting Using Multiple Criteria
   Excel Developer Tip Archives

[/os/windows/office/excel] permanent link

Creating a Watermark on an Excel Spreadsheet

I wanted to put a watermark of "Paid" on a one-page Excel 2000 spreadsheet I had created as an invoice form. I found instructions on how to do so at Print a Watermark on Excel Worksheet. To do so, take the following steps in Excel with the spreadsheet open.

1. Click on View.
2. Select Page Break Preview.
3. When you see the "You can adjust where the page breaks are by clicking and dragging them with your mouse" window, click on the OK button.
4. Click on the WordArt button, which is represented by a blue "A" tilted slightly to the right, in the Drawing toolbar at the bottom of the Excel window. If you don't see this toolbar, click on View, select Toolbars and check Drawing by selecting it.
5. When prompted to pick a WordArt style, pick the one at the top left corner of the rows of different styles, which will give you a simple outline style.
6. Click on OK.
7. Pick the font and the size you want for it or accept the default values, then type the text you want in the "Text" field, e.g. "Paid" to have that word as the "watermark".
8. Click on OK.
9. You will then see the word appear as an outline off to the right of the spreadsheet with small sqaures along the edge of the text that serve as handles where you can grab the text. You can click in the middle of the text to grab it and then hold down the left mouse button and drag it where you want to place it on the spreadsheet. You can also grab the text at one of the squares along one of the sides with the cursor and stretch it to make it larger, if you wish. On the WordArt toolbar there is also a "Free Rotate" button, which is represented by a curved arrow. You can use it to rotate the text by clicking on that button and then grabbing one of the corners of the text.
10. The watermark may obscure some of the text on the spreadsheet when you've placed it where you want it. To fix that problem, right click on the watermark text and choose "Format WordArt". You can change the color of the fill and the line color. I left the fill color as white, but checked "semitransparent" and changed the line color to gray.

    

11. You can go to View and select Normal View now, if you want to better see how the page looks with the watermark on it.
12. If you only have a one-page spreadsheet, you are finished. If you have multiple pages, right-click on the watermark, choose Copy. Then go to other pages and choose Edit and Paste.

[/os/windows/office/excel] permanent link