Excel Tutorial: Named Ranges

When referencing data in larger Excel spreadsheets, it can often lead to incorrect results because of errors when selecting data. To avoid this, use Excel Named Ranges.

Here's a quick video to do just that.

It's really quite simple.

Excel Tutorial: How Can I Customize SmartArt?

Elizabeth asked, "Once I insert SmartArt in Excel, how can I change the color and look?"

What Elizabeth is asking is very smart. Inserting the same old SmartArt and not changing it, or customizing it in any way, looks very boring. I recommend customizing SmartArt and other objects to match your company colors and logo, your presentation style and format, so that it doesn't feel like it was an after thought.

To customize SmartArt, simply follow these steps.

You can also watch the video tutorial below.

Click the "Insert" Tab.
Click "SmartArt" from the Illustrations group.

In the "Choose a SmartArt Graphic" dialog box, select the category on the left. Then you select the item in the middle. The right shows a preview of the item. Select OK to insert the content.

Excel inserts the selected SmartArt graphic in the middle of the spreadsheet.

You can simply click on one of the boxes and type in your text, if desired. Notice that the font sizes adjust, depending on how much text you enter.

Don't stop there, now it's time to customize the SmartArt.

With the SmartArt selected (click on it, if you need), you will see the "SmartArt Tools" contextual tabs "Design" and "Format."

Click on these tabs to see the customization options. Features on these tabs will be different based on the type of SmartArt you inserted.

You can customize things like the colors, the styles, and fonts.

Look around and practice to find the best look for your Excel spreadsheet and/or presentation.

Now that you know how to customize SmartArt, you will look like the expert professional for visualizing in Excel.

Watch the video tutorial here.

Excel Essentials: Tutorial for Beginners Now Open

I'm excited to announce my newest course - Excel Essentials: Tutorial for Beginners!

In this course, you will learn to use Excel in under 2 hours! We not only cover the basics, but teach useful Formulas, Functions, and Analysis.

I'm proud to offer my First 30 Fans with a Coupon to take the course for FREE!

Just CLICK HERE for the FREE Coupon.

All I ask in return is that you write a review on the course.

Watch this quick preview of the course to learn more:

I hope you enjoy the course, and I know that you will learn valuable Excel skills.

Enjoy!
Debbie

Excel Styles and Themes

In Excel, it is easy to just type numbers for a boring looking spreadsheet. But, did you know you can apply styles and themes similar to PowerPoint?

Here's a brief tutorial how to do just that.

How to Print Excel Formulas

I have many students that ask me how to create a list of the most used Excel Formulas.  There are a few ways... 

If you want a list from Microsoft Help, just open your help by pressing F1 or use your Office Assistant.  Search for Formulas and Print the Help page.

But sometimes this list isn't entirely what you want.  So, you can simply create your own list of formulas and print your list for future reference.

To do so, follow these instructions.

Type a list of the common formulas you use (or use an existing spreadsheet that someone else has created).

Click Tools | Options.

Click on the VIEW Tab.

On the View Tab, check "Formulas" in the Windows Options area of the Tab.

Click OK and you will return to your spreadsheet with the Formulas showing, instead of the results.

It's that simple.

Create Excel Forms

There are a few ways to create forms within Excel. Here, I will show you (in my opinion) the easiest.

An easy example to use is a Timesheet or Expense form.

First, type in the information as you would like it captured. You may type in the line descriptions or categories for the Expenses and place colored cells for the areas in which you want users to type in their actual expense amount. Once the form is created, you will "unlock" the cells (the colored cells in my example) that you wish the user to input data.

To do this, click on Format Cells and click on the "Protection" tab. On this tab you will notice that the cells are "locked" by default. Simply uncheck the box and click OK.

Next, click on Tools Protection and choose "Protect Sheet" (or "Protect Workbook" if you are working with more than one Sheet). This will open a separate window asking you what you would like to protect. In the "Allow all users of this worksheet to" area, make sure the only item checked is "Select unlocked cells".

NOTE: When you are Protecting a Sheet or Workbook, Excel will ask you for a password. Make sure you choose a password if you do not want users to edit the form. Excel does NOT require you to use a password.

Finally, click File Save As and choose "Excel Template" from the "Save as Type" drop down. This will save your file in the default folder. If you are working on a network, be sure and get with your administrator for the exact location to save this file.

Now that you have saved your form, users will simply click File New and choose "on my computer" for the location of the template. Once the users double-click to open the template, they will only be able to type information in the unlocked cells.

Add Row Numbers in Excel

There are any number of formulas you can use in column A that will return a row number.

Perhaps the easiest is to use the ROW function, like this:

=ROW( )
This formula returns the row number of the cell in which the formula appears.

If you want to offset the row number returned (for instance, if you have some headers in rows 1 and 2 and you want cell A3 to return a row value of "1", then you can modify the formula to reflect the desired adjustment:
=ROW( )-2

Of course, the ROW function isn't the only formula that will perform this function. Look for more Go Ask Debbie Tips on using Excel formulas and functions directly at Go Ask Debbie.