ExcelTip.com Links
| Search Results |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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: 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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: 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 | |||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| Adding the spreadsheet name into a custom header or footer. Provided by ExcelTips, a free weekly productivity newsletter: 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 | |||
| 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 |
|||
| 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: 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: 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 |
|||
| 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: 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| Turning off the scroll bars through the options menu. Provided by ExcelTips, a free weekly productivity newsletter: 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 |
|||
| Troubleshooting Excel when a shortcut no longer works on just one computer. Provided by ExcelTips, a free weekly productivity newsletter: 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: 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 |
|||
| 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 | |||
| 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: www.VitalNews.com. |
|||
| http://exceltips.vitalnews.com/Pages/T001941_Working_with_Fonts.html | |||
| 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 |
|||
| 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: 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: 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 |
|||
| 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 |
|||
| 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: 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: 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: www.VitalNews.com. |
|||
| http://exceltips.vitalnews.com/Pages/T001921_Understanding_Operators.
html |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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: www.VitalNews.com. |
|||
| http://exceltips.vitalnews.com/Pages/T002842_Setting_Page_Margins.htm
l |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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: 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: www.VitalNews.com. |
|||
| http://exceltips.vitalnews.com/Pages/T002007_Spell_Checking_Your_Work
sheet.html |
|||
| 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 |
|||
| Turning off the file converter to protect your Excel files. Provided by ExcelTips, a free weekly productivity newsletter: 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 |
|||
| 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: 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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: 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 |
|||
| 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 |
|||
| 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: 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: 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 | |||
| 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: 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 |
|||
| 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 |
|||
| An explanation of If … End If structure of a VBA macro in Excel. Provided by ExcelTips, a free weekly productivity newsletter: 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 |
|||
| 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 |
|||
| 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 |
|||
| 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: 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: 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 |
|||
| 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 |
|||
| 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 |
|||
| Get a custom view of a spreadsheet with Excel’s pane function. Provided by ExcelTips, a free weekly productivity newsletter: 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: 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: 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 | |||
| A few ways to show a hidden first column in a worksheet. Provided by ExcelTips, a free weekly productivity newsletter: 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 | |||
| 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 |
|||
| 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 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: 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 |
|||
| Tired of closing a new spreadsheet every time Excel is opened? Provided by ExcelTips, a free weekly productivity newsletter: 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 |
|||
| 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 | |||
| 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 |
|||
| 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 |
|||
| 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 | |||
| 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: 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: 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 |
|||
| 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: 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 | |||
| 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 |
|||
| 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 |
|||
| 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 | |||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 | |||
| 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 | |||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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: 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 |
|||
| 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 |
|||
| 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: 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 |
|||
| 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 | |||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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: www.VitalNews.com. |
|||
| http://exceltips.vitalnews.com/Pages/T001958_Finding_and_Deleting_Lin
ks.html |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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: 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 | |||
| 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 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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: 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 |
|||
| Using the DATE function to convert a calendar system. Provided by ExcelTips, a free weekly productivity newsletter: www.VitalNews.com. |
|||
| http://exceltips.vitalnews.com/Pages/T002049_Converting_Between_Buddhist_and
_Gregorian_Calendar_Systems.html |
|||
| 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 |
|||
| 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: 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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: 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 | |||
| 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 |
|||
| 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 |
|||
| 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 | |||
| 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 |
|||
| 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 | |||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 | |||
| 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 |
|||
| 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 |
|||
| 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: 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 |
|||
| 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 |
|||
| 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 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 | |||
| Using a few categories of functions in Excel to count unique values. Provided by ExcelTips, a free weekly productivity newsletter: 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: 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 |
|||
| 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 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 | |||
| 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 |
|||
| 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 |
|||
| 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 |
|||
| 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 | |||
| 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 … |
|||