Create a Drop Down Menu in a Cell of Excel

I recently received an email from a reader asking how to add a hidden drop down menu inside a cell. I was unable to respond back to the reader (email address was invalid), but I thought it was a great question and wanted to add it to Excel Hints.  In just a couple steps, you’ll be able to add this feature to your spreadsheet and improve the validity of many spreadsheets you create.



How To Add the Hidden Drop Down Menu
 
Unlike using a drop down menu from the forms toolbar (there will eventually be a post about that), this drop down menu is not seen until that particular cell is selected.  Within your particular form you are creating, select the cell where you like the drop down menu to appear.  After selecting the cell, select Data–>Validation and a pop up box will appear ingeniously called “Data Validation”.
Under the ‘Settings’ Tab, select ‘list’ under the allow pull-down menu. Then under Source either select the range of cell you want included in the drop down menu, or type in the value with a comma to separate them.  When you are finished entering the range or numbers, hit OK.  That’s it.  Check out the screenshots below which will walk you through the steps.

In Cell Drop Down Menu Example

In this example, we have created a form where the user types in their personal information, but instead of entering an age, we want them to select an age range.  This is where an in cell drop down menu can come in handy.


















1. Select the cell where you want to place the drop down menu.













2. Select Data–>Validation from the top menu.  The box that pops up should look like this.













3. Select “list” from the Allow Menu, and enter (or select) the strings you want in your drop down menu.


















4. You should now see the drop down menu you have created with the options you entered in step 3.

















5. You’re done.  You can now use the form you just created.

Trim Functions in Excel

How many times have you tried to import a file into Excel, only to have extra blanks all over the place in the document?  Well that’s where the Trim Formula can come into play.  The Trim formula is used to clean up and remove all the leading and trailing blanks as well as all but one blank in-between text that you may have in a list of cells.  So now instead of going into each cell one-by-one, there is one formula to do all the work for you.  Let’s take a look at the formula first (it’s a short one):

=Trim(text)

This formula is very easy to use but can make you job a whole lot easier. Let’s take a look at some of the examples now.


Examples

Example 1: =trim(“  Bird  “), will return “Bird”
Example 2: =trim(“  First       Second  Third     “), will return “First Second Third”








Example 3: =trim(A2), will return “Mark Smith”
Example 4: =trim(A5), will return “Gene William Wilder”

Hopefully the few examples I listed above help you to understand how to use this formula and will save you some time the next time nothing seems to line up correctly.  If you have any other questions please leave a comment below

The scoop on Excel's counting functions.

Takeaway: Excel offers three counting functions, so it’s important to know each function’s specific purpose and expectations.

As you can imagine, Excel is all about the values. Sometimes that means complex number crunching. Often, it’s just a simple task of summing or counting. Where counting is concerned, Excel offers three functions: COUNT(), COUNTA(), and COUNTBLANK(). Regardless of your counting tasks, one of these functions should meet your needs.

COUNT()

Excel’s COUNT() function counts only the numbers in a range. This function uses the following forms:

COUNT(v1, v2[, …])

COUNT(range)

where v1 and v2 represent the literal values you want to count and range identifies a range of cells whose contents you want to count.

This function counts only numbers. Cells can contain any type of data, but the function counts only numbers, including dates. The function ignores empty cells, logical values, text, and error values.

In the following figure, the COUNT() function in cell D12, COUNT(D2:D10), returns the value 7. Although D2:D10 comprises nine cells, only seven contain valid numbers. The string N/A in D6 is a string, not a number and cell D8 is blank. The function does count 0 in cell D7.

COUNT() is straightforward. It’s easy to understand and implement, but it isn’t always adequate.

COUNTA()

When you need to count all values, not just number, use COUNTA(). This function uses the same forms as COUNT() but be careful because this function considers all values–even the ones you can’t see.

Notice that the COUNTA() function in D13 returns 9, even though D8 appears empty. Did you expect the function to return 8? Cell D8 actually contains a formula, =”", which returns an empty string. Consequently, the cell isn’t truly empty and every cell in D2:D10 contains a value. If you remove that formula, COUNTA() would return 8.

