ExcelTip.com Links

Search
Results
Flipping Landscape Orientation when Printing
Excel
allows you to print your worksheet in landscape mode, which means that the
printed lines are parallel to the long edge of the paper.
http://exceltips.vitalnews.com/Pages/T002220_Flipping_Landscape_Orientation_

when_Printing.html

Ampersands in Headers and Footers
If you
need an ampersand (&) to appear in a header or footer, you may be
surprised the first time you type one that it disappears when the header or
footer
http://exceltips.vitalnews.com/Pages/T002910_Ampersands_in_Headers_and_Foote

rs.html

Deleting All Graphics
Need to
delete all the graphics in a worksheet? It’s easy to do when you use the Go
To Special dialog box. Provided by ExcelTips, a free weekly productivity
http://exceltips.vitalnews.com/Pages/T003018_Deleting_All_Graphics.ht

ml

Working While a Macro is Running
Some
macros may take quite a bit of time to run—time you may want to use for other
purposes. This tip describes the best way to continue using Excel while a
http://exceltips.vitalnews.com/Pages/T002021_Working_While_a_Macro_is

_Running.html

Determining the Least Common Multiple
A least
common multiple is the number into which a series of numbers can be divided
evenly. Excel provides a worksheet function, LCM, which allows you to
http://exceltips.vitalnews.com/Pages/T002306_Determining_the_Least_Common_Mu

ltiple.html

Cropping Graphics
It is
not unusual to place graphics in a worksheet. Excel provides a few tools you
can use to adjust the appearance of the graphic once it is placed.
http://exceltips.vitalnews.com/Pages/T002196_Cropping_Graphics.html
Moving and Copying Graphics Objects
Graphics,
when first added to a worksheet, are probably not in the final place that you
want them. Moving and copying them is easy, once you know the simple
http://exceltips.vitalnews.com/Pages/T002317_Moving_and_Copying_Graphics_Obj

ects.html

Editing a Toolbar Button Image
Don’t
like the toolbar button images in your version of Excel? Don’t fret; you can
pull up the button editor and make changes pixel by pixel, if desired.
http://exceltips.vitalnews.com/Pages/T002726_Editing_a_Toolbar_Button

_Image.html

Opening a Workbook as Read-Only
Want to
work on a workbook but not accidentally change the original workbook file?
The best way to protect yourself is to open the workbook as read-only.
http://exceltips.vitalnews.com/Pages/T002223_Opening_a_Workbook_as_Re

adOnly.html

Making a Cell’s Contents Bold within a Macro
If you
are formatting or processing data within a macro, you might want to make the
contents of a particular cell bold. This tip explains how easy it is,
http://exceltips.vitalnews.com/Pages/T002485_Making_a_Cells_Contents_Bold_wi

thin_a_Macro.html

Turning Off Screen Updating
Your
Excel macros may be a bit slow if they do a lot of changes to data in your
worksheets. You an significantly speed up the macros by adding just a couple
http://exceltips.vitalnews.com/Pages/T002498_Turning_Off_Screen_Updat

ing.html

Understanding Column Widths
A handy
explanation of how Excel determines the column widths. Provided by ExcelTips,
a free weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002121_Understanding_Column_Wid

ths.html

Printing Workbook Properties
When you
take a look at the Properties dialog box associated with any workbook, you’ll
notice that Excel tracks quite a bit of information about the
http://exceltips.vitalnews.com/Pages/T002491_Printing_Workbook_Proper

ties.html

Using the CONCATENATE Worksheet Function
Want to
join snippets of text together in a cell? You can do this using the
CONCATENATE worksheet function, as described in this tip.
http://exceltips.vitalnews.com/Pages/T002433_Using_the_CONCATENATE_Worksheet

_Function.html

Converting Text Case
If your
worksheets include lots of text, you may need this VBA macro to convert large
ranges of text from upper to lower case. Provided by ExcelTips, a free
http://exceltips.vitalnews.com/Pages/T001970_Converting_Text_Case.htm

l

Merging Cells to a Single Sum
Want to
get rid of a bunch of values but keep the sum of those values in your
worksheet? The technique described in this tip can make quick work of this
http://exceltips.vitalnews.com/Pages/T003026_Merging_Cells_to_a_Singl

e_Sum.html

Converting Radians to Degrees
The
DEGREES function, explained in this tip, is used to convert radians to
degrees. Provided by ExcelTips, a free weekly productivity newsletter: www.
http://exceltips.vitalnews.com/Pages/T002162_Converting_Radians_to_De

grees.html

Returning a Worksheet Name
It can
be handy to reference the name of a worksheet tab directly in a cell of the
worksheet. This tip explains a couple of ways you can accomplish this
http://exceltips.vitalnews.com/Pages/T002146_Returning_a_Worksheet_Na

me.html

Finding the Nth Root of a Number
Figuring
out square roots is easy, but how do you figure out third, fourth, fifth, or
any number of other roots? While Excel doesn’t provide built-in
http://exceltips.vitalnews.com/Pages/T002172_Finding_the_Nth_Root_of_

a_Number.html

Shortcut to Merge Cells
Merging
cells is a common activity, so it is a little surprising that Excel doesn’t
include a shortcut for the task. You can, however, create your own
http://exceltips.vitalnews.com/Pages/T003095_Shortcut_to_Merge_Cells.

html

Shortcut for Selecting a Data Range
The
easiest way to select a contiguous data range is to use Ctrl+Shift+This tip
introduces the shortcut and explains how it works.
http://exceltips.vitalnews.com/Pages/T002531_Shortcut_for_Selecting_a_Data_R

ange.html

Cannot Format Cells
There
are some known problems importing a file from Access 2002 into Excel 2000 and
Excel 97 that will not allow a user to format cells the imported data is
http://exceltips.vitalnews.com/Pages/T002596_Cannot_Format_Cells.html

Getting Help when Entering Functions
Remembering
the different parameters you can use with a worksheet function is sometimes
difficult, particularly if the function is one you use only
http://exceltips.vitalnews.com/Pages/T003059_Getting_Help_when_Entering_Func

tions.html

Getting Input from a Text File
Your
macros, if desired, can open and manipulate text files. This tip explains how
this can be done using several simple VBA commands.
http://exceltips.vitalnews.com/Pages/T002227_Getting_Input_from_a_Tex

t_File.html

Flipping a Drawing Object
Drawing
objects can easily be flipped horizontally or vertically in Excel. How you
accomplish the task depends, of course, on the version of Excel you are
http://exceltips.vitalnews.com/Pages/T002447_Flipping_a_Drawing_Objec

t.html

Changing Paper Size for a Complete Workbook
Do you
need to change the type of paper used to print each worksheet in a workbook.
There are two ways you can make a universal change, and the one you
http://exceltips.vitalnews.com/Pages/T002896_Changing_Paper_Size_for_a_Compl

ete_Workbook.html

Putting Headers and Footers On Multiple

Worksheets

Do you
need to put the same header or footer on a range of worksheets in the same
workbook? It’s easier to do than you think, following the technique in
http://exceltips.vitalnews.com/Pages/T002600_Putting_Headers_and_Footers_On_

Multiple_Worksheets.html

How Excel Stores Dates and Times
Excel is
outstanding at working with numbers. In fact, it stores both dates and times
internally as numbers. This tip explains how that storage is done.
http://exceltips.vitalnews.com/Pages/T002176_How_Excel_Stores_Dates_a

nd_Times.html

Reading Values from Graphs
Adding a
trendline to a graph will allow you to see trends a bit easier, depending on
your data. You can even utilize an option that allows you to see what
http://exceltips.vitalnews.com/Pages/T003247_Reading_Values_from_Grap

hs.html

Using Named Formulas or Constants
An easy
way to name a formula or constant value. Provided by ExcelTips, a free weekly
productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002659_Using_Named_Formulas_or_Constan

ts.html

Converting Numbers to Strings
Need to
convert a numeric value into a string? It’sa snap to do by using the Str
function in your macros. Provided by ExcelTips, a free weekly productivity
http://exceltips.vitalnews.com/Pages/T002334_Converting_Numbers_to_St

rings.html

Updating Multiple PivotTables at Once
PivotTables
are great for analyzing large amounts of data. If you have a workbook that
contains multiple PivotTables, you may want to update all those
http://exceltips.vitalnews.com/Pages/T002287_Updating_Multiple_PivotTables_a

t_Once.html

Protecting a Graphic
If you
place a graphic in a worksheet, you may want to protect it so that it cannot
be deleted. The ability to protect a graphic depends on where you place
http://exceltips.vitalnews.com/Pages/T003118_Protecting_a_Graphic.htm

l

Making AutoComplete Work for an Entire Column
AutoComplete
is a great feature for quickly adding data to a worksheet. If you are
confused by why some things are picked up by AutoComplete and others
http://exceltips.vitalnews.com/Pages/T001986_Making_AutoComplete_Work_for_an

_Entire_Column.html

Putting Cell Contents in Footers
Do you
need the contents of a particular cell to always appear in the footer of a
worksheet? You can take care of this using a macro, as described in this
http://exceltips.vitalnews.com/Pages/T002522_Putting_Cell_Contents_in

_Footers.html

Combining Cell Contents
The
formulas you can create in Excel can be simple or complex, but there are some
basic building blocks you need to use. This tip examines one building
http://exceltips.vitalnews.com/Pages/T002623_Combining_Cell_Contents.

html

Pausing Macros for User Input
Your
macros may need to get some input from whoever is using those macros. The
best way to get that input is through the use of the MsgBox and InputBox
http://exceltips.vitalnews.com/Pages/T002561_Pausing_Macros_for_User_

Input.html

Understanding Outlining
Outlining,
a feature built into Excel, can be a great way to help organize large amounts
of data. This tip provides an overview to the feature,
http://exceltips.vitalnews.com/Pages/T002542_Understanding_Outlining.

html

Formatting a PivotTable
You can
format PivotTables using either manual formatting or automatic formatting.
You need to be careful, however, as only one type of formatting will not
http://exceltips.vitalnews.com/Pages/T002482_Formatting_a_PivotTable.

html

Determining the Current Directory
When
creating a macro, you may need to determine what the current directory is
according to Windows. You can easily figure this out by using the CurDir
http://exceltips.vitalnews.com/Pages/T002572_Determining_the_Current_

Directory.html

Removing Hyperlinks without a Macro, Take Two
Need to
get rid of hyperlinks in a worksheet? Here’s an easy way to do it without
using a macro. Provided by ExcelTips, a free weekly productivity
http://exceltips.vitalnews.com/Pages/T002831_Removing_Hyperlinks_without_a_M

acro_Take_Two.html

Drawing Lines
Want to
add some lines to your worksheet? You can add all sorts of them by following
the techniques described in this tip. Provided by ExcelTips, a free
http://exceltips.vitalnews.com/Pages/T002460_Drawing_Lines.html
Suppressing Zero Values in PivotTables
PivotTables
are great for digesting and analyzing huge amounts of data. But what if you
want part of that data excluded, such as information that has a zero
http://exceltips.vitalnews.com/Pages/T002041_Suppressing_Zero_Values_in_Pivo

tTables.html

Selecting the First Cell In a Row
Write
enough macros, and it is inevitable that you will need to select the
left-most cell in a given row. This tip explains the technique to use.
http://exceltips.vitalnews.com/Pages/T002329_Selecting_the_First_Cell

_In_a_Row.html

Undoing an AutoFormat
Excel
allows you to automatically format data tables using a number of different
schemes. What happens if you want to undo this type of formatting?
http://exceltips.vitalnews.com/Pages/T002138_Undoing_an_AutoFormat.ht

ml

Controlling the Plotting of Empty Cells
Excel
provides a great deal of control in how it constructs a chart based on
worksheet data. This tip explains how you can configure Excel to treat empty
http://exceltips.vitalnews.com/Pages/T003054_Controlling_the_Plotting_of_Emp

ty_Cells.html

Converting a Range of URLs to Hyperlinks
When you
enter a URL into a cell of your worksheet and press Enter, Excel normally
recognizes it as a URL and automatically converts it to a hyperlink.
http://exceltips.vitalnews.com/Pages/T003110_Converting_a_Range_of_URLs_to_H

yperlinks.html

Determining a Random Value
One of
the better-known VBA functions is Rnd, which allows you to generate a random
value between 0 and You can use this for all sorts of purposes, …
http://exceltips.vitalnews.com/Pages/T002340_Determining_a_Random_Val

ue.html

Limiting Scroll Area
If you
need to limit the cells that are accessible by the user of a worksheet, VBA
can come to the rescue. This doesn’t involve the use of macros,
http://exceltips.vitalnews.com/Pages/T002669_Limiting_Scroll_Area.htm

l

Unhiding a Single Column
In a
worksheet with lots of hidden columns it is a real pain to try to unhide just
one or two columns. The best solution is to use a macro to do the
http://exceltips.vitalnews.com/Pages/T002405_Unhiding_a_Single_Column

.html

Determining If a Number is Odd or Even
Macros
are used to process values. When working with numeric values, you may need to
know if the value is odd or even. This tip explains how you can make
http://exceltips.vitalnews.com/Pages/T002273_Determining_If_a_Number_is_Odd_

or_Even.html

Trimming Spaces from Strings
It is
not unusual for user-generated input or for text from unknown sources to
include either leading or trailing spaces. VBA includes a trio of functions
http://exceltips.vitalnews.com/Pages/T002303_Trimming_Spaces_from_Str

ings.html

Determining if Caps Lock is On
If you
have a macro that requests user input, you may want to make sure that the
user doesn’t make entries if the Caps Lock key is engaged.
http://exceltips.vitalnews.com/Pages/T002469_Determining_if_Caps_Lock

_is_On.html

Controlling Display of the Formula Bar
The
Formula Bar is a staple of Excel, used for many different purposes. If you
want to get rid of it, you can configure the program so that it is no longer
http://exceltips.vitalnews.com/Pages/T002729_Controlling_Display_of_the_Form

ula_Bar.html

Running Macros on Hidden Worksheets
You can
format a worksheet so that it is hidden from someone looking through the
worksheets in a workbook. This means, however, that you cannot access
http://exceltips.vitalnews.com/Pages/T002548_Running_Macros_on_Hidden_Worksh

eets.html

Understanding the While…Wend Structure
The
programming language in Excel (VBA) provides some powerful structures that
allow you to repeat portions of your program or modify the order in which
http://exceltips.vitalnews.com/Pages/T002261_Understanding_the_WhileWend_Str

ucture.html

Printing an Entire Workbook by Default
Need to
print an entire workbook? It’s as easy as adding a single line of code to
your macros. Provided by ExcelTips, a free weekly productivity newsletter:
http://exceltips.vitalnews.com/Pages/T002001_Printing_an_Entire_Workbook_by_

Default.html

Macro Fails after AutoFilter
When
developing a macro that others may use, you might want to test it out to make
sure it works properly if an AutoFilter is in effect. If it doesn’t,
http://exceltips.vitalnews.com/Pages/T003314_Macro_Fails_after_AutoFi

lter.html

Cell Movement After Enter
Most
people don’t give a second thought as to which way Excel moves after you
press Enter. Excel thinks about it a lot, however, and allows you to tell it
http://exceltips.vitalnews.com/Pages/T002096_Cell_Movement_After_Ente

r.html

Pulling Cell Names into VBA
Excel
allows you to create names for cells, ranges of cells, constants, and
formulas. These names are very helpful in creating worksheet formulas.
http://exceltips.vitalnews.com/Pages/T002283_Pulling_Cell_Names_into_

VBA.html

Changing Directories in a Macro
Macros
can do all sorts of manipulations of files and directories. One command you
can use is the ChDir command, which allows you to change from one disk
http://exceltips.vitalnews.com/Pages/T002472_Changing_Directories_in_

a_Macro.html

Automating Copying Macros
You can
manually copy macros from one workbook to another, but what if you want to
automate the copying process? Here’s some ideas you can try out.
http://exceltips.vitalnews.com/Pages/T002010_Automating_Copying_Macro

s.html

Evaluating Formulas
Need a
bit of help in figuring out how Excel is evaluating a particular formula?
It’s easy to figure out if you use the Evaluate Formula tool.
http://exceltips.vitalnews.com/Pages/T002999_Evaluating_Formulas.html

Getting User Input in a Dialog Box

When
writing macros, you often need to get user input. One way of doing that is
with the InputBox function. This tip explains how to use this function in
http://exceltips.vitalnews.com/Pages/T002266_Getting_User_Input_in_a_

Dialog_Box.html

Grouping and Ungrouping Objects
Graphics
can be placed easily into a worksheet. You can combine graphics
together—group them—so they are treated as a single unit. This tip explains
how.
http://exceltips.vitalnews.com/Pages/T002466_Grouping_and_Ungrouping_

Objects.html

Can’t Open Multiple Workbooks from the Desktop
Do you
want to open multiple workbooks, all at the same time, from your Windows
desktop? The trick is in what you do with the workbooks once they are
http://exceltips.vitalnews.com/Pages/T002427_Cant_Open_Multiple_Workbooks_fr

om_the_Desktop.html

Changing Cell Colors
There
are two ways that you can fill a cell with a color. The first is to use the
Fill Color tool from the Formatting toolbar, and the other is to use the
http://exceltips.vitalnews.com/Pages/T002757_Changing_Cell_Colors.htm

l

Determining Differences Between Dates
Need to
do a bit of “date math” in a macro? It’s easy using the DateDiff function,
described in this tip. Provided by ExcelTips, a free weekly productivity
http://exceltips.vitalnews.com/Pages/T002535_Determining_Differences_Between

_Dates.html

Creating a String in a Macro
In VBA,
the String function can be used to create a string consisting of almost any
number of characters. This tip explains how to use the function in your
http://exceltips.vitalnews.com/Pages/T002325_Creating_a_String_in_a_M

acro.html

Reversing Cell Contents
Need to
reverse what is in a cell? Use a quick user-defined function, presented in
this tip, to accomplish the feat. Provided by ExcelTips, a free weekly
http://exceltips.vitalnews.com/Pages/T002322_Reversing_Cell_Contents.

html

Creating a Center Across Selection Button
The
Merge and Center tool is used to do just what it says—merge cells and center
their contents. If you want to center information across cells without
http://exceltips.vitalnews.com/Pages/T001944_Creating_a_Center_Across_Select

ion_Button.html

Putting Spreadsheet Names in Headers or Footers
Adding
the spreadsheet name into a custom header or footer. Provided by ExcelTips, a
free weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002638_Putting_Spreadsheet_Names_in_He

aders_or_Footers.html

Drawing Borders
Adding
borders around cells is a common formatting task. You can make the task more
intuitive by actually drawing the borders using the mouse.
http://exceltips.vitalnews.com/Pages/T002397_Drawing_Borders.html
Controlling Where You Edit Cell Contents
Editing
information in a worksheet can be done in two places: in the Formula bar or
in the cell itself. You can turn off the ability to edit in the cell by
http://exceltips.vitalnews.com/Pages/T003023_Controlling_Where_You_Edit_Cell

_Contents.html

Adding Text Boxes to Charts
Need to
add a text box to your charting masterpiece? There are a couple of ways you
can do so. Provided by ExcelTips, a free weekly productivity newsletter:
http://exceltips.vitalnews.com/Pages/T003000_Adding_Text_Boxes_to_Cha

rts.html

Hiding and Unhiding Rows
Excel
allows you to hide rows from sight. Just select the rows and choose Format |
Row | Hide, and they are gone. This tip discusses not only hiding rows,
http://exceltips.vitalnews.com/Pages/T002122_Hiding_and_Unhiding_Rows

.html

Entering Info into Multiple Cells
Want a
quick way to enter the same information into a large group of cells? This tip
explains a handy shortcut that doesn’t require any copying or
http://exceltips.vitalnews.com/Pages/T002094_Entering_Info_into_Multi

ple_Cells.html

Making Short Work of Menu Names
If your
menu bar has become cluttered due to different add-ins you’ve added, you can
free up space by shortening the menu names.
http://exceltips.vitalnews.com/Pages/T002381_Making_Short_Work_of_Menu_Names

.html

Setting Data Validation Input Messages
If you
are using data validation in a worksheet, you can instruct Excel to display a
message every time the validating cell is selected.
http://exceltips.vitalnews.com/Pages/T002854_Setting_Data_Validation_Input_M

essages.html

Clean Up Your Macro List
Over
time you may accumulate a whole collection of macros you no longer need,
particularly if those macros were created for only one or two uses.
http://exceltips.vitalnews.com/Pages/T002297_Clean_Up_Your_Macro_List

.html

Viewing Formulas versus Results
Instructing
Excel to display the formulas used instead of the results. Provided by
ExcelTips, a free weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002641_Viewing_Formulas_versus_

Results.html

Converting Cells to Proper Case
An Excel
macro to change cells from uppercase to lowercase. Provided by ExcelTips, a
free weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002026_Converting_Cells_to_Prop

er_Case.html

Using Strikethrough Formatting
Need a
line through the middle of your text? Use strikethrough formatting, which is
easy to apply using the Format Cells dialog box.
http://exceltips.vitalnews.com/Pages/T003305_Using_Strikethrough_Form

atting.html

Turning Off AutoFill
AutoFill
can be a great timesaver when adding information to a worksheet. Even so,
some users may not want the feature to be available, so Excel provides a
http://exceltips.vitalnews.com/Pages/T002958_Turning_Off_AutoFill.htm

l

Using Custom Add-Ins
Add-ins
are special Excel programs that enable features or capabilities not in the
original program. Once you’ve created your own custom add-in,
http://exceltips.vitalnews.com/Pages/T002277_Using_Custom_AddIns.html

Creating an AutoShape
Excel
includes some predefined shapes (called AutoShapes) that you can add o your
worksheet. It’s easy to do once you discover how to access them through
http://exceltips.vitalnews.com/Pages/T002324_Creating_an_AutoShape.ht

ml

Rounding to Powers of 10
Need to
round a value to a power of 10? You can do it by using the ROUND function as
described in this tip. Provided by ExcelTips, a free weekly
http://exceltips.vitalnews.com/Pages/T002404_Rounding_to_Powers_of_10

.html

Running Out of Memory
Do you
get an error when you try to insert just one more chart in your workbook? It
could be because of an obscure resource-related problem in Excel,
http://exceltips.vitalnews.com/Pages/T002732_Running_Out_of_Memory.ht

ml

Setting Text Attributes
Using
the toolbar and the format menu to set the text attributes in an Excel
workbook. Provided by ExcelTips, a free weekly productivity newsletter: www.
http://exceltips.vitalnews.com/Pages/T002675_Setting_Text_Attributes.

html

Running Macros in the Background
Got a
long-running macro that seems to be tying up your machine? You can safely
allow Excel macros to work away in a background window while you continue to
http://exceltips.vitalnews.com/Pages/T002513_Running_Macros_in_the_Ba

ckground.html

Backing Up Your AutoCorrect Entries
If
you’ve invested a great deal of time in putting together your own set of
AutoCorrect entries, you know that they are a valuable asset that you need to
http://exceltips.vitalnews.com/Pages/T002087_Backing_Up_Your_AutoCorrect_Ent

ries.html

Turning Off Error Checking
A little
green triangle in the corner of a cell means that Excel thinks there is an
error with the cell contents. If those triangles bother you,
http://exceltips.vitalnews.com/Pages/T002369_Turning_Off_Error_Checki

ng.html

Renaming a Toolbar
You can
easily create custom toolbars in Excel. Each of the toolbars has a name, and
you can just as easily change that toolbar’s name at a later time.
http://exceltips.vitalnews.com/Pages/T002721_Renaming_a_Toolbar.html
Taskbar Setting isn’t Sticky
Understanding
how Excel sets the taskbars upon opening. Provided by ExcelTips, a free
weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002549_Taskbar_Setting_isnt_Sti

cky.html

Changing Toolbar Location
Toolbars
don’t need to be tethered to the top of your program window. Although they
are right at home there, you may want them some place else on your
http://exceltips.vitalnews.com/Pages/T002066_Changing_Toolbar_Locatio

n.html

Rounding Numbers
You can
use the ROUND function to round numeric values to any number of digits. The
function is easy, once you understand the two arguments you use with it.
http://exceltips.vitalnews.com/Pages/T002147_Rounding_Numbers.html

Cleaning Text
You can
use the CLEAN worksheet function to remove any non-printable characters from
a cell. This can come in handy when you want to pare down what is
http://exceltips.vitalnews.com/Pages/T002454_Cleaning_Text.html
Printing a Multi-Line Footer
Add a
footer to your document and you may want to make it span more than a single
line. This can be easy to do, provided you remember how to “break” the
http://exceltips.vitalnews.com/Pages/T001993_Printing_a_MultiLine_Foo

ter.html

Adding a Little Animation to Your Life
One of
the configuration options in Excel allows you to “spice up” the program’s
interface by adding animation to certain tasks. You can turn this feature
http://exceltips.vitalnews.com/Pages/T002737_Adding_a_Little_Animation_to_Yo

ur_Life.html

Getting the Name of the Parent Workbook
If you
need to insert into a cell the name of the workbook in which a worksheet is
contained, you can use the CELL function. This tip explains the proper
http://exceltips.vitalnews.com/Pages/T002047_Getting_the_Name_of_the_Parent_

Workbook.html

Understanding AutoComplete
Entering
data in a worksheet can be a much easier task if you take advantage of the
AutoComplete feature of Excel. This tip explains what the feature is,
http://exceltips.vitalnews.com/Pages/T002110_Understanding_AutoComple

te.html

Reordering the Display of a Data Series
Once you
create a chart, you aren’t limited to keeping the data series in the order
they originally appeared. You can shift them around by applying the
http://exceltips.vitalnews.com/Pages/T002699_Reordering_the_Display_of_a_Dat

a_Series.html

Bogging Down with Calculated Items

Create a
complex PivotTable and you may find that your system slows to a crawl. The
reason for this may be due to the way in which you directed the
http://exceltips.vitalnews.com/Pages/T002619_Bogging_Down_with_Calcul

ated_Items.html

Adding a Drop Shadow to a Text Box

Text
boxes on a printed page can appear “flat,” almost without definition. You can
make them stand out more by applying shadows to the text box.
http://exceltips.vitalnews.com/Pages/T002505_Adding_a_Drop_Shadow_to_a_Text_

Box.html

Controlling Display of the Scroll Bars
Turning
off the scroll bars through the options menu. Provided by ExcelTips, a free
weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002644_Controlling_Display_of_the_Scro

ll_Bars.html

Understanding Monospace Fonts
There
are two types of fonts available in Windows (and Excel): proportional and
monospace. This tip explains the latter type of font and some of the font
http://exceltips.vitalnews.com/Pages/T002132_Understanding_Monospace_

Fonts.html

Changing Chart Size
Got a
chart that isn’t quite the right size? Adjusting the size is just as easy as
resizing any other graphic, as explained in this tip.
http://exceltips.vitalnews.com/Pages/T002209_Changing_Chart_Size.html

Collating Copies
When you
print worksheets in Excel, you can choose to collate the printed pages or not
collate them. If you are only printing a single sheet of paper,
http://exceltips.vitalnews.com/Pages/T003237_Collating_Copies.html

Who Has the File Open?
If a
workbook is shared on a network server, it is possible that someone could
have it open and prohibit others from opening it. This tip discusses ways you
http://exceltips.vitalnews.com/Pages/T002492_Who_Has_the_File_Open.ht

ml

Odd Behavior when Opening a Shared File with a
Shortcut
Troubleshooting
Excel when a shortcut no longer works on just one computer. Provided by
ExcelTips, a free weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002470_Odd_Behavior_when_Opening_a_Sha

red_File_with_a_Shortcut.html

Header and Footer Data Codes
A
listing of command codes for Excel headers and footers. Provided by
ExcelTips, a free weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002056_Header_and_Footer_Data_C

odes.html

Spell-checking Uppercase Words
Excel’s
spell-checker normally checks uppercase words for spelling. If your worksheet
contains a lot of acronyms, you might not want those uppercase words
http://exceltips.vitalnews.com/Pages/T003321_Spellchecking_Uppercase_

Words.html

Controlling Sorting Order
You can
sort items in a data table in either ascending or descending order. Another
way to affect sorting order, as described in this tip,
http://exceltips.vitalnews.com/Pages/T002921_Controlling_Sorting_Orde

r.html

Nifty Zooming
If you
are using a mouse that has a center wheel, you can use the wheel to zoom in
and out of your work. This tip shows how easy it is to make the necessary
http://exceltips.vitalnews.com/Pages/T002966_Nifty_Zooming.html
Enlarging the Formula Bar
When the
Formula bar contains a very long formula, it can overlay information in your
worksheet. You cannot enlarge the Formula bar, but you can control
http://exceltips.vitalnews.com/Pages/T002730_Enlarging_the_Formula_Ba

r.html

Copying Conditional Formatting
Conditional
formatting is a very powerful feature in Excel. If you want to copy
conditional formatting from one cell to a group of another cells,
http://exceltips.vitalnews.com/Pages/T002797_Copying_Conditional_Form

atting.html

Determining If a File Exists
Macros
can be used to access and manipulate data files on your hard drive. It is a
good idea for the macro to determine if the file exists before actually
http://exceltips.vitalnews.com/Pages/T002516_Determining_If_a_File_Ex

ists.html

Accessing Old Excel Data
If you
have some legacy data stored in very old spreadsheet files, you may be
wondering how to get that data into an Excel worksheet.
http://exceltips.vitalnews.com/Pages/T001973_Accessing_Old_Excel_Data

.html

How Many Rows and Columns Have I Selected?
When
selecting a range of cells, it is often helpful to know exactly how many rows
or columns you’ve selected. Fortunately, Excel provides this information
http://exceltips.vitalnews.com/Pages/T002474_How_Many_Rows_and_Columns_Have_

I_Selected.html

Creating a New Toolbar
Excel’s
interface can be easily modified to reflect the way you want to do your work.
This tip explains how you can create a custom toolbar with just the
http://exceltips.vitalnews.com/Pages/T002722_Creating_a_New_Toolbar.h

tml

Using the TRUNC Worksheet Function

The
TRUNC function is used to return the integer portion of a value—the part to
the left of the decimal point. It is similar to the INT function,
http://exceltips.vitalnews.com/Pages/T002151_Using_the_TRUNC_Worksheet_Funct

ion.html

Superscripts in Custom Formats
The
Format | Cells option is not available while you are creating a custom
format. This means you need to be more creative when doing your formats;
http://exceltips.vitalnews.com/Pages/T001946_Superscripts_in_Custom_F

ormats.html

Adjusting Row Height for Your Text

If you
have a lot of text in a cell or group of cells, you can have Excel
automatically wrap the text within the cell and adjust the row height.
http://exceltips.vitalnews.com/Pages/T003187_Adjusting_Row_Height_for

_Your_Text.html

Reducing File Size
Adding
macros to a workbook can make the file larger that contains that workbook.
This tip explains why this happens and some ways you can make the workbook
http://exceltips.vitalnews.com/Pages/T002507_Reducing_File_Size.html
File Format Not Valid
Sometimes
Excel can give some very cryptic errors. Such is the case with Bob, who ran
into two “File Format is not Valid” errors.
http://exceltips.vitalnews.com/Pages/T003166_File_Format_Not_Valid.ht

ml

Black and White Blues
If your
color worksheets and charts don’t look exactly right when printed on your
monochrome printer, it could be because the printer doesn’t handle the
http://exceltips.vitalnews.com/Pages/T002848_Black_and_White_Blues.ht

ml

