FC2Video

The Scenaro? Brendan created a cool Formula about six (6) months ago. He edited that Formula today and it won't work anymore. In Episode #1368, Bill shows you why and how to correct the issue.

Wyatt asks if there is a way to have SmartArt float above a worksheet and always stay in view. Today, in Episode #1366, Bill shows us how to create a short macro to solve the problem, although not perfectly. …Learn Excel 97-2007 from MrExcel.

Today we look at a question posed in Episode #1358 "How did Mike and Bill predict the Super Bowl winner?" Bill gives us the answer in Episode #1365 - and announces the winner of the contest.

Today's Dueling Excel, with Mike "ExcelIsFun" Girvin and Bill "MrExcel" Jelen [Episode #1364]looks at selecting specific entries in a row and then concatenating those entries in one cell.

When should you use the Insert Hyperlink compared to the =HYPERLINK() function? In Episode #1363, Bill compares the two methods. Also learn how to build 100's of hyperlinks by concatenating together some text in quotes with values in a column.

Today, in Episode #1362, Bill shows us how to paste links with the 'Friendly Name', and then extract the actual 'URL' from the link. Also learn how to easily remove unwanted buttons, and graphics that are inadvertently copied into your workbook.

Today, Bill continues to look at Hyperlinks in Microsoft Excel - like when you paste a column of website addresses into Excel, none of them automatically turn into a hyperlink. Episode #1361 looks at a couple of solutions to make your hyperlinks hot.

Today's Question: "How can I select a cell that contains an active hyperlink without actually clicking and following the hyperlink?" Bill shows us two solutions in Episode #1360. Learn Excel 97-2007 from MrExcel.

Bill and Mike take a look at how to do a reverse lookup in Episode #1000. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Ann from Wisconsin writes with a really annoying Wrap Text problem in Excel 2003. Episode 999 suggest a few workarounds to solve the problem. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Tom & Vicky ask about using Excel for basic household finance. Excel has a great suite of functions for this. Today, a look at using NPER to pay off your credit cards faster. Episode 998 shows you how. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Shanna sends in today's question. When she types three digits in a cell such as 150, the cell automatically changes to 1.5. Episode 997 takes a look at this bizarre problem and when this setting might actually be useful. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Rich wonders why you can not center across selection vertically. Episode 996 will show you two not-very-satisfying workarounds and asks if you have anything better. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Another Dueling Excel podcast. Bill and Mike discuss formulas to grab the last entry in a row. Episode 995 shows you two methods for solving the problem. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

In Episode 994, another solution to Ally's problem of how to make a chart expand when you type new data in the worksheet. Today's method shows an easier way to set up a dynamic chart range in Excel 2003 and newer. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

A second solution to Don's problem from podcast 991: add a textbox inside the cell. With a bit of formatting, the words inside the textbox will appear to float next to the number, but will not cause any subsequent formulas to incorporate the text. Episode 992 shows you how. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Don from Europe calls with today's question: How can I add some text next to a number and still keep the number "as a number" that will flow into future formulas. Episode 991 shows you a cool trick for achieving this effect. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Mike from ExcelIsFun throws down this week's challenge: adding horizontal subtotals. Bill and Mike duel it out in Episode 990. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Rod sends in today's question. How can his pivot table indicate if ALL of the records for a certain level are marked as complete? Episode 989 will show you a use for the PRODUCT calculation in a pivot table. Also - the arcane custom number format to display positive, negative, zero values in different manners. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

While creating a scatter chart, Don is trying to change the marker color in Excel 2007. This was relatively easy to find in Excel 2003. In Excel 2007, the equivalent dialog was split into several panes, but there is a much easier way to go. Episode 988 discusses the three charting tabs and shows you the solution. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

When you declare a range as a table in Excel 2007, any calculations entered to the right of the table are automatically copied throughout the table. Episode 987 talks about how to solve this problem. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Many people use =NOW(), but it is frustrating when you try to calculate how many days away a certain event is occurring. Episode 986 shows a method for solving this problem. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

You have a really long data set with all of the data in column A. When you print, you will be wasting 75% of the horizontal white space on the page. Is there any way to have the column A data snake through the other columns? It would be nice if there was a checkbox for this, but Episode 984 resorts to using INDEX, ROW and COLUMN! This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Bill from Washington asks how to track vacation time, specifically times greater than 24 hours and times less than 0 hours. This requires two different tricks. Episode 983 shows you how. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