COUNTBLANK()

COUNTBLANK() counts empty cells in a specified range of cells. Unlike the other two count functions, this function takes only one form:

COUNTBLANK(range)

which makes sense—you can’t really enter a blank as an argument.

Now, here’s where things get a bit sticky. COUNTA() counts cells that contain a formula that returns an empty string. Since COUNTBLANK() is counting blanks, you might expect it to not count cells that contain formulas that return an empty string—but it does. The COUNTBLANK() function in cell D14 returns 1 because it treats the =”" formula in cell D8 as a blank cell.

Next time you need to count something, consider all three counting functions and make an informed decision!

How to Concatenate in Excel

In Excel, to concatenate means to combine cell contents.

Use the formula below (do not use any spaces) then hit enter to process it:

=concatenate(A1,B1)

Definition of concatenate (dictionary.com): to connect or link in a series or chain.

Examples

Let’s start with a 2-row database with some punctuation. We are going to merge the first 4 cells (A1:D1) into our fifth cells (E1 and E2) as shown below:

Example 1: for “Betty Boop, cartoon” (cell E1) the formula is =concatenate(A1,B1,C1,D1)

To get the space between the contents of two cells, add the space in the cell to be concatenated and not in the concatenation formula: to get the space between the comma and “cartoon” add a space after the comma in the comma cell (C1) or before “cartoon” in the “cartoon” cell (D1). Note that adding spaces in both C1 and D1 will give you 2 spaces in your concatenated result.

Example 2: for “Catherine Zeta-Jones” (cell E2) use formula =concatenate(A2,B2,C2,D2)

Note: if Excel identifies the dash as a minus sign then precede the dash by an apostrophe in the dash cell (C2), not in the formula.

You may change the content of the cells you concatenated after you process the formula and the formula will update the results automatically. For example, if you want to change “Catherine” to “Katie” just go in cell A2, delete “Catherine,” type in “Katie” and E2 will automatically change to “Katie Zeta-Jones.”

Copying and Pasting the Formula

After you write your formula and hit enter you will see the concatenation result automatically (cells E1 and E2). HOWEVER, the content of the cells is still just the formula and not what you see on your screen. Take a look at cell E1 in Image 1 above and you’ll see “Betty Boop, cartoon” but in Image 2 below I have clicked in E1 and you may see that the value of the cell is actually the concatenation formula:

Note: to get out of the formula hit Escape. Do not click into another cell as that will only add that cell to the formula (if you do this though just hit Escape).

At this point you may copy the formula to concatenate other rows of cells: click on the formula cell (E1), press Control or +C to copy the formula, select the cell where you want to paste the formula then press Control or +V to paste. In our example I entered the contents into A1:D1 manually then copied and pasted the formula in E1 into E2, which instantly showed “Catherine Zeta-Jones.”

There is no need to change the values of the formula to reflect the new cells you want to concatenate: even though the formula in E1 includes A1,B1,C1,D1, once you paste it into E2 Excel knows that you are referring to A2,B2,C2,D2. This is really useful because if you have a long list of cells to concatenate you can just paste the formula into all the destination cells and each of them will concatenate automatically.

Concatenating More Cells or Fewer Cells

Some of your data might be spread out in fewer cells or in more cell than shown so far. In the example below “John Wayne” is spread into just 2 cells but you can use the same formula you used to concatenate 4 cells since cells C3 and D3 are blank anyway:

To concatenate more than 4 cells you’ll need to add the additional cells to your formula manually by pasting the formula then double clicking into it and placing your cursor where you want the additional cells to go. In the screenshot below I pasted our formula in cell F4 and you can see how the formula used only the last four cells preceding the formula cell (B4, C4, D4 and E4). See Image 4:

To add cell A4 to the formula we double click in F4 and either type in “A4” or just click on cell A4. Do not forget to add the comma after “A4” in the formula in either case (Image 5):

Moving the Formula Cells

