Creating a Dropdown List in Excel for Mac 2011

 

In this tutorial, we will learn how to create a dropdown list in Excel for Mac 2011.  This is useful if you have a list of items that you input frequently and would like to choose from a dropdown list instead of the standard method of typing in the data.

For our example, we will be keeping a spreadsheet of statistics for a sports team and we need a dropdown list for the result of games.  Our options are Win, loss or Tie.  I will show you two methods of creating the list.

Here is our spreadsheet:

dropdown list1 Creating a Dropdown List in Excel for Mac 2011

 

 

 

In column D, we want to be able to choose Win, Loss or Tie from a dropdown list.  Let’s create the list.  What we will do is select some cells in column D.  Let’s go down as far as D10.

Screenshot 2014 01 23 13.01.13 Creating a Dropdown List in Excel for Mac 2011

 

 

 

 

 

 

 

Now we will go to our Ribbon and click on Data, then click on Validate.  The data validation window will now appear.

Screenshot 2014 01 23 13.01.55 Creating a Dropdown List in Excel for Mac 2011

 

 

 

Under the validation criteria area we will click on the dropdown list under the heading Allow and we will choose List.

Screenshot 2014 01 23 13.03.55 Creating a Dropdown List in Excel for Mac 2011

 

 

 

 

 

 

 

 

 

The Source box now becomes available.  In this box we will type our list options separated by a comma.  Then we will click OK.

 

Screenshot 2014 01 23 13.06.28 Creating a Dropdown List in Excel for Mac 2011

 

 

 

 

 

 

 

 

 

Let’s fill in some data in the first row.  We will say that our team won this game 4-1. When we get to the result column, we’ll click on the dropdown arrow in the cell then select Win as our result.

Screenshot 2014 01 23 13.09.39 Creating a Dropdown List in Excel for Mac 2011

 

The other way we can create a dropdown list is to create the list from an already existing list in the spreadsheet.  Let’s enter our Win, Loss and Tie options into cells, then highlight the cells that you want to apply the data validation to as we did in the first example.

Screenshot 2014 01 23 13.31.10 Creating a Dropdown List in Excel for Mac 2011

 

 

 

 

 

 

 

Now go to Data on the ribbon then click Validate just was we did in the first example. We will again choose List from the Allow box under Validation Criteria heading.  This time however, we will click on the little button to the right of the Source box.

Screenshot 2014 01 23 13.26.54 Creating a Dropdown List in Excel for Mac 2011

 

This will minimize the Data Validation window.  Next, we will highlight the data that we want to use for our list.  You will see the cell reference appear in the source box.

Screenshot 2014 01 23 13.28.41 Creating a Dropdown List in Excel for Mac 2011

 

 

 

 

 

Click on the small button to the right of the source box to maximize the data validation window, click the OK button and your list has been created.

 

Posted in General Excel | Leave a comment

Microsoft Excel Nested AND and IF Function

The Excel AND and OR functions allow you to test the conditions of certain cells and apply a result based on the result of those tests.

AND Function

With the AND function, ALL criteria must be met in order for the test to come out as true.  Let’s say we have a listing of our sales staff and their sales AND performance rating must both be at a certain level in order to earn a bonus.  If both criteria are met the salesperson will receive a bonus of $2500.  If only one condition is met or if no conditions are met they will not receive a bonus.

To accomplish this we must combine the IF and AND functions.  Let’s look at the following data in our worksheet.

IF AND Function 1 Microsoft Excel Nested AND and IF Function

 

 

 

 

Let’s say that to earn a bonus, the salesperson must have sales at or above $150,000 and a performance rating at or above 80.  In cell D2 we will type the following formula:

=IF(AND(B2>150000,C2>80),5000,”No Bonus”)

This is an AND function nested inside an IF function.  If we break it down, we are basically saying IF b2 is greater than or equal to 150000 AND c2>80 then the bonus is 5000.  If only one or none of the criteria are met then the bonus=”No Bonus”.

 

IF AND Function 2 Microsoft Excel Nested AND and IF Function

 

 

 

 

If we ook at our first salesperson, Steve, his sales are above the $150,000 threshold but his rating is not above 80.  Therefore his bonus should be “No Bonus”.

IF AND Function 3 Microsoft Excel Nested AND and IF Function

 

 

 

 

Now we will copy this formula down so that it applies to the rest of the rows by clicking and dragging on the little “grabber” in the bottom right hand corner of the cell.

IF AND Function 4 Microsoft Excel Nested AND and IF Function

 

 

 

 

Since the other salespeople meet both the sales and performance criteria they receive the $5000 bonus.

Posted in General Excel | Leave a comment

Creating a Custom List in Excel 2007

If there is a list of items that you enter into an Excel worksheet frequently, instead of typing the list time and time again, you can create a custom list.  Excel already has some built in lists such as days of the week and months of the year but let’s say you have the same list of cities that you use often.  Here is how you can create a custom list to save you time.

First, type the list entries into any cells of a worksheet in the order that you want them in the list.  In the example below, I will be using a list of cities.  Then highlight the list.

ScreenHunter 01 Jun. 12 10.28 Creating a Custom List in Excel 2007

 

 

 

 

 

Now, click on the Microsoft Office button in the top left hand corner and then click on Excel Options.  Then, on the “Popular” screen, click on Edit Custom Lists.

ScreenHunter 02 Jun. 12 10.31 Creating a Custom List in Excel 2007

 

 

The custom lists window will appear.

ScreenHunter 03 Jun. 12 10.34 Creating a Custom List in Excel 2007

 

 

 

 

 

 

 

 

 

In the “Import List From Cells” section, you will see a text box with the range of cells highlighted that contains your list of cities that we created early.  Now we simply click the “Import” button and our list is imported as seen below.

ScreenHunter 04 Jun. 12 10.36 Creating a Custom List in Excel 2007

 

 

 

 

 

 

 

 

 

Now we click OK and our custom list has been created.  Now let’s test it.  Go to any blank cell in your worksheet and type in any city that appears in our list then point your mouse at the little square grab handle in the bottom right corner of the cell.

ScreenHunter 05 Jun. 12 10.39 Creating a Custom List in Excel 2007

 

 

 

 

Now click and drag downward.  As you do so, you will see your list of cities appearing.  Once you have all the cities that you want listed.  Simply release your mouse button and the list of cities will be automatically entered into your cells.

 

 

Posted in Excel 2007, General Excel | Leave a comment

Video: Add Images to Excel Worksheets Using Comments

In this video tutorial, you will learn how to add images to Excel worksheets using the comments feature.  This is useful if you wish to add images of products or people that you list in your worksheet.

0 Video:  Add Images to Excel Worksheets Using Comments
Posted in General Excel | Leave a comment

Video: Deleting Data But Not Formulas in Excel

In this video tutorial we learn how to delete data from a worksheet but leave the formulas intact.

0 Video:  Deleting Data But Not Formulas in Excel
Posted in Functions and Formulas, General Excel | Leave a comment

Importing Text Files Into Excel

Have you ever had a list of names and addresses that were in plain old Microsoft Word format or a text file and you wished they were in Microsoft Excel?  Instead of re-typing everything, you can just import a delimited file.  Delimited means that the fields are separated by a comma, or a space or another symbol.

Let’s say you have data that looks like this:

Name  Age  Job Title
Bill   39  President
Tim  40  Vice President
Anne  37  Vice President

What we will do is copy that data to a text editor such as notepad or simply save it as a text (.txt) file.

ScreenHunter 01 Apr. 27 17.00 Importing Text Files Into Excel

 

 

 

 

 

 

 

 

What I will do next is separate the fields with commas.

ScreenHunter 01 Apr. 28 16.05 Importing Text Files Into Excel

Notepad with data delimited with commas

 

 

 

 

 

 

 

 

The next step is to save the text file.  I saved my copy onto the desktop with the name data.txt.  Now, let’s go into Excel.  As you will see in the image below, I have chosen Open and then I have told Excel that I wish to open a file of the Text File type that resides on the desktop.

ScreenHunter 02 Apr. 28 16.09 Importing Text Files Into Excel

 

 

 

 

 

 

 

 

 

 

 

Once you have found the file you saved, open the file.  You will then see the “Text Import Wizard” screen.

ScreenHunter 03 Apr. 28 16.13 Importing Text Files Into Excel

 

 

 

 

 

 

 

 

 

As you can see, the file type is already set to “delimited”.  Now, click the “Next” button.  On the next part of the text import wizard, we have to select how are data is delimited.  If you recall, we used commas to delimit our data, so make sure you select Comma from the option list as seen in the image below.  You will notice that you will see a preview of how your data will appear. Now click “Next”.

ScreenHunter 04 Apr. 28 16.16 Importing Text Files Into Excel

 

 

 

 

 

 

 

 

 

On step 3 of the text import wizard you will have the option of formatting your columns if you wish.  For example, you may need a column in currency style, or date style etc.  If you do, make those choices now.  You can also format the columns later.  If you don’t need such changes, or when you have finished making your changes, click “Finish”.

Voila!  Your data appears in nice, neat Excel format.  Make sure to make any chances that you wish such as bolding the headings, enlarging the columns etc.  Here is my finished product.

ScreenHunter 05 Apr. 28 16.21 Importing Text Files Into Excel

 

 

 

 

 

 

 

Remember, you do not have to use commas as your delimiter.  You can use tabs, spaces, semi-colon or any symbol that you wish to use as a delimiter.

 

 

 

Posted in Excel 2007, General Excel | Tagged , , , , | Leave a comment

Video: Using Excel’s Sum Function to Add a Range Of Cells

In this video tutorial, you will learn how to use the sum function in Excel to total up cells in a certain range.

0 Video: Using Excels Sum Function to Add a Range Of Cells
Posted in Excel 2003, Excel 2007, Functions and Formulas | Tagged , , , , , , , | Leave a comment

Excel 2007 Conditional Formatting

Conditional formatting in Excel is used to change the look of certain cells, or the content of certain cells based on the data contained within.  We’ll start off with a simple example.  We’ll have a list of names and a grade.  If the grade is 50 or higher, we will change the colour of the text to green.  First we enter the data labels.

ScreenHunter 02 Aug. 11 14.03 Excel 2007 Conditional FormattingNext we must select the cells to which we wish to apply the conditional formatting.

ScreenHunter 03 Aug. 11 14.04 Excel 2007 Conditional FormattingNow we will click the Conditional Formatting button on the Home tab of the ribbon.

ScreenHunter 04 Aug. 11 14.06 Excel 2007 Conditional FormattingNext we will click on the button that says New Rule, then click on Format Only Cells That Contain.  Your screen should appear as it does below.

ScreenHunter 05 Aug. 11 14.09 Excel 2007 Conditional FormattingHow we will choose our parameters.  Since we want to highlight cells where the grade is 50 or higher, we will choose Greater Than or Equal To and then we’ll type in the number 50 in the third box.  The bottom part of your screen should look like the graphic below.

ScreenHunter 06 Aug. 11 14.12 Excel 2007 Conditional FormattingNow we will change the setting that will determine what will happen if the cell contents meet our requirement.  Click on the Format button.

We want our text to change to green if the value of the cells is 50 or higher.  So in the color box, choose green.  Your screen should look like the image below.

ScreenHunter 07 Aug. 11 14.15 300x279 Excel 2007 Conditional FormattingNow click the OK button.  Click OK on the next screen as well.  You should now be back on your worksheet.  Now enter the following grades for each student.

Steve – 57
Robert – 46
Amanda – 93
Stephanie – 82
Scott – 30

If our conditional formatting has worked correctly, anyone with a grade of 50 or higher should have their grade listed in green text as seen below.

ScreenHunter 08 Aug. 11 14.19 Excel 2007 Conditional FormattingThat is a very basic tutorial on conditional formatting.  There is much more you can do with conditional formatting.  Please watch for more conditional formatting tutorials and videos coming soon.

Posted in Excel 2007 | Tagged , , , , | Leave a comment

Wrap Text in Excel 2007

When creating an Excel worksheet, you may want all the text to fit in one cell without having to increase the size of the column.  We can use the wrap text feature in Excel 2007 to accomplish this task.

For our example, we will start is cell A1.  In this cell we want to type the words “Product Description”.  Obviously this will not fit in the cell unless we use wrap text or enlarge the column.  Make sure that cell A1 is selected.  Next, on the ribbon we have to click on the Wrap Text button as shown in the graphic below.

ScreenHunter 02 Aug. 09 10.091 Wrap Text in Excel 2007Now we can type our text into cell A1.

ScreenHunter 03 Aug. 09 10.13 Wrap Text in Excel 2007Finally, we will do a little housekeeping and stretch column A just a little bit to make sure our text fits onto 2 lines instead of 3 as it is now.

ScreenHunter 04 Aug. 09 10.15 Wrap Text in Excel 2007If necessary, we could edit how the text is aligned vertically in the text so that it is centered vertically in our cell.  We’ll examine how to do that in another post.  For now, we have accomplished what we wanted to do which was wrap text in a cell in Excel 2007.

Posted in Excel 2007, General Excel | Tagged , , | Leave a comment

(Video) Importing Texts Lists Into Excel

In this video lesson, I will show you how to take lists that are text-based and import them into Excel 2003.

0 (Video) Importing Texts Lists Into Excel
Posted in Excel 2007, General Excel | Leave a comment