You need to chart two different orders of magnitude in a chart. I always end up creating a 2-axis combo chart, with a column series and a line series. Today in Episode 979, Bill writes in with a cool way to solve this problem with both series shown as columns. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Cullen sends in today's Where Is It Wednesday question: how can you change the comment shape in Excel 2007? This was a cool feature in Excel 2003, we just can't find it anymore. Episode 978 shows you how. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Joe asks if there is any way to do a Filtered Paste operation. I can't figure it out, so I had to resort to a macro. Check out Episode 977 and see if you have a better way. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Laura from Wisconsin asks about changing the default Excel comment from light yellow to another color. While you can format an individual comment in Excel, the solution to this problem is in the control panel. Episode 976 shows you how. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Awesome. There is a video on the 'net with Microsoft's Donald Farmer showing off a new add-in called Gemini. This is an amazing product. In Episode 975, I point out where you can get a sneak peak at Excel 2010 pivot tables in the Donald Farmer video. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

You may have seen how to create a spearing or 3-D formula such as =SUM(Jan:Dec!B3). In Episode 974, an amazing way to create a 3D reference to non-contiguous sheets. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

A question from YouTube asks how to write a macro to cut selected rows to Sheet2, adding a completion date. Episode 973 shows you how to write this short macro. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

The data table option in a chart can have some annoyances. Today, a question (and a solution) from the Virginia Tech seminar. Episode 972 shows yet another cool use for the camera tool. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Jennifer is relatively new to Excel and asks about using the Insert Name dialog. Any time that you are in the RefEdit field and hit a left or right arrow key to move within the formula, Excel starts inserting cell addresses. Episode 971 shows you how to turn off that annoying behavior. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Michael from Shanghai asks about missing data in a pivot table. If some customers have data from every month and other customers have data from only a few months, the pivot table will change shape as you select new customers from the filter. Episode 970 will show you the hidden setting to prevent this problem. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

There is a new podcast featuring my Excel tips. Learn about this podcast and also see how to repeat your title rows at the top of each printed page. Episode 969 shows you how. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Smack83 sends in today's question via YouTube. How do you freeze specific rows and columns in Excel and have them appear without retyping. Episode 968 shows you how to you Freeze Panes to keep the headings visible on the screen. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Bob Umlas from the Excel Magic book passed on another amazing tip to me at the Microsoft Summit. If you need to add a total to data that was subtotaled using SUM formulas, you can do it in 1 double-click. Episode 967 shows you how. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Another dual podcast as Bill Jelen and Mike Gel Girvin show you multiple ways to reduce a range by 35%. Shot in Seattle, Washington. Episode 966 shows you how. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

I bet you thought that I screwed up and put the date of the podcast as the title! But no... that really is the title. Tony asks how to change the long date format to show Thu Mar 5, 2009. Episode 964 shows you how. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

One of the requests from last Friday's podcast was the ability to sort columns like you can sort rows. It's in there! In Episode 962, I will show you how to sort left to right. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Jason has five pivot tables based on the same data. All five have a page field holding the month. After changing the month on the first pivot table, he would like the other four pivot tables to also change. Episode 961 shows Mike Alexander's trick for solving this problem. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Paul sends in a few tricks to help deal with the Function Tooltip that appears when you edit a cell. Episode 960 shows you how. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Someone used Alt+Enter to enter three fields in hundreds of cells. How can this be broken into separate columns. In Episode 959, I discuss MID, CODE, CHAR, and SUBSTITUTE functions to solve the problem. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Dave from San Francisco suggests you should use Excel without the mouse and challenges you to convert a formula to values in three keystrokes. Episode 958 shows various attempts to solve the problem without using the mouse. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Episode 954 brought an avalanche of mail on three fronts. (1) SendKeys is really cool, how do you do Alt instead of Ctrl? (2) Why the macro when you could select Entire Workbook in the dialog? (3) Application.Dialogs would have worked, the dialog is misnamed. Episode 957 discusses all of these tips. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Tamir asks if you can add labels between columns in a chart. Episode 956 shows a tricky way to solve this. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Laura asks how to display the Find dialog in a macro. The macro recorder won't do it. Application.Dialogs().Show won't do it. Episode 954 shows you a kludge to solve this problem. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

A question from Gary: Why does Excel 2007 open with only 65,536 rows? Wouldn't Microsoft want to show off the cool functionality available in Excel 2007 without forcing you to up convert each worksheet? Episode 953 discusses the unfortunate setting causing this problem. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Laura from Wisconsin provides today's cool Excel trick: Double click any edge of the active cell and Excel will jump to the edge of the data. Episode 952 shows you how. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Jamie shows how to use EOMonth to find the first of next month and Melanie shows how to sort paragraphs in Word. Episode 951 shows you how. This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!