Inserting Rows
Inserting
a single row is easy when you use the Insert menu. When you need to insert
multiple rows, then you can use the simple techniques described in this
http://exceltips.vitalnews.com/Pages/T002339_Inserting_Rows.html
Rounding Time
Do you
need to round time values to the nearest quarter hour or some other portion
of an hour? You can use the MROUND function (part of the Analysis
http://exceltips.vitalnews.com/Pages/T002186_Rounding_Time.html
Ordering Worksheets Based on a Cell Value
Macros
that sort your worksheets based on their name have been around for quite some
time. You may not want to sort the worksheets based on name,
http://exceltips.vitalnews.com/Pages/T002890_Ordering_Worksheets_Based_on_a_

Cell_Value.html

Working with Fonts
Excel
has the ability to use any font Windows can use. Provided by ExcelTips, a
free weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T001941_Working_with_Fonts.html
Grabbing a User’s Name from Excel
One of
the settings you can make in Excel is to specify a user’s name. This name is
accessible through macros, and can therefore be used within your
http://exceltips.vitalnews.com/Pages/T003289_Grabbing_a_Users_Name_fr

om_Excel.html

Quickly Entering Dates and Times
Do you
use Excel to track dates and times, perhaps for a timesheet? You can make
entering the data much easier if you learn a couple of quick shortcuts that
http://exceltips.vitalnews.com/Pages/T002178_Quickly_Entering_Dates_a

nd_Times.html

Generating a List of Macros
Creating
a macro to compile a list of macros in an Excel workbook. Provided by
ExcelTips, a free weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002715_Generating_a_List_of_Mac

ros.html

Getting Rid of All Hyperlinks
Need to
get rid of all the hyperlinks in a worksheet? It’s easy when you use this
single-line macro. Provided by ExcelTips, a free weekly productivity
http://exceltips.vitalnews.com/Pages/T002005_Getting_Rid_of_All_Hyper

links.html

Using the REPT Function
The REPT
worksheet function is helpful for repeating a character or series of
characters through the whole width of a cell. This tip explains how to use
http://exceltips.vitalnews.com/Pages/T002160_Using_the_REPT_Function.

html

Returning the Smallest Non-Zero Value
If you
have a range of values that include some zero values, you may want to find
out what the smallest value is within that range that isn’t zero.
http://exceltips.vitalnews.com/Pages/T003260_Returning_the_Smallest_NonZero_

Value.html

Changes in Excel 2007 Color System

Everyone
knows that there have been some major changes in the user interface used by
Excel 200There have been some big changes “under the hood,” as well.
http://exceltips.vitalnews.com/Pages/T003285_Changes_in_Excel_2007_Color_Sys

tem.html

Closing All Open Workbooks
Excel
provides a handy (but little-known) shortcut for closing all the workbooks
you have open. This tip explains how easy it is to use this shortcut.
http://exceltips.vitalnews.com/Pages/T003065_Closing_All_Open_Workboo

ks.html

Connecting To a Discussion Server
The
first task in using the Web Discussions feature is to connect to a discussion
server. This tip explains the steps you need to go through to accomplish
http://exceltips.vitalnews.com/Pages/T002953_Connecting_To_a_Discussi

on_Server.html

Sorting Files
The Open
dialog box allows you to sort the files it presents to you. How you do the
sorting depends on the version of Word and Windows you are using.
http://exceltips.vitalnews.com/Pages/T002240_Sorting_Files.html
Changing the Default Drive
Macros
often need to work on various files on your disk drive in order to process
data in a worksheet. You can specify the drive that your macro should use
http://exceltips.vitalnews.com/Pages/T002541_Changing_the_Default_Dri

ve.html

Changing the Comment Font
The
comment boxes font and color can be customized using this tip. Provided by
ExcelTips, a free weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002628_Changing_the_Comment_Fon

t.html

Appending to a Non-Document Text File
Do you
need to write information out to a text file so it can be used by other
programs? You can easily accomplish this task using a macro, as described
http://exceltips.vitalnews.com/Pages/T002536_Appending_to_a_NonDocument_Text

_File.html

Recalculating when Filtering
Filter a
large worksheet, and Excel will helpfully recalculate every time you apply a
different filter. This can get bothersome, particularly if the
http://exceltips.vitalnews.com/Pages/T003136_Recalculating_when_Filte

ring.html

Formatting for Hundredths of Seconds
Want to
record times down to the hundredth of a second in Excel? You can do so if you
know how to create a custom format that displays the time properly.
http://exceltips.vitalnews.com/Pages/T003085_Formatting_for_Hundredths_of_Se

conds.html

Counting the Number of Blank Cells

Do you
want to know how many blank cells are within a range? You can use the
COUNTBLANK function, described in this tip, to get the answer.
http://exceltips.vitalnews.com/Pages/T002157_Counting_the_Number_of_Blank_Ce

lls.html

Repeating Rows at the Bottom of a Page
Excel
allows you to repeat rows at the top of every page of a printout. If you want
to repeat rows at the bottom of every page, there is no easy way to
http://exceltips.vitalnews.com/Pages/T002361_Repeating_Rows_at_the_Bottom_of

_a_Page.html

Short-Lived Book1
When you
first start Excel, a blank workbook named Book1 is opened, by default. If
some other workbook opens—and you haven’t configured Excel to start that
http://exceltips.vitalnews.com/Pages/T003048_ShortLived_Book1.html

Using BIN2DEC In a Macro
The
BIN2DEC worksheet function is used to convert a binary value into its decimal
equivalent. Because it is part of the Analysis ToolPak, using the BIN2DEC
http://exceltips.vitalnews.com/Pages/T003063_Using_BIN2DEC_In_a_Macro

.html

Converting Units
Using
the Analysis ToolPak for the vast conversions the CONVERT function in an
Excel worksheet has availabile. Provided by ExcelTips, a free weekly
http://exceltips.vitalnews.com/Pages/T002630_Converting_Units.html

Locking Worksheet Names
Want to
stop other people from changing the names of your worksheets? You can provide
the desired safeguard by using the workbook protection features built
http://exceltips.vitalnews.com/Pages/T002689_Locking_Worksheet_Names.

html

Special Characters In Hyperlinks
The
typical way of creating a hyperlink is to use the Insert Hyperlink tool.
Doing so, while easy, can limit what characters you can use in the link.
http://exceltips.vitalnews.com/Pages/T002004_Special_Characters_In_Hy

perlinks.html

Cropping Pictures
How to
crop a graphic placed in an Excel worksheet. Provided by ExcelTips, a free
weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T001997_Cropping_Pictures.html
Reordering Last Name and First Name
Names
listed in a worksheet are often entered last name first. This tip provides a
formula that can “turn around” such names so that the last name appears
http://exceltips.vitalnews.com/Pages/T002168_Reordering_Last_Name_and_First_

Name.html

Styles for Lines, Dashes, and Arrows

When you
add lines or arrows to your worksheet, you can format those objects in a
variety of ways. Excel allows you to modify the style, weight,
http://exceltips.vitalnews.com/Pages/T002461_Styles_for_Lines_Dashes_

and_Arrows.html

Dealing with Long Formulas
Formulas
in Excel can, at times, be very long. By judicious use of Alt+Enter, you can
make your formulas easier to understand and less overwhelming.
http://exceltips.vitalnews.com/Pages/T003043_Dealing_with_Long_Formul

as.html

Preparing a Chart Sheet for Printing
Excel
allows you to create charts in two general formats: on their own sheet or as
an embedded object in a worksheet. If you’ve created a chart sheet,
http://exceltips.vitalnews.com/Pages/T002849_Preparing_a_Chart_Sheet_

for_Printing.html

Setting Your Default Directory
How to
set a new default directory for your Excel file. Provided by ExcelTips, a
free weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002632_Setting_Your_Default_Dir

ectory.html

Calculating Combinations
The
COMBIN worksheet function is used to calculate the number of different
combinations that can be derived from a given set. This tip explains the two
http://exceltips.vitalnews.com/Pages/T002158_Calculating_Combinations

.html

Fixing "Can’t Find Files" Errors
If you
get errors about unfindable files when you first start Excel, it can be
frustrating. Here’s how to track down and fix the problem.
http://exceltips.vitalnews.com/Pages/T002375_Fixing_Cant_Find_Files_E

rrors.html

Searching Comments
Need to
find the next comment in your workbook? You can use the Find and Replace
capabilities of Excel to do just that. Provided by ExcelTips, a free weekly
http://exceltips.vitalnews.com/Pages/T002945_Searching_Comments.html
Setting Column Width in a Macro
Need to
change the layout of your worksheet in a macro? You can adjust the width of
individual columns by using the ColumnWidth property.
http://exceltips.vitalnews.com/Pages/T002558_Setting_Column_Width_in_

a_Macro.html

Importing Many Files Into Excel
Excel
includes import filters that allow you to load data from non-Excel formatted
files into a worksheet. (The CSV import filter is an example.
http://exceltips.vitalnews.com/Pages/T002225_Importing_Many_Files_Int

o_Excel.html

Creating Charts in VBA
Most
charts you create in Excel are based on information stored in a worksheet.
You can also create charts based on information supplied by a macro,
http://exceltips.vitalnews.com/Pages/T002622_Creating_Charts_in_VBA.h

tml

Undoing Smart Tag Exclusions
Smart
Tags are a handy way to get more information related to some words or letters
in a worksheet, such as financial or stock symbols.
http://exceltips.vitalnews.com/Pages/T003094_Undoing_Smart_Tag_Exclus

ions.html

Understanding Operators
An
explanation of the operators used in Excel. Provided by ExcelTips, a free
weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T001921_Understanding_Operators.

html

Inserting the Saved Date In a Header or Footer
Using a
macro to set the date the workbook is saved in the header or footer. Provided
by ExcelTips, a free weekly productivity newsletter: www.
http://exceltips.vitalnews.com/Pages/T002055_Inserting_the_Saved_Date_In_a_H

eader_or_Footer.html

Forcing a Macro to Run When a Worksheet is
Recalculated
Normally
a macro is only calculated when you specifically tell Excel to calculate it.
Some macros need to be calculated whenever your worksheet is
http://exceltips.vitalnews.com/Pages/T002013_Forcing_a_Macro_to_Run_When_a_W

orksheet_is_Recalculated.html

Indirect References to a DSUM Parameter
DSUM,
like most other Excel worksheet functions, requires parameters that define
how the function does its work. If you don’t provide those parameters in
http://exceltips.vitalnews.com/Pages/T002885_Indirect_References_to_a_DSUM_P

arameter.html

Turning Off Capital Corrections
Excel,
in an effort to be helpful, can modify what you type if it thinks you meant
something different. For instance, if you type a word that begins with
http://exceltips.vitalnews.com/Pages/T002753_Turning_Off_Capital_Corr

ections.html

Changing Excel’s Starting Date
When
storing the serial numbers for dates, Excel can start its calculations from
either of two starting points. This tip explains why there are two starting
http://exceltips.vitalnews.com/Pages/T002413_Changing_Excels_Starting

_Date.html

Automatic Recalculation Not Happening
Excel
97, under certain circumstances, has a problem recalculating worksheets
reliably. This tip tells you how to patch that issue using a link to
http://exceltips.vitalnews.com/Pages/T002082_Automatic_Recalculation_Not_Hap

pening.html

Functioning Check Boxes in a Protected Worksheet
When you
insert check boxes into a worksheet from the Forms toolbar, you need to link
or associate the check box with a cell. If you protect the worksheet,
http://exceltips.vitalnews.com/Pages/T003078_Functioning_Check_Boxes_in_a_Pr

otected_Worksheet.html

Exact Formula Copies
When you
copy a formula from one cell to another, Excel normally adjusts the cell
references within the formula so they are relative to where the formula is
http://exceltips.vitalnews.com/Pages/T002990_Exact_Formula_Copies.htm

l

Searching for Line Breaks
If you
have line breaks in some of the cells of your worksheet, you might want to
search for them. There is no “special character” you can use for the
http://exceltips.vitalnews.com/Pages/T003220_Searching_for_Line_Break

s.html

Setting Up Your Printer
Printers
have many capabilities that you can take advantage of in Excel, if you just
know how. This tip explains how to access all the features of a printer
http://exceltips.vitalnews.com/Pages/T003235_Setting_Up_Your_Printer.

html

Changing Horizontal Orientation
One of
the international features of Excel is the ability to switch the orientation
of how information is presented. This tip shows how you can change
http://exceltips.vitalnews.com/Pages/T003038_Changing_Horizontal_Orie

ntation.html

Comma-Delimited Differences for PC and Mac
When you
choose to save worksheet data in CSV format, Excel gives you three choices
for file formats. Those choices are detailed in this tip.
http://exceltips.vitalnews.com/Pages/T002519_CommaDelimited_Differences_for_

PC_and_Mac.html

Hiding Rows Based on a Cell Value
This tip
contains a macro to hide rows that contain data you don’t want to see.
Provided by ExcelTips, a free weekly productivity newsletter: www.
http://exceltips.vitalnews.com/Pages/T001940_Hiding_Rows_Based_on_a_C

ell_Value.html

Relative VBA Selections
Macros
often need to access other cells in a worksheet, relative to the currently
selected cell. There are a number of ways you can implement this type of
http://exceltips.vitalnews.com/Pages/T002298_Relative_VBA_Selections.

html

Days Left in the Year
Sometimes
it is handy to know how many days are left in the current year. This tip
provides a quick formula that indicates the number remaining.
http://exceltips.vitalnews.com/Pages/T003051_Days_Left_in_the_Year.ht

ml

Turning Off AutoFill for a Workbook
Don’t
want people using your workbook to be able to use AutoFill? You can add two
quick macros that disable and enable the feature when your workbook is
http://exceltips.vitalnews.com/Pages/T002594_Turning_Off_AutoFill_for_a_Work

book.html

Forcing Stubborn Recalculation
Is Excel
not recalculating your worksheet as you expect? There are two ways you can
force a recalculation, and knowing when to use each method can help you
http://exceltips.vitalnews.com/Pages/T002075_Forcing_Stubborn_Recalcu

lation.html

Controlling the Behavior of the Mouse Wheel
If you
have a mouse that has a track wheel between the two buttons, you can use that
wheel to help navigate around your worksheets. In order to do that,
http://exceltips.vitalnews.com/Pages/T003024_Controlling_the_Behavior_of_the

_Mouse_Wheel.html

Using the Same Range Name on Different Worksheets
Defined
names are normally available throughout a workbook, regardless of the
worksheet on which they are defined. If you know the trick (found in this
tip)
http://exceltips.vitalnews.com/Pages/T002662_Using_the_Same_Range_Name_on_Di

fferent_Worksheets.html

Setting Page Margins
An easy
tip on how to set the page margins of your Excel workbook. Provided by
ExcelTips, a free weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002842_Setting_Page_Margins.htm

l

Deriving an Absolute Value in a Macro
Need to
figure out an absolute value within your macro code? It’s easy to do using
the Abs function, described in this tip. Provided by ExcelTips, a free
http://exceltips.vitalnews.com/Pages/T002446_Deriving_an_Absolute_Value_in_a

_Macro.html

Adding a ScreenTip
When you
visit a Web site in your browser and you roll your mouse over a hyperlink,
you may see a small pop-up tip concerning the link under the mouse.
http://exceltips.vitalnews.com/Pages/T002804_Adding_a_ScreenTip.html
Printing without Opening
Need to
print out a worksheet? Chances are good that you can do it without even
starting up Excel. Provided by ExcelTips, a free weekly productivity
http://exceltips.vitalnews.com/Pages/T002515_Printing_without_Opening

.html

References to Hyperlinks Aren’t Hyperlinks
If you
have a hyperlink in a cell and you reference that cell in a formula, you may
wonder why the result of the formula isn’ta hyperlink as well.
http://exceltips.vitalnews.com/Pages/T003282_References_to_Hyperlinks_Arent_

Hyperlinks.html

Counting Shaded Cells
Ever
want to know how many cells in a worksheet (or a selection) are shaded in
some way? You can create a handy little macro that will do the counting for
http://exceltips.vitalnews.com/Pages/T002059_Counting_Shaded_Cells.ht

ml

Arranging Workbook Windows
If you
have multiple workbooks open at the same time, you can tell Excel to arrange
the windows for those workbooks so you can see them all.
http://exceltips.vitalnews.com/Pages/T002738_Arranging_Workbook_Windo

ws.html

Random Numbers in a Range
The
RANDBETWEEN function is a valuable worksheet function that allows you to
generate a random number between some boundary values that you specify.
http://exceltips.vitalnews.com/Pages/T002812_Random_Numbers_in_a_Rang

e.html

Generating Unique Sequential Numbers
Using
Excel to generate unique sequential numbers for invoices or company
statements. Provided by ExcelTips, a free weekly productivity newsletter:
www.
http://exceltips.vitalnews.com/Pages/T001965_Generating_Unique_Sequential_Nu

mbers.html

Creating a CSV File
Need to
share information with another program? Chances are good that you can do it
if you save your data in CSV (comma separated values) format.
http://exceltips.vitalnews.com/Pages/T002483_Creating_a_CSV_File.html

Changing Gridline Color
The
gridlines used in a workbook are normally light black. You can modify the
gridline color by following the steps in this tip.
http://exceltips.vitalnews.com/Pages/T002736_Changing_Gridline_Color.

html

Ignoring Other Applications
If Excel
is giving you problems when working with other applications, you may want to
configure Excel so that it doesn’t share information with other
http://exceltips.vitalnews.com/Pages/T003046_Ignoring_Other_Applicati

ons.html

Swapping Two Numbers
Need to
exchange what is stored in two variables in a macro? It’s easy to do if you
apply the technique in this tip. Provided by ExcelTips, a free weekly
http://exceltips.vitalnews.com/Pages/T002525_Swapping_Two_Numbers.htm

l

Resetting Toolbars to Their Default
Once
you’ve edited your toolbars, you may want to change them back to their
default appearance and behavior. This tip explains how you can quickly revert
to
http://exceltips.vitalnews.com/Pages/T002727_Resetting_Toolbars_to_Th

eir_Default.html

More Than Two Conditions for Custom AutoFiltering
Using
the AutoFilter feature of Excel, you can define a custom filter that controls
what information you see from a data table. Custom AutoFilters allow you
http://exceltips.vitalnews.com/Pages/T003036_More_Than_Two_Conditions_for_Cu

stom_AutoFiltering.html

Adding Leading Zeroes to ZIP Codes

If you
import some address data into Excel, you may notice that the leading zeroes
from your ZIP Codes have been chopped off. Here’s the reason why and what
http://exceltips.vitalnews.com/Pages/T002598_Adding_Leading_Zeroes_to_ZIP_Co

des.html

Solving Simultaneous Equations
Simultaneous
equations are those in which there are multiple variables that need to be
solved for. A full discussion of how to handle simultaneous equations
http://exceltips.vitalnews.com/Pages/T003079_Solving_Simultaneous_Equ

ations.html

DOS From Macros
Do you
need to execute some DOS (command line) commands from within a macro? You can
do so by using the Shell command, as described in this tip.
http://exceltips.vitalnews.com/Pages/T002506_DOS_From_Macros.html
Creating Individual Workbooks
As a
workbook grows, there may be a time when you need to “break out” all the
worksheets to their own workbooks. This tip presents a way that you can
http://exceltips.vitalnews.com/Pages/T002230_Creating_Individual_Work

books.html

Non-Tiled Background Pictures
Background
images for a worksheet are automatically repeated over and over (tiled) by
Excel. This tip describes a workaround you can use to minimize the
http://exceltips.vitalnews.com/Pages/T002203_NonTiled_Background_Pict

ures.html

Sorting or Filtering by Conditional Format

Results

Conditional
formatting is a great feature in Excel. Unfortunately, you can’t sort or
filter by the results of that formatting. Here’s why.
http://exceltips.vitalnews.com/Pages/T002384_Sorting_or_Filtering_by_Conditi

onal_Format_Results.html

Counting Colors of Cells
Many
people use colors of cells as a common method of communicating information in
a worksheet. If you need a way to count cells that are formatted with a
http://exceltips.vitalnews.com/Pages/T001978_Counting_Colors_of_Cells

.html

Understanding Macros
As you
can tell from reading various ExcelTips, macros are used for a wide variety
of purposes that expand what you can do with Excel.
http://exceltips.vitalnews.com/Pages/T002256_Understanding_Macros.htm

l

Pasting a Hyperlink
When you
copy information from one workbook to another, you may want to link that
information as a hyperlink. You can quickly achieve this goal by using the
http://exceltips.vitalnews.com/Pages/T002465_Pasting_a_Hyperlink.html

Inserting Tomorrow’s Date
If you
need to add tomorrow’s date to a worksheet, you can do it using simple math.
The formulas provided in this tip are both illuminating and helpful.
http://exceltips.vitalnews.com/Pages/T002529_Inserting_Tomorrows_Date

.html

Combining Columns
You can
concatenate cells using formulas, but you may want a way to concatenate lots
of cells using a macro. The macro presented in this tip handles
http://exceltips.vitalnews.com/Pages/T002116_Combining_Columns.html
Creating a Drawing Object
Excel
worksheets can contain graphics as well as data. When you add drawing
objects, they are placed on a special drawing layer that allows them to be
http://exceltips.vitalnews.com/Pages/T002197_Creating_a_Drawing_Objec

t.html

Protecting Worksheets from Deletion
If you
share a workbook with others in your office, you will probably want to make
sure that some of the worksheets don’t get deleted.
http://exceltips.vitalnews.com/Pages/T003267_Protecting_Worksheets_from_Dele

tion.html

Using the IRR Function
One of
the handy financial functions available in Excel is IRR, which is used to
calculate an internal rate of return for an investment.
http://exceltips.vitalnews.com/Pages/T003209_Using_the_IRR_Function.h

tml

Working With Multiple Printers
If you
have multiple printers attached to (or available from) your system, you can
print to any of them using Excel. For a quick way to switch printers,
http://exceltips.vitalnews.com/Pages/T002217_Working_With_Multiple_Pr

inters.html

Displaying a Hidden First Row
If
someone has hidden the first rows of a worksheet, figuring out how to unhide
them can throw you for a loop—unless you know the handy secret in this tip.
http://exceltips.vitalnews.com/Pages/T002743_Displaying_a_Hidden_Firs

t_Row.html

Negatives in Pie Charts
Pie
charts are a great way to graphically display some types of data. Displaying
negative values is not so great in pie charts, however.
http://exceltips.vitalnews.com/Pages/T003208_Negatives_in_Pie_Charts.

html

Turning Off Insert Options
When you
insert a row, column, or cell, Excel displays a helpful Insert Options
button. To turn this button off so that it is not displayed again,
http://exceltips.vitalnews.com/Pages/T003045_Turning_Off_Insert_Optio

ns.html

Disabling the F1 Key
The
function keys are used for a variety of purposes in Excel. If you want to
disable any of the function keys (so they don’t actually perform their
http://exceltips.vitalnews.com/Pages/T002089_Disabling_the_F1_Key.htm

l

Stepping Through a Macro with a Worksheet Visible
When
developing a macro, it is often necessary to step through the various code
lines so you can see what is happening on the worksheet. This is easy to do;
http://exceltips.vitalnews.com/Pages/T002061_Stepping_Through_a_Macro_with_a

_Worksheet_Visible.html

Correctly Saving Delimited Files
Delimited
files are often created through Excel so that your data can be exported to
other programs. If the delimited data generated by Excel isn’t exactly
http://exceltips.vitalnews.com/Pages/T002589_Correctly_Saving_Delimit

ed_Files.html

Creating Custom Chart Formats
When you
create a chart in Excel, you can spend quite a bit of time getting the look
just the way you want. Don’t let that work go to waste; save the chart
http://exceltips.vitalnews.com/Pages/T003211_Creating_Custom_Chart_Fo

rmats.html

Using Multiple Test Conditions
When
creating conditional formats, you are not limited to only one condition. You
can create up to three conditions, all for the same cell or range of cells
http://exceltips.vitalnews.com/Pages/T002796_Using_Multiple_Test_Cond

itions.html

Determining the Length of a Text File
When
creating a macro to process a text file, you may need to know the length of
that file. You can figure this out by using the LOF function, as described
http://exceltips.vitalnews.com/Pages/T002517_Determining_the_Length_of_a_Tex

t_File.html

Picking a Group of Cells
A group
of cells is different from a contiguous range of cells—sometimes a group of
cells is called a selection set. Here you learn not only the differences
http://exceltips.vitalnews.com/Pages/T001967_Picking_a_Group_of_Cells

.html

Calculating the Day of the Year
Excel
allows you to perform math with date values. That means it is easy (as shown
in this tip) to figure out what day of the year it is and how many days
http://exceltips.vitalnews.com/Pages/T002931_Calculating_the_Day_of_t

he_Year.html

AutoFilling with Weekdays
You can
easily use Excel’s AutoFill feature to place the days of the week in a range
of cells. With a small change in how you use the feature,
http://exceltips.vitalnews.com/Pages/T002315_AutoFilling_with_Weekday

s.html

Sudden Increases in Workbook File Size
As you
add information to a workbook, a little at a time, you’ll notice that the
size of the workbook file slowly increases. If you notice a huge jump in
http://exceltips.vitalnews.com/Pages/T003115_Sudden_Increases_in_Workbook_Fi

le_Size.html

Using Multiple Print Settings
If you
routinely print different parts of your worksheet, you know that it is a pain
to repeatedly set the print area and print. This tip presents two ways
http://exceltips.vitalnews.com/Pages/T002703_Using_Multiple_Print_Set

tings.html

Other "Phantom" Users
Microsoft
has a fix for an Excel 2002 glitch. Provided by ExcelTips, a free weekly
productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002614_Other_Phantom_Users.html

Make that Chart Quickly!
Want to
create a chart in the quickest way possible? This tip introduces a very
simple shortcut key that allows you to create a chart with a single keypress
http://exceltips.vitalnews.com/Pages/T002210_Make_that_Chart_Quickly.

html

Spell Checking Your Worksheet
Using
spell checker on Excel worksheets. Provided by ExcelTips, a free weekly
productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002007_Spell_Checking_Your_Work

sheet.html

Resize Graphics Outside of Excel
Graphics
are a common addition to almost any workbook. If you need to change the size
of your graphics (which Excel lets you do), you may want to give
http://exceltips.vitalnews.com/Pages/T003310_Resize_Graphics_Outside_

of_Excel.html

Protecting Excel Files from Word
Turning
off the file converter to protect your Excel files. Provided by ExcelTips, a
free weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002624_Protecting_Excel_Files_f

rom_Word.html

Print Quantity is Stuck
When you
display the Print dialog box, the number of copies to be printed should
default to one. If this does not happen (there is some other value in the
http://exceltips.vitalnews.com/Pages/T002773_Print_Quantity_is_Stuck.

html

Entering Formulas in Excel
The
ability to enter formulas in a cell is at the heart of Excel’s usefulness.
Excel provides a primary method of entering formulas (using the equal sign)
http://exceltips.vitalnews.com/Pages/T002091_Entering_Formulas_in_Exc

el.html

Deleting a Toolbar
After a
time you may not need one of the custom toolbars you’ve created. This tip
explains how to free up resources in Excel by getting rid of those
http://exceltips.vitalnews.com/Pages/T002723_Deleting_a_Toolbar.html
Using AutoCorrect
AutoCorrect
is a handy tool that allows Excel (and other Microsoft Office applications)
to automatically change what you type to something that is hopefully
http://exceltips.vitalnews.com/Pages/T002852_Using_AutoCorrect.html
Pasting Leading Zeroes
When you
paste data that contains leading zeros into a worksheet, those zeros are
normally dropped by Excel. If you want to retain the zeros, you’ll need to
http://exceltips.vitalnews.com/Pages/T002752_Pasting_Leading_Zeroes.h

tml

Setting Default Print Margins
You may
not like the default margins set by Excel for worksheets. The way to change
those margins is to create a template for new workbooks that has the
http://exceltips.vitalnews.com/Pages/T002211_Setting_Default_Print_Ma

rgins.html

Removing Pictures for a Worksheet in VBA
If you
are having a problem deleting pictures using a macro, it could be due to
either bad syntax or different groupings of the images.
http://exceltips.vitalnews.com/Pages/T002480_Removing_Pictures_for_a_Workshe

et_in_VBA.html

Using the MROUND Worksheet Function
Need to
round to the nearest multiple of some value? The MROUND worksheet function
can round to any nearest value you want to specify.
http://exceltips.vitalnews.com/Pages/T002148_Using_the_MROUND_Worksheet_Func

tion.html

Sorting Data on Protected Worksheets
Protect
a worksheet and you limit exactly what can be done with the data in the
worksheet. One of the things that could be limited is the ability to sort
http://exceltips.vitalnews.com/Pages/T002458_Sorting_Data_on_Protected_Works

heets.html

Renaming Worksheets
Some
easy steps to rename the worksheets in your Excel workbook. Provided by
ExcelTips, a free weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T001949_Renaming_Worksheets.html

Formatting Raw Data
Formatting
raw data into consistent reports on an ongoing basis is easy with this tip.
Provided by ExcelTips, a free weekly productivity newsletter: www.
http://exceltips.vitalnews.com/Pages/T001971_Formatting_Raw_Data.html

Understanding Functions in Macros
Functions
are a special type of subroutine that returns a value to whatever program
called it. This tip explains how to create functions and how to pass
http://exceltips.vitalnews.com/Pages/T002259_Understanding_Functions_

in_Macros.html

Changing Cell Patterns
Cell
patterns are a great way to highlight a cell and its contents. You set a
pattern (or change an existing one) by using the Patterns tab of the Format
http://exceltips.vitalnews.com/Pages/T002763_Changing_Cell_Patterns.h

tml

Controlling Chart Gridlines
When you
create a chart based on your data, Excel automatically applies gridlines,
based on the type of chart you create. With just a few clicks you have
http://exceltips.vitalnews.com/Pages/T003181_Controlling_Chart_Gridli

nes.html

Ctrl+V Pasting is Flakey
When you
copy and paste a formula, you usually want to see the formula where you
pasted. If you don’t get the formula, but instead get the result,
http://exceltips.vitalnews.com/Pages/T002362_CtrlV_Pasting_is_Flakey.

html

Changing Font Color
There
are any number of reasons to format different cells in different colors.
Excel allows you to easily change the color used to display information.
http://exceltips.vitalnews.com/Pages/T002674_Changing_Font_Color.html

Controlling Display of Page Breaks

Page
breaks in a worksheet are helpful, as they allow you to format your data for
printout. It can be helpful to see, on-screen, where those page breaks are
http://exceltips.vitalnews.com/Pages/T003008_Controlling_Display_of_P

age_Breaks.html

Getting Big Macros to Run
Troubleshooting
an Excel macro when it causes the entire computer to freeze. Provided by
ExcelTips, a free weekly productivity newsletter: www.
http://exceltips.vitalnews.com/Pages/T002579_Getting_Big_Macros_to_Ru

n.html

Develop Macros In Their Own Workbook
If you
develop macros and edit them quite a bit, you may be running the risk of
causing problems with the macros or with your workbooks.
http://exceltips.vitalnews.com/Pages/T002566_Develop_Macros_In_Their_Own_Wor

kbook.html

Changing the Font Size in Combo Boxes
When you
add a combo box to a worksheet, Excel makes some assumptions about the best
font size to use in the control. This tip explains how you can select a
http://exceltips.vitalnews.com/Pages/T002555_Changing_the_Font_Size_in_Combo

_Boxes.html

Using a Protected Worksheet
If you
have a worksheet protected, it may not be immediately evident that it really
is protected. This tip explains some of the tell-tale signs you can use
http://exceltips.vitalnews.com/Pages/T002467_Using_a_Protected_Worksh

eet.html

Moving a Worksheet to Another Workbook
It is
easy to move an Excel worksheet to another workbook following this tip.
Provided by ExcelTips, a free weekly productivity newsletter: www.
http://exceltips.vitalnews.com/Pages/T002686_Moving_a_Worksheet_to_Another_W

orkbook.html

Calculating Months of Tenure
If you
have an employee starting date, you can figure out how many months that
employee has been working for your company. All you need to do is apply the
http://exceltips.vitalnews.com/Pages/T002595_Calculating_Months_of_Te

nure.html

Adding Excel Information to a Web Page
Besides
saving a worksheet as a complete Web page, you can also save smaller portions
of your data to an existing Web page. This tip shows how easy such an
http://exceltips.vitalnews.com/Pages/T002468_Adding_Excel_Information_to_a_W

eb_Page.html

Altering the Displayed Format of Numbers to the
Nearest 100
Want
information in a worksheet to be formatted and displayed as rounded to a
power of ten? You may be out of luck, unless you want to round to either the
http://exceltips.vitalnews.com/Pages/T001937_Altering_the_Displayed_Format_o

f_Numbers_to_the_Nearest_100.html

Forcing Input to Uppercase
If you
type information into a workbook, you may want to make sure that what you
type is always stored in uppercase. You can implement this type of feature
http://exceltips.vitalnews.com/Pages/T002568_Forcing_Input_to_Upperca

se.html

Making Revisions
Revision
tracking in Excel is very handy for keeping track of the last changes made to
a workbook. This tip explains how the revisions are shown and how you
http://exceltips.vitalnews.com/Pages/T002250_Making_Revisions.html

Selective Summing
The
SUMIF function is used to return the sum of a set of values within a range of
cells, but only if the values meet whatever criteria that you specify.
http://exceltips.vitalnews.com/Pages/T002169_Selective_Summing.html
Counting within Criteria
Counting
cells in Excel is easy; counting cells that meet a particular criterion is a
bit more difficult. This tip explains how you can put together a
http://exceltips.vitalnews.com/Pages/T002166_Counting_within_Criteria

.html

Moving Macros from the Personal Workbook
Need to
move a macro out of your Personal.xls workbook and into a regular workbook?
You can do it using familiar editing techniques, as illustrated in this
http://exceltips.vitalnews.com/Pages/T002575_Moving_Macros_from_the_Personal

_Workbook.html

Moving Cells Using the Mouse
Moving
cells in an Excel worksheet without using cut and paste. Provided by
ExcelTips, a free weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002648_Moving_Cells_Using_the_M

ouse.html

Using Duplex Printing
Duplexing
is the printing of information on both sides of a sheet of paper. Many
printers will handle duplexing automatically, but you need to instruct
http://exceltips.vitalnews.com/Pages/T002894_Using_Duplex_Printing.ht

ml

Fixing the Decimal Point
If
you’ve got a lot of similar data to enter (such as dollars and cents), you
can be more productive if you tell Excel to assume a decimal point at a
http://exceltips.vitalnews.com/Pages/T002755_Fixing_the_Decimal_Point

.html

Combining Numbers and Text in a Cell
Most
people put either text or numbers in cells, but seldom both. You can easily
combine the two data types in a single cell, however.
http://exceltips.vitalnews.com/Pages/T002582_Combining_Numbers_and_Text_in_a

_Cell.html

Inserting Worksheet Values with a Macro
Placing
values into a cell with a macro is easy when you use the Value property. This
tip demonstrates how you can use the Value property in your own macros
http://exceltips.vitalnews.com/Pages/T002313_Inserting_Worksheet_Values_with

_a_Macro.html

Putting a Graphic in a Header or Footer
Beginning
with Excel 2002, Microsoft made it much easier to put a graphic in a
worksheet header or footer. Here’s how to do it.
http://exceltips.vitalnews.com/Pages/T002697_Putting_a_Graphic_in_a_Header_o

r_Footer.html

Creating a Splash Screen
For
those who are creating their own applications in Excel, user forms are a
common way of presenting information to users. You can even create a basic
http://exceltips.vitalnews.com/Pages/T003167_Creating_a_Splash_Screen

.html

Writing a Macro from Scratch
How to
write a macro for use in an Excel workbook. Provided by ExcelTips, a free
weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002712_Writing_a_Macro_from_Scr

atch.html

Exiting a For … Next Loop Early
One of
the most common programming structures used in macros is the For … Next loop.
This structure is used to repeat a series of programming statements any
http://exceltips.vitalnews.com/Pages/T002260_Exiting_a_For__Next_Loop

_Early.html

Inserting a Row or Column
Excel
makes it very easy to insert a column or row in a worksheet. Provided by
ExcelTips, a free weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T001926_Inserting_a_Row_or_Colum

n.html

Debugging a Macro
Develop
your own macros and, sooner or later, you will need to debug them. The VB
Editor provides a couple of fundamental debugging tools, introduced in
http://exceltips.vitalnews.com/Pages/T002310_Debugging_a_Macro.html
Ranges on Multiple Worksheets
Formulas
can refer to ranges of cells, but they can also refer to ranges of
worksheets. You may need to create such a range reference if you want to
operate
http://exceltips.vitalnews.com/Pages/T002104_Ranges_on_Multiple_Works

heets.html

Removing Page Break Preview Page Numbers
Page
Break Preview allows you to see exactly where Excel will slice and dice your
worksheet when it is sent to the printer. If you want to change the way
http://exceltips.vitalnews.com/Pages/T002088_Removing_Page_Break_Preview_Pag

e_Numbers.html

Easily Changing the Default Drive and Directory
Need to
change the drive and directory considered the “default” by the operating
system? This tip explains how easy it is to do this within a macro.
http://exceltips.vitalnews.com/Pages/T002547_Easily_Changing_the_Default_Dri

ve_and_Directory.html

Trouble Recording Paste Special—Formula
Sometimes,
when you upgrade to a new version of Excel, you could run into a problem
recording macros that you had no problem with in the earlier version.
http://exceltips.vitalnews.com/Pages/T003149_Trouble_Recording_Paste_Special

Formula.html

Disabling the Windows Start Menu Key
The
Start Menu key is the one on your keyboard that has the Windows logo on it.
You cannot disable the key from within a macro, but this tip provides a
http://exceltips.vitalnews.com/Pages/T003071_Disabling_the_Windows_Start_Men

u_Key.html

Fitting Your Printout on a Page
Want to
make sure that your entire printout fits on a single page or a specific
number of pages? This tip shows how easy this is.
http://exceltips.vitalnews.com/Pages/T003033_Fitting_Your_Printout_on

_a_Page.html

Unlocking Charts
Objects
within a workbook are often locked as a form of protection. Your macro,
however, may have a need to work with some of those locked objects.
http://exceltips.vitalnews.com/Pages/T002264_Unlocking_Charts.html

Extracting E-mail Addresses from Hyperlinks
If
you’ve got a load of e-mail addresses in a worksheet, chances are good that
Excel converted them to hyperlinks as they were entered.
http://exceltips.vitalnews.com/Pages/T002897_Extracting_Email_Addresses_from

_Hyperlinks.html

Hiding Excel in VBA
Want to
have you macro completely hide the Excel interface? You can do so by using
the Visible property for the Excel Application object.
http://exceltips.vitalnews.com/Pages/T002020_Hiding_Excel_in_VBA.html

Referencing Worksheet Tabs
Ever
want to use the name of a worksheet tab within a cell? Here’s how you can
access that information using the CELL function.
http://exceltips.vitalnews.com/Pages/T001951_Referencing_Worksheet_Ta

bs.html

Formatting Combo Box Text
If you
insert objects, such as a combo box, in your worksheet, you may need a way to
modify the font used in the object. The ability to make such changes
http://exceltips.vitalnews.com/Pages/T002387_Formatting_Combo_Box_Tex

t.html

Hiding Graphics
Graphics
are a great addition to a worksheet, but there may be times when you don’t
want them printed. The easy way to handle this is to just turn off the
http://exceltips.vitalnews.com/Pages/T002951_Hiding_Graphics.html
Using the Camera in VBA
The
camera tool allows you to capture dynamic “pictures” of portions of a
worksheet. If you want to use the camera tool from within a macro, you’ll
need to
http://exceltips.vitalnews.com/Pages/T002207_Using_the_Camera_in_VBA.

html

Changing Page Orientation
Follow
these steps to set the page orientation of the Excel worksheet you want to
print. Provided by ExcelTips, a free weekly productivity newsletter: www.
http://exceltips.vitalnews.com/Pages/T002701_Changing_Page_Orientatio

n.html

Worksheet Events
You can
program Excel so that when specific events happen, your macro is executed.
Your event handlers can react to a variety of events relative to a
http://exceltips.vitalnews.com/Pages/T002569_Worksheet_Events.html

Limits to Filtering
Excel
limits the number of items that can be shown in an AutoFilter drop-down list.
Granted, it is a high limit, but it is a limit nonetheless.
http://exceltips.vitalnews.com/Pages/T002914_Limits_to_Filtering.html

Inserting Different Dashes
When
entering text into a worksheet, you may want to use some of the different
dashes available in Windows. This tip explains the difference between those
http://exceltips.vitalnews.com/Pages/T002118_Inserting_Different_Dash

es.html

Using Old Lotus Macros
Using a
Lotus 1-2-3 macro in Excel. Provided by ExcelTips, a free weekly productivity
newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002294_Using_Old_Lotus_Macros.h

tml

Forcing Editing to Be Done in a Cell
Excel
can be configured to allow editing in the Formula bar or in the Formula bar
and directly in a cell. It cannot be configured to allow only editing in a
http://exceltips.vitalnews.com/Pages/T003229_Forcing_Editing_to_Be_Done_in_a

_Cell.html

Getting Rid of Extra Quote Marks in Exported Text
Files
In an
effort to be helpful, Excel may add extra quote marks around text that it
exports to a text file. If you don’t like the format in which text is
http://exceltips.vitalnews.com/Pages/T002237_Getting_Rid_of_Extra_Quote_Mark

s_in_Exported_Text_Files.html

Understanding the If … End If Structure
An
explanation of If … End If structure of a VBA macro in Excel. Provided by
ExcelTips, a free weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002023_Understanding_the_If__End_If_St

ructure.html

Protecting Print Settings
Need to
have your print settings always be a certain way? Tired of resetting the
settings after others use the workbook or your system?
http://exceltips.vitalnews.com/Pages/T002993_Protecting_Print_Setting

s.html

Graphically Charting Geographic Data
Excel
can do all sorts of charts, but its ability to create geographic charts
depends on the version of Excel you are using. This tip discusses the options
http://exceltips.vitalnews.com/Pages/T003134_Graphically_Charting_Geographic

_Data.html

Changing Fonts in Data Validation Drop-Down Lists
When you
use Excel’s data validation features to create a drop-down list, you might
not be pleased with Excel’s choice of fonts for the list.
http://exceltips.vitalnews.com/Pages/T003264_Changing_Fonts_in_Data_Validati

on_DropDown_Lists.html

Filtering for Comments
Excel
makes it easy to filter a data table based on various values in that table.
It isn’t so easy to filter according to whether a cell contains comments
http://exceltips.vitalnews.com/Pages/T003508_Filtering_for_Comments.h

tml

Single-Use Drop-Down List
Want to
create an easy drop-down list? You can do so by using the data validation
features of Excel. Provided by ExcelTips, a free weekly productivity
http://exceltips.vitalnews.com/Pages/T003182_SingleUse_DropDown_List.

html

Seeing Full File Names in the Files Menu
The MRU
(most recently used) file list shows the workbook files you’ve most recently
worked with. If the file name is too long, then an abbreviated version
http://exceltips.vitalnews.com/Pages/T002229_Seeing_Full_File_Names_in_the_F

iles_Menu.html

Dealing with Circular References
Circular
references are created when a formula includes a reference to the cell in
which the formula is located. This tip provides a helpful example of a
http://exceltips.vitalnews.com/Pages/T002163_Dealing_with_Circular_Re

ferences.html

Linking Comments to Multiple Cells

In
Excel, single comments are associated with single cells. If you want to have
a comment be linked to multiple cells, you’ll need to use the workaround
http://exceltips.vitalnews.com/Pages/T002393_Linking_Comments_to_Mult

iple_Cells.html

Displaying Zeros
How to
direct Excel to not show zero sums in a worksheet. Provided by ExcelTips, a
free weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002629_Displaying_Zeros.html

Checking All Cell Formatting in VBA
Excel
allows two different types of formatting in a cell: explicit formatting and
conditional formatting. When you check formatting in a macro,
http://exceltips.vitalnews.com/Pages/T002127_Checking_All_Cell_Formatting_in

_VBA.html

Creating an Organization Chart
Excel
allows for easy creation of organization charts from the insert menu.
Provided by ExcelTips, a free weekly productivity newsletter:
http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T003204_Creating_an_Organization

_Chart.html

Removing Cells from a Selected Range
Selecting
a large range of cells is easy; unselecting some of the cells in that range
is not so easy. In fact, there is no click or shortcut you can use to
http://exceltips.vitalnews.com/Pages/T003102_Removing_Cells_from_a_Selected_

Range.html

Printing a List of Named Ranges
If your
workbook has quite a few defined name ranges, then you may find it helpful to
print out a list of those ranges for reference.
http://exceltips.vitalnews.com/Pages/T002944_Printing_a_List_of_Named

_Ranges.html

Pulling Apart Characters in a Long String
Excel
includes some powerful ways that you can process text in a worksheet. This
tip covers two ways (both with formulas and macros) that you can pull a
http://exceltips.vitalnews.com/Pages/T002790_Pulling_Apart_Characters_in_a_L

ong_String.html

Setting Up Custom AutoFiltering
The
AutoFiltering capabilities of Excel provide quick and easy ways to view only
portions of your available data. Beside the standard AutoFilter methods,
http://exceltips.vitalnews.com/Pages/T002857_Setting_Up_Custom_AutoFi

ltering.html

Tab Key Jumps a Screen at a Time
When you
press the Tab key, Excel moves the cell cursor one cell to the right. If you
press the Tab key and, instead, Excel moves the cell cursor more than
http://exceltips.vitalnews.com/Pages/T002747_Tab_Key_Jumps_a_Screen_at_a_Tim

e.html

Splitting Your Spreadsheet Window Into Panes
Get a
custom view of a spreadsheet with Excel’s pane function. Provided by
ExcelTips, a free weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T001960_Splitting_Your_Spreadsheet_Wind

ow_Into_Panes.html

Deleting a File in a Macro
Need to
delete a file within your macro? If you know the path and name of the file,
it is easy to do with a simple command. Provided by ExcelTips, a free
http://exceltips.vitalnews.com/Pages/T002450_Deleting_a_File_in_a_Mac

ro.html

Defining a Name
How to
name a cell or range of cells. Provided by ExcelTips, a free weekly
productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002044_Defining_a_Name.html
Rounding to the Nearest Half Dollar
Do you
need to round your data to the nearest half dollar? This tip presents several
ways (three formulas, to be exact) that you can use to get the desired
http://exceltips.vitalnews.com/Pages/T002620_Rounding_to_the_Nearest_

Half_Dollar.html

Last Saved Date in a Footer
It is
sometimes helpful to have the page footer of your worksheet contain the date
that the workbook was last saved. Unfortunately, Excel doesn’t include a
http://exceltips.vitalnews.com/Pages/T002190_Last_Saved_Date_in_a_Foo

ter.html

Delays in Saving Workbooks
A few
reasons why it may take awhile to save an Excel workbook. Provided by
ExcelTips, a free weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002532_Delays_in_Saving_Workboo

ks.html

Converting Mainframe Date Formats
If you
import information from other computer systems into an Excel worksheet, the
formats used for that information may not be understood directly by Excel
http://exceltips.vitalnews.com/Pages/T002524_Converting_Mainframe_Dat

e_Formats.html

Fixing Macro Button Behavior in Protected

Worksheets

If
circumstances are just right, you can end up with an ineffective macro button
when you protect a worksheet. This tip explains the circumstances,
http://exceltips.vitalnews.com/Pages/T002830_Fixing_Macro_Button_Behavior_in

_Protected_Worksheets.html

Specifying Chart Sizes
When
Excel creates a chart in a worksheet, it sets the initial size of the cart
based on a variety of factors. If you add multiple charts to a worksheet,
http://exceltips.vitalnews.com/Pages/T002429_Specifying_Chart_Sizes.h

tml

Error Opening Second Workbook, Take Two
In
addition to a previous tip, this tip will bypass the error message you
receive when opening a second Excel workbook. Provided by ExcelTips, a free
weekly
http://exceltips.vitalnews.com/Pages/T003015_Error_Opening_Second_Workbook_T

ake_Two.html

Adding Data Labels to Your Chart
When
creating a chart, you can use data labels to indicate what different parts of
the chart represent. Excel allows you to selectively display or hide
http://exceltips.vitalnews.com/Pages/T002840_Adding_Data_Labels_to_Yo

ur_Chart.html

Activating the Formula Bar with the Keyboard
Hate to
take your hands off the keyboard while working on a worksheet? Here’s one way
to activate the Formula Bar without the need of using the mouse.
http://exceltips.vitalnews.com/Pages/T002651_Activating_the_Formula_Bar_with

_the_Keyboard.html

Saving in Two Locations
When you
save a workbook to disk, you may want to automatically save a duplicate
workbook in a separate location. This can be easily done using a macro.
http://exceltips.vitalnews.com/Pages/T003042_Saving_in_Two_Locations.

html

Excluding Zero Values from a PivotTable
If you
have a large set of data that includes some zero values, you may want to
exclude those zero values from a PivotTable based on that data.
http://exceltips.vitalnews.com/Pages/T003276_Excluding_Zero_Values_from_a_Pi

votTable.html

Opening Two Workbooks with the Same Name
It is
possible to have two workbooks that use the same file name, but on different
drives or in different folders. If you try to open them both at the same
http://exceltips.vitalnews.com/Pages/T002222_Opening_Two_Workbooks_with_the_

Same_Name.html

Shading Rows with Conditional Formatting
Conditional
formatting can be used to shade every other row in a data table. This is
handy for those times when it may not be that easy to follow the lines
http://exceltips.vitalnews.com/Pages/T002799_Shading_Rows_with_Conditional_F

ormatting.html

Viewing Two Worksheets At Once
If you
need to work on two worksheets in the same workbook at the same time, Excel
makes this rather easy to do. All you need to do is display two windows
http://exceltips.vitalnews.com/Pages/T002688_Viewing_Two_Worksheets_A

t_Once.html

Deleting a Chart
At some
point you might want to delete a chart you previously created. How you go
about the deletion depends on whether the chart was created as an embedded
http://exceltips.vitalnews.com/Pages/T002523_Deleting_a_Chart.html

Extracting Proper Words
If
you’ve got a list of potential words, and you want to know which of those
potential words are real, you’ll appreciate the techniques described in this
http://exceltips.vitalnews.com/Pages/T002834_Extracting_Proper_Words.

html

Sorting a Range of Cells
When you
sort data in a worksheet, you don’t need to sort everything at once. You can
sort just a portion of your data by using the techniques in this tip.
http://exceltips.vitalnews.com/Pages/T002708_Sorting_a_Range_of_Cells

.html

Conditionally Formatting an Entire Row
Conditional
formatting is a great tool, allowing you to adjust formatting dynamically
based on the contents of your worksheet. Sometimes it can be confusing
http://exceltips.vitalnews.com/Pages/T002798_Conditionally_Formatting_an_Ent

ire_Row.html

Using the EOMONTH Function
The
EOMONTH function returns the last day of a month you specify, either in the
future or in the past. The function is easy to use and can be very helpful
http://exceltips.vitalnews.com/Pages/T002810_Using_the_EOMONTH_Functi

on.html

Unwanted Hyperlinks
Enter a
URL or an e-mail address into a cell, and Excel helpfully converts it into a
hyperlink that you can click to activate.
http://exceltips.vitalnews.com/Pages/T002803_Unwanted_Hyperlinks.html

Inserting from a Camera or Scanner

One of
the ways you can insert graphics into a worksheet is by grabbing them from a
digital camera or a scanner. This tip discusses how you can add pictures
http://exceltips.vitalnews.com/Pages/T003001_Inserting_from_a_Camera_

or_Scanner.html

Opening a Workbook with Two Windows
If you
open a workbook and you notice that it shows two windows for the data, you
may wonder how to get rid of one of those windows.
http://exceltips.vitalnews.com/Pages/T002224_Opening_a_Workbook_with_Two_Win

dows.html

Turning Off Sharing
When you
no longer want to share a workbook with multiple users, you need to turn the
sharing feature off. This tip explains the quickest way to again make
http://exceltips.vitalnews.com/Pages/T002917_Turning_Off_Sharing.html

Additional Ways of Displaying a Hidden First

Column

A few
ways to show a hidden first column in a worksheet. Provided by ExcelTips, a
free weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T001963_Additional_Ways_of_Displaying_a

_Hidden_First_Column.html

Removing Add-ins
Add-ins
are used to extend the functionality of Excel by providing functions and
capabilities not available in the plain vanilla version of the program.
http://exceltips.vitalnews.com/Pages/T002733_Removing_Addins.html
Referencing the Last Cell in a Column
Do you
want to reference whatever is stored in the last-used cell in a column? How
you go about accessing that information in a formula depends on whether
http://exceltips.vitalnews.com/Pages/T002103_Referencing_the_Last_Cell_in_a_

Column.html

Problems with Custom Views
If you
get an error when you try to use one of your custom views, it could be due to
the protection you have applied to the worksheets.
http://exceltips.vitalnews.com/Pages/T002621_Problems_with_Custom_Vie

ws.html

Getting Rid of the "Enable Macros"

Notice

Do you
get tired of the dialog box that says “do you want to enable macros” that is
displayed when you open a workbook. You can get rid of it by getting rid
http://exceltips.vitalnews.com/Pages/T002587_Getting_Rid_of_the_Enable_Macro

s_Notice.html

Macros in Template Files
People
often place macros in template files to perform any number of tasks. This tip
describes a situation where the link between a toolbar button and a
http://exceltips.vitalnews.com/Pages/T002717_Macros_in_Template_Files

.html

Get Rid of Web Stuff
When you
copy information from the Web and paste it into a worksheet, you may get more
than you bargain for. You may end up with all sorts of other items,
http://exceltips.vitalnews.com/Pages/T002881_Get_Rid_of_Web_Stuff.htm

l

Viewing Workbook Statistics
Excel
tracks a large number of statistics about workbooks. The quick way to view
them is through the Properties dialog box, as discussed in this tip.
http://exceltips.vitalnews.com/Pages/T002739_Viewing_Workbook_Statist

ics.html

Backing Up Your Customized Toolbars
After
customizing your Excel toolbars, it is a good idea to make a backup of the
file that contains the information. Provided by ExcelTips, a free weekly
http://exceltips.vitalnews.com/Pages/T002557_Backing_Up_Your_Customized_Tool

bars.html

Using Named Formulas Across Workbooks
Using
Excel, you can create names that represent ranges of cells, constant values,
or even formulas. If you want to use a named formula in more than just
http://exceltips.vitalnews.com/Pages/T003130_Using_Named_Formulas_Across_Wor

kbooks.html

Using Go To to Jump to a Chart Sheet
Using Go
To with a cell reference on a different worksheet is a great way of jumping
somewhere. Using Go To with a chart sheet is impossible, however,
http://exceltips.vitalnews.com/Pages/T003124_Using_Go_To_to_Jump_to_a_Chart_

Sheet.html

Returning Blanks with VLOOKUP
By
default, VLOOKUP returns a 0 if the source cell contains either 0 or is
empty. In some situations you may want to differentiate between actual zero
http://exceltips.vitalnews.com/Pages/T003075_Returning_Blanks_with_VL

OOKUP.html

Using Revision Tracking
Excel
allows you to track the revisions you make to a workbook. This tip explains
what revision tracking is and how you enable it for a particular workbook.
http://exceltips.vitalnews.com/Pages/T002868_Using_Revision_Tracking.

html

Creating a Watermark
Adding a
picture to the background of an Excel worksheet. Provided by ExcelTips, a
free weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002698_Creating_a_Watermark.htm

l

Copying Formats to a New Worksheet

Do you
want to copy formats from one worksheet to another? You can do so easily by
using the Format Painter. It even works on column widths and row heights.
http://exceltips.vitalnews.com/Pages/T002567_Copying_Formats_to_a_New_Worksh

eet.html

Getting Rid of the Startup Spreadsheet
Tired of
closing a new spreadsheet every time Excel is opened? Provided by ExcelTips,
a free weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T001991_Getting_Rid_of_the_Startup_Spre

adsheet.html

Using InputBox to Get Data
Macros
often need to prompt users for information. One of the best ways to do this
is with the InputBox function. This tip explains how the function works
http://exceltips.vitalnews.com/Pages/T002290_Using_InputBox_to_Get_Da

ta.html

No New Fonts Error
Do you
see a message saying something about “no new fonts” when you try to print a
worksheet? You may have fallen victim to an obscure error in Excel,
http://exceltips.vitalnews.com/Pages/T002129_No_New_Fonts_Error.html
Printing a Worksheet List
Want a
quick way to create a list of worksheets in a workbook? Here’sa super-fast
method using a simple little macro. Provided by ExcelTips, a free weekly
http://exceltips.vitalnews.com/Pages/T002216_Printing_a_Worksheet_Lis

t.html

Don’t Update Links to Other Programs
Excel
allows you to link to information stored in or created by other programs.
Normally the links are updated automatically. This tip explains how to turn
http://exceltips.vitalnews.com/Pages/T003039_Dont_Update_Links_to_Other_Prog

rams.html

Increasing Font Size In Worksheet Tabs
While
Excel does not have an intrinsic way to change the font in on a worksheet
tab, Windows does. Provided by ExcelTips, a free weekly productivity
http://exceltips.vitalnews.com/Pages/T002690_Increasing_Font_Size_In_Workshe

et_Tabs.html

Editing Reports
The
Report Manager allows you to create specialized reports that can be easily
printed from your worksheet data. This tip explains how to edit reports you
http://exceltips.vitalnews.com/Pages/T002249_Editing_Reports.html
Disabling Printing
Don’t
want your worksheets to be printed out? You can make it a bit harder to get a
printout by applying the techniques in this tip.
http://exceltips.vitalnews.com/Pages/T002556_Disabling_Printing.html
Progression Indicator in a Macro
If you
use macros to process data in a workbook, it can often appear like the macro
is doing nothing, even though it is working hard behind the scenes.
http://exceltips.vitalnews.com/Pages/T003223_Progression_Indicator_in

_a_Macro.html

Setting Print Quality
Excel
can take advantage of different print quality settings that may be available
in your printer driver. (This comes in handy when you want to print text
http://exceltips.vitalnews.com/Pages/T002937_Setting_Print_Quality.ht

ml

Waiting for Update Completion
If you
use Excel to grab data from an external source, you may notice that the macro
doing the data grabbing doesn’t always wait for the data to be ready.
http://exceltips.vitalnews.com/Pages/T002612_Waiting_for_Update_Compl

etion.html

Opening an HTML Page in Excel
Excel is
a Web-aware application that allows you to access Internet information from
within a worksheet. If you want to display a Web page from within a
http://exceltips.vitalnews.com/Pages/T002003_Opening_an_HTML_Page_in_

Excel.html

Forcing Manual Calculation For a Workbook
If you
have a large, complex workbook, you may want to make sure that it is always
calculated manually instead of automatically.
http://exceltips.vitalnews.com/Pages/T001988_Forcing_Manual_Calculation_For_

a_Workbook.html

Using a Progress Indicator in Macros
A few
tips on adding a progress indicator that runs during long macro calculations.
Provided by ExcelTips, a free weekly productivity newsletter: www.
http://exceltips.vitalnews.com/Pages/T002496_Using_a_Progress_Indicator_in_M

acros.html

Setting Header/Footer Margins
The
default margins for your headers and footers is .75 inches, left and right.
Unfortunately there is no way to change this margin; it is hard-coded into
http://exceltips.vitalnews.com/Pages/T002191_Setting_HeaderFooter_Mar

gins.html

Deriving High and Low Non-Zero Values
Excel
provides handy functions that allow you to determine the high and low values
in a range. You may want to limit what is returned to non-zero values,
http://exceltips.vitalnews.com/Pages/T002332_Deriving_High_and_Low_NonZero_V

alues.html

Summing Digits in a Value
Do you
need to calculate a total for all the digits in a number? (Are you dying to
know that for the value 123, 1+2+3 adds up to 6?
http://exceltips.vitalnews.com/Pages/T002424_Summing_Digits_in_a_Valu

e.html

Suppressing the Reviewing Toolbar on E-mailed
Workbooks
If you
get tired of the Reviewing toolbar popping up whenever you open a workbook
you received via e-mail. You’ll love the information in this tip.
http://exceltips.vitalnews.com/Pages/T002309_Suppressing_the_Reviewing_Toolb

ar_on_Emailed_Workbooks.html

Scaling Your Printing
Excel
includes the ability to reduce a printout so that it fits on a desired number
of pages. This tip explains the scaling feature, and provides a way you
http://exceltips.vitalnews.com/Pages/T002841_Scaling_Your_Printing.ht

ml

Setting Row Height
You can
adjust the height of each row in an Excel worksheet. There are two ways you
can easily adjust the row height, as described in this tip.
http://exceltips.vitalnews.com/Pages/T002756_Setting_Row_Height.html
Multiple Data Points in a Chart Column
Excel
provides lots of ways you can create charts. This tip provides some pointers
on how you can combine stacked column charts and clustered column charts
http://exceltips.vitalnews.com/Pages/T002431_Multiple_Data_Points_in_a_Chart

_Column.html

Selecting a Specific Cell in a Macro
How you
select a specific cell in a worksheet depends on many factors. This tip
examines how you can select a specific cell in a specific worksheet of a
http://exceltips.vitalnews.com/Pages/T002791_Selecting_a_Specific_Cell_in_a_

Macro.html

I Hate the Excel 2007 User Interface
If the
new interface used in Excel 2007 drives you nuts, there are a few things you
can try to make the program more like the “old Excel.
http://exceltips.vitalnews.com/Pages/T003299_I_Hate_the_Excel_2007_User_Inte

rface.html

One Shortcut for Two Macros
Macros
and the shortcuts used to invoke those macros are stored on a
workbook-by-workbook basis. If you load two workbooks, each of which uses the
same
http://exceltips.vitalnews.com/Pages/T002872_One_Shortcut_for_Two_Mac

ros.html

Page Numbers in VBA
When you
print a larger worksheet, Excel breaks the printout across several pages. You
may want to know, before you print, which page a particular cell will
http://exceltips.vitalnews.com/Pages/T003135_Page_Numbers_in_VBA.html

Entering Dates in Excel
If the
information you enter in a cell can possibly be understood as a date, Excel
will try to fit it into a date format. This tip discusses differing ways
http://exceltips.vitalnews.com/Pages/T002177_Entering_Dates_in_Excel.

html

Too Many Formats when Sorting
Sorting
is one of the basic operations done in a worksheet. If your sorting won’t
work and you instead get an error message, it could be because of the
http://exceltips.vitalnews.com/Pages/T002554_Too_Many_Formats_when_So

rting.html

Unhiding Multiple Worksheets
Excel
allows you to only unhide a single worksheet at a time. The way around this
is to use a macro that allows you to unhide lots of worksheets at once.
http://exceltips.vitalnews.com/Pages/T002603_Unhiding_Multiple_Worksh

eets.html

Removing All Macros
Macros
are stored as part of a workbook so that they are always available when you
have the workbook open. If you want to get rid of the macros in a
http://exceltips.vitalnews.com/Pages/T002713_Removing_All_Macros.html

Multiple Line Headers and Footers
Using a
macro code to set up a multi line header or footer. Provided by ExcelTips, a
free weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T001992_Multiple_Line_Headers_an

d_Footers.html

Opening Personal.xlsb in Excel 2007

If you
start up Excel 2007 and all you see is your Personal.xlsb file, then you may
wonder what you are doing wrong. The answer is nothing—all you need to
http://exceltips.vitalnews.com/Pages/T003290_Opening_Personalxlsb_in_

Excel_2007.html

Making Squares
Use the
following macro to make your Excel cells a set square shape. Provided by
ExcelTips, a free weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T001943_Making_Squares.html
Creating a Sort Order
If you
have special sorting needs, Excel can help you out. It will even allow you to
create custom non-standard sorting orders. This tip explains how you
http://exceltips.vitalnews.com/Pages/T002920_Creating_a_Sort_Order.ht

ml

Using Dynamic Chart Titles
Want the
title used in your chart to reflect information that is actually stored in
your worksheet? You can do it by using a simple formula, as described
http://exceltips.vitalnews.com/Pages/T002606_Using_Dynamic_Chart_Titl

es.html

Embedding Fonts in Excel
While
Excel doesn’t have the capability to embed fonts, this Excel tip includes a
workaround for the situation. Provided by ExcelTips, a free weekly
http://exceltips.vitalnews.com/Pages/T001969_Embedding_Fonts_in_Excel

.html

Changing the Axis Scale
When you
create a chart that uses an axis, Excel makes best-guess estimation of what
the scale should be that is applied to the axis.
http://exceltips.vitalnews.com/Pages/T003031_Changing_the_Axis_Scale.

html

Checking for an Entry in a Cell
Does a
particular cell contain something, or is it completely empty? Such questions
are the minutia of which worksheets are made, and you are left with
http://exceltips.vitalnews.com/Pages/T002112_Checking_for_an_Entry_in

_a_Cell.html

Changing the Color of Worksheet Gridlines
Want the
gridlines in your worksheet to be a different color? You aren’t limited to
stodgy black; Excel lets you make them just about any color you want.
http://exceltips.vitalnews.com/Pages/T002374_Changing_the_Color_of_Worksheet

_Gridlines.html

Identifying Scatter Plot Points
Do you
want to add data labels to the data points in an xy graph? Excel doesn’t
provide a way to get the desired labels, but you can get them with the
http://exceltips.vitalnews.com/Pages/T002615_Identifying_Scatter_Plot

_Points.html

Using AutoFiltering
When
working with large collections of data, it is often necessary to choose
subsets of that data. One easy way to limit the data visible is to apply
http://exceltips.vitalnews.com/Pages/T002246_Using_AutoFiltering.html

Resetting Excel Menus
Getting
rid of custom menus by resetting them through Excel’s Tool menu. Provided by
ExcelTips, a free weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002645_Resetting_Excel_Menus.ht

ml

Creating Add-Ins
Add-ins
are special Excel programs that enable features or capabilities not in the
original program. This tip explains how you can create your own custom
http://exceltips.vitalnews.com/Pages/T002927_Creating_AddIns.html
Understanding Manual Calculation
By
default, Excel automatically recalculates your workbook after an edit or when
you save your file. You can modify when Excel recalculates by applying the
http://exceltips.vitalnews.com/Pages/T002970_Understanding_Manual_Cal

culation.html

Using Color in Headers and Footers

Applying
color to the text in your headers and footers is a bit of a dream in some
versions of Excel. Here’s an overview of where color can be used and how
http://exceltips.vitalnews.com/Pages/T003251_Using_Color_in_Headers_a

nd_Footers.html

Hiding Outline Symbols
Outlining
a worksheet can be helpful as you try to manage the data that the worksheet
contains. However, the outline symbols may detract from the appearance
http://exceltips.vitalnews.com/Pages/T003020_Hiding_Outline_Symbols.h

tml

Displaying a Set Column Range
You may
want to display a specific range of columns in Excel’s program window. You
can do this with a simple macro that makes use of the Zoom feature built
http://exceltips.vitalnews.com/Pages/T003117_Displaying_a_Set_Column_

Range.html

Combining Multiple Rows in a Column
When
importing data from other programs, you often end up with information split
across separate rows of a worksheet. This tip explains a couple of ways you
http://exceltips.vitalnews.com/Pages/T002417_Combining_Multiple_Rows_in_a_Co

lumn.html

Finding the Last-Used Cell in a Macro
In a
worksheet, Ctrl+End jumps to the bottom-right cell in the data table. Doing
the same thing under the control of a macro, however, takes knowledge of
http://exceltips.vitalnews.com/Pages/T002271_Finding_the_LastUsed_Cell_in_a_

Macro.html

Updating Automatically When Opening Under Macro
Control
When you
open a workbook that contains links to another data source, Excel asks if you
want to update those links. This is great if you are opening the
http://exceltips.vitalnews.com/Pages/T002899_Updating_Automatically_When_Ope

ning_Under_Macro_Control.html

Setting a Default File Format
Excel
normally saves workbooks using a default file format that is peculiar to your
version of the program. You can configure Excel to save workbooks in an
http://exceltips.vitalnews.com/Pages/T002968_Setting_a_Default_File_F

ormat.html

Conditionally Deleting Rows
Want to
delete a bunch of rows in a worksheet based on the value in a certain cell of
each row? There are a couple of ways you can approach the problem,
http://exceltips.vitalnews.com/Pages/T002386_Conditionally_Deleting_R

ows.html

Ignoring Paragraph Marks when Pasting
When you
paste information from another application into Excel, the information is
“parsed” by Excel’s import filter, sometimes in ways you may not want.
http://exceltips.vitalnews.com/Pages/T003164_Ignoring_Paragraph_Marks_when_P

asting.html

Changing Page Number Format
Do you
need a way to print something besides just Arabic numerals for your page
numbers? Excel doesn’t provide a way to perform that task,
http://exceltips.vitalnews.com/Pages/T002126_Changing_Page_Number_For

mat.html

Using Data Validation
Data
validation is very helpful when creating worksheets into which others will
enter data. The feature allows you to specify criteria that user input must
http://exceltips.vitalnews.com/Pages/T002853_Using_Data_Validation.ht

ml

Jumping to a Specific Worksheet
When
you’ve got a workbook that has a huge number of worksheets in it, jumping to
a specific worksheet can be a challenge. Excel provides some tools to
http://exceltips.vitalnews.com/Pages/T003119_Jumping_to_a_Specific_Wo

rksheet.html

Summing Absolute Values
Excel
provides functions that allow you to calculate the sum of a range of values,
but what if you want to know the sum of the absolute values of the range?
http://exceltips.vitalnews.com/Pages/T002913_Summing_Absolute_Values.

html

External Data Validation
Data
validation allows you to control what information is entered into a
worksheet. Validating based on information internal to a workbook is easy;
http://exceltips.vitalnews.com/Pages/T002813_External_Data_Validation

.html

Sorting Worksheets
This VBA
macro will sort all of the worksheets in a workbook by worksheet name.
Provided by ExcelTips, a free weekly productivity newsletter: www.
http://exceltips.vitalnews.com/Pages/T001959_Sorting_Worksheets.html
Automatically Changing References to VBA

Libraries

VBA
libraries are used to provide some functions to your macros. These libraries
are often tied to the version of Excel you are using, and can thus make it
http://exceltips.vitalnews.com/Pages/T003340_Automatically_Changing_Referenc

es_to_VBA_Libraries.html

Dividing Values
When
working with large numbers, you may need a way to quickly divide a range of
those numbers by a specific value. Here’s the absolute fastest way to make
http://exceltips.vitalnews.com/Pages/T002658_Dividing_Values.html
Changing Axis Tick Marks
Create a
chart in Excel, and you may find that the tick marks shown on the axes in the
chart aren’t to your liking. It is easy to change the interval at
http://exceltips.vitalnews.com/Pages/T003200_Changing_Axis_Tick_Marks

.html

Embedding an Excel Chart in a Word Document
As
components of the Microsoft Office suite, one would expect Excel and Word to
work together. One of the most common tasks between the two is to embed a
http://exceltips.vitalnews.com/Pages/T002950_Embedding_an_Excel_Chart_in_a_W

ord_Document.html

Setting the Calculation Default
When you
open a workbook, you normally expect Excel to automatically calculate the
formulas it may contain. If you aren’t seeing automatic calculation by
http://exceltips.vitalnews.com/Pages/T003255_Setting_the_Calculation_

Default.html

Self-Aware Macros
Sometimes
it may be helpful for a macro to know exactly where it is being executed.
This tip provides a way that you can build such functionality into a
http://exceltips.vitalnews.com/Pages/T002607_SelfAware_Macros.html

Using the WEEKNUM Function
The
Analysis Toolpak includes the WEEKNUM function, which returns the week of the
year in which a particular date falls. Understanding how the function
http://exceltips.vitalnews.com/Pages/T002326_Using_the_WEEKNUM_Functi

on.html

Appearance of Excel on the Taskbar

Do you
want Excel to use a task button, on the Windows Task Bar, for each of your
open worksheets? Then just make this simple change to get the appearance
http://exceltips.vitalnews.com/Pages/T003028_Appearance_of_Excel_on_the_Task

bar.html

Adding Buttons to Your Worksheet
You can
easily add a button to your worksheet that will allow you to run various
macros. This tip shows how easy it is. Provided by ExcelTips, a free weekly
http://exceltips.vitalnews.com/Pages/T002479_Adding_Buttons_to_Your_W

orksheet.html

Y-Axis Chart Titles are Truncated
If your
chart titles are inexplicably truncated when you view or print the chart, it
can be frustrating. While this phenomenon happens periodically,
http://exceltips.vitalnews.com/Pages/T003126_YAxis_Chart_Titles_are_T

runcated.html

The EDATE Function
You can
use the EDATE function to determine a date a number of months in the past or
future. This function is part of the Analysis ToolPak.
http://exceltips.vitalnews.com/Pages/T003186_The_EDATE_Function.html
Increasing the AutoFilter Drop-Down Limit
When you
turn on AutoFiltering, Excel displays a drop-down list at the top of each
column in your data table. This list contains the unique values within
http://exceltips.vitalnews.com/Pages/T002403_Increasing_the_AutoFilter_DropD

own_Limit.html

Using Macros in Protected Workbooks
When you
protect or share worksheets and workbooks, it can affect how you use macros.
This tip explains the different ways in which your macros can be
http://exceltips.vitalnews.com/Pages/T002833_Using_Macros_in_Protected_Workb

ooks.html

Non-Printing Controls
Excel
allows you to create all sorts of form-based controls in a worksheet. The
problem is, the controls can mess up the appearance of your printout.
http://exceltips.vitalnews.com/Pages/T003081_NonPrinting_Controls.htm

l

Using Fractional Number Formats
One of
the formats you can use to display information is to show fractions, such as
12 1/4 (instead of 12.25). This tip explains how to implement this type
http://exceltips.vitalnews.com/Pages/T002759_Using_Fractional_Number_

Formats.html

Limiting Entries to Numeric Values

Excel
allows you to control what users can put into a cell. Using data validation,
you can specify exactly what people can enter, and then Excel enforces
http://exceltips.vitalnews.com/Pages/T002425_Limiting_Entries_to_Nume

ric_Values.html

Clearing the Print Area
If you
want to print only a given portion of a worksheet, Excel allows you to set up
a print area. This is a named range that is used by the printing
http://exceltips.vitalnews.com/Pages/T002963_Clearing_the_Print_Area.

html

Speeding Up Printing
Changing
the print setting can speed up the printing of your Excel worksheet. Provided
by ExcelTips, a free weekly productivity newsletter: www.
http://exceltips.vitalnews.com/Pages/T003238_Speeding_Up_Printing.htm

l

End-of-Month Calculations
Need to
put the last day of the month in a range of cells? This tip explains a few
different ways you can quickly add the desired information to your
http://exceltips.vitalnews.com/Pages/T002181_EndofMonth_Calculations.

html

Printing Comments
Excel
allows you to add comments to cells. At some point you may want to print out
those comments, along with your worksheet. Doing so is easy, provided you
http://exceltips.vitalnews.com/Pages/T002846_Printing_Comments.html
Specifying a Delimiter when Saving a CSV File in a
Macro
When you
create a CSV file from within Excel, you can do some things (like specify
delimiters) that you cannot do from within a macro.
http://exceltips.vitalnews.com/Pages/T003232_Specifying_a_Delimiter_when_Sav

ing_a_CSV_File_in_a_Macro.html

Fixed-Width Settings when Converting Text to

Columns

When you
use the Convert Text to Columns Wizard to parse fixed-width data into
individual columns, the guesses proffered by Excel on where to break up the
http://exceltips.vitalnews.com/Pages/T003253_FixedWidth_Settings_when_Conver

ting_Text_to_Columns.html

Moving Subtotals
When you
add subtotals to a worksheet, Excel normally places them in whatever column
seems best to it at the time. If you want to move these subtotals to a
http://exceltips.vitalnews.com/Pages/T002119_Moving_Subtotals.html

Finding Unused Names
After
months or years of naming things (such as cell ranges), you may find your
workbook cluttered with a bunch of names that don’t even reference anything.
http://exceltips.vitalnews.com/Pages/T003312_Finding_Unused_Names.htm

l

Working with Roman Numerals
Understanding
and using a function to replace an Arabic number with Roman numerals.
Provided by ExcelTips, a free weekly productivity newsletter: www.
http://exceltips.vitalnews.com/Pages/T001956_Working_with_Roman_Numer

als.html

Specifying a Data Validation Error Message
Data
validation is a great tool that helps you control exactly what can be entered
in a cell. Part of data validation allows you to specify an error message
http://exceltips.vitalnews.com/Pages/T002855_Specifying_a_Data_Validation_Er

ror_Message.html

Setting the Print Area
When you
print a worksheet, normally Excel prints everything on the worksheet. If you
want to limit what is actually printed, you can set a print area that
http://exceltips.vitalnews.com/Pages/T002844_Setting_the_Print_Area.h

tml

Understanding Cell Indenting
Enter
text information in to a cell, and Excel normally butts it up next to the
left edge of that cell. You can adjust the placement of the data relative to
http://exceltips.vitalnews.com/Pages/T003270_Understanding_Cell_Inden

ting.html

Filling References to Another Workbook
When you
create references to cells in other workbooks, Excel, by default, makes the
references absolute. This makes it difficult to automatically fill a
http://exceltips.vitalnews.com/Pages/T002437_Filling_References_to_Another_W

orkbook.html

Using the COLUMN Function
The
COLUMN worksheet function returns the column number of whatever column it
appears within. This can be handy for some formulas, as described in this
tip.
http://exceltips.vitalnews.com/Pages/T002305_Using_the_COLUMN_Functio

n.html

Rotating a Drawing Object
Add a
new shape or drawing object to your worksheet, and there it sits—ram-rod
straight and ready to enhance your presentation. You don’t have to settle for
http://exceltips.vitalnews.com/Pages/T002822_Rotating_a_Drawing_Objec

t.html

Printing Selected Worksheets
Need to
print just a few worksheets out of a group of workbooks? There’s no need to
manually load each workbook and print the sheets; instead you can use
http://exceltips.vitalnews.com/Pages/T002215_Printing_Selected_Worksh

eets.html

Setting the AutoRecover Directory
Excel,
by default, periodically writes information to AutoRecover files that can
help protect your data in case Excel is ended abnormally.
http://exceltips.vitalnews.com/Pages/T003342_Setting_the_AutoRecover_

Directory.html

Counting the Times a Worksheet is Used
Do you
need to know how many times a worksheet has been used? Excel doesn’t track
that information, but you can develop some ways to track it on your own.
http://exceltips.vitalnews.com/Pages/T002497_Counting_the_Times_a_Worksheet_

is_Used.html

Understanding the VLOOKUP Function

Need to
look up information in a data table based on what is in the first column of
that table? The worksheet function of choice in such situations is
http://exceltips.vitalnews.com/Pages/T002610_Understanding_the_VLOOKUP_Funct

ion.html

Placing Textbox Text Into a Worksheet
Want to
get rid of your text boxes and move their text into the worksheet? It’s going
to take a macro-based approach, discussed here.
http://exceltips.vitalnews.com/Pages/T002388_Placing_Textbox_Text_Into_a_Wor

ksheet.html

Using Overtype Mode
Using
overtype mode in Excel cells. Provided by ExcelTips, a free weekly
productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002028_Using_Overtype_Mode.html

Deriving the Worksheet Name
Using a
macro to reference the worksheet tab name in a worksheet cell in Excel.
Provided by ExcelTips, a free weekly productivity newsletter: www.
http://exceltips.vitalnews.com/Pages/T002019_Deriving_the_Worksheet_N

ame.html

Too Many Cell Formats
The
error message “too many cell formats” can be difficult to fix. This tip
describes ways you can attempt to get rid of the message.
http://exceltips.vitalnews.com/Pages/T001938_Too_Many_Cell_Formats.ht

ml

Extracting URLs from Hyperlinks
When you
add a hyperlink to and Excel worksheet, it consists of a minimum of two
parts: display text and URL. If you have a whole series of hyperlinks from
http://exceltips.vitalnews.com/Pages/T003281_Extracting_URLs_from_Hyp

erlinks.html

Creating Selections
Most
things you do in Excel—from formatting to copying and creating formulas to
deleting cells—requires that you make selections in a worksheet.
http://exceltips.vitalnews.com/Pages/T002099_Creating_Selections.html

Precision and Number Display
How
Excel handles precision and what it displays may be two different things.
Provided by ExcelTips, a free weekly productivity newsletter: www.
http://exceltips.vitalnews.com/Pages/T001982_Precision_and_Number_Dis

play.html

Formulas Don’t Calculate as Formulas
You are
typing along in your workbook, and you notice that a formula you enter is not
being processed right by Excel. What do you do?
http://exceltips.vitalnews.com/Pages/T003087_Formulas_Dont_Calculate_as_Form

ulas.html

Concatenating Ranges of Cells
The
ampersand (&) operator and the CONCATENATE function are great for
combining the contents of a limited number of cells. If you want to combine
the
http://exceltips.vitalnews.com/Pages/T003062_Concatenating_Ranges_of_

Cells.html

Sorting by Fill Color
Sorting
data in a worksheet is easy, unless you want to sort by the color used to
fill a range of cells. There are ways to do the sorting, however,
http://exceltips.vitalnews.com/Pages/T002009_Sorting_by_Fill_Color.ht

ml

History of Excel
If you
need to know the history of Excel, there are a number of different resources
available to you. This tip provides some guidance on places you can find
http://exceltips.vitalnews.com/Pages/T002546_History_of_Excel.html

Noting the Workbook Creation Date
You may
want to add, to your worksheet, the date on which a particular workbook was
created. Excel doesn’t provide a way to do this, but you can use one or
http://exceltips.vitalnews.com/Pages/T002367_Noting_the_Workbook_Crea

tion_Date.html

Simulating Alt+Enter in a Formula
When
entering data in a cell, Alt+Enter allows you to start a new line within the
current cell. In a formula you may want to create a new line,
http://exceltips.vitalnews.com/Pages/T002788_Simulating_AltEnter_in_a

_Formula.html

Closing a Read-Only Workbook
When you
create a workbook that is read-only, users can still make changes to the
workbook. When they exit, they are prompted to save their changes to
http://exceltips.vitalnews.com/Pages/T003153_Closing_a_ReadOnly_Workb

ook.html

Nesting IF Worksheet Functions
One
powerful worksheet function is the IF function, which allows your formulas to
make “decisions” based on whatever test you specify.
http://exceltips.vitalnews.com/Pages/T002154_Nesting_IF_Worksheet_Fun

ctions.html

Delimited Text-to-Columns in a Macro

Macros,
in processing data, may need to spread text out among different columns in a
worksheet. This tip explains how you can utilize the power in Excel’s
http://exceltips.vitalnews.com/Pages/T002929_Delimited_TexttoColumns_

in_a_Macro.html

Adjusting Row Height when Wrapping Text
If you
have some cells merged in a worksheet, and you wrap text within that merged
cell, Excel won’t automatically resize the height of the row as it would
http://exceltips.vitalnews.com/Pages/T002602_Adjusting_Row_Height_when_Wrapp

ing_Text.html

Counting Precedents and Dependents

Do you
need to know how many precedents or dependents there are on a worksheet? You
could count them manually, or you could let a short macro derive the
http://exceltips.vitalnews.com/Pages/T002015_Counting_Precedents_and_Depende

nts.html

Copying a Worksheet
Duplicating
an Excel worksheet is easy with the Edit menu. Provided by ExcelTips, a free
weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002684_Copying_a_Worksheet.html

Flashing Cells
Want to
make information in a cell blink on and off? There is no Excel formatting
feature that allows this, but you can create your own flashing by using
http://exceltips.vitalnews.com/Pages/T002134_Flashing_Cells.html
Rounding To the Nearest Even Integer
Do you
need your numbers to be rounded to an even integer value? How you accomplish
the task depends on the nature of the numbers you are starting with.
http://exceltips.vitalnews.com/Pages/T002828_Rounding_To_the_Nearest_Even_In

teger.html

Deleting a Hyperlink
Once a
hyperlink has been placed in a workbook, it can be perplexing to figure out
how to remove it. The process is easy, once you learn it,
http://exceltips.vitalnews.com/Pages/T002336_Deleting_a_Hyperlink.htm

l

Relative References when Recording Macros
When you
record macros, Excel normally assumes that any movements you make are to be
interpreted as absolute movements. That can cause a few problems when
http://exceltips.vitalnews.com/Pages/T003108_Relative_References_when_Record

ing_Macros.html

Deriving Antilogs
Antilogs
are not one of the trigonometric functions that are built into Excel. You can
easily calculate these values by creatively using some of the other
http://exceltips.vitalnews.com/Pages/T002171_Deriving_Antilogs.html
Understanding the Select Case Structure
The
Select Case programming structure allows you to create a framework of
conditional code that will be executed only under the conditions you specify.
http://exceltips.vitalnews.com/Pages/T002262_Understanding_the_Select_Case_S

tructure.html

Increasing the Number of MRU Files

MRU
(most recently used) files can be a great help when you work with a given set
of common workbooks. Excel versions up through Excel 2003 only allow you
http://exceltips.vitalnews.com/Pages/T003509_Increasing_the_Number_of_MRU_Fi

les.html

Getting a File Name
When
creating a macro, you can access the standard Open dialog box so that a user
can select a file to process. This tip explains how to do that and grab
http://exceltips.vitalnews.com/Pages/T002272_Getting_a_File_Name.html

Copying Subtotals
If you
have added subtotals to your worksheet data, you might want to copy those
subtotals somewhere else. This is easy to do using a special feature of the
http://exceltips.vitalnews.com/Pages/T002647_Copying_Subtotals.html
Removing a Macro from a Shortcut Key
When you
create a macro, you can add a shortcut key to the macro. Simply use the
shortcut, and the macro is executed. If you want to later disassociate the
http://exceltips.vitalnews.com/Pages/T003184_Removing_a_Macro_from_a_Shortcu

t_Key.html

Default Cell Movement when Deleting
When you
delete cells from a worksheet, you may want the surrounding cells to “fill
in” the deleted spot from a particular direction.
http://exceltips.vitalnews.com/Pages/T002097_Default_Cell_Movement_when_Dele

ting.html

Changing a Toolbar Button Image
Excel
allows you to modify virtually all aspects of its user interface. One of the
things you can change is the images used for almost all toolbar buttons.
http://exceltips.vitalnews.com/Pages/T002725_Changing_a_Toolbar_Butto

n_Image.html

Putting More than One Hyperlink in a Cell
Excel
allows you to put a single hyperlink in a cell. If you have a need to put
multiple hyperlinks in a cell, then you need to enlist the capabilities of
http://exceltips.vitalnews.com/Pages/T002805_Putting_More_than_One_Hyperlink

_in_a_Cell.html

Positioning a Column on the Screen

If you
have static columns and dynamic columns on the screen, you may want the
dynamic columns to always show a particular range. Getting just what you want
http://exceltips.vitalnews.com/Pages/T002464_Positioning_a_Column_on_the_Scr

een.html

Simultaneous Scrolling
If you
have two worksheets displayed at the same time, you might want those
worksheets to remain visually “in sync” with each other.
http://exceltips.vitalnews.com/Pages/T001979_Simultaneous_Scrolling.h

tml

Counting Colors from Conditional Formats
The
background of a cell can be colored based either on direct formatting or on
conditional formatting. Counting the number of cells that are directly
http://exceltips.vitalnews.com/Pages/T002873_Counting_Colors_from_Conditiona

l_Formats.html

Determining the Length of a String

In a
macro it is often helpful to know how long a particular text string is.
That’s where the Len() function proves invaluable, as described in this tip.
http://exceltips.vitalnews.com/Pages/T002299_Determining_the_Length_o

f_a_String.html

Using an Input Mask
Do you
need to enter times into a cell, and you want to do it without the necessity
of entering the colons? This tip explains how you can create an “input
http://exceltips.vitalnews.com/Pages/T002746_Using_an_Input_Mask.html

Hyperlinks in Shared Workbooks
Sharing
workbooks can be a great way for multiple people to safely share the same
data. Unfortunately, some Excel features (such as adding a hyperlink) are
http://exceltips.vitalnews.com/Pages/T002912_Hyperlinks_in_Shared_Wor

kbooks.html

Searching for Wildcards
When
using Find or Find and Replace, you can use the question mark and asterisk as
wildcards in your search. If you want to search for those actual
http://exceltips.vitalnews.com/Pages/T002333_Searching_for_Wildcards.

html

Can’t Delete Web Page Created by Excel
Excel
can be used to create Web pages, either from a portion of a worksheet or from
the entire thing. What happens, though, when you create a Web page from
http://exceltips.vitalnews.com/Pages/T003129_Cant_Delete_Web_Page_Created_by

_Excel.html

Mouse Click Event in VBA
Excel
does not have a dedicated “mouse click” event that you can trap and handle.
You can, however, use the SelectionChange event (as described in this tip)
http://exceltips.vitalnews.com/Pages/T003070_Mouse_Click_Event_in_VBA

.html

Using the FORECAST Function
The
FORECAST worksheet function can be used to do an extrapolation of future data
points based on a linear regression of existing data.
http://exceltips.vitalnews.com/Pages/T002573_Using_the_FORECAST_Funct

ion.html

Personal.xls File Not Opening
The
Personal.xls workbook is used primarily to store macros that you want
available through all of your workbooks. Personal.xls is normally opened
http://exceltips.vitalnews.com/Pages/T002888_Personalxls_File_Not_Ope

ning.html

Inserting a Voice Annotation in Your Worksheet
Adding
audio notes to a workbook is easy to do in Windows. This tip explains how you
can record an audio file for saving with a worksheet, and then play the
http://exceltips.vitalnews.com/Pages/T002870_Inserting_a_Voice_Annotation_in

_Your_Worksheet.html

Preparing Data for Import into Access
When
importing Excel information into Access, you need to be concerned with the
condition of the data. Here’s how to make sure that two common
http://exceltips.vitalnews.com/Pages/T002400_Preparing_Data_for_Import_into_

Access.html

Copying a Cell without Formatting
Using
the Paste Special function of Excel to copy cell contents without formatting.
Provided by ExcelTips, a free weekly productivity newsletter: www.
http://exceltips.vitalnews.com/Pages/T003012_Copying_a_Cell_without_F

ormatting.html

Excel Won’t Display Different Windows in the

Taskbar

If you
want individual Taskbar buttons for each open Excel workbook but cannot seem
to get those buttons, the culprit could be two individual settings.
http://exceltips.vitalnews.com/Pages/T002905_Excel_Wont_Display_Different_Wi

ndows_in_the_Taskbar.html

Changing Links
If
you’ve got information linked into a workbook, you need to know how to change
those links. Why? Because the source of that linked information may have
http://exceltips.vitalnews.com/Pages/T003084_Changing_Links.html
Cleaning Up Lists
When you
have huge amounts of data you need to check for matches, Excel may not be the
best tool to use. If you can fit the data within a worksheet,
http://exceltips.vitalnews.com/Pages/T002319_Cleaning_Up_Lists.html

Removing Items from a Context Menu

Context
menus appear when you right-click an item in Excel. If you want to modify the
menu that appears, the way to do so is not immediately evident.
http://exceltips.vitalnews.com/Pages/T003089_Removing_Items_from_a_Context_M

enu.html

Hiding Entries in an InputBox
When
entering a password into an InputBox, you may want to “mask” what is entered.
The InputBox function doesn’t provide this capability, but there are ways
http://exceltips.vitalnews.com/Pages/T003120_Hiding_Entries_in_an_Inp

utBox.html

Duplicate Workbooks Opening
If you
ever open a workbook and always see two workbooks instead of one, chances are
good the reason is because of the number of windows you’ve saved in the
http://exceltips.vitalnews.com/Pages/T002994_Duplicate_Workbooks_Open

ing.html

Determining if Calculation is Necessary
When
writing macros, you can turn off the automatic recalculation feature of Excel
so that your macro isn’t slowed down. When the macro is done,
http://exceltips.vitalnews.com/Pages/T002323_Determining_if_Calculation_is_N

ecessary.html

Displaying the Selected Cell’s Address
Do you
want a cell to contain the address of whatever cell is currently selected in
the workbook? There are a couple of ways you can achieve this goal,
http://exceltips.vitalnews.com/Pages/T002302_Displaying_the_Selected_Cells_A

ddress.html

Adding Ordinal Notation to Dates
If you
use dates in your worksheet, you may want to add an ordinal indicator to the
day of the month. This tip shows the best ways to achieve this result.
http://exceltips.vitalnews.com/Pages/T002510_Adding_Ordinal_Notation_

to_Dates.html

Specifying Date Formats in Headers

Dates in
headers and footers are formatted using the same pattern used in Windows for
the system date. If you need to use a different format,
http://exceltips.vitalnews.com/Pages/T002188_Specifying_Date_Formats_

in_Headers.html

Determining Your Version of Excel
If you
inherit a computer system from someone else or if you are just starting out
with Excel, it may not be entirely clear to you which version of the
http://exceltips.vitalnews.com/Pages/T002959_Determining_Your_Version

_of_Excel.html

Finding and Deleting Links
A VBA
macro to find and delete external links. Provided by ExcelTips, a free weekly
productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T001958_Finding_and_Deleting_Lin

ks.html

Defining and Using Custom Colors
Normally
Excel allows you to work with a well-defined, but limited, palette of colors.
If you want to change out some of the default colors for ones more to
http://exceltips.vitalnews.com/Pages/T002734_Defining_and_Using_Custo

m_Colors.html

Speeding Up Opening and Saving a Workbook
When
workbooks get very large, opening and saving them can seem to take forever.
Here are a number of ideas on how you can make your large workbooks open
http://exceltips.vitalnews.com/Pages/T002402_Speeding_Up_Opening_and_Saving_

a_Workbook.html

Entering or Importing Times without Colons
You can
speed up the entry of times, provided you can figure a way to not need to
enter the colon between hours and minutes. (It is much faster to enter
http://exceltips.vitalnews.com/Pages/T002412_Entering_or_Importing_Times_wit

hout_Colons.html

Concatenating Names with Delimiters
Combining
information from various cells is an easy task in Excel. If you want to
conditionally include information based on what is in the various cells,
http://exceltips.vitalnews.com/Pages/T002173_Concatenating_Names_with_Delimi

ters.html

Colors and Fonts for Worksheet Tabs
If you
want to change the color used for a worksheet tab, the change is only a click
away. This tip examines how easy it is to make the changes you wan to
http://exceltips.vitalnews.com/Pages/T002625_Colors_and_Fonts_for_Worksheet_

Tabs.html

Understanding Fill Effects
Adding
drawing objects to a worksheet is a common task in Excel. If you apply the
techniques in this tip, you can fill those drawing objects in all sorts of
http://exceltips.vitalnews.com/Pages/T002824_Understanding_Fill_Effec

ts.html

Turning Off Hyperlink Activation
Enter an
e-mail address or URL into a cell, and Excel helpfully converts it to a
clickable hyperlink. How you control this feature, first added in Excel
http://exceltips.vitalnews.com/Pages/T002707_Turning_Off_Hyperlink_Ac

tivation.html

Filling a Drawing Object
Excel
allows you to add drawing objects to your worksheets. These objects can be
filled with a variety of colors or effects, as described in this tip.
http://exceltips.vitalnews.com/Pages/T002823_Filling_a_Drawing_Object

.html

Jumping to the Start of the Next Data Entry Row
Want a
quick way to jump to the end of your data entry area in a worksheet? The
macro in this tip makes quick work of the task.
http://exceltips.vitalnews.com/Pages/T002407_Jumping_to_the_Start_of_the_Nex

t_Data_Entry_Row.html

Understanding R1C1 References
Excel
supports two types of cell referencing. This tip examines both types and
explains how you can switch between them. Provided by ExcelTips, a free
http://exceltips.vitalnews.com/Pages/T003010_Understanding_R1C1_Refer

ences.html

Drop-Down List Font Sizes
Apply an
AutoFilter, and drop-down lists appear next to the top of each filtered
column. Choose a drop-down list, and you see different filters you can
http://exceltips.vitalnews.com/Pages/T002086_DropDown_List_Font_Sizes

.html

Triggering a Macro for Drop-Down List Changes
Excel 97
has a bug that causes the Worksheet_Calculate event not to trigger when
someone picks a value from a data validation drop-down list.
http://exceltips.vitalnews.com/Pages/T002398_Triggering_a_Macro_for_DropDown

_List_Changes.html

Using the Status Bar
Your
macros can use the status bar area, at the bottom of the Excel window, to
display messages. This is normally done to post status messages that indicate
http://exceltips.vitalnews.com/Pages/T002296_Using_the_Status_Bar.htm

l

Sorting Dates by Month
Do you
need to sort information in a worksheet by month name when all you have
available in a column is a group of dates? You can do so by applying any of
http://exceltips.vitalnews.com/Pages/T003183_Sorting_Dates_by_Month.h

tml

Alerts About Approaching Due Dates

If you
keep a series of dates in a worksheet, you may need some way to highlight if
those dates are within a certain range. This can be done in a couple of
http://exceltips.vitalnews.com/Pages/T003179_Alerts_About_Approaching_Due_Da

tes.html

Adding and Using a Combo Box
Combo
boxes can be added to a worksheet to help users enter data just the way you
want. This tip explains how to both add and configure a combo box for
http://exceltips.vitalnews.com/Pages/T002711_Adding_and_Using_a_Combo

_Box.html

Spell-Checking in a Protected Worksheet
When you
protect a worksheet, you can’t use some tools, including the spell-checker.
If you want to use it, you must unprotect the worksheet, run the check,
http://exceltips.vitalnews.com/Pages/T003144_SpellChecking_in_a_Protected_Wo

rksheet.html

Maintaining the Active Cell
Move
from one worksheet to another, and Excel selects whatever cell was last used
in the worksheet you are selecting. If you don’t want this behavior (you
http://exceltips.vitalnews.com/Pages/T003205_Maintaining_the_Active_C

ell.html

Counting Non-Blank Cells
Need to
count the number of cells in a range that are not blank? You can use the
COUNTA function of a more complex formula to get the result.
http://exceltips.vitalnews.com/Pages/T002996_Counting_NonBlank_Cells.

html

Conditionally Formatting for Multiple Date
Comparisons
The
Conditional Formatting capabilities of Excel are very powerful. Getting them
to behave exactly as you expect can be a bit tricky, however.
http://exceltips.vitalnews.com/Pages/T002780_Conditionally_Formatting_for_Mu

ltiple_Date_Comparisons.html

Contingent Validation Lists
Data
validation can be used to create lists of choices for entry into a particular
cell. Using the techniques in this tip you can create multiple lists in
http://exceltips.vitalnews.com/Pages/T003195_Contingent_Validation_Li

sts.html

Saving a Workbook Using Passwords
One of
the security features built-in to Excel is the ability to save a workbook
using a password that limits access to the information in the workbook.
http://exceltips.vitalnews.com/Pages/T002938_Saving_a_Workbook_Using_Passwor

ds.html

Goal Seeking
Using
Excel to return values for future goals. Provided by ExcelTips, a free weekly
productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002008_Goal_Seeking.html
Full Path Names in Excel
If you
want to see the full path name for a particular workbook, you’ll like the
macro in this tip. It allows you to display the path on Excel’s status bar.
http://exceltips.vitalnews.com/Pages/T002238_Full_Path_Names_in_Excel

.html

Controlling the Printer in a Macro

Want to
access the advanced features of your printer from within a macro? You may be
out of luck, as those features are often not made accessible to VBA
http://exceltips.vitalnews.com/Pages/T002530_Controlling_the_Printer_

in_a_Macro.html

Converting UNIX Date/Time Stamps
If you
import information generated on a UNIX system, you may need to figure out how
to change the date/time stamps to something that Excel can recognize
http://exceltips.vitalnews.com/Pages/T002051_Converting_UNIX_DateTime

_Stamps.html

More than Three Conditional Formats
Conditional
formatting is a powerful feature of Excel, but it has traditionally been
limited to three explicit formats. This tip examines ways around this
http://exceltips.vitalnews.com/Pages/T002439_More_than_Three_Conditional_For

mats.html

Converting Phone Numbers
Sometimes
companies will use mnemonics to quote their phone number, such as 1-800-Weld
Inc. In order to know what number to call, you must substitute a
http://exceltips.vitalnews.com/Pages/T002269_Converting_Phone_Numbers

.html

Setting Vertical Alignment
One of
the ways that Excel allows you to format cells is by adjusting the vertical
alignment of the cell’s data. Depending on your version of Excel,
http://exceltips.vitalnews.com/Pages/T002124_Setting_Vertical_Alignme

nt.html

Selecting Cells of a Specific Color
Do you
need to find cells that are formatted with a particular color? How you
accomplish this task depends on your version of Excel.
http://exceltips.vitalnews.com/Pages/T002396_Selecting_Cells_of_a_Spe

cific_Color.html

Formatting Axis Patterns
Create a
chart in Excel can you can then modify it almost any way you desire. One
modification is to adjust the color or pattern applied to an axis.
http://exceltips.vitalnews.com/Pages/T003196_Formatting_Axis_Patterns

.html

Using Early Dates
Excel is
brilliant at handling dates—as long as they aren’t dates earlier than the
base date used by the program. If you need to use earlier dates,
http://exceltips.vitalnews.com/Pages/T002382_Using_Early_Dates.html
Using an Exact Number of Digits
Excel
allows you to format numeric data in all sorts of ways, but specifying a
number of digits independent of the decimal point is not one of the ways.
http://exceltips.vitalnews.com/Pages/T001933_Using_an_Exact_Number_of

_Digits.html

Printing Columns and Rows
When you
print something from Excel, you normally print an entire worksheet, a page,
or a selected range of cells. If you want to print a row and a column
http://exceltips.vitalnews.com/Pages/T003029_Printing_Columns_and_Row

s.html

Checking for Time Input
Do you
want to know whether the information entered in a cell is a time value?
Determining the status of such data is not as clear-cut as one might hope.
http://exceltips.vitalnews.com/Pages/T003292_Checking_for_Time_Input.

html

Locked File Puzzle
What
would you do if every time you opened a workbook Excel told you it was
locked? Here’s how you can try to recover from this type of error.
http://exceltips.vitalnews.com/Pages/T002705_Locked_File_Puzzle.html
Cannot Double-Click to Open a Workbook
When you
double-click on a workbook in Windows, the Excel program should be started
and the workbook loaded. When this doesn’t happen, it means something
http://exceltips.vitalnews.com/Pages/T002949_Cannot_DoubleClick_to_Open_a_Wo

rkbook.html

Unwanted Read-Only Workbook Status
If you
have a workbook on a floppy disk, you might run into a problem where the
workbook suddenly becomes “read-only.” This is typically caused by removing
http://exceltips.vitalnews.com/Pages/T002898_Unwanted_ReadOnly_Workbook_Stat

us.html

Working with Lotus 1-2-3 Spreadsheets
Lotus
1-2-3, a spreadsheet program popular before Excel came on the scene, treats
data in a worksheet differently than Excel does.
http://exceltips.vitalnews.com/Pages/T003057_Working_with_Lotus_123_Spreadsh

eets.html

Loading Unwanted Files at Startup
Imagine
how painful it would be if every time you started Excel it tried to load all
the files in your root directory? That is what was happening to Stephen
http://exceltips.vitalnews.com/Pages/T002706_Loading_Unwanted_Files_a

t_Startup.html

Generating Random Testing Data
Testing
is an intrinsic part of developing any software or any worksheet that will be
used by others. If you need to create data to use in your testing,
http://exceltips.vitalnews.com/Pages/T002077_Generating_Random_Testin

g_Data.html

Selecting All Visible Worksheets in a Macro
Selecting
all the visible worksheets in a workbook is easy using the mouse and the
sheet tabs. Doing the same thing in a macro is not as straightforward.
http://exceltips.vitalnews.com/Pages/T003058_Selecting_All_Visible_Worksheet

s_in_a_Macro.html

Totaling Across Worksheets
Putting
together formulas to sum a range of cells is fast and easy, as long as the
cells are all on the same worksheet. If you want to get a sum of cells on
http://exceltips.vitalnews.com/Pages/T002514_Totaling_Across_Workshee

ts.html

Deleting Every X Rows
Need a
quick way to delete every second or third row in a worksheet? Here’s one easy
technique that you’ll use again and again.
http://exceltips.vitalnews.com/Pages/T002292_Deleting_Every_X_Rows.ht

ml

Calculating Averages by Date
When you
have a huge amount of daily data to analyze, you may want to calculate an
average of values for any given date in the data. This may seem daunting,
http://exceltips.vitalnews.com/Pages/T002350_Calculating_Averages_by_

Date.html

Displaying Row and Column Labels
If you
add row and column labels to your worksheet data, you may want them to appear
on-screen at all times so that you can better work with the data.
http://exceltips.vitalnews.com/Pages/T002591_Displaying_Row_and_Colum

n_Labels.html

Understanding Conditional Formatting Conditions
An
explanation of conditional formatting and how to employ it in your Excel
worksheet. Provided by ExcelTips, a free weekly productivity newsletter: www.
http://exceltips.vitalnews.com/Pages/T002795_Understanding_Conditional_Forma

tting_Conditions.html

Selecting Noncontiguous Ranges with the Keyboard
It’s
easy to select non-contiguous ranges using the mouse, but may seem more
daunting if you are simply using the keyboard. Here’s how you can do it,
http://exceltips.vitalnews.com/Pages/T002401_Selecting_Noncontiguous_Ranges_

with_the_Keyboard.html

Working In Feet and Inches
Your
chosen occupation may require that you work with linear distances in feet and
inches. Excel can do this, to a degree, but you need to understand what
http://exceltips.vitalnews.com/Pages/T002036_Working_In_Feet_and_Inch

es.html

Using AutoFormat
The
AutoFormat tool can save a great deal of time when formatting tables of data.
This tip explains how to use the tool to get the best results for your
http://exceltips.vitalnews.com/Pages/T002801_Using_AutoFormat.html

Easily Changing Links
If you
have linked information in your worksheets, you may want a way you can easily
change the targets to which those links are pointing.
http://exceltips.vitalnews.com/Pages/T001968_Easily_Changing_Links.ht

ml

Inserting and Copying Rows
Want an
easy way to insert a new row in a worksheet and copy everything from the row
above? (You end up with two identical rows this way.
http://exceltips.vitalnews.com/Pages/T002042_Inserting_and_Copying_Ro

ws.html

Saving in Multiple Locations
Got a
workbook that you need to save copies of in several locations? Going through
the manual process of saving the same file in five locations can be
http://exceltips.vitalnews.com/Pages/T002774_Saving_in_Multiple_Locat

ions.html

Specifying the Size of Chart Objects
Create a
new chart object in an existing worksheet, and Excel automatically makes the
object 25% of whatever the screen size is.
http://exceltips.vitalnews.com/Pages/T002208_Specifying_the_Size_of_Chart_Ob

jects.html

Shading a Cell Until Something is Entered
Using
conditional formatting, you can shade a particular cell (or cells) until a
value is entered in the cell. This is a handy way for users to tell when
http://exceltips.vitalnews.com/Pages/T002800_Shading_a_Cell_Until_Something_

is_Entered.html

Unable to Format Cells
If you
ever get to a situation where you can no longer format cells in a workbook,
you’ll realize just how important the formatting capabilities of Excel
http://exceltips.vitalnews.com/Pages/T003344_Unable_to_Format_Cells.h

tml

Alphabetic Column Designation
Want to
find out the alphabetic designation of a particular column? There are several
ways you can go about the task, but the results will depend on your
http://exceltips.vitalnews.com/Pages/T003254_Alphabetic_Column_Design

ation.html

Increasing Undo Levels
Each
time you take some action in Excel, the action is saved in an “undo stack” so
that the action can be undone, if desired. The number of undo levels that
http://exceltips.vitalnews.com/Pages/T003145_Increasing_Undo_Levels.h

tml

Inserting Symbols
Using
the Character Map to insert symbols in Excel. Provided by ExcelTips, a free
weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002046_Inserting_Symbols.html
Determining Your Serial Number
The
serial number assigned to your copy of Excel is valuable. It allows you to
get support and is necessary for some upgrading needs.
http://exceltips.vitalnews.com/Pages/T002476_Determining_Your_Serial_

Number.html

Removing Spaces
Need to
get rid of spaces in a range of cells? There are two ways you can approach
the task, as described here. Provided by ExcelTips, a free weekly
http://exceltips.vitalnews.com/Pages/T003037_Removing_Spaces.html
Macro Runs Slowly, but Steps Quickly
When you
have a macro that processes a huge amount of data, it can seem like it takes
forever to finish up. These slowdowns can be aggravating,
http://exceltips.vitalnews.com/Pages/T002436_Macro_Runs_Slowly_but_Steps_Qui

ckly.html

Ensuring Unique Values in a Column

If you
want to make sure that only unique values are entered in a particular column,
you can use the data validation capabilities of Excel to your advantage
http://exceltips.vitalnews.com/Pages/T002650_Ensuring_Unique_Values_in_a_Col

umn.html

Removing Duplicate Rows
If you
have a large table of data, you might want to remove all the records (rows)
in which there is some duplicate value in the first column.
http://exceltips.vitalnews.com/Pages/T002108_Removing_Duplicate_Rows.

html

Editing a Hyperlink
Got a
hyperlink in a worksheet you need to change? It’s easier than you think, if
you utilize the Edit Hyperlink dialog box. This tip explains the different
http://exceltips.vitalnews.com/Pages/T002139_Editing_a_Hyperlink.html

Sorting Letters and Numbers
If you
have data that includes a mixture of letters and numbers in the same cells,
you may wonder why Excel sorts that data a particular way.
http://exceltips.vitalnews.com/Pages/T002609_Sorting_Letters_and_Numb

ers.html

Automatically Printing a Range
If you
want to automatically print a particular area of your worksheet at a specific
time of day, you’ll love this tip. With the use of two small macros,
http://exceltips.vitalnews.com/Pages/T001977_Automatically_Printing_a

_Range.html

No More Custom Formats Can Be Added
If you
make too many formatting changes to your workbook, you could end up with a
situation where you cannot make any such additional changes.
http://exceltips.vitalnews.com/Pages/T002133_No_More_Custom_Formats_Can_Be_A

dded.html

Converting Between Buddhist and Gregorian Calendar
Systems
Using
the DATE function to convert a calendar system. Provided by ExcelTips, a free
weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002049_Converting_Between_Buddhist_and

_Gregorian_Calendar_Systems.html

Out of Memory Errors when Accessing the VBA

Editor

It can
be frustrating when you get error messages doing something that you
previously did with no errors. If you get an out of memory error when pulling
up
http://exceltips.vitalnews.com/Pages/T003325_Out_of_Memory_Errors_when_Acces

sing_the_VBA_Editor.html

Controlling How Excel Interprets Percentages
Need to
instruct Excel in how it should interpret the percentage values you enter
into cells? You can do so by making a couple of quick configuration
http://exceltips.vitalnews.com/Pages/T003013_Controlling_How_Excel_Interpret

s_Percentages.html

Drop-Down List of Hyperlinks
Creating
a drop-down list with Excel’s data validation feature can be a nice touch for
a worksheet. What if you want the drop-down list to include active
http://exceltips.vitalnews.com/Pages/T003458_DropDown_List_of_Hyperli

nks.html

Changing Months in a Workbook
Excel
has a few methods to change the months in a workbook. Provided by ExcelTips,
a free weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002748_Changing_Months_in_a_Wor

kbook.html

Printing a Number of Different Pages
If you
don’t need to print an entire workbook, it can be confusing to figure out how
to print just certain pages. This tip explains your options and shows
http://exceltips.vitalnews.com/Pages/T002390_Printing_a_Number_of_Different_

Pages.html

Watermarks in Excel
Excel
allows you to add backgrounds to your worksheets, but this isn’t the same as
a watermark. There is no way to create a real watermark in Excel,
http://exceltips.vitalnews.com/Pages/T002202_Watermarks_in_Excel.html

Converting to Hexadecimal
One of
the numbering systems often used by programmers is hexadecimal. Excel
includes a function (DEC2HEX) that allows you to convert decimal values to
http://exceltips.vitalnews.com/Pages/T002308_Converting_to_Hexadecima

l.html

Returning Blanks or Asterisks from a Lookup
Want to
return more than a value when doing a lookup? Here’s one way to do it by
adding an IF clause to your formula. Provided by ExcelTips, a free weekly
http://exceltips.vitalnews.com/Pages/T003335_Returning_Blanks_or_Asterisks_f

rom_a_Lookup.html

Linking to a Specific Page in a PDF File
The
HYPERLINK function is great for linking to Web pages and some other
resources, but it won’t allow you to access a target page in a PDF file.
http://exceltips.vitalnews.com/Pages/T003350_Linking_to_a_Specific_Page_in_a

_PDF_File.html

Rounding to the Nearest $50
Excel
provides a worksheet function, MROUND, that allows you to round a value to
the nearest multiple of a given rounding value. This tip explains how you
http://exceltips.vitalnews.com/Pages/T002149_Rounding_to_the_Nearest_

50.html

Roman Numerals for Page Numbers
Add page
numbers to a worksheet, and they print out as Arabic numerals. If you want to
use Roman numerals instead, you’ll want to use the techniques
http://exceltips.vitalnews.com/Pages/T002518_Roman_Numerals_for_Page_

Numbers.html

Restoring the Analysis ToolPak
Add-ins
for Excel, such as the Analysis ToolPak, are stored in files on your hard
drive that can be deleted. If you delete the ToolPak files by accident,
http://exceltips.vitalnews.com/Pages/T003152_Restoring_the_Analysis_T

oolPak.html

Getting Rid of Alphabetic Characters
If you
have some data that has a combination of alphabetic and other characters, and
you want to get rid of only the alphabetic characters,
http://exceltips.vitalnews.com/Pages/T003219_Getting_Rid_of_Alphabetic_Chara

cters.html

Sizing Text Boxes and Cells the Same
Adding
text boxes to worksheets is a snap. At some point you may want to make sure
that the textbox you are adding is exactly the same size as the cell (or
http://exceltips.vitalnews.com/Pages/T002192_Sizing_Text_Boxes_and_Cells_the

_Same.html

Hiding Rows Based on Two Values
A common
task in accounting (and other areas) is to suppress the display of tables in
a row based on whether the values in two cells of a row meet a
http://exceltips.vitalnews.com/Pages/T002416_Hiding_Rows_Based_on_Two

_Values.html

Static Sizes for Comment Boxes
Comment
boxes can be resized as you like, but those sizes may not “stick” in Excel.
This tip explains when they should stick and when they won’t.
http://exceltips.vitalnews.com/Pages/T003160_Static_Sizes_for_Comment

_Boxes.html

Freezing Top Rows and Bottom Rows
It is
not unusual to need to see both your column headers and column totals in your
data. How to display all this information at one time can be a
http://exceltips.vitalnews.com/Pages/T003286_Freezing_Top_Rows_and_Bottom_Ro

ws.html

Selecting Combo Boxes in Locked Worksheets
Combo
boxes are a nice touch for controlling entries made by users of your
worksheets. If you protect the worksheet, there are a couple of things you
need
http://exceltips.vitalnews.com/Pages/T002691_Selecting_Combo_Boxes_in_Locked

_Worksheets.html

Dynamic Worksheet Tab Names
Would it
be helpful if your worksheet tab names were determined by the contents of a
cell in your worksheet? Using the macros described in this tip,
http://exceltips.vitalnews.com/Pages/T002145_Dynamic_Worksheet_Tab_Na

mes.html

Getting Rid of "Copy of"
When you
open a read-only workbook, and then display the Save As dialog box, the words
“Copy of” are added as a prefix to the suggested file name.
http://exceltips.vitalnews.com/Pages/T003064_Getting_Rid_of_Copy_of.h

tml

Converting Text to Values
Got some
imported data that is being improperly parsed as text? This tip explains how
you can convert the text so you can use it properly.
http://exceltips.vitalnews.com/Pages/T002745_Converting_Text_to_Value

s.html

Inserting and Deleting Rows in a Protected

Worksheet

You’ve
protected and saved your worksheet with explicit instructions that you be
allowed to insert and delete rows. But when you try it, you find that you
http://exceltips.vitalnews.com/Pages/T003004_Inserting_and_Deleting_Rows_in_

a_Protected_Worksheet.html

Understanding Color and Conditional Formatting

Codes

Using
custom formatting, you can format a cell so that different types of values
display exactly as you want them to. This tip explains how to create the
http://exceltips.vitalnews.com/Pages/T002136_Understanding_Color_and_Conditi

onal_Formatting_Codes.html

Automatically Numbering Rows
Do you
need to keep track of a row number for your data table? Excel includes some
worksheet functions that make numbering your rows easy, provided you know
http://exceltips.vitalnews.com/Pages/T002911_Automatically_Numbering_

Rows.html

Counting Words
Excel is
not a word processor, but you can count the words in a group of cells. The
way to do this is with a macro, as outlined in this tip.
http://exceltips.vitalnews.com/Pages/T002105_Counting_Words.html
Creating a Plus/Minus Button
The
plus/minus button on a calculator provides a quick way to change a value from
a positive to a negative value, and back again. There is no such button in
http://exceltips.vitalnews.com/Pages/T003256_Creating_a_PlusMinus_But

ton.html

SUMIF Doesn’t Recalc Automatically
Do you
have a function that doesn’t seem to recalculate as you expect it to? If so,
there are only a limited number of reasons that could be causing the
http://exceltips.vitalnews.com/Pages/T003069_SUMIF_Doesnt_Recalc_Auto

matically.html

Converting to Hyperlinks in a Shared Workbook
When you
enter a URL or e-mail address in a worksheet, Excel usually converts it to a
clickable hyperlink. This doesn’t occur, however, if the workbook is
http://exceltips.vitalnews.com/Pages/T003155_Converting_to_Hyperlinks_in_a_S

hared_Workbook.html

Unlocking a Worksheet with an Unknown Password
Excel
uses several different types of security for data in a workbook. At a
worksheet level, you can protect the contents of the sheet so they cannot be
http://exceltips.vitalnews.com/Pages/T002776_Unlocking_a_Worksheet_with_an_U

nknown_Password.html

Copying Comments to Cells
Need to
copy whatever is in a comment into a cell on your worksheet? If you have lots
of comments, manually doing this can be tedious.
http://exceltips.vitalnews.com/Pages/T002981_Copying_Comments_to_Cell

s.html

Adding the Set Print Area Tool
Need to
set print areas quite often? Adding a tool that allows you to quickly make
the specification can be a great time saver.
http://exceltips.vitalnews.com/Pages/T003035_Adding_the_Set_Print_Are

a_Tool.html

Easily Adding Blank Rows
If you
need to “open up” the data in a worksheet so that there are blank lines
available, you’ll like this tip. It explains the easiest ways to add just the
http://exceltips.vitalnews.com/Pages/T003011_Easily_Adding_Blank_Rows

.html

Working with Imperial Linear Distances
Excel is
great when working with decimal-based numeric values. It is not so great when
it comes to other numbering systems, such as those used for linear
http://exceltips.vitalnews.com/Pages/T003137_Working_with_Imperial_Linear_Di

stances.html

Using Chart Titles
Adding
chart titles to give meaning to Excel graphs is easy with this tip. Provided
by ExcelTips, a free weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002838_Using_Chart_Titles.html

Patterns of Numbers with a Formula

Some
uses of Excel may require you to create patterns of numbers. This is the case
with Helen, who needed a specific pattern, but was at a loss as to how to
http://exceltips.vitalnews.com/Pages/T002889_Patterns_of_Numbers_with_a_Form

ula.html

Removing Subtotals from Many PivotTable Fields
Put
together a large PivotTable, and you can have many different fields to format
or otherwise configure. If you want to get rid of all the subtotals on
http://exceltips.vitalnews.com/Pages/T003103_Removing_Subtotals_from_Many_Pi

votTable_Fields.html

Searching a Workbook by Default
When you
display the Find tab of the Find and Replace dialog box, you’ll notice that
any search, by default, will be on the current worksheet.
http://exceltips.vitalnews.com/Pages/T003170_Searching_a_Workbook_by_

Default.html

Recognizing a Header Row when Sorting
Excel
provides a couple of ways you can sort the information in a worksheet. Before
sorting, it pays to know whether Excel will recognize your header row
http://exceltips.vitalnews.com/Pages/T002586_Recognizing_a_Header_Row_when_S

orting.html

Saving Worksheets in Lotus 1-2-3 Format
You can
export an Excel worksheet in a variety of formats. One of those formats is
Lotus 1-2-3, which is available as an export format on many versions of
http://exceltips.vitalnews.com/Pages/T002509_Saving_Worksheets_in_Lotus_123_

Format.html

Highlighting the Rows of Selected Cells
If you
lose your place on the screen quite often, you might find it helpful to have
not just a single cell highlighted, but the entire row.
http://exceltips.vitalnews.com/Pages/T002457_Highlighting_the_Rows_of_Select

ed_Cells.html

Replacing and Converting in a Macro
Sometimes
the data you import from other programs needs to be converted in some way in
order to be useful in Excel. This tip explains how you can use a
http://exceltips.vitalnews.com/Pages/T002291_Replacing_and_Converting_in_a_M

acro.html

Changing Coordinate Colors
Want to
change the colors used by Excel for the column letters and row numbers on a
worksheet? You can’t do it directly in Excel; instead you need to make
http://exceltips.vitalnews.com/Pages/T002932_Changing_Coordinate_Colo

rs.html

Reducing File Sizes for Workbooks with

PivotTables

Adding
PivotTables to a workbook can balloon the size of the workbook file
dramatically. There are ways to reduce the file size, and you should take
http://exceltips.vitalnews.com/Pages/T002851_Reducing_File_Sizes_for_Workboo

ks_with_PivotTables.html

Specifying a Print Tray for a Worksheet
It is
not uncommon for modern printers, particularly those in offices, to have
several different paper trays available. Accessing those paper trays in Excel
http://exceltips.vitalnews.com/Pages/T003177_Specifying_a_Print_Tray_for_a_W

orksheet.html

Character Limits for Cells
Excel
places limits on how much information you can enter into a cell and how much
of that information it will display. This tip explains those limits,
http://exceltips.vitalnews.com/Pages/T003163_Character_Limits_for_Cel

ls.html

Buttons for Workbooks
Most
custom toolbar buttons are used to execute macros, and you can easily create
a macro to open a workbook at the click of a button.
http://exceltips.vitalnews.com/Pages/T002884_Buttons_for_Workbooks.ht

ml

Establishing a FLOOR and CEILING
Two of
the more esoteric rounding functions in Excel are FLOOR and CEILING. These
functions allow you to round in very specific ways using multiples of
http://exceltips.vitalnews.com/Pages/T002152_Establishing_a_FLOOR_and_CEILIN

G.html

AutoFilling from a Custom List
Custom
lists can be a boon for special sorting needs. They can also come in handy
for filling cells with repeating data. This tip discloses how to use
http://exceltips.vitalnews.com/Pages/T003050_AutoFilling_from_a_Custo

m_List.html

Workbook Events
You can
program Excel so that when specific events happen, your macro is executed.
Your event handlers can react to almost 30 different events relative to a
http://exceltips.vitalnews.com/Pages/T002570_Workbook_Events.html
Returning Zero When a Referenced Cell is Blank
Formulas
in Excel must return some sort of value; they cannot return nothing. This can
cause problems for some formulas, as described in this tip.
http://exceltips.vitalnews.com/Pages/T002174_Returning_Zero_When_a_Reference

d_Cell_is_Blank.html

Automatically Protecting After Input
Do you
want user-entered data to be immediately protected so that it cannot be
changed? This can be done relatively easily using a simple macro.
http://exceltips.vitalnews.com/Pages/T002034_Automatically_Protecting

_After_Input.html

Protecting Worksheets
Excel
allows data protection for particular cells or a whole worksheet in a shared
work environment. Provided by ExcelTips, a free weekly productivity
http://exceltips.vitalnews.com/Pages/T001961_Protecting_Worksheets.ht

ml

Converting Imported Information to Numeric Values
When
importing data into Excel that was created in a different program, the
results you get are dependant on the characteristics of the data.
http://exceltips.vitalnews.com/Pages/T002248_Converting_Imported_Information

_to_Numeric_Values.html

Specifying an Order for Page Printing
When
printing a multi-page worksheet, you may want your pages to print in a
particular order. Excel allows you to specify whether they should print left
to
http://exceltips.vitalnews.com/Pages/T002979_Specifying_an_Order_for_Page_Pr

inting.html

Shortening ZIP Codes
US ZIP
Codes can be of two varieties: five-digits or nine-digits. Here’s how to
convert longer ZIP Codes to the shorter variety. Provided by ExcelTips
http://exceltips.vitalnews.com/Pages/T002654_Shortening_ZIP_Codes.htm

l

Creating Scenarios
The
Scenario Manager is a tool you can use to store and easily recall different
settings within a worksheet. This tip introduces the Scenario Manager and
http://exceltips.vitalnews.com/Pages/T002860_Creating_Scenarios.html
Dynamic Hyperlinks in Excel
Hyperlinks
to many types of Web sites rely on passing parameters in the URL. Knowing
this, you can construct a dynamic hyperlink by pulling information from
http://exceltips.vitalnews.com/Pages/T002445_Dynamic_Hyperlinks_in_Ex

cel.html

Resizing Checkboxes
User
forms, created in VBA, can be very helpful for a user interface. If the
default checkboxes in the form are too small for your liking, there are only
a
http://exceltips.vitalnews.com/Pages/T002282_Resizing_Checkboxes.html

Getting Rid of Non-Printing Characters

Intelligently

Is your
worksheet, imported from an external source, plagued by non-printing
characters that show up like small boxes on-screen?
http://exceltips.vitalnews.com/Pages/T002947_Getting_Rid_of_NonPrinting_Char

acters_Intelligently.html

Protecting an Entire Workbook
You can
protect an entire workbook so that its structure and its windows cannot be
changed. This tip discloses how to apply this protection, and discusses
http://exceltips.vitalnews.com/Pages/T002231_Protecting_an_Entire_Wor

kbook.html

Sorting by Colors
Excel
allows you to sort by the data within a list, but it doesn’t allow you to
inherently sort by cell color. This tip shows you how to overcome that
http://exceltips.vitalnews.com/Pages/T003122_Sorting_by_Colors.html
Using Subtotals and Totals
You can
easily add subtotals or totals to a data table, but adding them both to the
same column as your data could give erroneous results.
http://exceltips.vitalnews.com/Pages/T002749_Using_Subtotals_and_Tota

ls.html

Pulling Apart Cells
The Text
to Columns tool is a great boon for analyzing textual data in a worksheet.
You can use the tool to strip text into component parts that you can
http://exceltips.vitalnews.com/Pages/T002967_Pulling_Apart_Cells.html

Relative Worksheet References
When you
copy a formula from one place to another, Excel automatically adjusts the
references within that formula relative to the target location of the
http://exceltips.vitalnews.com/Pages/T003088_Relative_Worksheet_Refer

ences.html

Adding a Custom Format to those Offered by Excel
Custom
formats in Excel provide a way for you to specify exactly how you want data
to look in a cell. You can define custom formats in a workbook,
http://exceltips.vitalnews.com/Pages/T003096_Adding_a_Custom_Format_to_those

_Offered_by_Excel.html

Easily Entering Dispersed Data
Do you
need to enter data in a bunch of widely dispersed cells in a worksheet? You
can make the job easier by using Excel’s protection capabilities,
http://exceltips.vitalnews.com/Pages/T003027_Easily_Entering_Disperse

d_Data.html

Adding AutoShapes
The
graphics features of Excel allow you to add a number of predefined AutoShapes
to a workbook. If you want to add shapes to the AutoShapes feature,
http://exceltips.vitalnews.com/Pages/T001995_Adding_AutoShapes.html
Opening a Workbook but Disabling Macros
Macros
that run automatically when you open or close a workbook are quite helpful.
You may not want them to run, however, when you open or close a workbook
http://exceltips.vitalnews.com/Pages/T003158_Opening_a_Workbook_but_Disablin

g_Macros.html

Using a Different Footer on Secondary Pages
The
header and footer capabilities of Excel are not that great. For instance, you
cannot instruct Excel to print one footer on the first page of a printout
http://exceltips.vitalnews.com/Pages/T002187_Using_a_Different_Footer_on_Sec

ondary_Pages.html

Calculating Time Differences between Two Machines
If you
need to compare the time on your machine with the time maintained by a server
on your network, you’ll be interested in the information presented in
http://exceltips.vitalnews.com/Pages/T003258_Calculating_Time_Differences_be

tween_Two_Machines.html

Always Open at 100% Zoom
Tired of
shared workbooks opening at some strange zoom factor that makes viewing your
data difficult? Here’s how to make sure that your workbooks always
http://exceltips.vitalnews.com/Pages/T002668_Always_Open_at_100_Zoom.

html

Setting Print Titles
Want
some of the rows or columns in your worksheet to repeat on each page of your
printout? Using the controls in the Page Setup dialog box, you can specify
http://exceltips.vitalnews.com/Pages/T002847_Setting_Print_Titles.htm

l

Setting the Default Font Size for Comment

Balloons

Straining
your eyes to see the comments in a document? You can modify the size of the
default font used for the comments, but it involves making a change in
http://exceltips.vitalnews.com/Pages/T002974_Setting_the_Default_Font_Size_f

or_Comment_Balloons.html

Changing the Shortcut Menu
If you
want to change the Context menus used in Excel, on purpose, here’s how to go
about it. Just create a macro and make the change to the controls on a
http://exceltips.vitalnews.com/Pages/T001962_Changing_the_Shortcut_Me

nu.html

Hiding Columns Based on a Cell Value
Want to
hide the information in a particular column based on the value stored in a
specific cell in your worksheet? It’s relatively easy to do if you have a
http://exceltips.vitalnews.com/Pages/T003283_Hiding_Columns_Based_on_a_Cell_

Value.html

Running a Macro When a Worksheet is Activated
One of
the automatic macros that Excel allows you to set up is one that will run
every time a worksheet is activated. This is relatively simple to do,
http://exceltips.vitalnews.com/Pages/T002564_Running_a_Macro_When_a_Workshee

t_is_Activated.html

Maintaining Text Formatting in a Lookup
The
lookup functions provided by Excel are very powerful, but they cannot copy
formatting from the source to the target cells. This tip explains why this is
http://exceltips.vitalnews.com/Pages/T002318_Maintaining_Text_Formatting_in_

a_Lookup.html

Matching Formatting when Concatenating
You can
use a couple of different methods to use formulas to put text strings
together. When doing so, you may notice that the results of the
concatenations
http://exceltips.vitalnews.com/Pages/T003213_Matching_Formatting_when_Concat

enating.html

Inconsistent Output for Empty Columns in a CSV

File

When you
create a CSV file in Excel, the information stored in the file may not
contain all the fields that you think it should.
http://exceltips.vitalnews.com/Pages/T003068_Inconsistent_Output_for_Empty_C

olumns_in_a_CSV_File.html

Changing the Color of a Cell Border
Many
people add borders to cells on a regular basis, yet have no clue that you can
change the color of those borders. This tip shows how easy it is to get
http://exceltips.vitalnews.com/Pages/T002977_Changing_the_Color_of_a_

Cell_Border.html

Converting Numeric Values to Times

If you
enter times such as “14:45” into a worksheet without the colon, Excel
interprets them as regular numbers. Getting Excel to recognize these numbers
as
http://exceltips.vitalnews.com/Pages/T002775_Converting_Numeric_Values_to_Ti

mes.html

Removing Duplicate Cells
If you
have a list of data in a worksheet, you know how difficult it can be to get
rid of duplicates. This tip presents a macro that makes paring down your
http://exceltips.vitalnews.com/Pages/T002109_Removing_Duplicate_Cells

.html

Using Check Boxes
One of
the form controls you can add to a worksheet is a check box. This tip
explains how to add and configure this handy control. Provided by ExcelTips
http://exceltips.vitalnews.com/Pages/T003246_Using_Check_Boxes.html
Synchronized Workbook and Worksheet Names
When you
work on older workbooks in Excel, you may notice that the name of the
worksheet tab and the workbook itself are the same. This is by design,
http://exceltips.vitalnews.com/Pages/T001950_Synchronized_Workbook_and_Works

heet_Names.html

Conditionally Highlighting Cells Containing

Formulas

You can
use conditional formatting for all sorts of things, including the
highlighting of cells that contain formulas. This tip explains how you can do
this
http://exceltips.vitalnews.com/Pages/T003188_Conditionally_Highlighting_Cell

s_Containing_Formulas.html

Automatic Row Height For Merged Cells with Text

Wrap

When you
have text wrap turned on in a cell, Excel expands the height of the row as
you add more text to the cell. When you merge two cells that have text
http://exceltips.vitalnews.com/Pages/T003207_Automatic_Row_Height_For_Merged

_Cells_with_Text_Wrap.html

Printing a Chart
Got a
chart that you’ve created? Printing it can be as easy as printing your
worksheets, but there are a couple of chart-specific things you’ll want to
http://exceltips.vitalnews.com/Pages/T003244_Printing_a_Chart.html

Header and Footer Formatting Codes

Formatting
codes to use in Excel headers and footers. Provided by ExcelTips, a free
weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002057_Header_and_Footer_Formatting_Co

des.html

Selectively Importing Records
Got a
huge amount of data you need to import into Excel? Sometimes the best way is
through the use of a macro, as described in this tip.
http://exceltips.vitalnews.com/Pages/T002239_Selectively_Importing_Re

cords.html

Returning a Weight and a Date
If you
have two columns containing dates and weights from those dates, you may want
to pick a date associated with a given weight.
http://exceltips.vitalnews.com/Pages/T001972_Returning_a_Weight_and_a

_Date.html

Coloring Cells with Formulas
Want a
quick way to tell where all the formulas are in a worksheet? In a few quick
steps you can color all those cells. This tip explains how to do it
http://exceltips.vitalnews.com/Pages/T002766_Coloring_Cells_with_Form

ulas.html

Finding the Analysis ToolPak Add-In

The
Analysis ToolPak is used to add some very handy worksheet functions to Excel.
If you don’t have it installed, and you can’t find your original Microsoft
http://exceltips.vitalnews.com/Pages/T003284_Finding_the_Analysis_Too

lPak_AddIn.html

Jumping to Alphabetic Worksheets
Workbooks
can contain many, many worksheets. If you want a quick way to jump to a
specific worksheet, there are a couple of techniques you can apply.
http://exceltips.vitalnews.com/Pages/T002718_Jumping_to_Alphabetic_Wo

rksheets.html

Converting From Numbers to Text
If you
have a range of numeric values in your worksheet, you may want to change them
from numbers to text values. Here’s how you can make the switch.
http://exceltips.vitalnews.com/Pages/T002680_Converting_From_Numbers_

to_Text.html

Conditional Formatting Based on Date Proximity
Creating
conditional formatting to turn cells different colors depending on the values
entered. Provided by ExcelTips, a free weekly productivity
http://exceltips.vitalnews.com/Pages/T002664_Conditional_Formatting_Based_on

_Date_Proximity.html

Exporting from Excel to AutoCAD
If your
office uses AutoCAD, you may wonder how you can get information stored in
Excel into your AutoCAD drawings. There are known limitations to placing
http://exceltips.vitalnews.com/Pages/T002992_Exporting_from_Excel_to_

AutoCAD.html

Transposing Your Data
Sometimes
data is oriented horizontally in a worksheet, but you really need it
vertically oriented (or vice versa). This tip explains how you can use the
http://exceltips.vitalnews.com/Pages/T002744_Transposing_Your_Data.ht

ml

Sharing Your Workbook
Workbook
sharing is a rather esoteric feature in Excel that enables multiple people to
work on the same workbook at once. If you have this need,
http://exceltips.vitalnews.com/Pages/T002916_Sharing_Your_Workbook.ht

ml

Making Changes in a Group of Workbooks
Making
changes in a workbook is easy. Making changes in hundreds of workbooks is not
easy. This tip explains a variety of ways in which you can make quick
http://exceltips.vitalnews.com/Pages/T003176_Making_Changes_in_a_Group_of_Wo

rkbooks.html

Selecting Random Names
Got a
list of names from which you need to select a few at random? There are
several ways you can accomplish this task, using either formulas or macros.
http://exceltips.vitalnews.com/Pages/T002811_Selecting_Random_Names.h

tml

Using the Report Manager
The
Report Manager allows you to create specialized reports that can be easily
printed from your worksheet data. How you install and use the Report Manager
http://exceltips.vitalnews.com/Pages/T002902_Using_the_Report_Manager

.html

Selective Headers and Footers
Excel’s
tools for creating headers and footers aren’t that great, which means you
might not be able to control the headers and footers like you may need to.
http://exceltips.vitalnews.com/Pages/T002189_Selective_Headers_and_Fo

oters.html

Changing AutoFilter Drop-Down Arrow Colors
When you
use the AutoFilter capabilities of Excel (Data | Filter | AutoFilter), small
drop-down arrows appear at the top of each column in your data table.
http://exceltips.vitalnews.com/Pages/T002371_Changing_AutoFilter_DropDown_Ar

row_Colors.html

Sorting ZIP Codes
If you
have both five- and nine-digit ZIP Codes in a column, you may have noticed
that they don’t sort properly. The problem is related to how Excel treats
http://exceltips.vitalnews.com/Pages/T002793_Sorting_ZIP_Codes.html
Limiting Input to a Format
When
setting up a worksheet for others to use, you might want to make some
limitations on what can be entered in certain cells. This tip provides a
couple
http://exceltips.vitalnews.com/Pages/T002627_Limiting_Input_to_a_Form

at.html

Error Using ATAN2 Function in Macro
You can
use worksheet functions from within a macro, but you may not always get the
results you expect. This tip explains how using the ATAN2 worksheet
http://exceltips.vitalnews.com/Pages/T002892_Error_Using_ATAN2_Function_in_M

acro.html

Changing the Default PivotTable Functions
When you
create a PivotTable, Excel automatically sums the data that you place into
the Data Items area of the table. This tip discusses ways you can change
http://exceltips.vitalnews.com/Pages/T002392_Changing_the_Default_PivotTable

_Functions.html

Specifying a Paper Tray in a Macro

Different
printers have different capabilities. One common option on printers is a
multitude of paper trays. Specifying which paper tray that Excel should
http://exceltips.vitalnews.com/Pages/T002213_Specifying_a_Paper_Tray_

in_a_Macro.html

Noting When a Workbook was Changed

You may
want to keep track of when changes were last made to a workbook, particularly
if the workbook is accessible to multiple people who may change it.
http://exceltips.vitalnews.com/Pages/T002935_Noting_When_a_Workbook_was_Chan

ged.html

Refreshing PivotTable Data
If you
modify the data on which a PivotTable is based, you’ll need to refresh the
table so it reflects the modified data. This tip explains the various ways
http://exceltips.vitalnews.com/Pages/T002471_Refreshing_PivotTable_Da

ta.html

Quickly Identifying Applied AutoFilters
The
AutoFilters feature is really handy when you want to work with a portion of a
large dataset. After a while it is easy to forget which columns have
http://exceltips.vitalnews.com/Pages/T002891_Quickly_Identifying_Applied_Aut

oFilters.html

Accessing a Problem Shared Workbook
What are
you to do is you share a workbook with others, and then suddenly the workbook
won’t open properly? Dealing with a corrupted workbook can be a
http://exceltips.vitalnews.com/Pages/T003154_Accessing_a_Problem_Shared_Work

book.html

Printing a Single Column in Multiple Columns
The data
in some worksheets consists of just a column or two of data, extending for
many rows. When you print these worksheets, you end up with a printout
http://exceltips.vitalnews.com/Pages/T002219_Printing_a_Single_Column_in_Mul

tiple_Columns.html

Converting from SuperCalc
The
scrapheap of computing history is littered with programs that had their
moment in the sun and then fell by the wayside. One such program is
SuperCalc,
http://exceltips.vitalnews.com/Pages/T002443_Converting_from_SuperCal

c.html

Sorting Huge Lists
When
sorting large amounts of data, you may not always get the results you expect.
In fact, it may appear that Excel hasn’t really sorted all you data.
http://exceltips.vitalnews.com/Pages/T002922_Sorting_Huge_Lists.html
Counting Unique Values with Functions
Using a
few categories of functions in Excel to count unique values. Provided by
ExcelTips, a free weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002167_Counting_Unique_Values_with_Fun

ctions.html

Using GEOMEAN With a Large List
GEOMEAN
allows you to calculate the geometric mean of a series of values. This is
easy with a limited number of values in your series,
http://exceltips.vitalnews.com/Pages/T002580_Using_GEOMEAN_With_a_Lar

ge_List.html

Big File Memory Blues
Excel
places limits on how much memory your workbooks can use. Despite these
limits, it is possible to create a workbook that you later cannot open.
http://exceltips.vitalnews.com/Pages/T002827_Big_File_Memory_Blues.ht

ml

Missing PivotTable Data
Stephen’s
workbook, created by someone else, contains a PivotTable that he cannot edit.
This tip explains possible causes (and cures) for the problem.
http://exceltips.vitalnews.com/Pages/T003112_Missing_PivotTable_Data.

html

Using the SUBTOTAL Function
One of
the very versatile and flexible functions in Excel is SUBTOTAL. Provided by
ExcelTips, a free weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002048_Using_the_SUBTOTAL_Funct

ion.html

Highlighting Values in a Cell
Excel
allows you, through normal editing, to overwrite formulas in a cell with
explicit values. Wouldn’t it be nice to know where such overwriting occurred,
http://exceltips.vitalnews.com/Pages/T003224_Highlighting_Values_in_a

_Cell.html

Dynamic Headers and Footers
Do you
want to change the headers and footers that appear on different pages of your
printout? Here’s how you can get just what you want.
http://exceltips.vitalnews.com/Pages/T002058_Dynamic_Headers_and_Foot

ers.html

Adding a Macro to a Toolbar
Macros
can make Excel much more powerful, allowing you to quickly perform repetitive
tasks, process data, and do just about anything else you can think of.
http://exceltips.vitalnews.com/Pages/T002926_Adding_a_Macro_to_a_Tool

bar.html

Working with Minutes
For many
Excel users—particularly beginners—working with elapsed time can be
bewildering. This tip explains how you can display and work with elapsed
http://exceltips.vitalnews.com/Pages/T002820_Working_with_Minutes.htm

l

Skipping Hidden Rows in a Macro
Macros
often have to step through rows of data in a worksheet in order to do their
processing. If you have such a macro, you may want it to automatically
http://exceltips.vitalnews.com/Pages/T002286_Skipping_Hidden_Rows_in_

a_Macro.html

Removing Conditional Formats, but Not the Effects
Conditional
formatting is very powerful, but at some point you may want to make the
formatting “unconditional.” In other words, you might want to remove the
http://exceltips.vitalnews.com/Pages/T001947_Removing_Conditional_Formats_bu

t_Not_the_Effects.html

Using Message Boxes
When
creating a macro, one of the ways you can communicate with users is through
the use of a message box. This tip explains what message boxes are and how
http://exceltips.vitalnews.com/Pages/T002265_Using_Message_Boxes.html

Pasting a Graphic to Multiple Worksheets
Pasting
a graphic into a worksheet is easy. Pasting the same graphic to a lot of
worksheets is repetitious. Excel doesn’t make it easy to paste graphics to
http://exceltips.vitalnews.com/Pages/T003222_Pasting_a_Graphic_to_Multiple_W

orksheets.html

Generating Unique Numbers for Worksheets
You may
need to automatically generate unique numbers when you create new worksheets
in a workbook. Here’sa couple of easy ways to do it.
http://exceltips.vitalnews.com/Pages/T003336_Generating_Unique_Numbers_for_W

orksheets.html

Calculating the Interval Between Occurrences
With a
long list of items in a worksheet, you may want to determine the last time a
particular item appeared in the list. There are a couple of ways you can
http://exceltips.vitalnews.com/Pages/T002338_Calculating_the_Interval_Betwee

n_Occurrences.html

Entering Large Time Values
If you
need to input humongous times into a worksheet, you may run into a problem if
you need to enter times greater than 10000 hours.
http://exceltips.vitalnews.com/Pages/T002038_Entering_Large_Time_Valu

es.html

Hiding Individual Cells
Got some
information in a worksheet that you need to hide so it doesn’t show up on a
printout? It’s easy to hide rows and column, but less obvious is how
http://exceltips.vitalnews.com/Pages/T002880_Hiding_Individual_Cells.

html

Summing Only Visible Values
The SUM
function doesn’t allow you to ignore hidden values in a range being summed.
Instead, you are better off using the SUBTOTAL function or a special
http://exceltips.vitalnews.com/Pages/T003082_Summing_Only_Visible_Val

ues.html

Adding Items to a Context Menu
Context
menus appear when you right-click on items in a worksheet. They are not the
easiest things in the world to change, but you can change them through
http://exceltips.vitalnews.com/Pages/T002064_Adding_Items_to_a_Contex

t_Menu.html

Creating Worksheets with a Macro
Using a
macro to add worksheets to your workbook is easy. This tip provides two
different methods you can use. Provided by ExcelTips, a free weekly
http://exceltips.vitalnews.com/Pages/T002022_Creating_Worksheets_with

_a_Macro.html

Removing All Formatting
Need to
remove the formatting from a range of cells? There are a number of different
approaches you can take, from using the Format Painter to adding a new
http://exceltips.vitalnews.com/Pages/T002794_Removing_All_Formatting.

html

Relative References within Named Ranges
Named
ranges are a great boon when putting together formulas. They can make complex
cell references easier and faster. This tip explains some additional
http://exceltips.vitalnews.com/Pages/T003202_Relative_References_within_Name

d_Ranges.html

Counting Cells with Specific Characters
If
you’ve got a long list of data and you want to count the number of cells in
the range that begin with a specific character, putting together the proper
http://exceltips.vitalnews.com/Pages/T002342_Counting_Cells_with_Specific_Ch

aracters.html

Excluding Values from Averaging
Calculating
an average of a group of numbers is easy. What if you want to exclude a
couple of the numbers from the group you are averaging?
http://exceltips.vitalnews.com/Pages/T002050_Excluding_Values_from_Av

eraging.html

Pictures in AutoShapes
Filling
an AutoShape with a picture of your choosing is a neat trick. Excel makes it
easy to do. Provided by ExcelTips, a free weekly productivity
http://exceltips.vitalnews.com/Pages/T002391_Pictures_in_AutoShapes.h

tml

Formatting Currency
If you
want to format currency values so that Excel uses periods between groups of
thousands and commas as a decimal separator, there are a few ways you can
http://exceltips.vitalnews.com/Pages/T001935_Formatting_Currency.html

Shortcut to Move Between Two Worksheets
It is
not uncommon to have workbook where two of the worksheets get more use than
any of the others. If you want to easily switch between these two
http://exceltips.vitalnews.com/Pages/T003216_Shortcut_to_Move_Between_Two_Wo

rksheets.html

Typing Check Marks into Excel
Need to
enter a check mark into a cell? There are a number of ways you can get the
desired character, depending on the font you want to use.
http://exceltips.vitalnews.com/Pages/T003201_Typing_Check_Marks_into_

Excel.html

Selecting a Cell in the Current Row
When
creating a macro, you may wonder how you can access cells in the same row as
the currently selected cell. The techniques covered in this tip can save
http://exceltips.vitalnews.com/Pages/T002267_Selecting_a_Cell_in_the_

Current_Row.html

Calculating Business Days
Excel
allows you to determine the number of work days (business days) between two
dates by using the NETWORKDAYS worksheet function.
http://exceltips.vitalnews.com/Pages/T002155_Calculating_Business_Day

s.html

Word Documents from Excel Macros
You can
use Excel macros to open and manipulate not just Excel workbooks, but also
Word documents. This tip discusses some ways that you can perform this
http://exceltips.vitalnews.com/Pages/T002423_Word_Documents_from_Excel_Macro

s.html

Anchoring Comment Boxes in Desired Locations
Comment
boxes, when they pop up, always do so to the right of the column in which the
comment appears. This can cause problems for some worksheet layouts,
http://exceltips.vitalnews.com/Pages/T002887_Anchoring_Comment_Boxes_in_Desi

red_Locations.html

Turning Off Track Changes without Unsharing
The
Track Changes tool in Excel can be helpful, but it can also be aggravating
because it doesn’t allow you to use it on a shared workbook.
http://exceltips.vitalnews.com/Pages/T002511_Turning_Off_Track_Changes_witho

ut_Unsharing.html

Using Print Preview
Print
Preview allows you to see what your output will look like before actually
committing it to paper. This tip explains some techniques for getting the
http://exceltips.vitalnews.com/Pages/T002212_Using_Print_Preview.html

Sorting with Graphics
If the
graphics that you insert in your worksheet meet a couple of simple
requirements, it is possible to have those graphics be sorted right along
with the
http://exceltips.vitalnews.com/Pages/T002954_Sorting_with_Graphics.ht

ml

Editing Macros
If you
start using macros to process data in Excel, you’ll eventually have to edit
those macros. You can also display macros developed by other people (or
http://exceltips.vitalnews.com/Pages/T002331_Editing_Macros.html
Changing Chart Type
Excel
allows you to add two distinct types of charts to your workbooks: embedded or
chart sheets. You can switch between the two by following the steps
http://exceltips.vitalnews.com/Pages/T003030_Changing_Chart_Type.html

Picking Different Random Numbers from a Range
If you
need to randomly select multiple items from a list, you obviously don’t want
those items to duplicate each other. This tip explains a couple of
http://exceltips.vitalnews.com/Pages/T002874_Picking_Different_Random_Number

s_from_a_Range.html

Automatically Updating Charts for Additional Data
If you
have a chart that is based on data in your worksheet, you might want the
chart to update automatically as you add new information.
http://exceltips.vitalnews.com/Pages/T002933_Automatically_Updating_Charts_f

or_Additional_Data.html

Dealing With the Euro
How to
add the Euro symbol to your Excel worksheets. Provided by ExcelTips, a free
weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T001929_Dealing_With_the_Euro.ht

ml

Using SUM In a Macro
If you
need to use the SUM worksheet function from within a macro, you can easily do
it by using the techniques discussed in this tip.
http://exceltips.vitalnews.com/Pages/T003217_Using_SUM_In_a_Macro.htm

l

Determining "Highest Since" or
"Lowest Since"
When
compiling statistics on a collection of data points, you may want to know
whether a particular value is the “highest since” or “lowest since” when
http://exceltips.vitalnews.com/Pages/T003138_Determining_Highest_Since_or_Lo

west_Since.html

Selecting Columns in VBA when Cells are Merged
If you
manually select an entire column, and there are some cells in that column
that are merged with cells in an adjacent column, Excel sort of “skips” the
http://exceltips.vitalnews.com/Pages/T003093_Selecting_Columns_in_VBA_when_C

ells_are_Merged.html

Saving Changes in Personal.xls
When you
exit Excel and you’ve made changes to a workbook, the program asks if you
want to save those changes. It can be frustrating if the request happens
http://exceltips.vitalnews.com/Pages/T003092_Saving_Changes_in_Person

alxls.html

Header and Footer Background Color

When
designing a worksheet, you’ve no doubt noticed that Excel provides only
minimal formatting for headers and footers on a printout.
http://exceltips.vitalnews.com/Pages/T003056_Header_and_Footer_Background_Co

lor.html

Highlighting Cells Containing Specific Text
If you
want to highlight cells that contain certain characters, you can use the
conditional formatting features of Excel to help out.
http://exceltips.vitalnews.com/Pages/T002671_Highlighting_Cells_Containing_S

pecific_Text.html

Counting Wins and Losses
If you
want to count specific characters in rows of a data table, you’ll find this
tip helpful. Here you discover three different ways you can count the
http://exceltips.vitalnews.com/Pages/T003049_Counting_Wins_and_Losses

.html

Understanding Views
Excel
allows you to save different views of your data. This tip explains what views
are and how you can create your own views. Provided by ExcelTips, a free
http://exceltips.vitalnews.com/Pages/T002865_Understanding_Views.html

Complex Lookup Formulas
Not all
lookup operations are straight-forward and easy. This tip discusses a more
complicated lookup using concatenated string data to look up information
http://exceltips.vitalnews.com/Pages/T002787_Complex_Lookup_Formulas.

html

Thoughts and Ideas on Significant Digits in Excel
Ruminations
and reflections about significant digits in Excel. Includes examples of how
significant digits can affect the outcome of various functions and
http://exceltips.vitalnews.com/Pages/T001983_Thoughts_and_Ideas_on_Significa

nt_Digits_in_Excel.html

Printing a Chart Across Multiple Pages
Wouldn’t
it be great to have your huge charts print out on multiple pieces of paper
that you could then piece together? While Excel doesn’t provide this
http://exceltips.vitalnews.com/Pages/T002543_Printing_a_Chart_Across_Multipl

e_Pages.html

Inserting Hyperlinks
One type
of data that Excel can store within a cell is a hyperlink to another resource
either on your local computer or on the Web.
http://exceltips.vitalnews.com/Pages/T002802_Inserting_Hyperlinks.htm

l

Summing Based on Formatting in Adjacent Cells
It is
easy to use Excel functions to sum values based on criteria you establish,
unless those criteria involve the formatting applied to cells in adjacent
http://exceltips.vitalnews.com/Pages/T003298_Summing_Based_on_Formatting_in_

Adjacent_Cells.html

Hyperlinks to Charts
You can
create hyperlinks to all sorts of worksheets in a workbook, but you cannot
create a hyperlink to a chart sheet. This tip provides a workaround that
http://exceltips.vitalnews.com/Pages/T002539_Hyperlinks_to_Charts.htm

l

Protecting an Entire Folder of Workbooks
If you
have a lot of Excel workbooks in a folder on your hard drive, you may wonder
if there is a way to protect them all by simply protecting the folder.
http://exceltips.vitalnews.com/Pages/T002878_Protecting_an_Entire_Folder_of_

Workbooks.html

Moving and Selecting Rows
Some
people may find it helpful to move down a row, and then select that entire
row. This would allow them to easily move one row at a time through a
http://exceltips.vitalnews.com/Pages/T002106_Moving_and_Selecting_Row

s.html

Independent Radio Buttons
Radio
buttons and check boxes can be inserted in a worksheet using the Control
toolbox. The purposes for which each is used is very different,
http://exceltips.vitalnews.com/Pages/T003076_Independent_Radio_Button

s.html

Error Opening Second Workbook
If you
try to open a second workbook and you see an error message, it could be
because of the way you are opening the workbook. This tip explains what the
http://exceltips.vitalnews.com/Pages/T002501_Error_Opening_Second_Wor

kbook.html

Editing Custom Dictionaries
Excel
provides spell-checking capabilities on the text you enter in a worksheet. It
utilizes the same dictionaries and custom dictionaries used by other
http://exceltips.vitalnews.com/Pages/T003034_Editing_Custom_Dictionar

ies.html

Sheets for Months
One
common type of workbook used in offices is one that contains a single
worksheet for each month of the year. If you need to create these types of
http://exceltips.vitalnews.com/Pages/T002017_Sheets_for_Months.html
Character Replacement in Simple Formulas
Do you
see some small rectangular boxes appearing in your formula results? It could
be because Excel is substituting that box for a character it cannot
http://exceltips.vitalnews.com/Pages/T003206_Character_Replacement_in_Simple

_Formulas.html

Showing Filter Criteria on a Printout
When you
print out a filtered worksheet, you may want some sort of printed record as
to what filtering was applied to the worksheet.
http://exceltips.vitalnews.com/Pages/T003248_Showing_Filter_Criteria_

on_a_Printout.html

Slowing Down Mouse Selection
Trying
to select a range of cells using the mouse? If such selections involve
scrolling the screen, sometimes the scrolling can occur just way too fast.
http://exceltips.vitalnews.com/Pages/T002422_Slowing_Down_Mouse_Selec

tion.html

Multiple Rows of Worksheet Tabs
If
you’ve got a lot of worksheets in your workbook, you may want to display
their tabs in to rows at the bottom of the Excel window.
http://exceltips.vitalnews.com/Pages/T003009_Multiple_Rows_of_Workshe

et_Tabs.html

Accessing Dependant and Precedent Information
The
auditing tools in Excel allow you to easily see what cells are precedents to
or dependent upon the current cell. In large, complex worksheets,
http://exceltips.vitalnews.com/Pages/T003121_Accessing_Dependant_and_Precede

nt_Information.html

Limiting Precision
There
may be times you need to limit the amount of precision Excel uses in its
calculations. Provided by ExcelTips, a free weekly productivity newsletter:
http://exceltips.vitalnews.com/Pages/T002642_Limiting_Precision.html
Seeing All Open Workbook Names
Having a
lot of workbooks open in Excel at the same time presents some unique
management challenges you won’t experience with only one or two open
workbooks
http://exceltips.vitalnews.com/Pages/T002807_Seeing_All_Open_Workbook

_Names.html

Can’t Copy Data between Workbooks
When you
open two workbooks at the same time, do you have trouble copying data from
one to the other? This problem could be related to the way you opened
http://exceltips.vitalnews.com/Pages/T003111_Cant_Copy_Data_between_Workbook

s.html

Specifying the Behavior of the Enter Key
When you
press Enter while adding information to a worksheet, Excel normally drops to
the next cell down in the column. You have complete control over what
http://exceltips.vitalnews.com/Pages/T002975_Specifying_the_Behavior_of_the_

Enter_Key.html

Editing the Same Cell in Multiple Sheets
When
creating a workbook, you may need to make changes on one worksheet and have
those edits appear on the same cells in other worksheets.
http://exceltips.vitalnews.com/Pages/T002605_Editing_the_Same_Cell_in_Multip

le_Sheets.html

Where is Toolbar Customization Information Stored?

When you
make a change to one of Excel’s toolbars, do you know where that change is
stored? This tip explains the name and characteristics of the special
http://exceltips.vitalnews.com/Pages/T002068_Where_is_Toolbar_Customization_

Information_Stored.html

Repeating Cell Contents
Do you
want to repeat the contents of a cell throughout the entire width of a cell?
There are two ways you can do it in Excel—one method formulaic and the
http://exceltips.vitalnews.com/Pages/T003214_Repeating_Cell_Contents.

html

Expiration Date for Excel Programs

If you
use Excel to create a macro-based application, you may want to make sure that
your programs cease working after a certain time.
http://exceltips.vitalnews.com/Pages/T002590_Expiration_Date_for_Exce

l_Programs.html

Flipping Data
How to
reverse the order of rows in a table. Provided by ExcelTips, a free weekly
productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002653_Flipping_Data.html
Counting Unique Values
If you
have a list of values in a range of cells, you may want to know how many
unique values appear in the list. The ways you can go about making that
http://exceltips.vitalnews.com/Pages/T002337_Counting_Unique_Values.h

tml

Leap Years and Fiscal Periods
If your
fiscal period ends in February, and you need to calculate the date either a
year in the past or the future, then you will run into the problem of
http://exceltips.vitalnews.com/Pages/T002314_Leap_Years_and_Fiscal_Pe

riods.html

Using the XIRR Function
The XIRR
function is used to determine the rate of return with irregular payments.
This tip explains how to use the function. Provided by ExcelTips, a free
http://exceltips.vitalnews.com/Pages/T002503_Using_the_XIRR_Function.

html

Converting Time Notation to Decimal Notation
Want to
convert an elapsed time, such as 8:37, to a decimal time, such as 8.62? If
you know how Excel stores times internally, coming up with the conversion
http://exceltips.vitalnews.com/Pages/T002052_Converting_Time_Notation_to_Dec

imal_Notation.html

Running a Macro When a Worksheet is Deactivated
You can
easily configure Excel so that it runs a specific macro whenever a worksheet
is deactivated. Just follow the easy steps in this tip.
http://exceltips.vitalnews.com/Pages/T002955_Running_a_Macro_When_a_Workshee

t_is_Deactivated.html

Selecting a Range of Cells Relative to the Current
Cell
Macros
often need to select cells relative to the current cell, all in an effort to
process data in a worksheet. This tip explains how you can easily change
http://exceltips.vitalnews.com/Pages/T002268_Selecting_a_Range_of_Cells_Rela

tive_to_the_Current_Cell.html

Understanding Functions
Excel
uses Functions to assist in creating spreadsheets that perform a multitude of
calculations. Provided by ExcelTips, a free weekly productivity
http://exceltips.vitalnews.com/Pages/T001953_Understanding_Functions.

html

Using Drag-and-Drop to Create a Hyperlink
If you
open workbooks in two instances of Excel, you can use drag-and-drop
techniques to create hyperlinks from one workbook to another.
http://exceltips.vitalnews.com/Pages/T002335_Using_DragandDrop_to_Create_a_H

yperlink.html

Freezing Worksheet Tabs
It is
possible for your workbooks to contain scores of worksheets. If you want to
repeatedly jump back to a specific worksheet, navigating those sheets can
http://exceltips.vitalnews.com/Pages/T003178_Freezing_Worksheet_Tabs.

html

Testing for an Empty Worksheet
If you
using a macro to print out a series of worksheets, you may want a way to
determine if a particular worksheet contains information before printing it.
http://exceltips.vitalnews.com/Pages/T003280_Testing_for_an_Empty_Wor

ksheet.html

CSV File Opens with Data in a Single Column
When you
import a CSV file into an Excel worksheet, you may be surprised at how the
program allocates the information among columns.
http://exceltips.vitalnews.com/Pages/T003002_CSV_File_Opens_with_Data_in_a_S

ingle_Column.html

Converting an Unsupported Date Format
If you
import data produced by a program other than Excel, it might include dates
stored in a format not automatically recognized by Excel.
http://exceltips.vitalnews.com/Pages/T003014_Converting_an_Unsupported_Date_

Format.html

Deleting Macros from within a Macro
Macros
are most often used to change data within a worksheet. However, macros can
also be used to make changes to macros—including deleting entire
http://exceltips.vitalnews.com/Pages/T003073_Deleting_Macros_from_within_a_M

acro.html

Seeing Excel’s Program Window
When you
change the display resolution of your screen, it can cause some problems with
a few programs. Excel is no exception; you might open the program to
http://exceltips.vitalnews.com/Pages/T002079_Seeing_Excels_Program_Wi

ndow.html

Editing PivotTables without Underlying Data
If you
ever try to edit a PivotTable and get an error that tells you that the
“underlying data was not included,” it can be frustrating.
http://exceltips.vitalnews.com/Pages/T003295_Editing_PivotTables_without_Und

erlying_Data.html

Getting Rid of Stubborn Icons
Icons
are often added to toolbars by add-in programs for Excel. If you have trouble
getting rid of such icons, there are a number of different things you
http://exceltips.vitalnews.com/Pages/T003097_Getting_Rid_of_Stubborn_

Icons.html

Changing the Pattern Used in a Data Series
When you
create a chart, Excel attempts to assign colors to your data series that it
thinks will work best for you. If you want to change those colors to
http://exceltips.vitalnews.com/Pages/T003022_Changing_the_Pattern_Used_in_a_

Data_Series.html

Iterating Circular References
Circular
references often, but not always, represent a mistake in the logic of your
workbook. If you purposefully add a circular reference,
http://exceltips.vitalnews.com/Pages/T002816_Iterating_Circular_Refer

ences.html

Getting Rid of Negative Zero Amounts
Have you
ever done some calculations, only to see a value appear that is a zero with a
negative sign in front of it? This is a result of your display
http://exceltips.vitalnews.com/Pages/T002576_Getting_Rid_of_Negative_Zero_Am

ounts.html

Elapsed Days as Years, Months and Days
Excel
allows you to work with dates and times, but sometimes it takes some real
ingenuity to get your output in just the format you want.
http://exceltips.vitalnews.com/Pages/T002184_Elapsed_Days_as_Years_Months_an

d_Days.html

Determining Business Quarters from Dates
Many
businesses organize information according to calendar quarters, especially
when it comes to fiscal information. Given a particular date, you may wonder
http://exceltips.vitalnews.com/Pages/T003339_Determining_Business_Quarters_f

rom_Dates.html

Rounding Religious Wars
How to
round values half way between two boundaries is apparently a topic of
contention in some sectors of the statistical world.
http://exceltips.vitalnews.com/Pages/T002829_Rounding_Religious_Wars.

html

Changing Excel’s Background Color
Excel
doesn’t provide a built-in means to change the standard background color of a
worksheet. There are a few different ways you can work around this
http://exceltips.vitalnews.com/Pages/T003133_Changing_Excels_Backgrou

nd_Color.html

Determining Combinations to Make a Total
Determining
how to create combinations of various elements to achieve a desired total can
be frustrating. This tip takes a look at how to accomplish such a
http://exceltips.vitalnews.com/Pages/T002408_Determining_Combinations_to_Mak

e_a_Total.html

Decimal Tab Alignment
Microsoft
Word allows you to set up all sorts of tab stops, including decimal tabs.
Excel provides no way for you to do this same type of formatting,
http://exceltips.vitalnews.com/Pages/T002765_Decimal_Tab_Alignment.ht

ml

Counting Records Matching Multiple Criteria
Counting
items in a data table that match a single criterion is easy; counting items
that match two criteria is a bit more involved.
http://exceltips.vitalnews.com/Pages/T002809_Counting_Records_Matching_Multi

ple_Criteria.html

Creating a Log/Log Chart
If you
need to create a chart that uses logarithmic values on both axes, it can be
confusing how to get what you want. This tip explains which of the chart
http://exceltips.vitalnews.com/Pages/T003025_Creating_a_LogLog_Chart.

html

Running a Macro in a Number of Workbooks
Got a
macro that you need to run in a whole bunch of workbooks? Figuring out how to
accomplish the task can give you headaches, but the techniques described
http://exceltips.vitalnews.com/Pages/T002278_Running_a_Macro_in_a_Number_of_

Workbooks.html

Retaining Formatting After a Paste Multiply
You can
use the Paste Special feature in Excel to multiple the values in a range of
cells. If you don’t want Excel to mess up the formatting of those cells,
http://exceltips.vitalnews.com/Pages/T002655_Retaining_Formatting_After_a_Pa

ste_Multiply.html

Displaying a Hidden First Column
You can
hide columns in a worksheet; this is no secret. How to display the left-most
hidden columns can be tricky, but this tip presents several ways you
http://exceltips.vitalnews.com/Pages/T002626_Displaying_a_Hidden_Firs

t_Column.html

Slow Excel Response Times
Many
people use Excel on a notebook computer, so they can do work at the office
and elsewhere. In some situations, Excel may run very slowly when not
http://exceltips.vitalnews.com/Pages/T002537_Slow_Excel_Response_Time

s.html

Understanding Date and Time Formatting Codes
Dates,
which are stored internally by Excel as numbers, can be formatted in a wide
variety of ways. There are built-in formats for dates, but you can also
http://exceltips.vitalnews.com/Pages/T002179_Understanding_Date_and_Time_For

matting_Codes.html

Formatting Subtotal Rows
Using
Data | Subtotals you can add different types of subtotals to a data table.
These subtotals are normally shown in bold, but you may want to apply other
http://exceltips.vitalnews.com/Pages/T002984_Formatting_Subtotal_Rows

.html

Adjusting Cell Margins for More White Space
There
are multiple ways that you can adjust the margins within a cell to allow more
white space. This tip examines the most common methods you can try.
http://exceltips.vitalnews.com/Pages/T002762_Adjusting_Cell_Margins_for_More

_White_Space.html

Inserting a Radical Symbol
The
radical symbol is used frequently in some branches of mathematics. If you
want to insert a radical symbol in a cell, here’s how to go about it.
http://exceltips.vitalnews.com/Pages/T002997_Inserting_a_Radical_Symb

ol.html

Automatically Closing a Workbook
Walk
away from your computer, and your work is visible on the screen for all to
see. For security purposes you may want a workbook to close automatically if
http://exceltips.vitalnews.com/Pages/T002281_Automatically_Closing_a_

Workbook.html

Shortcut for Pasting Only Values
Do you
use Paste Special to quickly convert formulas to their values for large
amounts of data? If you use this trick quite often, then information in this
http://exceltips.vitalnews.com/Pages/T002657_Shortcut_for_Pasting_Onl

y_Values.html

Understanding Phantom Macros
Knowing
how Excel sees macros and how to fix phantom macros. Provided by ExcelTips, a
free weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002011_Understanding_Phantom_Ma

cros.html

Using the Keyboard to Select and Resize a Chart
Object
When
working with charts and chart objects, Excel is very dependent on the mouse.
If you don’t want to use the mouse, but instead want to use the keyboard,
http://exceltips.vitalnews.com/Pages/T002837_Using_the_Keyboard_to_Select_an

d_Resize_a_Chart_Object.html

Recovering Macros from Corrupted Workbooks
Corruption
of a workbook can cause data loss. Sometimes it is a bigger problem to lose
macros than it is to lose data. Here’sa couple different ways you can
http://exceltips.vitalnews.com/Pages/T002399_Recovering_Macros_from_Corrupte

d_Workbooks.html

Stopping Date Parsing when Opening a CSV File
You can
double-click on a CSV (comma separated values) file in Windows, and Excel
obediently opens and loads the file. You may not get the results you
http://exceltips.vitalnews.com/Pages/T003228_Stopping_Date_Parsing_when_Open

ing_a_CSV_File.html

Sorting Data Containing Merged Cells
When
formatting the layout of your worksheet, Excel allows you to easily merge
adjacent cells together. This can cause havoc later, however, since you can’t
http://exceltips.vitalnews.com/Pages/T002581_Sorting_Data_Containing_Merged_

Cells.html

Modifying Default Year for Dates
When
entering dates into a worksheet, you may want the dates to default to last
year instead of this year. Here’sa way you can accomplish this type of data
http://exceltips.vitalnews.com/Pages/T003360_Modifying_Default_Year_f

or_Dates.html

Automatically Creating Charts for Individual Rows in
a …
Got a
huge amount of data in a data table, but you only want to select a single
record from the table to create a chart? This tip presents three ways you
http://exceltips.vitalnews.com/Pages/T002377_Automatically_Creating_Charts_f

or_Individual_Rows_in_a_Data_Table.html

Breaking Up Variable-Length Part Numbers
Part
numbers can often be long, made up of other component elements. Breaking up
part numbers into individual components is easy if each component is a
http://exceltips.vitalnews.com/Pages/T003141_Breaking_Up_VariableLength_Part

_Numbers.html

Calculating Future Workdays
Excel
makes doing math with dates quite easy. This tip starts with the simplest of
date math—adding a few days to a date. Things get more interesting when
http://exceltips.vitalnews.com/Pages/T002164_Calculating_Future_Workd

ays.html

Non-adjusting References in Formulas
Insert
and delete cells and Excel will routinely modify the formulas affected by
those edits. You may not want the references in those formulas to be
http://exceltips.vitalnews.com/Pages/T002876_Nonadjusting_References_in_Form

ulas.html

Copying Worksheets in a Macro
Do you
need to write a macro that copies worksheets from one workbook to another, or
even within the same workbook? If so, you need to become familiar with
http://exceltips.vitalnews.com/Pages/T002784_Copying_Worksheets_in_a_

Macro.html

Aborting a Macro and Retaining Control
If you
need to exit a macro before it is finished running, you can do it using a
brute force method, or you can build in a way to gracefully exit.
http://exceltips.vitalnews.com/Pages/T003021_Aborting_a_Macro_and_Retaining_

Control.html

Precision in Excel
The best
way to understand why Excel provides the answers it does is to examine how it
handles precision. This tip provides a high-level overview of
http://exceltips.vitalnews.com/Pages/T001981_Precision_in_Excel.html
Every Second Tuesday
There
are multiple ways that you can generate a list of every other Tuesday. This
tip explores several of the fastest and easiest ways to do it—all without
http://exceltips.vitalnews.com/Pages/T002428_Every_Second_Tuesday.htm

l

Entering Numbers in Excel
What
really happens when you enter information into a cell? Well, Excel tries to
make sense of what you entered—it tries to parse the information.
http://exceltips.vitalnews.com/Pages/T002090_Entering_Numbers_in_Exce

l.html

Exporting Black and White Charts
Excel’s
charts are normally created in color, but you can print them in black and
white. You may be looking for a way to export the black and white version
http://exceltips.vitalnews.com/Pages/T002204_Exporting_Black_and_Whit

e_Charts.html

Choosing Direction After Enter On a Workbook

Basis

Excel
allows you to configure what happens when you press Enter in a cell. This
change, which controls the direction in which the following cell is selected
http://exceltips.vitalnews.com/Pages/T002073_Choosing_Direction_After_Enter_

On_a_Workbook_Basis.html

Importing Multiple Files to a Single Workbook
If you
use Excel to work with data exported from another program, you might be
interested in a way to import a large number of text files into an Excel
http://exceltips.vitalnews.com/Pages/T003148_Importing_Multiple_Files_to_a_S

ingle_Workbook.html

Printing Multiple Worksheets on a Single Page
Do you
want to “condense” how Excel prints a workbook so less paper is used? This
tip describes a couple of things you can do to get the desired results.
http://exceltips.vitalnews.com/Pages/T003243_Printing_Multiple_Worksheets_on

_a_Single_Page.html

Setting Horizontal Alignment
You can
horizontally align the information in a cell in any of eight different ways.
This tip explains not only how to do the alignment,
http://exceltips.vitalnews.com/Pages/T002123_Setting_Horizontal_Align

ment.html

Zooming With the Keyboard
Zooming
in and out on a workbook allows you to magnify or reduce what you see on the
screen, without affecting the printout. This tip provides a couple of
http://exceltips.vitalnews.com/Pages/T002731_Zooming_With_the_Keyboar

d.html

Missing Equal Sign Tool
Excel
used to provide, prior to Excel 2002, an equal-sign tool near the Formula
bar. If you miss this tool, you may want to add a similar tool back to your
http://exceltips.vitalnews.com/Pages/T003146_Missing_Equal_Sign_Tool.

html

Using Data Forms
Lots of
people prefer to enter information directly into Excel, but there is another
way that may be helpful: Using data forms. This tip introduces data
http://exceltips.vitalnews.com/Pages/T002973_Using_Data_Forms.html

Creating a Chart
Excel
goes out of its way to make it very easy to chart your data. In fact, you can
create simple charts with just a couple of mouse clicks.
http://exceltips.vitalnews.com/Pages/T002839_Creating_a_Chart.html

Jumping to Text in Worksheet from an Index
Want to
jump right to a single piece of data from an index you develop on a
worksheet? It’s easy to do if you use the hyperlink method described in this
tip
http://exceltips.vitalnews.com/Pages/T003083_Jumping_to_Text_in_Worksheet_fr

om_an_Index.html

Inserting Dashes between Letters and Numbers
If you
need to add dashes between letters and numbers in a string, the work can
quickly get tedious. This tip examines some ways you can make the work
http://exceltips.vitalnews.com/Pages/T002613_Inserting_Dashes_between_Letter

s_and_Numbers.html

Default Worksheet when Opening
When
opening a workbook, you may want to make sure that a particular worksheet is
always displayed first. The only way to ensure this is through the use of
http://exceltips.vitalnews.com/Pages/T002014_Default_Worksheet_when_O

pening.html

Shifting Objects Off a Sheet
One day
you are just editing your worksheet like you normally do, then you see an
error that says “Cannot shift object off this sheet.
http://exceltips.vitalnews.com/Pages/T003151_Shifting_Objects_Off_a_S

heet.html

Automatically Copying Formatting
You can
easily copy the contents of one cell to another using a formula. There is no
way to similarly copy formatting from one cell to another.
http://exceltips.vitalnews.com/Pages/T002769_Automatically_Copying_Fo

rmatting.html

Detecting Types of Sheets in VBA
When
processing workbook information in a macro, you may need to step through each
worksheet to make some sort of changes. It is helpful to know what type
http://exceltips.vitalnews.com/Pages/T002538_Detecting_Types_of_Sheet

s_in_VBA.html

Transposing and Linking
Using
different ways to paste information with the Paste Special option. Provided
by ExcelTips, a free weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002652_Transposing_and_Linking.

html

Preserving the Undo List
The undo
list is normally wiped out when you run a macro. This makes it impossible for
a user to undo the changes that are wrought by your coding.
http://exceltips.vitalnews.com/Pages/T002060_Preserving_the_Undo_List

.html

Disappearing Toolbar Buttons for Macros
One of
the important configuration files for Excel is known as the XLB file. You
should periodically make backups of this file, in order to protect the
http://exceltips.vitalnews.com/Pages/T002368_Disappearing_Toolbar_Buttons_fo

r_Macros.html

Saving Non-Existent Changes
Do you
get frustrated when Excel asks if you want to save your changes when closing
a workbook in which you made no changes? It is helpful to understand not
http://exceltips.vitalnews.com/Pages/T002081_Saving_NonExistent_Chang

es.html

Self-Deleting Macros
Macros
eventually lose their purpose and are no longer needed. There can be any
number of reasons for them no longer being needed, such as a particular date
http://exceltips.vitalnews.com/Pages/T003123_SelfDeleting_Macros.html

Sending Single Worksheets via E-mail
Got a
single worksheet that you want to e-mail to someone, but don’t want them to
see the rest of the worksheets in the workbook?
http://exceltips.vitalnews.com/Pages/T003273_Sending_Single_Worksheets_via_E

mail.html

Adjusting Values with Formulas
You can
adjust values by using the Paste Special feature, but you may want to do it
by applying a formula. This tip describes the rationale for such an
http://exceltips.vitalnews.com/Pages/T002592_Adjusting_Values_with_Fo

rmulas.html

Setting Print Ranges for Multiple Worksheets
Setting
a print range for a worksheet is easy. What is more difficult is if you need
to set print ranges for multiple worksheets in the same workbook.
http://exceltips.vitalnews.com/Pages/T003157_Setting_Print_Ranges_for_Multip

le_Worksheets.html

Getting a Count of Unique Names
Do you
have a list of names in which there may be some duplication? You might want
to know how many unique names there are in the list.
http://exceltips.vitalnews.com/Pages/T003105_Getting_a_Count_of_Uniqu

e_Names.html

Defining Shortcut Keys for Symbols

When you
use the Insert Symbol dialog box, you can insert any character or symbol it
is possible to access in Excel. If you need to frequently insert a
http://exceltips.vitalnews.com/Pages/T003221_Defining_Shortcut_Keys_f

or_Symbols.html

Changing Section Headers
Excel
doesn’t provide a very easy way to vary headers or footers during a printout.
This tip explains how you can use a macro to do your printing and make
http://exceltips.vitalnews.com/Pages/T002821_Changing_Section_Headers

.html

Deriving Monthly Median Values
One of
the things that Excel is very good at is crunching numbers and processing
huge amounts of data. This tip gives step-by-step examples of how you can
http://exceltips.vitalnews.com/Pages/T003086_Deriving_Monthly_Median_

Values.html

Sorting by Five Columns
Excel
allows you to sort but up to three columns, but you may want to sort by more
than that. This tip provides ways you can get the desired sorted data.
http://exceltips.vitalnews.com/Pages/T002255_Sorting_by_Five_Columns.

html

Comparing Workbooks
Do you
need to compare two workbooks to each other? While you can use specialized
third-party software to do the comparisons, a simple formula or two might
http://exceltips.vitalnews.com/Pages/T002006_Comparing_Workbooks.html

Defeating Automatic Date Parsing
When
importing information into a worksheet, you might be puzzled to see that some
of the information ends up being treated as dates, even when it shouldn’t
http://exceltips.vitalnews.com/Pages/T003019_Defeating_Automatic_Date

_Parsing.html

Creating Superscript and Subscript Buttons
Want a
quick way to apply superscript and subscript to selected text within a cell?
This tip shows how the formatting can be done through a user form and a
http://exceltips.vitalnews.com/Pages/T001945_Creating_Superscript_and_Subscr

ipt_Buttons.html

Using a Numeric Portion of a Cell in a Formula
If you
have a mixture of numbers and letters in a cell, you may be looking for a way
to access and use the numeric portion of the value.
http://exceltips.vitalnews.com/Pages/T003185_Using_a_Numeric_Portion_of_a_Ce

ll_in_a_Formula.html

Sheets for Days
Need a
quick way to have a worksheet for each day in a month? Here’sa macro that
makes the worksheet creation a snap. Provided by ExcelTips, a free weekly
http://exceltips.vitalnews.com/Pages/T002836_Sheets_for_Days.html
Protecting Individual Worksheets, by User
Excel
allows you to protect worksheets, individually. Users can still see that the
worksheets are they, but they cannot display them if they don’t know the
http://exceltips.vitalnews.com/Pages/T001952_Protecting_Individual_Worksheet

s_by_User.html

Converting European Dates to US Dates
Those in
Europe use a date format that is different than those in the US; this is not
news. But what if you need to convert those European dates to US dates
http://exceltips.vitalnews.com/Pages/T003309_Converting_European_Dates_to_US

_Dates.html

Resizing a Text Box in a Macro
Text
boxes are easy to add to a document and manually resize, as needed. If you
want to resize the text box in a macro, however, the way to do it
http://exceltips.vitalnews.com/Pages/T003143_Resizing_a_Text_Box_in_a

_Macro.html

Creating Two-Line Custom Formats
Formatting
cells is easy in Excel; you can even create your own custom formats. Normally
those formats result in data that appears on a single line in a
http://exceltips.vitalnews.com/Pages/T002895_Creating_TwoLine_Custom_

Formats.html

Recording a Data Entry Time
When you
use Excel for data entry, you might want to keep track of the time when each
entry is entered. Coming up with a manual or formulaic way to
http://exceltips.vitalnews.com/Pages/T003116_Recording_a_Data_Entry_T

ime.html

Counting Cells with Text Colors
Excel
allows you to specify text colors on a character-by-character basis in your
cells, if desired. If you want to count how many cells contain text of a
http://exceltips.vitalnews.com/Pages/T002901_Counting_Cells_with_Text

_Colors.html

Adding a File Path and Filename
Do you
need to add a files’ path and name to your workbook? There are a couple of
ways you can go about adding the information, either to an existing cell
http://exceltips.vitalnews.com/Pages/T002875_Adding_a_File_Path_and_F

ilename.html

Printing Just the Visible Data
In a
large worksheet, you may want to display and print just a portion of the
available data. Displaying the desired information is easy; printing it is a
http://exceltips.vitalnews.com/Pages/T002702_Printing_Just_the_Visibl

e_Data.html

Weekdays in a Month
Excel
has no intrinsic formulas that allow you to determine how many of a
particular weekday occur within a month. You can create such a formula,
however,
http://exceltips.vitalnews.com/Pages/T002183_Weekdays_in_a_Month.html

Numbers Spelled Out
It is
not uncommon to have a need to spell out numbers, such that “123” becomes
“one hundred twenty three.” There is no intrinsic way to do this in Excel,
http://exceltips.vitalnews.com/Pages/T002270_Numbers_Spelled_Out.html

Specifying an Order for Drawing Objects
Drawing
objects can be layered over each other in almost any manner you desire. If
you want to change the order in which those drawing objects are layered,
http://exceltips.vitalnews.com/Pages/T002599_Specifying_an_Order_for_Drawing

_Objects.html

Inserting Large Numbers of Checkboxes
Easily
add any number of checkboxes to an Exel worksheet. Provided by ExcelTips, a
free weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T001964_Inserting_Large_Numbers_of_Chec

kboxes.html

Printing a Draft Watermark
Adding a
watermark to the background of a printout can be challenging. This tip
explains the different ways you can implement your own watermarking
http://exceltips.vitalnews.com/Pages/T002448_Printing_a_Draft_Waterma

rk.html

Two-Level Axis Labels
Need a
chart that uses two lines for axis labels? It’s easy to do if you know how to
set up your data in the worksheet, before creating the chart.
http://exceltips.vitalnews.com/Pages/T003203_TwoLevel_Axis_Labels.htm

l

Using AutoSave in Excel
It is a
good precaution to periodically save your work. That way, if you have an
unforeseen problem with your computer, you only lose what you were working
http://exceltips.vitalnews.com/Pages/T002777_Using_AutoSave_in_Excel.

html

Reorganizing Data
Do you
need to completely reformat the data you import into Excel? This tip shows
how you can break a single column of data into multiple columns of input.
http://exceltips.vitalnews.com/Pages/T002301_Reorganizing_Data.html
Weird Mouse Shortcut
If you
like to use the mouse in your worksheet navigation efforts, you’ll want to
pay attention to this tip. Here you discover a way you can navigate using
http://exceltips.vitalnews.com/Pages/T002453_Weird_Mouse_Shortcut.htm

l

Copying Comments when Filtering
The
advanced filtering feature in Excel allows you to quickly copy unique
information from one data list to another. If you want to copy the comments
http://exceltips.vitalnews.com/Pages/T003169_Copying_Comments_when_Fi

ltering.html

Merging Many Workbooks
Got a
whole slew of workbooks that you need to merge together? You can do it
manually, but it could take you all day. It’s much better to use a macro to
do
http://exceltips.vitalnews.com/Pages/T002409_Merging_Many_Workbooks.h

tml

Digital Signatures for Macros
The
security features built into Excel 2002 and 2003 allow you to digitally sign
your macros so that users can rest assured that they remain unchanged since
http://exceltips.vitalnews.com/Pages/T003107_Digital_Signatures_for_M

acros.html

Relative Worksheet References when Copying
When you
copy a formula that contains cell or range references, Excel adjusts the
references relative to where you are pasting the formula.
http://exceltips.vitalnews.com/Pages/T002946_Relative_Worksheet_References_w

hen_Copying.html

Toggling AutoFilter
Using
the menu sequence Data | Filter | AutoFilter, you can toggle AutoFilter on
and off for a data table. This tip explains how to create your own toolbar
http://exceltips.vitalnews.com/Pages/T002710_Toggling_AutoFilter.html

Conditional Formatting for Errant Phone Numbers
If
you’ve got a bunch of phone numbers in a worksheet, you may want to check if
they are all within certain bounds. You can use the Conditional Formatting
http://exceltips.vitalnews.com/Pages/T002978_Conditional_Formatting_for_Erra

nt_Phone_Numbers.html

Counting with Two Criteria
Want to
count the records in a data table that match two criteria? It’s not as easy
or straightforward as you might hope. This tip explores several
http://exceltips.vitalnews.com/Pages/T002815_Counting_with_Two_Criter

ia.html

Uncovering and Removing Links
Data can
be linked into a workbook from a variety of external sources. If you get a
workbook from a co-worker or friend, those links may not work any more.
http://exceltips.vitalnews.com/Pages/T001925_Uncovering_and_Removing_

Links.html

Weird Actions for Arrow Keys and Enter
Do your
arrow and Enter keys not work as you expect them to? Does using the keys
result in selecting a cell someplace else in the workbook.
http://exceltips.vitalnews.com/Pages/T002071_Weird_Actions_for_Arrow_Keys_an

d_Enter.html

Figuring Out the Low-Score Winner
Need to
figure out the lowest score in a range of scores? Here’s the formulas to get
the information you need. Provided by ExcelTips, a free weekly
http://exceltips.vitalnews.com/Pages/T002054_Figuring_Out_the_LowScor

e_Winner.html

Counting Groupings Below a Threshold
When
working with some data sets, you may need to know how many groups of values
in the data set have elements that fall below a certain threshold.
http://exceltips.vitalnews.com/Pages/T003171_Counting_Groupings_Below_a_Thre

shold.html

Weighted Averages in a PivotTable
Weighted
averages are very handy when analyzing certain types of data. If you want to
add a weighted average to a PivotTable, there are no intrinsic Excel
http://exceltips.vitalnews.com/Pages/T002900_Weighted_Averages_in_a_P

ivotTable.html

Conditional Page Breaks
When
printing a report from Excel, it is not unusual to want to start a new page
when something changes in your data. Using the Subtotals feature of Excel,
http://exceltips.vitalnews.com/Pages/T002792_Conditional_Page_Breaks.

html

Shortcut Key for Format Painter
The
Format Painter tool is very handy when you want to quickly duplicate
formatting form one place to another. The tool virtually requires the use of
the
http://exceltips.vitalnews.com/Pages/T003225_Shortcut_Key_for_Format_

Painter.html

Calculating Week-Ending Dates
When
working with dates, you may need to figure out all the dates on which weeks
end in a given year. There are several formulaic ways to accomplish this
http://exceltips.vitalnews.com/Pages/T002444_Calculating_WeekEnding_D

ates.html

Displaying the "Last Modified" Date
One of
the properties that Excel maintains for a workbook is the date it was last
modified or saved. This tip explains how you can access that date and
http://exceltips.vitalnews.com/Pages/T002285_Displaying_the_Last_Modi

fied_Date.html

Snapshots of Excel Worksheets for PowerPoint
If you
need to get lots of information from Excel to PowerPoint, the task can be
daunting. This tip explains different approaches you can take when you need
http://exceltips.vitalnews.com/Pages/T002378_Snapshots_of_Excel_Worksheets_f

or_PowerPoint.html

A Ruler in Excel
A few
workarounds for the fact that Excel does not have a built-in ruler. Provided
by ExcelTips, a free weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002484_A_Ruler_in_Excel.html

Copying Formulas using a Pattern
Copying
formulas from one cell to another is quite intuitive in Excel—unless you want
the copied formulas to follow some sort of pattern.
http://exceltips.vitalnews.com/Pages/T003067_Copying_Formulas_using_a

_Pattern.html

Shading Based on Odds and Evens
Given a
series of values in a range of cells, you may want to shade the cells based
on whether the values are odd or even. Using Conditional Formatting you
http://exceltips.vitalnews.com/Pages/T002767_Shading_Based_on_Odds_an

d_Evens.html

Making an Image Color Transparent
Excel
allows you to put all types of graphics in your workbooks. If you are using
bitmapped graphics (a very common type), you can instruct Excel to treat a
http://exceltips.vitalnews.com/Pages/T002200_Making_an_Image_Color_Tr

ansparent.html

Handling Leading Zeros in CSV Files
When
dealing with files containing comma-separated values, you want to make sure
that what gets imported into Excel reflects what is really in the file.
http://exceltips.vitalnews.com/Pages/T002588_Handling_Leading_Zeros_in_CSV_F

iles.html

Modifying Axis Scale Labels
Create a
chart in Excel, and the program decides how to scale the chart for best
appearance. If you want to override what Excel decides, you can adjust the
http://exceltips.vitalnews.com/Pages/T003180_Modifying_Axis_Scale_Lab

els.html

Moving Part of a Footer Down a Line
When
creating a footer for a printout, it is possible for parts of the footer to
overwrite other parts. This tip explains how you can use a two-line footer
http://exceltips.vitalnews.com/Pages/T002961_Moving_Part_of_a_Footer_Down_a_

Line.html

Faster Text File Conversions
When
importing a text file into Excel, you have the opportunity to define the
characteristics of the data being imported. If the data has quite a few
fields
http://exceltips.vitalnews.com/Pages/T002235_Faster_Text_File_Convers

ions.html

Using Excel for Timing
Excel
allows you to store times in a worksheet. If you want to use Excel to time
certain events, there are a couple of ways you can approach the task.
http://exceltips.vitalnews.com/Pages/T002037_Using_Excel_for_Timing.h

tml

Developing Reciprocal Conversion Formulas
Would
you like to develop a worksheet that has two cells that will convert whatever
is entered in either of the cells? For instance, you might put a
http://exceltips.vitalnews.com/Pages/T003277_Developing_Reciprocal_Conversio

n_Formulas.html

Counting Displayed Cells
When you
filter data, Excel displays only a portion of what is really in a worksheet.
If you want to count the number of cells that are displayed after
http://exceltips.vitalnews.com/Pages/T001955_Counting_Displayed_Cells

.html

Workbook, Once Created, is Too Big for Memory
Understanding
how Excel uses memory, how much it allows, and how to work around the
limitations. Provided by ExcelTips, a free weekly productivity
http://exceltips.vitalnews.com/Pages/T002545_Workbook_Once_Created_is_Too_Bi

g_for_Memory.html

Conditional Formatting
A few
ways to use conditional formatting on cells in your Excel worksheet. Provided
by ExcelTips, a free weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002665_Conditional_Formatting.h

tml

Shading Rows for Ease in Reading Output
Shading
every second, third, or fifth row of a printout can be helpful for reading
data. This tip describes how to use a macro to get the formatting you
http://exceltips.vitalnews.com/Pages/T002130_Shading_Rows_for_Ease_in_Readin

g_Output.html

Avoiding Scientific Notation on File Imports
Excel
can import some types of files automatically, and other types require some
intervention on your part. If you are not getting the results you want
http://exceltips.vitalnews.com/Pages/T002426_Avoiding_Scientific_Notation_on

_File_Imports.html

Unhiding or Listing All Objects
In
complex workbooks with lots of objects, it is easy to “lose” an object so
that you cannot select it. The macro techniques used in this tip allow you to
http://exceltips.vitalnews.com/Pages/T002025_Unhiding_or_Listing_All_

Objects.html

Changing Elements in Lots of Charts at One Time
If
you’ve got a workbook that has dozens of charts in it, you know that it can
be a real pain to select each chart and make a common change (such as to a
http://exceltips.vitalnews.com/Pages/T003125_Changing_Elements_in_Lots_of_Ch

arts_at_One_Time.html

Preventing Changes to Formatting and Page Size
When you
create workbooks for others to use, you might want to make sure that they
can’t change the formatting and paper size you’ve specified.
http://exceltips.vitalnews.com/Pages/T002559_Preventing_Changes_to_Formattin

g_and_Page_Size.html

Using List Box Controls
One of
the form controls you can place in a worksheet is a list box. Once you know
how to populate a list box and use it, this control can make a great
http://exceltips.vitalnews.com/Pages/T002918_Using_List_Box_Controls.

html

Using
WordArt in Excel
The
WordArt program has been available in Office for a long, long time. It allows
you to (dare I say it) create art from words.
http://exceltips.vitalnews.com/T003408 
Tying
a Hyperlink to a Specific Cell
Make a
hyperlink to a cell in your workbook, edit the structure of that workbook a
bit, and you may find that the hyperlink no longer targets the original
http://exceltips.vitalnews.com/T003466 
Modifying
Proper Capitalization
The
PROPER worksheet function is used to change the case of text so that the
first letter of each word is capitalized. If there are some words you don’t
http://exceltips.vitalnews.com/T003467 
Converting
UTC Times to Local Times
Dates
and times are often standardized on UTC time, which is analogous to GMT
times. How to convert such times to your local time is a relatively simple
http://exceltips.vitalnews.com/T003440 
Generating
Double-Digit Random Numbers
Normally
you use the RAND and RANDBETWEEN functions to generate random numbers. What
if you want to generate random numbers within a certain range but
http://exceltips.vitalnews.com/T003403 
Applying
Conditional Formatting to Multiple Worksheets
If you
just switched to Excel 2007, you may have noticed some differences in how the
program handles applying conditional formatting to multiple worksheets.
http://exceltips.vitalnews.com/T003433 
Excel
Proficiency Testing
Need to
figure out how good someone is with Excel? You need what is commonly called a
“proficiency test.” Here’s the low-down on what is available and what
http://exceltips.vitalnews.com/T003426 
Calculating
the Median Age of a Group of People
Suppose
you have a worksheet that contains a list of ages and then a count of people
who correspond with those ages. You may wonder how you can figure out
http://exceltips.vitalnews.com/T003397 
Determining
Mouse Cursor Coordinates On a Graphic
Add a
graphic to a worksheet as part of an Image object, and you can use some very
handy event handlers to figure out the coordinates of the mouse pointer.
http://exceltips.vitalnews.com/T003421 
Microsoft
Excel Tips
Welcome
to ExcelTips! Microsoft Excel is a powerful program, and you can make it even
more powerful by learning the Excel tips, Excel tricks,
http://exceltips.vitalnews.com/ 
Limiting Entry of Prior Dates
You can
use the data validation feature of Excel to limit what is entered in a cell,
including the way that dates are entered. This tip provides an advanced
http://exceltips.vitalnews.com/Pages/T002960_Limiting_Entry_of_Prior_

Dates.html

List of Macro Shortcuts in All Open Workbooks
Need a
list of macro shortcut keys? It’s not as easy in Excel as in some other
Office applications, such as Word. It can be done, however, with a little
http://exceltips.vitalnews.com/Pages/T003162_List_of_Macro_Shortcuts_in_All_

Open_Workbooks.html

Removing Duplicates Based on a Partial Match
If you
have a set of addresses in a worksheet, you may have some addresses that
partially match other ones. For instance, a set of addresses where the only
http://exceltips.vitalnews.com/Pages/T002782_Removing_Duplicates_Based_on_a_

Partial_Match.html

The Last Business Day
Many
businesses need to know when the last business day of the month occurs. This
tip discusses the many ways you can calculate this date in Excel.
http://exceltips.vitalnews.com/Pages/T002452_The_Last_Business_Day.ht

ml

Conditional Printing
If you
need to make what Excel prints be based upon conditions in a worksheet,
you’ll love the information in this tip. There are a variety of ways you can
http://exceltips.vitalnews.com/Pages/T002372_Conditional_Printing.htm

l

Library Not Registered Error
bottom.
Great ExcelTips! Your e-mail address is safe! Close Note. Search for answers:
Submit search form ExcelTips Site
http://exceltips.vitalnews.com/Pages/T002952_Library_Not_Registered_E

rror.html

Changing Font Face and Size Conditionally
Conditional
formatting does not allow you to change the typeface and font size used in a
cell. You can write your own macro to do the formatting change,
http://exceltips.vitalnews.com/Pages/T002380_Changing_Font_Face_and_Size_Con

ditionally.html

Saving Common Formulas
If
you’ve got a formula you like to use over and over again, you can assign the
formula a name, just like you can with cell ranges.
http://exceltips.vitalnews.com/Pages/T002957_Saving_Common_Formulas.h

tml

Automatically Converting to GMT
Greenwich
Meridian Time, or GMT, is a common reference time used around the world. If
you want to convert a time to GMT, this tip provides the information
http://exceltips.vitalnews.com/Pages/T002185_Automatically_Converting

_to_GMT.html

Using a Single Password for Multiple Workbooks
If you
have a group of password-protected workbooks linked through a common
worksheet, you may get frustrated by the need to continually enter the
password
http://exceltips.vitalnews.com/Pages/T002886_Using_a_Single_Password_for_Mul

tiple_Workbooks.html

Exporting Latitude and Longitude
Latitude
and longitude are expressed in hours, minutes, and seconds, which means that
Excel can store them as time values. This may cause problems when you
http://exceltips.vitalnews.com/Pages/T002770_Exporting_Latitude_and_L

ongitude.html

Stopping Fractions from Reducing
Excel
can display fractions just as easily as it can display other types of
formatting. When you enter a fraction Excel translates it into a “reduced”
http://exceltips.vitalnews.com/Pages/T002760_Stopping_Fractions_from_

Reducing.html

Finding Unknown Links
There
are several things to try when finding unknown links in Excel. Provided by
ExcelTips, a free weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002141_Finding_Unknown_Links.ht

ml

Finding Differences Between Lists
If you
have two data lists that are related to each other, you can use the
techniques in this tip. For instance, you may want to compile a list of the
http://exceltips.vitalnews.com/Pages/T002053_Finding_Differences_Betw

een_Lists.html

Looking Up Names when Key Values are Identical
Do you
have a list in which there are duplicates in the key field, and you want to
look up non-duplicate values associated with those keys?
http://exceltips.vitalnews.com/Pages/T003077_Looking_Up_Names_when_Key_Value

s_are_Identical.html

Numeric Value and Percentage Value in a Graph

Column

Do you
want to include two data labels with your chart? Exactly how you can achieve
this feat isn’t intuitive in Excel, but once you learn the trick you’ll
http://exceltips.vitalnews.com/Pages/T002411_Numeric_Value_and_Percentage_Va

lue_in_a_Graph_Column.html

Excluding a Specific Add-In at Startup
Got an
add-in that you don’t want loaded during start-up? Here’sa few ways that you
can exclude it. Provided by ExcelTips, a free weekly productivity
http://exceltips.vitalnews.com/Pages/T002383_Excluding_a_Specific_AddIn_at_S

tartup.html

Copying Headers and Footers
Got a
set of headers and footers in one worksheet that you want copied to another
worksheet? This tip explains the various approaches you can use to perform
http://exceltips.vitalnews.com/Pages/T002696_Copying_Headers_and_Foot

ers.html

Counting Dates in a Range
Given a
table of dates in a worksheet, you may want to determine how many of those
dates are within a certain date range. This tip explains the formulas you
http://exceltips.vitalnews.com/Pages/T002818_Counting_Dates_in_a_Rang

e.html

Losing Data in a Shared Workbook
Shared
workbooks are, unfortunately, prone to data corruption or loss. This tip
examines one such data loss problem and proposes various solutions for the
http://exceltips.vitalnews.com/Pages/T002998_Losing_Data_in_a_Shared_

Workbook.html

Referring to the Last Cell
If you
have lots of data in a table, and the length of that table can vary, how do
you reference the last cells in each column of the table?
http://exceltips.vitalnews.com/Pages/T002170_Referring_to_the_Last_Ce

ll.html

Splitting Information into Rows
If you
have data stored in multiple lines in a single cell, you might want to expand
that data so that it actually appears on multiple rows.
http://exceltips.vitalnews.com/Pages/T003263_Splitting_Information_in

to_Rows.html

Engineering Calculations
In an
engineering or scientific environment, it is not unusual to need data.
http://exceltips.vitalnews.com/Pages/T002928_Engineering_Calculations

.html

Large Numbers in the MOD Function
There is
a known bug in the MOD function that stops it from working with large numbers
and small divisors. This tip examines the bug and discusses ways to
http://exceltips.vitalnews.com/Pages/T003302_Large_Numbers_in_the_MOD_Functi

on.html

Counting All Characters
If you
need to count the number of text characters in a worksheet, the macro in this
tip can come in very handy. It looks at not only the text in cells,
http://exceltips.vitalnews.com/Pages/T002284_Counting_All_Characters.

html

Getting Rid of the Office Assistant
How to
rid yourself of the Office Assistant. Provided by ExcelTips, a free weekly
productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T003193_Getting_Rid_of_the_Offic

e_Assistant.html

Drawing Simple Objects
Want to
draw a few simple shapes or lines on your worksheet? Here’s how to do it.
Provided by ExcelTips, a free weekly productivity newsletter: www.
http://exceltips.vitalnews.com/Pages/T002520_Drawing_Simple_Objects.h

tml

Entering Dates Without Separators
When you
enter dates into a cell, Excel pays attention to the separators you use
between the month, day, and year. While entering dates could be done a lot
http://exceltips.vitalnews.com/Pages/T002039_Entering_Dates_Without_S

eparators.html

Using Graphics to Represent Data Series
You can
spice up your bar chart by using a graphic, of your choosing, to construct
the bars. This tip shows how easy it is to pick and use your graphic.
http://exceltips.vitalnews.com/Pages/T003199_Using_Graphics_to_Represent_Dat

a_Series.html

Importing Huge Data Files
Sometimes,
when importing data created by other programs, you may find that there is too
much for Excel to handle. Here’s how you can still accomplish the
http://exceltips.vitalnews.com/Pages/T002533_Importing_Huge_Data_File

s.html

Changing Fonts in Multiple Workbooks
If you
need to change fonts used in a lot of different workbooks, the task can be
daunting—if you need to do it manually. This tip looks at a macro you can
http://exceltips.vitalnews.com/Pages/T002526_Changing_Fonts_in_Multiple_Work

books.html

Displaying Images based on a Result
Got some
images that you want to appear in a worksheet based on the result displayed
in a cell? Figuring out how to “conditionally display” an image can be
http://exceltips.vitalnews.com/Pages/T003128_Displaying_Images_based_on_a_Re

sult.html

Positive and Negative Colors in a Chart
When
creating a line cart, the line can show values both positive and negative
values. This tip explains how you can use different colors to display that
http://exceltips.vitalnews.com/Pages/T001999_Positive_and_Negative_Colors_in

_a_Chart.html

Deleting Blank Columns
Import
data from another program, and you could end up with a lot of blank columns
in your data. Here’s the quickest way to get rid of these empty columns.
http://exceltips.vitalnews.com/Pages/T002660_Deleting_Blank_Columns.h

tml

Is Daylight Savings Time in Effect?

Daylight
savings time is supported by modern computers, but getting the information
into Excel as to whether daylight savings time is in effect can be a
http://exceltips.vitalnews.com/Pages/T003318_Is_Daylight_Savings_Time

_in_Effect.html

Exploded Pie Chart Sections
When you
create a pie chart in Excel, you can create it with all the slices together
or all the slices spread apart (“exploded”).
http://exceltips.vitalnews.com/Pages/T002825_Exploded_Pie_Chart_Secti

ons.html

Making All Occurrences Bold
Do you
need to change the formatting of all occurrences of a word in a worksheet?
How you approach the task depends on two factors: the version of Excel you
http://exceltips.vitalnews.com/Pages/T002414_Making_All_Occurrences_B

old.html

Locking Callouts to a Graph Location
If you
add callouts using the drawing tools in Excel, you may have noticed that they
don’t always stay where you expect them to stay.
http://exceltips.vitalnews.com/Pages/T003007_Locking_Callouts_to_a_Graph_Loc

ation.html

Working with Elapsed Time
Since
Excel stores times as numeric values, you can perform arithmetic operations
on those values. This allows you to work with elapsed time,
http://exceltips.vitalnews.com/Pages/T002819_Working_with_Elapsed_Tim

e.html

Deleting Worksheets in a Macro
Do you
need to delete a worksheet under the control of a macro you are creating?
You’ll love this tip, then. Provided by ExcelTips, a free weekly
http://exceltips.vitalnews.com/Pages/T002293_Deleting_Worksheets_in_a

_Macro.html

Separating Names into Individual Columns
You can
use the Text to Columns wizard to separate names into different cells. The
success you have with this, however, depends on the condition of the
http://exceltips.vitalnews.com/Pages/T002789_Separating_Names_into_Individua

l_Columns.html

AutoFilling with the Alphabet
Creating
your own custom word search puzzles in Excel can present you with an
interesting problem: how to fill a range of empty cells with letters of the
http://exceltips.vitalnews.com/Pages/T003109_AutoFilling_with_the_Alp

habet.html

Wildcards in ‘Replace With’ Text
When
doing searches in Excel, you can use wildcard characters in the specification
of what you are searching. However, you cannot use them in the
http://exceltips.vitalnews.com/Pages/T003303_Wildcards_in_Replace_Wit

h_Text.html

Easily Deploying Customizations
When you
create a whole set of customizations for Excel, you may want to share them
with others in your office or workgroup. This tip provides some guidance
http://exceltips.vitalnews.com/Pages/T002083_Easily_Deploying_Customi

zations.html

Determining Sorting Criteria
Excel
makes it easy to sort data, but once it is sorted, then determining how the
sort was done is not nearly as easy. There are a couple of approaches
http://exceltips.vitalnews.com/Pages/T002395_Determining_Sorting_Crit

eria.html

Disabling Moving Between Worksheets
If you
want someone to not be able to move from one worksheet to another in a
workbook, you’ve got your work cut out for you. Here are some things you can
exceltips.vitalnews.com/Pages/T003333_Disabling_Moving_Between_Worksheets.html
Displaying Messages When Automatic Data Changes
Worksheets
can be set up so that they are automatically updated with data pulled from a
variety of sources. As data changes in this manner, you may want to …
http://exceltips.vitalnews.com/Pages/T002906_Displaying_Messages_When_Automa

tic_Data_Changes.html

Unprotecting Groups of Worksheets
Protecting
worksheets is a handy way to make your work safe from other users. If your
workbook contains quite a few worksheets, unprotecting them
http://exceltips.vitalnews.com/Pages/T002275_Unprotecting_Groups_of_W

orksheets.html

Multiple Print Areas on a Single Printed Page
You can
select non-contiguous ranges of cells and set them as the print area for a
worksheet. Getting those ranges on a single sheet of printout, however,
http://exceltips.vitalnews.com/Pages/T002779_Multiple_Print_Areas_on_a_Singl

e_Printed_Page.html

Unique Military Date Format
The
military, for some purposes, uses a variation of a Julian date that is not
directly supported by Excel. This tip explains various formulas you can use
http://exceltips.vitalnews.com/Pages/T003227_Unique_Military_Date_For

mat.html

Converting Forced Text to Numbers
If you
have some numbers stored in cells that are formatted as text, you may get
some surprises when you try to use those cells in some of your formulas.
http://exceltips.vitalnews.com/Pages/T002670_Converting_Forced_Text_t

o_Numbers.html

Condensing Multiple Worksheets Into One
Excel
provides a consolidation tool that allows you to easily combine the data from
a bunch of worksheets into a single worksheet. This tip explains how you
http://exceltips.vitalnews.com/Pages/T003005_Condensing_Multiple_Worksheets_

Into_One.html

Macro Slows Down on More Powerful Machine
Macros
can run differently on different machines. This fact can be painfully obvious
if a macro runs slower on a machine that you thought was faster.
http://exceltips.vitalnews.com/Pages/T003072_Macro_Slows_Down_on_More_Powerf

ul_Machine.html

Full Path Names in Headers or Footers
Using a
macro to add the full path name into a header or footer in an Excel
worksheet. Provided by ExcelTips, a free weekly productivity newsletter: www.
http://exceltips.vitalnews.com/Pages/T002639_Full_Path_Names_in_Headers_or_F

ooters.html

Calculating Statistical Values on Different-Sized
Subsets …
Analyzing
huge amounts of data presents challenges that are different from those faced
with smaller amounts of data. This tip examines how you can analyze
http://exceltips.vitalnews.com/Pages/T002771_Calculating_Statistical_Values_

on_DifferentSized_Subsets_of_Data.html

Hiding Columns Not within a Date Range
Many
people use Excel to track lots and lots of data. Hiding some of that data
based upon some criteria, such as whether the data is outside of a date range
http://exceltips.vitalnews.com/Pages/T003261_Hiding_Columns_Not_within_a_Dat

e_Range.html

Official Color Names in VBA
When
creating macros, you can use the ColorIndex and Color properties to set or
determine the colors in a cell. Excel provides eight constants that can be
http://exceltips.vitalnews.com/Pages/T002778_Official_Color_Names_in_

VBA.html

Switching Headers in a Frozen Row
Excel
allows you to “freeze” rows in your worksheet. What if you want the rows that
are frozen to change as you scroll through the worksheet?
http://exceltips.vitalnews.com/Pages/T003446_Switching_Headers_in_a_F

rozen_Row.html

Conditional Formatting with Data Imported from
Access
If the
information you import from Access into Excel isn’t interpreted just right,
it can have consequences on your conditional formatting.
http://exceltips.vitalnews.com/Pages/T002418_Conditional_Formatting_with_Dat

a_Imported_from_Access.html

Calculating Only the Active Workbook
When you
have automatic calculation turned on, Excel treats all your open workbooks
the same and recalculates them all. You may not want this to occur;
http://exceltips.vitalnews.com/Pages/T002877_Calculating_Only_the_Active_Wor

kbook.html

Printing to a Disk File
It never
fails—there is one person in every office who has the best printer in the
world, while you get the hand-me-downs. You can still take advantage of
http://exceltips.vitalnews.com/Pages/T003240_Printing_to_a_Disk_File.

html

Counting Employees in Classes
Excel is
very good at counting things, even when those things need to meet specific
criteria. This tip shows how you can do a comparison to individual
http://exceltips.vitalnews.com/Pages/T002552_Counting_Employees_in_Cl

asses.html

Checking for Messages in Cells
If you
have a large range of cells that is used to display verification messages for
a worksheet, you know that looking through the range to check for
http://exceltips.vitalnews.com/Pages/T002772_Checking_for_Messages_in

_Cells.html

Displaying Letter Grades
Excel is
great at working with numbers, but it can be confusing to try to convert some
numbers (such as numeric grades) into letters (such as letter grades)
http://exceltips.vitalnews.com/Pages/T002611_Displaying_Letter_Grades

.html

Counting Cells According to Case
Text
placed in cells can either be lowercase, uppercase, or a mixture of the two.
If you want to count the cells based upon the case of the text within
http://exceltips.vitalnews.com/Pages/T003212_Counting_Cells_According

_to_Case.html

Floating Information in a Frozen Row
You can
freeze information in rows or columns using one of the built-in features of
Excel. As you move up or down in the worksheet, the information in the
http://exceltips.vitalnews.com/Pages/T003140_Floating_Information_in_a_Froze

n_Row.html

Retrieving Web Query Data without Interruption
If you
use Excel’s Web Query tools to grab data from a website, you may run into
some problems if the site isn’t available right away.
http://exceltips.vitalnews.com/Pages/T003233_Retrieving_Web_Query_Data_witho

ut_Interruption.html

Rounding Religious Wars, Take Two
How to
round values half way between two boundaries is apparently a topic of
contention in some sectors of the statistical world.
http://exceltips.vitalnews.com/Pages/T002835_Rounding_Religious_Wars_

Take_Two.html

Displaying Latitude and Longitude
If you
work with geographic data, you may need a way to display latitude and
longitude in a worksheet. This tip examines several different approaches you
an
http://exceltips.vitalnews.com/Pages/T003016_Displaying_Latitude_and_

Longitude.html

Deleting All Names but a Few
Excel
provides an easy way for you to delete one defined name at a time. If you
want to delete most of the names in a workbook, but leave a few still intact
http://exceltips.vitalnews.com/Pages/T002419_Deleting_All_Names_but_a

_Few.html

Conditional Formats that Distinguish Blanks and
Zeroes
Conditional
formatting is a great boon to Excel users. One use is that you can use it to
highlight cells that contain zero values.
http://exceltips.vitalnews.com/Pages/T002980_Conditional_Formats_that_Distin

guish_Blanks_and_Zeroes.html

Hiding and Protecting Columns
Excel
allows you to protect, to a degree, the contents of various cells in your
worksheets. If you want to protect an entire column, there are specific
http://exceltips.vitalnews.com/Pages/T002781_Hiding_and_Protecting_Co

lumns.html

Comparing Lists for Duplicates
Do you
have two similar data lists that you need to compare to see if there are
duplicates in the lists? This can be a daunting task, but you can add some
http://exceltips.vitalnews.com/Pages/T002251_Comparing_Lists_for_Dupl

icates.html

Adjusting the VLOOKUP Function
The
VLOOKUP function returns either an exact match with a lookup value or, if one
is not available, the first value that is less than the lookup value.
http://exceltips.vitalnews.com/Pages/T003090_Adjusting_the_VLOOKUP_Fu

nction.html

Maintaining Accuracy of Significant Digits
If you
work in the sciences or mathematics, you know that significant digits are
important. This tip answers questions about whether you can force Excel to
http://exceltips.vitalnews.com/Pages/T002045_Maintaining_Accuracy_of_Signifi

cant_Digits.html

Deleting Duplicate Text Values
Got a
long list from which you need to remove the duplicates? There are a couple of
ways you can perform this common task. This tip explains two ways to do
http://exceltips.vitalnews.com/Pages/T002430_Deleting_Duplicate_Text_

Values.html

Labeling X-Y Scatter Plots
Figuring
out how to get the data points in an XY scatter plot labeled can be
confusing; Excel certainly doesn’t make it easy. Here’s some ideas on how to
http://exceltips.vitalnews.com/Pages/T003503_Labeling_XY_Scatter_Plot

s.html

Ignoring N/A Values in a Sum
You can
use some of Excel’s worksheet functions across a range or worksheets, but not
all of them. One that has problems with multi-sheet use is SUMIF,
http://exceltips.vitalnews.com/Pages/T003156_Ignoring_NA_Values_in_a_

Sum.html

Magnifying Only the Current Cell
You can
use the Zoom feature of Excel to magnify what Excel shows of your workbook,
but it affects the entire screen. What if you want to only magnify a
http://exceltips.vitalnews.com/Pages/T003114_Magnifying_Only_the_Curr

ent_Cell.html

Pulling Formulas from a Worksheet
The
formulas in your worksheet can be displayed (instead of formula results) by a
simple configuration change. You can then easily make a copy of those
http://exceltips.vitalnews.com/Pages/T003190_Pulling_Formulas_from_a_

Worksheet.html

Getting Rid of Spaces in Cells
Getting
rid of stray spaces that clutter up otherwise empty cells can be a bother.
This tip explains how you can use a couple of different techniques to
http://exceltips.vitalnews.com/Pages/T002883_Getting_Rid_of_Spaces_in

_Cells.html

Changing Your Company Name
The
installation process for Office and Excel allows you to specify a company
name. If your company name later changes, you may be faced with the task of
http://exceltips.vitalnews.com/Pages/T002908_Changing_Your_Company_Na

me.html

Creating Dependent Drop-Lists
Drop-down
lists are handy in an Excel worksheet, and you they can be even more handy if
a selection in one drop-down lists controls the options presented in
http://exceltips.vitalnews.com/Pages/T002972_Creating_Dependent_DropL

ists.html

AutoFill with Random Numbers
AutoFilling
is a great tool in Excel; it allows you to place a wide range of values into
cells. If you want to use it to add random numbers to a worksheet,
http://exceltips.vitalnews.com/Pages/T002964_AutoFill_with_Random_Num

bers.html

Default Headers and Footers
Changing
the look of default headers and footers for your Excel workbooks. Provided by
ExcelTips, a free weekly productivity newsletter: http://www.VitalNews.com.
http://exceltips.vitalnews.com/Pages/T002635_Default_Headers_and_Foot

ers.html

Easily Changing Chart Data Ranges
Many
times you use the same chart format to graph a subset of information from a
full data table. This tip presents a detailed tutorial on how to make your
http://exceltips.vitalnews.com/Pages/T002376_Easily_Changing_Chart_Da

ta_Ranges.html

Summing Every Fourth Cell in a Row

Excel
provides several powerful functions to calculate a sum of values, but how you
put those functions to use in non-standard ways can be perplexing.
http://exceltips.vitalnews.com/Pages/T003234_Summing_Every_Fourth_Cell_in_a_

Row.html

Understanding Relative and Absolute Addressing
At the
heart of any formula in Excel is the ability to reference other cells. Excel
supports two types of address: relative and absolute.
http://exceltips.vitalnews.com/Pages/T002062_Understanding_Relative_and_Abso

lute_Addressing.html

Returning a Blank Value
Some
people want a formula to return a blank value based upon a calculation or
comparison. Such a condition is impossible in Excel—formulas can never return
http://exceltips.vitalnews.com/Pages/T002814_Returning_a_Blank_Value.

html

Pasting Without Updating References
Do you
need to paste formulas without updating the references in whatever you are
pasting? You can accomplish this, depending on what you are pasting and
http://exceltips.vitalnews.com/Pages/T002033_Pasting_Without_Updating_Refere

nces.html

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: