Learn Excel from Bill Jelen with this daily 2 minute video podcast.
You can specify up to eight colors in the custom number format box. But wait...you can actually do all 56 colors from the Excel 2003 color palette! Episode 1142 shows you how.
Labels: custom format, Formatting
posted by Bill at 9:00 AM
Roger from Columbus Indiana passes along a cool trick for formatting all of the text in a chart at once. Episode 1131 shows you how.
Labels: charts, Formatting, Text
posted by Bill at 9:00 AM
Brett from Australia asks how to format a date to include the words Opening Balance so that he can do a VLOOKUP into a report. The format is easy, the VLOOKUP is tougher. Episode 1115 shows you how.
Labels: DATE, dates, Formatting, Text, VLOOKUP
posted by Bill at 9:00 AM
In yesterday's netcast, I talked about using four zones in custom number formats for positive, negative, zero, and text. In Episode 1064, you will learn how to create your own zones for formatting.
Labels: custom format, Formatting, Number Formats
posted by Bill at 9:00 AM
LearnAccessByCrystal at YouTube asked for a podcast with information about the zones in a custom number format. In Episode 1063, an explanation of the four zones in a custom number format.
Labels: Formatting, Number Formats
posted by Bill at 9:00 AM
Carrie from yesterday's podcast called in to say that the links are not working. It turns out that Carrie and Mike wanted to copy the cell shading of the linked cell and regular links don't do that. For Episode 1057, I go back to Excel 2003 to show where the Paste Picture Link is hidden.
Labels: Formatting
posted by Bill at 9:00 AM
If Jerry from podcast 1052 had been using cell styles to mark his input cells, he could have used advanced options in the Find dialog to clear all of the input cells. Episode 1053 shows you how.
Labels: Formatting
posted by Bill at 9:00 AM
Jerry has a model with input cells and formula cells. He wants to copy the model for next month and clear out all the input cells. Episode 1052 will show you how to clear out the numeric input cells.
Labels: Excel 2007, Formatting, Go To Special
posted by Bill at 9:00 AM
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.
Labels: Formatting
posted by Bill at 9:00 AM
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.
Labels: Formatting
posted by Bill at 9:00 AM
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.
Labels: Formatting
posted by Bill at 9:00 AM
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!
Labels: Formatting, Microsoft Word, printing
posted by Bill at 9:00 AM
If you like my videos, check out the YouTube channel EXCELISFUN. Mike Gel Girvin has an amazing collection of enthusiastic Excel Videos on YouTube. Also in Episode 938 - an improvement for episode 933: =MOD(ROW(),2) is equivalent to =MOD(ROW(A2),2)=1.
Labels: conditional formatting, ExcelIsFun, Formatting
posted by Bill at 9:00 AM
In case 54x20 formats is not enough for you, you can design your own table format. Episode 935 shows you how.
Labels: Formatting, Table
posted by Bill at 9:00 AM
The every-other-row formatting becomes simple in Excel 2007 using the Table functionality. Choose from 54x20 formats in seconds. Episode 934 shows you how.
Labels: Formatting, Greenbar, Table
posted by Bill at 9:00 AM
Excel 2003 used to offer an obscure conditional formatting option called Formula Is. It is still obscure in Excel 2007. Episode 933 will show you the ROW, MOD functions and a dynamic solution to formatting every other row.
Labels: conditional formatting, Formatting
posted by Bill at 9:00 AM
By the time I answered Lisa's question from yesterday, she had already formatted her spreadsheet, using a clever method that I would not have thought of. Episode 932 will show you how to format every other row using the fill handle.
Labels: Formatting, Greenbar
posted by Bill at 9:00 AM
Lisa asks how to shade every other row in the worksheet. While there are several solutions, Episode 931 shows you how to use the old AutoFormat dialog to solve this problem.
Labels: Formatting, Greenbar
posted by Bill at 9:00 AM
Jamie from Hampshire UK offers a better way for hiding data in cells. Rather than changing the font color to white, you can use this custom number format. Episode 919 shows you how.
Labels: Formatting
posted by Bill at 9:00 AM
Chuck has a spreadsheet where he has bolded the client name in a dozen cells. Unfortunately, only a portion of the cell is bolded and not the whole cell. The Excel 2007 Edit-Replace can not deal with this and changes the entire cell to bold. Episode 915 will show you a solution.
Labels: Formatting, replace
posted by Bill at 9:00 AM
Nancy sends in a cool tip to solve some of the frustrations from Episode 875 -- Excel changing the column widths of a pivot table as you choose new values from a page field. In Episode 887, I will take a look at how to solve this problem.
Labels: Formatting, Pivot Table
posted by Bill at 9:00 AM
Excel 2007 offers a new visualization called an Icon Set. I usually hate this feature, because my numbers are right justified and the icons are left justified. However, in Episode 879, Joanna from Columbus, Ohio offers a cool tip to get the numbers and icons next to each other.
Labels: Excel 2007, Formatting
posted by Bill at 9:00 AM
Neville from Germany sends in a good suggestion for overcoming pivot formatting problems from Episode 865. By using conditional formatting on the underlying worksheet, Neville comes up with a way to make sure that the total rows are always highlighted in a pivot table. Episode 875 shows you how.
Labels: conditional formatting, Formatting, Pivot Table
posted by Bill at 9:00 AM
Donna from Colorado asks how to clear all of the input cells from a model. In today's Where Is It Wednesday Episode 868, we take a look at how to select only the numeric constants. Also - following up on 858 - Copy as Picture, 860 Resizing Header Logo, 863 Protect Sheet.
Labels: Formatting
posted by Bill at 9:00 AM
In our Where Is it Wednesday podcast, we go looking for a command that most people never would have been able to find in Excel 2003! If you want to send a perfect copy of a formatted spreadsheet in an e-mail, you could use Snag-It or PrtScrn, but by holding down the shift key, you can find a hidden Copy as Picture command in Excel 2003. Episode 858 shows you how.
Labels: Formatting
posted by Bill at 9:00 AM
Episodes 790, 795, 797, and 798 generated a lot of e-mail. We will review different ways to create split cells, copy without borders, and type months and years. Episode 802 will show you how.
Labels: Formatting
posted by Bill at 9:00 AM
Back in Episode 795, I asked about the best way to fill 1, 2, 3 without copying the formatting. 3 different approaches to solve this problem will appear in Episode 797. Even if you don't have this issue, you will learn some surprising things about the fill handle's behavior.
Labels: Formatting
posted by Bill at 9:00 AM
Kathy asks how to type numbers in a diagonally split cell. Episode 790 shows you how.
Labels: Formatting
posted by Bill at 9:00 AM
In Episode 761, a look at three cool custom icons you can add to your toolbar in Excel 2007 or 2003. See how easy it is to use Select Current Region, Select Visible Cells, and Set Print Area.
Labels: Customizing, Formatting
posted by Bill at 9:00 AM
There is a way to set the default border color that is used when you use the Borders dropdown. Episode 759 shows you how. Also, a look at the new Podcast DVD.
Labels: color, Formatting, gridline
posted by Bill at 9:00 AM
Chris from Scotts sends in a cool trick today...how to set up an icon set in Excel 2003. Episode 729 takes a look at how to set up this trick.
Labels: Formatting, Icon Sets
posted by Bill at 9:00 AM
I used an old custom number formatting trick in yesterday's podcast. Today, we take a look at more possibilities using custom number formats. Episode 724 shows you how.
Labels: Formatting, number formatting
posted by Bill at 9:00 AM
As a follow-up to yesterday's podcast, we'll take a look at how to customize the formatting used in the Excel 2007 table formatting. Episode 694 shows you how.
Labels: Excel 2007, Formatting, Greenbar, Table Format
posted by Bill at 9:00 AM
Excel 2007 offers easy greenbar formatting using the table functionality. While the table functionality offers amazing tools that we'll discuss on another day, today's Episode 693 looks at the few clicks necessary to format a range with alternating row formatting.
Labels: Excel 2007, Formatting
posted by Bill at 9:00 AM
Need to convert a report or a chart to display in thousands or millions? Episode 555 shows two different methods to achieve this result.
Labels: charts, custom format, Formatting, Number Formats
posted by Bill at 9:00 AM
Today, Bob calls in with a question. He has an Excel spreadsheet where someone managed to cross out only part of a cell. For example, someone crossed out the original due date and then keyed a new due date in the same cell and the new date does not have the strikethrough formatting applied. Episode 547 shows you how.
Labels: Formatting, shortcut keys
posted by Bill at 9:00 AM
Dermot sends in today’s question; a spreadsheet is showing zeroes as a dash, and positive numbers with a plus sign! It turns out that the custom number format has four zones, where you can specify different formatting for positive, negative, zero, and text values. Episode 521 shows you how.
Labels: Formatting, Zeroes
posted by Bill at 9:00 AM
Six accent colors x 7 options x 20 themes x your imagination leads to thousands of different formats that you can apply to a table with a single click. Episode 425 shows you how.
Labels: color, Formatting, Table
posted by Bill at 9:00 AM
If you try to use the concatenation character to join text with a date, you will not get the results that you expected. Episode 414 shows you how to modify the formula to properly format the date.
Labels: Concatenation, dates, Formatting, Text
posted by Bill at 9:00 AM
Get quick access to all of the text formatting commands by moving your mouse towards the completely invisible mini toolbar! (Who at Microsoft comes up with this stuff?) Episode 413 shows you how to get the shy mini toolbar to appear.
Labels: Formatting, Text, toolbar
posted by Bill at 9:00 AM
Create cool headlines by adding text to your autoshape and then formatting it using the drawing toolbar. Episode 369 shows you how.
Labels: autoshapes, Formatting, Text
posted by Bill at 9:00 AM
You have two dates in Excel and want to find the elapsed years and months. The solution is to use an ancient function from the days of Lotus 1-2-3. Episode 357 shows you how to use the function, as well as a cool formatting trick to show years and months in the same cell.
Labels: dates, Formatting
posted by Bill at 9:00 AM
The complete guide to applying borders to a selection. Learn what all of those icons actually do on the borders tab. Episode 344 shows you how.
Labels: Formatting
posted by Bill at 9:00 AM
Tricks for adding a double underline to your cells. Episode 343 shows you how.
Labels: Formatting
posted by Bill at 9:00 AM
Do you get annoyed when you copy a formula and the borders get copied along with the formula? Episode 342 shows you a cool trick to prevent the borders from being copied as you extend a formula.
Labels: border, Formatting
posted by Bill at 9:00 AM