You may move the formula cells and keep them intact by cutting and pasting. In the example above your columns are out of line: 3 concatenated cells in column E and 1 in column F. To get all your results into column F just select and cut cells E1, E2 and E3 and paste them into F1, F2 and F3:

Getting Rid of the Formula and Keeping the Contents

Once you have finished concatenating you may get rid of the formulas and keep just the concatenated results:

Copy cell E1 then right-click in E1 and select “Paste Special” (Image 7):

In the dialog box that pops up, select “Values” then click “OK” (Image 8):

Your formula is all gone now and the value of the cell is actually what you see on your screen. In this case the value of cell E1 is now “Betty Boop, cartoon” (Image 9):

Note that once you paste the values you may only change the value of the cells manually. For example if you want to delete “, cartoon” from cell E1 you must double click in E1 then select and delete “, cartoon.”

Tip: Select a whole column or row by clicking on the letter or number header of the target column or row.

Introduction to Excel text functions - LEFT, RIGHT, MID, FIND

These four functions are your essentials for manipulating text in Excel. Let’s take a closer look…

LEFT

=left(text,num_chars)

As the name suggests, LEFT snips you a section of text starting from the left hand side. Our example in the picture above shows there are two parts; first you state which text you’re using, in this case the contents of cell B5; second you specify how many letters you want, in this case we chose 3 returning “XLC”.

RIGHT

=right(text,num_chars)

RIGHT does exactly the same as LEFT but – yep you guessed it! – starting from the right hand side. So our example shows us taking the last 5 letters from cell B5 giving “Libre”.

MID

=mid(text,start_num,num_chars)

MID takes things up a notch. It’s going to give us a number of characters from the middle of a text string, but we also need to tell it where to start. So the example above looks 3 characters along in B5, then gives you the next 3 characters “Cal”. If you changed it to =MID(B5,3,4) it would return “Cali”, whereas making it =MID(B5,4,3) would give “ali”.

FIND

=FIND(find_text,within_text,start_num)

FIND checks for one text string within another one (e.g. here we look for “Libre” within “XLCalibre”) and tells us how many characters along it appears (in this case 5). You can tell it how many characters in to start (start_num), but don’t worry about that for now, you can leave it out if you want. There are two good uses for FIND that spring to mind:

1. Is the text there? Say you want to check whether someone has used a company e-mail address or not. When the e-mail address is in cell A1, you could try this:

=FIND(“@xlcalibre.com”,A1)

If our friend Al is using his XLCalibre e-mail address, al@xlcalibre.com, this formula will return 3, as the text string “@xlcalibre.com” starts from the third character. If it can’t find the string, it will return a #VALUE! error.

2. Combine FIND with other functions to manipulate text.

So we have our list of e-mail addresses starting in A1, and we know they will all be in the format firstname@xlcalibre.com. We want to get all of the first names. We could nest the FIND in a LEFT function like this:

=LEFT(A1,FIND(“@”,A1)-1)

For our example of “al@xlcalibre.com” this will return “al” Can you see how it works? If you omit the -1 at the end it gives “al@”.

You could go further and nest this in a PROPER function to get the capitalisation right:

=PROPER(LEFT(A1,FIND(“@”,A1)-1))

This returns “Al”.

How to Create a Formula to Correctly Evaluate Blank Cells

Excel’s ISBLANK function is one of a group of “Information Functions” that can be used to find out information about a specific cell, worksheet or workbook.

The ISBLANK function's job is to check to see if a certain cell is empty or not. If the cell is empty, a value of TRUE is returned by the function.

If data is later added to an empty cell the function will automatically update and return a FALSE value.

The syntax for the ISBLANK function is:

= ISBLANK ( Value )

Value: Refers to the cell reference that is being checked.

Example Using Excel's ISBLANK Function:

Type a number, such as " 3 ", or a word of text into cell C1. 

Click on cell D1 - the location where the results will be displayed and type the syntax of isblank then select the cell i.e C1 and close the bracket.

The value FALSE should appear in cell D1 because cell C1 is not empty.

The complete function = ISBLANK ( C1 ) appears in the formula bar above the worksheet when you click on cell D1.

To return a value of TRUE in cell D1, delete the data in cell C1 or change the cell reference in the function to a cell that is empty.

Excel Your Skill In Excel Vol - I

Hi Friends,

Want to work in excel smartly & fun... Just to make you more comfortable i am about to share daily tips which may help you to excel in excel..

Happy learning...!! Lets start with basic formatting.

Simple formatting tricks

 

To change a cell from all upper or lower case to proper case,where the first letter of each word is capitalized :


Syntax = Proper(cell)

e.g =PROPER(A2)



To change a cell so all of the letters appear in upper case:

Syntax =Upper(cell)

e.g: =UPPER(A2)



To change to all lower case:

Syntax =Lower(cell)

e.g: =LOWER(A2)

Formatting Spreadsheets using AutoFormat

Spreadsheets usually contain a lot of data. This can make it difficult, if not impossible for users to find the information they are looking for. Formatting a spreadsheet effectively can change that. A good choice of background color, font style and font size, plus other formatting options can make the most important information on the spreadsheet stand out from the rest of the data.
If you are new to Excel, and not yet comfortable with all the formatting options available, you can use the AutoFormat option to give your spreadsheets a professional look and make them easy to read.
There are 17 AutoFormat styles available. These styles affect six main formatting areas: number formatting, borders, fonts, patterns and background color, alignment, and column and row size. 

Choose an Appropriate Style
 
Not all the styles are appropriate for all spreadsheets. Keep in mind how the spreadsheet will be used when selecting an AutoFormat style:

• Printed off and given to the audience as a hand out.
  
• Used solely in a slide show presentation.
  
• Emailed to just a few associates.

Some of the styles are very "graphic intensive" and would not print well, and some are just not appropriate for business reports. Be sure to choose a style in keeping with your subject and your audience. If none of the styles are quite to your liking, you can modify them either before or after they have been applied to your worksheet. To modify a style before applying it, click on the options button in the AutoFormat dialog box. Doing so, will allow you to deselect any of the six formatting areas such as font, borders, or alignment before applying the style. The spreadsheet example in the dialog box will show you what the style will look like with the different options deselected. 

Fine-Tune Your Style Choice
 
Once you have applied a style you can modify it further using Excel’s regular formatting options located under the Format menu and on the formatting toolbar. Many people use the AutoFormat options as a starting point and then use the regular formatting options to fine-tune their selection so that it meets their needs exactly. 

To apply an AutoFormat style to your worksheet

1. Select only the data on the worksheet that you want formatted.
2. Choose Format > AutoFormat from the menus to bring up the AutoFormat dialog box.
3. Scroll through the list of styles.
4. Click on the one you want to use.
5. Click OK.

Mozilla Firefox Shorcuts

Mozilla Firefox shortcut keys

Below is a listing of all the major shortcut keys in Mozilla Firefox. See the computer shortcut page if you are looking for other shortcut keys used in other programs.

Shortcut Keys	Description
Alt + Home	Open your home page.
Alt + Left Arrow	Back a page.
Alt + Right Arrow	Forward a page.
F5	Refresh current page, frame, or tab.
F11	Display the current website in full screen mode. Pressing F11 again will exit this mode.
Esc	Stop page or download from loading.
Ctrl + (- or +)	Increase or decrease the font size, pressing '-' will decrease and '+' will increase. Ctrl + 0 will reset back to default.
Ctrl + Enter	Quickly complete an address. For example, type computerhope in the address bar and press CTRL + ENTER to get http://www.computerhope.com.
Ctrl + Shift + Del	Open the Clear Data window to quickly clear private data.
Ctrl + D	Add a bookmark for the page currently opened.
Ctrl + I	Display available bookmarks.
Ctrl + J	Display the download window.
Ctrl + K or Ctrl + E	Move the cursor to the search box.
Ctrl + N	Open New browser window.
Ctrl + P	Print current page or frame.
Ctrl + T	Opens a new tab.
Ctrl + F4 or Ctrl + W	Closes the currently selected tab.
Ctrl + Shift + T	Undo the close of a window.
Ctrl + Tab	Moves through each of the open tabs.
Spacebar	Moves down a page at a time.
Shift + Spacebar	Moves up a page at a time.
Alt + Down arrow	Display all previous text entered in a text box and available options on drop down menu.

