Category Archives: MS Excel

Excel: Loan Repayment Worksheet

Hi Friends!

I created a new page here on my blog. I’m planning on creating a new page for each of several projects. Most will be in Excel. The current project (May 2011) is a Loan Repayment Worksheet. Today, we just set up the interest rates table to calculate monthly and daily rates based on a yearly interest rate.

To follow along, go to the menu bar at the top of this page, and click “Loan Repayment – Special Project.”

Hope you enjoy it!

Leave a comment

Filed under MS Excel

Excel: Drop-down Column Headers

Hi Friends!

Today I will show you how to create drop-downs for your columns in Excel. I like it more than sorting, because you can sort by a value, or values, and exclude all other data. It’s another really quick tip.

If you have a column or more that has just a lot of repetitiion, e.g. seven possible categories, filtering helps. I did that with my tips catalog. I keep a running “master” of all the posts I do, so I like to filter them by category. That way I can quickly see how evenly I’m spreading my attention to each topic.

Here’s how to do it:

1.   Select one or more column header(s)

2.   Click on the Data tab

3.   Click on “Filter” (the funnel icon)

Now you have drop-down arrows for each column. I’m not sure why you can’t filter just select columns.

Here’s a tip on using the drop-downs: Notice that you have the option to choose to sort the list or more than one item in the list (see below). To select one or just a few items, click the “select all” checkbox to deselect the whole list. Then check each item you wish to see exclusively.

And there you have it! That’s filtering.

Thanks for stopping by!

Leave a comment

Filed under MS Excel

Excel Formula Check

Hi friends!

When your spreadsheet has formulas, it’s easy for unexpected problems to occur within your formulas, particularly if you copy and paste or autofill your formulas. This tip is one way to get a clear visual of what’s happening with any given formula.

Double-click on the cell you want to check. The formula will appear in the formula bar, but looking at it in its resident cell, you can see each cell reference is a different color. A1 is blue, A2 is green, and C3 is purple.

In my graphic, you can see that cell C3 has nothing in it. I really wanted that to be A3 instead. A quick, easy fix for that is to click and drag the purple box up to C3. Hit enter.

Thanks for stopping by!

Leave a comment

Filed under MS Excel

Excel: Converting Formulas to Static Values

Hi Friends!

You have a spreadsheet that’s full of formulas, and you need to share the results with others. You might not want to throw confusion in by leaving your formulas in the cells. You might also not want them to be able to change the formulas, accidentally (or on purpose). Yes, that can be managed with security settings, but this is another way.

Converting formulas to values is simple. Right click on the cell, select copy. Right click again, hit “Paste Special,” then select “Values.” OR, if you have Excel 2007 or 2010, make sure you’re on the home tab (circled below). Select your target cells or just hold “Ctrl” and hit A to select the entire worksheet.

1.   Click on “Copy”

2.   Click the down-arrow under “Paste”

3.   Click “Paste Values.”

Yes, it’s that easy!

Thanks for stopping by!

2 Comments

Filed under MS Excel

Using Excel in Word

Hi Friends!

Today’s tip is about using Excel in MS Word. Of course you can copy and paste an existing spreadsheet into a Word document, but you can also create a spreadsheet from scratch. As you may know Word tables don’t do calculations, so if you need to do calculations and you want the numbers to format correctly, insert an Excel “object.”

1.   In Word, click the Insert tab.

2.   Click on “Object”

3.   A menu box will open from which you will choose one of the Excel options. I selected the 2007, Binary workbook.

4.   Up pops an edit-ready worksheet, like this:

(I put those words in the example.)

When you click outside the table, it looks like this and cannot be modified as long as it looks this way:

To get back to editing your spreadsheet, just double-click inside the spreadsheet area.

Have fun with that!

Thanks for stopping by!

 

Leave a comment

Filed under MS Excel, MS Word

Excel: Linking Worksheets

Hello Friends!

Today’s tip is about linking cells in Excel. This is pretty cool AND easy. All you have to do is type the = sign in the destination cell, then navigate to the location of the data you want linked. Click on the cell you want linked, and hit “Enter.” Presto! It’s done! Now any time you change the value of the linked cell, the destination cell will change with it.

You can link to cells in another workbook, but whenever you make changes in the original cell, the other workbook needs to be open as well. If it’s not, you’ll have to remember to refresh data when you do open the destination workbook next. It’s likely you will get an alert about links being disabled. Just click “enable” to update the data.

To link to a cell in another workbook, open that other workbook. When you type the = sign in the destination cell, navigate to the cell you want linked from the other workbook, click on it, and hit “enter.” Again, Presto!  It’s DONE! Here’s a sample formula like the one you will see “just appear”:

Thanks for stopping by! Let me know if I can help you with this further.

Leave a comment

Filed under MS Excel

Excel: Making Good Use of Sheet Tabs

Hello Friends!

Today I’ll be talking about the Sheet Tabs at the bottom of the screen in Excel. They say “Sheet 1,” Sheet 2,” and “Sheet 3,” until you change the names or delete them. I’m going to show you your options and talk about the different ways to change the names on each tab.

Here’s how the tabs look untouched:

One method of changing the tab name is to double-click the tab and when the current name is highlighted (Figure 1, below), just type the new name (no need to hit “delete”). The other method is to right-click the tab you wish to rename, and click on “rename” in the pop-up menu (Figure 2, below). The current name will become highlighted, and you can then type the new name.

Notice that the same pop-up menu offers “delete.” Deleting any unused tabs makes your workbook cleaner. How’s that for a little bonus tip?

Thanks for stopping by! Come back tomorrow for my next tip. Comment to let me know what you want to see here, and subscribe to receive e-mail reminders to check my tip of the day!

Leave a comment

Filed under MS Excel