Excel Digest

  Excel help for the rest of us

Archive for May, 2008

29 May

How to collect and paste multiple items

The Microsoft Office Clipboard allows you to collect text and graphic items from any number of Office documents or other programs and then paste them into any Office document. Examples are Microsoft Excel, Microsoft Word and Microsoft PowerPoint.
To use the Office Clipboard you must open it in the task pane of an Office program.

On the [...]

25 May

Random Lookup Revisited

In a previous post I explained how to perform random lookup based on the VLOOKUP function. The formula was:
=VLOOKUP(INT(RAND()*10+3),P12:Q23,2,FALSE)
I explained how I used the RAND() function to generate values between 1 and 12 for the first parameter of the function (red color). However there is another direct function which generates random numbers within a given [...]

21 May

Random lookup based on VLOOKUP function

A few months ago I designed a monthly calendar template in Excel. One of my requirements was to pick a proverb randomly from a list and show it in the calendar. This is how I managed to do it:
I first created the list of proverbs as shown below.

This is the calendar template

To pick the proverb [...]

18 May

How to format a cell based on the value of another cell (conditional formatting)

Select the cells for which you want to add conditional formatting.
On the Format menu, click Conditional Formatting.
The Conditional Formatting dialogue will open.

In the first combo box select Formula is.
In the text box next to it [...]

15 May

How to use the VLOOKUP Function

The VLOOKUP function (short for Vertical Lookup), searches for a value in the first column of a table array and returns the corresponding value in the same row from another column in the table array.
Syntax

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Lookup_value:The value to search for in the first column of the table array.
Table_array:Two or more columns of data. The [...]

11 May

Function to return the day of the week in text (Name of the day)

Excel has a built in function which returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default, or 1 (Monday) to 7 (Sunday) if you use a return type of 2. But there is no direct function to return [...]

07 May

How to protect or unprotect cells in a worksheet

In a previous post we talked about how to protect worksheet elements. When you protect a worksheet, by default all its cells will be locked.
If you want to protect or unprotect some cells selectively then do the following:

Select the cells you want to protect / unprotect.
In the main menu click Format.
Click Cells. The Format Cells [...]

03 May

How to protect Worksheet elements

To prevent unauthorized users from performing certain worksheet tasks, like formatting cells, deleting rows or inserting rows, follow these steps:

Switch to the worksheet you want to protect.

On the Tools menu, point to Protection, and then click Protect Sheet.
Type a password for the sheet. Then reconfirm [...]

© 2008 Excel Digest | Entries (RSS) and Comments (RSS)

GPS Reviews and news from GPS Gazettewordpress logo