General Keyboard shortcuts - Windows

The General Shortcuts

We’ll kickoff the list with some really general shortcuts that you often used.

• CTRL+C (Copy)
• CTRL+X (Cut)
• CTRL+V (Paste)
• CTRL+Z (Undo)
• Delete (Delete)
• Shift+Delete (Delete the selected item permanently without placing the item in the Recycle Bin)
• CTRL while dragging an item (Copy the selected item)
• CTRL+Shift while dragging an item (Create a shortcut to the selected item)
• F2 key (Rename the selected item)
• CTRL+RIGHT ARROW (Move the insertion point to the beginning of the next word)
• CTRL+LEFT ARROW (Move the insertion point to the beginning of the previous word)
• CTRL+DOWN ARROW (Move the insertion point to the beginning of the next paragraph)
• CTRL+UP ARROW (Move the insertion point to the beginning of the previous paragraph)
• CTRL+Shift with any of the arrow keys (Highlight a block of text)
• Shift with any of the arrow keys (Select more than one item in a window or on the desktop, or select text in a document)
• CTRL+A (Select all)
• F3 key (Search for a file or a folder)
• Alt+Enter (View the properties for the selected item)
• Alt+F4 (Close the active item, or quit the active program)
• Alt+Enter (Display the properties of the selected object)
• Alt+Spacebar (Open the shortcut menu for the active window)
• CTRL+F4 (Close the active document in programs that enable you to have multiple documents open simultaneously)
• Alt+Tab (Switch between the open items)
• Alt+ESC (Cycle through items in the order that they had been opened)
• F6 key (Cycle through the screen elements in a window or on the desktop)
• F4 key (Display the Address bar list in My Computer or Windows Explorer)
• Shift+F10 (Display the shortcut menu for the selected item)
• Alt+Spacebar (Display the System menu for the active window)
• CTRL+ESC (Display the Start menu)
• Alt+Underlined letter in a menu name (Display the corresponding menu)
• Underlined letter in a command name on an open menu (Perform the corresponding command)
• F10 key (Activate the menu bar in the active program)
• RIGHT ARROW (Open the next menu to the right, or open a submenu)
• LEFT ARROW (Open the next menu to the left, or close a submenu)
• F5 key (Update the active window)
• Backspace (View the folder one level up in My Computer or Windows Explorer)
• ESC (Cancel the current task)
• Shift when you insert a CD-ROM into the CD-ROM drive (Prevent the CD-ROM from automatically playing)

Dialog Box Keyboard Shortcuts

• CTRL+Tab (Move forward through the tabs)
• CTRL+Shift+Tab (Move backward through the tabs)
• Tab (Move forward through the options)
• Shift+Tab (Move backward through the options)
• Alt+Underlined letter (Perform the corresponding command or select the corresponding option)
• Enter (Perform the command for the active option or button)
• Spacebar (Select or clear the check box if the active option is a check box)
• Arrow keys (Select a button if the active option is a group of option buttons)
• F1 key (Display Help)
• F4 key (Display the items in the active list)
• Backspace (Open a folder one level up if a folder is selected in the Save As or Open dialog box)

Microsoft Natural Keyboard Shortcuts

• Win (Display or hide the Start menu)
• Win+BREAK (Display the System Properties dialog box)
• Win+D (Display the desktop)
• Win+M (Minimize all of the windows)
• Win+Shift+M (Restore the minimized windows)
• Win+E (Open My Computer)
• Win+F (Search for a file or a folder)
• CTRL+Win+F (Search for computers)
• Win+F1 (Display Windows Help)
• Win+ L (Lock the keyboard)
• Win+R (Open the Run dialog box)
• Win+U (Open Utility Manager)

Accessibility Keyboard Shortcuts

• Right Shift for eight seconds (Switch FilterKeys either on or off)
• Left Alt+left Shift+PRINT SCREEN (Switch High Contrast either on or off)
• Left Alt+left Shift+NUM LOCK (Switch the MouseKeys either on or off)
• Shift five times (Switch the StickyKeys either on or off)
• NUM LOCK for five seconds (Switch the ToggleKeys either on or off)
• Win +U (Open Utility Manager)

Windows Explorer Keyboard Shortcuts

• END (Display the bottom of the active window)
• HOME (Display the top of the active window)
• NUM LOCK+* (Display all of the subfolders that are under the selected folder)
• NUM LOCK++ (Display the contents of the selected folder)
• NUM LOCK+- (Collapse the selected folder)
• LEFT ARROW (Collapse the current selection if it is expanded, or select the parent folder)
• RIGHT ARROW (Display the current selection if it is collapsed, or select the first subfolder)

Shortcut Keys for Character Map

• After you double-click a character on the grid of characters, you can move through the grid by using the keyboard shortcuts:
• RIGHT ARROW (Move to the right or to the beginning of the next line)
• LEFT ARROW (Move to the left or to the end of the previous line)
• UP ARROW (Move up one row)
• DOWN ARROW (Move down one row)
• PAGE UP (Move up one screen at a time)
• PAGE DOWN (Move down one screen at a time)
• HOME (Move to the beginning of the line)
• END (Move to the end of the line)
• CTRL+HOME (Move to the first character)
• CTRL+END (Move to the last character)
• Spacebar (Switch between Enlarged and Nor mal mode when a character is selected)

Microsoft Management Console (MMC) Main Window Keyboard Shortcuts

• CTRL+O (Open a saved console)
• CTRL+N (Open a new console)
• CTRL+S (Save the open console)
• CTRL+M (Add or remove a console item)
• CTRL+W (Close window/tab)
• F5 key (Update the content of all console windows)
• Alt+Spacebar (Display the MMC window menu)
• Alt+F4 (Close the console)
• Alt+A (Display the Action menu)
• Alt+V (Display the View menu)
• Alt+F (Display the File menu)
• Alt+O (Display the Favorites menu)

MMC Console Window Keyboard Shortcuts

• CTRL+P (Print the current page or active pane)
• Alt+- (Display the window menu for the active console window)
• Shift+F10 (Display the Action shortcut menu for the selected item)
• F1 key (Open the Help topic, if any, for the selected item)
• F5 key (Update the content of all console windows)
• CTRL+F10 (Maximize the active console window)
• CTRL+F5 (Restore the active console window)
• Alt+Enter (Display the Properties dialog box, if any, for the selected item)
• F2 key (Rename the selected item)
• CTRL+F4 (Close the active console window. When a console has only one console window, this shortcut closes the console)

Remote Desktop Connection Navigation

• CTRL+Alt+END (Open the m*cro$oft Windows NT Security dialog box)
• Alt+PAGE UP (Switch between programs from left to right)
• Alt+PAGE DOWN (Switch between programs from right to left)
• Alt+INSERT (Cycle through the programs in most recently used order)
• Alt+HOME (Display the Start menu)
• CTRL+Alt+BREAK (Switch the client computer between a window and a full screen)
• Alt+Delete (Display the Windows menu)
• CTRL+Alt+- (Place a snapshot of the active window in the client on the Terminal server clipboard and provide the same functionality as pressing PRINT SCREEN on a local computer.)
• CTRL+Alt++ (Place a snapshot of the entire client window area on the Terminal server clipboard and provide the same functionality as pressing Alt+PRINT SCREEN on a local computer.)

Internet Explorer navigation

• CTRL+B (Open the Organize Favorites dialog box)
• CTRL+E (Open the Search bar)
• CTRL+F (Start the Find utility)
• CTRL+H (Open the History bar)
• CTRL+I (Open the Favorites bar)
• CTRL+L (Open the Open dialog box)
• CTRL+N (Start another instance of the browser with the same Web address)
• CTRL+O (Open the Open dialog box, the same as CTRL+L)
• CTRL+P (Open the Print dialog box)
• CTRL+R (Update the current Web page)
• CTRL+W (Close the current window)