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 … |
|||
| http://exceltips.vitalnews.com/Pages/T002874_Picking_Different_Random_Number
s_from_a_Range.html |
|||
| If you have a chart that is based on data in your worksheet, you might want the chart to update automatically as you add new information. |
|||
| http://exceltips.vitalnews.com/Pages/T002933_Automatically_Updating_Charts_f
or_Additional_Data.html |
|||
| Dealing With the Euro | |||
| How to add the Euro symbol to your Excel worksheets. Provided by ExcelTips, a free weekly productivity newsletter: www.VitalNews.com. |
|||
| http://exceltips.vitalnews.com/Pages/T001929_Dealing_With_the_Euro.ht
ml |
|||
| Using SUM In a Macro | |||
| If you need to use the SUM worksheet function from within a macro, you can easily do it by using the techniques discussed in this tip. |
|||
| http://exceltips.vitalnews.com/Pages/T003217_Using_SUM_In_a_Macro.htm
l |
|||
| When compiling statistics on a collection of data points, you may want to know whether a particular value is the “highest since” or “lowest since” when … |
|||
| http://exceltips.vitalnews.com/Pages/T003138_Determining_Highest_Since_or_Lo
west_Since.html |
|||
| If you manually select an entire column, and there are some cells in that column that are merged with cells in an adjacent column, Excel sort of “skips” the … |
|||
| http://exceltips.vitalnews.com/Pages/T003093_Selecting_Columns_in_VBA_when_C
ells_are_Merged.html |
|||
| Saving Changes in Personal.xls | |||
| When you exit Excel and you’ve made changes to a workbook, the program asks if you want to save those changes. It can be frustrating if the request happens … |
|||
| http://exceltips.vitalnews.com/Pages/T003092_Saving_Changes_in_Person
alxls.html |
|||
|
Header and Footer Background Color |
|||
| When designing a worksheet, you’ve no doubt noticed that Excel provides only minimal formatting for headers and footers on a printout. |
|||
| http://exceltips.vitalnews.com/Pages/T003056_Header_and_Footer_Background_Co
lor.html |
|||
| If you want to highlight cells that contain certain characters, you can use the conditional formatting features of Excel to help out. |
|||
| http://exceltips.vitalnews.com/Pages/T002671_Highlighting_Cells_Containing_S
pecific_Text.html |
|||
| Counting Wins and Losses | |||
| If you want to count specific characters in rows of a data table, you’ll find this tip helpful. Here you discover three different ways you can count the … |
|||
| http://exceltips.vitalnews.com/Pages/T003049_Counting_Wins_and_Losses
.html |
|||
| Understanding Views | |||
| Excel allows you to save different views of your data. This tip explains what views are and how you can create your own views. Provided by ExcelTips, a free … |
|||
| http://exceltips.vitalnews.com/Pages/T002865_Understanding_Views.html | |||
| Complex Lookup Formulas | |||
| Not all lookup operations are straight-forward and easy. This tip discusses a more complicated lookup using concatenated string data to look up information … |
|||
| http://exceltips.vitalnews.com/Pages/T002787_Complex_Lookup_Formulas.
html |
|||
| Ruminations and reflections about significant digits in Excel. Includes examples of how significant digits can affect the outcome of various functions and … |
|||
| http://exceltips.vitalnews.com/Pages/T001983_Thoughts_and_Ideas_on_Significa
nt_Digits_in_Excel.html |
|||
| Wouldn’t it be great to have your huge charts print out on multiple pieces of paper that you could then piece together? While Excel doesn’t provide this … |
|||
| http://exceltips.vitalnews.com/Pages/T002543_Printing_a_Chart_Across_Multipl
e_Pages.html |
|||
| Inserting Hyperlinks | |||
| One type of data that Excel can store within a cell is a hyperlink to another resource either on your local computer or on the Web. |
|||
| http://exceltips.vitalnews.com/Pages/T002802_Inserting_Hyperlinks.htm
l |
|||
| It is easy to use Excel functions to sum values based on criteria you establish, unless those criteria involve the formatting applied to cells in adjacent … |
|||
| http://exceltips.vitalnews.com/Pages/T003298_Summing_Based_on_Formatting_in_
Adjacent_Cells.html |
|||
| Hyperlinks to Charts | |||
| You can create hyperlinks to all sorts of worksheets in a workbook, but you cannot create a hyperlink to a chart sheet. This tip provides a workaround that … |
|||
| http://exceltips.vitalnews.com/Pages/T002539_Hyperlinks_to_Charts.htm
l |
|||
| If you have a lot of Excel workbooks in a folder on your hard drive, you may wonder if there is a way to protect them all by simply protecting the folder. |
|||
| http://exceltips.vitalnews.com/Pages/T002878_Protecting_an_Entire_Folder_of_
Workbooks.html |
|||
| Moving and Selecting Rows | |||
| Some people may find it helpful to move down a row, and then select that entire row. This would allow them to easily move one row at a time through a … |
|||
| http://exceltips.vitalnews.com/Pages/T002106_Moving_and_Selecting_Row
s.html |
|||
| Independent Radio Buttons | |||
| Radio buttons and check boxes can be inserted in a worksheet using the Control toolbox. The purposes for which each is used is very different, … |
|||
| http://exceltips.vitalnews.com/Pages/T003076_Independent_Radio_Button
s.html |
|||
| Error Opening Second Workbook | |||
| If you try to open a second workbook and you see an error message, it could be because of the way you are opening the workbook. This tip explains what the … |
|||
| http://exceltips.vitalnews.com/Pages/T002501_Error_Opening_Second_Wor
kbook.html |
|||
| Editing Custom Dictionaries | |||
| Excel provides spell-checking capabilities on the text you enter in a worksheet. It utilizes the same dictionaries and custom dictionaries used by other … |
|||
| http://exceltips.vitalnews.com/Pages/T003034_Editing_Custom_Dictionar
ies.html |
|||
| Sheets for Months | |||
| One common type of workbook used in offices is one that contains a single worksheet for each month of the year. If you need to create these types of … |
|||
| http://exceltips.vitalnews.com/Pages/T002017_Sheets_for_Months.html | |||
| Do you see some small rectangular boxes appearing in your formula results? It could be because Excel is substituting that box for a character it cannot … |
|||
| http://exceltips.vitalnews.com/Pages/T003206_Character_Replacement_in_Simple
_Formulas.html |
|||
| When you print out a filtered worksheet, you may want some sort of printed record as to what filtering was applied to the worksheet. |
|||
| http://exceltips.vitalnews.com/Pages/T003248_Showing_Filter_Criteria_
on_a_Printout.html |
|||
| Slowing Down Mouse Selection | |||
| Trying to select a range of cells using the mouse? If such selections involve scrolling the screen, sometimes the scrolling can occur just way too fast. |
|||
| http://exceltips.vitalnews.com/Pages/T002422_Slowing_Down_Mouse_Selec
tion.html |
|||
| Multiple Rows of Worksheet Tabs | |||
| If you’ve got a lot of worksheets in your workbook, you may want to display their tabs in to rows at the bottom of the Excel window. |
|||
| http://exceltips.vitalnews.com/Pages/T003009_Multiple_Rows_of_Workshe
et_Tabs.html |
|||
| The auditing tools in Excel allow you to easily see what cells are precedents to or dependent upon the current cell. In large, complex worksheets, … |
|||
| http://exceltips.vitalnews.com/Pages/T003121_Accessing_Dependant_and_Precede
nt_Information.html |
|||
| Limiting Precision | |||
| There may be times you need to limit the amount of precision Excel uses in its calculations. Provided by ExcelTips, a free weekly productivity newsletter: … |
|||
| http://exceltips.vitalnews.com/Pages/T002642_Limiting_Precision.html | |||
| Seeing All Open Workbook Names | |||
| Having a lot of workbooks open in Excel at the same time presents some unique management challenges you won’t experience with only one or two open workbooks … |
|||
| http://exceltips.vitalnews.com/Pages/T002807_Seeing_All_Open_Workbook
_Names.html |
|||
| When you open two workbooks at the same time, do you have trouble copying data from one to the other? This problem could be related to the way you opened … |
|||
| http://exceltips.vitalnews.com/Pages/T003111_Cant_Copy_Data_between_Workbook
s.html |
|||
| When you press Enter while adding information to a worksheet, Excel normally drops to the next cell down in the column. You have complete control over what … |
|||
| http://exceltips.vitalnews.com/Pages/T002975_Specifying_the_Behavior_of_the_
Enter_Key.html |
|||
| When creating a workbook, you may need to make changes on one worksheet and have those edits appear on the same cells in other worksheets. |
|||
| http://exceltips.vitalnews.com/Pages/T002605_Editing_the_Same_Cell_in_Multip
le_Sheets.html |
|||
| When you make a change to one of Excel’s toolbars, do you know where that change is stored? This tip explains the name and characteristics of the special … |
|||
| http://exceltips.vitalnews.com/Pages/T002068_Where_is_Toolbar_Customization_
Information_Stored.html |
|||
| Repeating Cell Contents | |||
| Do you want to repeat the contents of a cell throughout the entire width of a cell? There are two ways you can do it in Excel—one method formulaic and the … |
|||
| http://exceltips.vitalnews.com/Pages/T003214_Repeating_Cell_Contents.
html |
|||
|
Expiration Date for Excel Programs |
|||
| If you use Excel to create a macro-based application, you may want to make sure that your programs cease working after a certain time. |
|||
| http://exceltips.vitalnews.com/Pages/T002590_Expiration_Date_for_Exce
l_Programs.html |
|||
| Flipping Data | |||
| How to reverse the order of rows in a table. Provided by ExcelTips, a free weekly productivity newsletter: www.VitalNews.com. |
|||
| http://exceltips.vitalnews.com/Pages/T002653_Flipping_Data.html | |||
| Counting Unique Values | |||
| If you have a list of values in a range of cells, you may want to know how many unique values appear in the list. The ways you can go about making that … |
|||
| http://exceltips.vitalnews.com/Pages/T002337_Counting_Unique_Values.h
tml |
|||
| Leap Years and Fiscal Periods | |||
| If your fiscal period ends in February, and you need to calculate the date either a year in the past or the future, then you will run into the problem of … |
|||
| http://exceltips.vitalnews.com/Pages/T002314_Leap_Years_and_Fiscal_Pe
riods.html |
|||
| Using the XIRR Function | |||
| The XIRR function is used to determine the rate of return with irregular payments. This tip explains how to use the function. Provided by ExcelTips, a free … |
|||
| http://exceltips.vitalnews.com/Pages/T002503_Using_the_XIRR_Function.
html |
|||
| Want to convert an elapsed time, such as 8:37, to a decimal time, such as 8.62? If you know how Excel stores times internally, coming up with the conversion … |
|||
| http://exceltips.vitalnews.com/Pages/T002052_Converting_Time_Notation_to_Dec
imal_Notation.html |
|||
| You can easily configure Excel so that it runs a specific macro whenever a worksheet is deactivated. Just follow the easy steps in this tip. |
|||
| http://exceltips.vitalnews.com/Pages/T002955_Running_a_Macro_When_a_Workshee
t_is_Deactivated.html |
|||
| Macros often need to select cells relative to the current cell, all in an effort to process data in a worksheet. This tip explains how you can easily change … |
|||
| http://exceltips.vitalnews.com/Pages/T002268_Selecting_a_Range_of_Cells_Rela
tive_to_the_Current_Cell.html |
|||
| Understanding Functions | |||
| Excel uses Functions to assist in creating spreadsheets that perform a multitude of calculations. Provided by ExcelTips, a free weekly productivity … |
|||
| http://exceltips.vitalnews.com/Pages/T001953_Understanding_Functions.
html |
|||
| If you open workbooks in two instances of Excel, you can use drag-and-drop techniques to create hyperlinks from one workbook to another. |
|||
| http://exceltips.vitalnews.com/Pages/T002335_Using_DragandDrop_to_Create_a_H
yperlink.html |
|||
| Freezing Worksheet Tabs | |||
| It is possible for your workbooks to contain scores of worksheets. If you want to repeatedly jump back to a specific worksheet, navigating those sheets can … |
|||
| http://exceltips.vitalnews.com/Pages/T003178_Freezing_Worksheet_Tabs.
html |
|||
| Testing for an Empty Worksheet | |||
| If you using a macro to print out a series of worksheets, you may want a way to determine if a particular worksheet contains information before printing it. |
|||
| http://exceltips.vitalnews.com/Pages/T003280_Testing_for_an_Empty_Wor
ksheet.html |
|||
| When you import a CSV file into an Excel worksheet, you may be surprised at how the program allocates the information among columns. |
|||
| http://exceltips.vitalnews.com/Pages/T003002_CSV_File_Opens_with_Data_in_a_S
ingle_Column.html |
|||
| If you import data produced by a program other than Excel, it might include dates stored in a format not automatically recognized by Excel. |
|||
| http://exceltips.vitalnews.com/Pages/T003014_Converting_an_Unsupported_Date_
Format.html |
|||
| Deleting Macros from within a Macro | |||
| Macros are most often used to change data within a worksheet. However, macros can also be used to make changes to macros—including deleting entire … |
|||
| http://exceltips.vitalnews.com/Pages/T003073_Deleting_Macros_from_within_a_M
acro.html |
|||
| Seeing Excel’s Program Window | |||
| When you change the display resolution of your screen, it can cause some problems with a few programs. Excel is no exception; you might open the program to … |
|||
| http://exceltips.vitalnews.com/Pages/T002079_Seeing_Excels_Program_Wi
ndow.html |
|||
| If you ever try to edit a PivotTable and get an error that tells you that the “underlying data was not included,” it can be frustrating. |
|||
| http://exceltips.vitalnews.com/Pages/T003295_Editing_PivotTables_without_Und
erlying_Data.html |
|||
| Getting Rid of Stubborn Icons | |||
| Icons are often added to toolbars by add-in programs for Excel. If you have trouble getting rid of such icons, there are a number of different things you … |
|||
| http://exceltips.vitalnews.com/Pages/T003097_Getting_Rid_of_Stubborn_
Icons.html |
|||
| When you create a chart, Excel attempts to assign colors to your data series that it thinks will work best for you. If you want to change those colors to … |
|||
| http://exceltips.vitalnews.com/Pages/T003022_Changing_the_Pattern_Used_in_a_
Data_Series.html |
|||
| Iterating Circular References | |||
| Circular references often, but not always, represent a mistake in the logic of your workbook. If you purposefully add a circular reference, … |
|||
| http://exceltips.vitalnews.com/Pages/T002816_Iterating_Circular_Refer
ences.html |
|||
| Getting Rid of Negative Zero Amounts | |||
| Have you ever done some calculations, only to see a value appear that is a zero with a negative sign in front of it? This is a result of your display … |
|||
| http://exceltips.vitalnews.com/Pages/T002576_Getting_Rid_of_Negative_Zero_Am
ounts.html |
|||
| Excel allows you to work with dates and times, but sometimes it takes some real ingenuity to get your output in just the format you want. |
|||
| http://exceltips.vitalnews.com/Pages/T002184_Elapsed_Days_as_Years_Months_an
d_Days.html |
|||
| Many businesses organize information according to calendar quarters, especially when it comes to fiscal information. Given a particular date, you may wonder … |
|||
| http://exceltips.vitalnews.com/Pages/T003339_Determining_Business_Quarters_f
rom_Dates.html |
|||
| Rounding Religious Wars | |||
| How to round values half way between two boundaries is apparently a topic of contention in some sectors of the statistical world. |
|||
| http://exceltips.vitalnews.com/Pages/T002829_Rounding_Religious_Wars.
html |
|||
| Excel doesn’t provide a built-in means to change the standard background color of a worksheet. There are a few different ways you can work around this … |
|||
| http://exceltips.vitalnews.com/Pages/T003133_Changing_Excels_Backgrou
nd_Color.html |
|||
| Determining how to create combinations of various elements to achieve a desired total can be frustrating. This tip takes a look at how to accomplish such a … |
|||
| http://exceltips.vitalnews.com/Pages/T002408_Determining_Combinations_to_Mak
e_a_Total.html |
|||
| Decimal Tab Alignment | |||
| Microsoft Word allows you to set up all sorts of tab stops, including decimal tabs. Excel provides no way for you to do this same type of formatting, … |
|||
| http://exceltips.vitalnews.com/Pages/T002765_Decimal_Tab_Alignment.ht
ml |
|||
| Counting items in a data table that match a single criterion is easy; counting items that match two criteria is a bit more involved. |
|||
| http://exceltips.vitalnews.com/Pages/T002809_Counting_Records_Matching_Multi
ple_Criteria.html |
|||
| Creating a Log/Log Chart | |||
| If you need to create a chart that uses logarithmic values on both axes, it can be confusing how to get what you want. This tip explains which of the chart … |
|||
| http://exceltips.vitalnews.com/Pages/T003025_Creating_a_LogLog_Chart.
html |
|||
| Got a macro that you need to run in a whole bunch of workbooks? Figuring out how to accomplish the task can give you headaches, but the techniques described … |
|||
| http://exceltips.vitalnews.com/Pages/T002278_Running_a_Macro_in_a_Number_of_
Workbooks.html |
|||
| You can use the Paste Special feature in Excel to multiple the values in a range of cells. If you don’t want Excel to mess up the formatting of those cells, … |
|||
| http://exceltips.vitalnews.com/Pages/T002655_Retaining_Formatting_After_a_Pa
ste_Multiply.html |
|||
| You can hide columns in a worksheet; this is no secret. How to display the left-most hidden columns can be tricky, but this tip presents several ways you … |
|||
| http://exceltips.vitalnews.com/Pages/T002626_Displaying_a_Hidden_Firs
t_Column.html |
|||
| Slow Excel Response Times | |||
| Many people use Excel on a notebook computer, so they can do work at the office and elsewhere. In some situations, Excel may run very slowly when not … |
|||
| http://exceltips.vitalnews.com/Pages/T002537_Slow_Excel_Response_Time
s.html |
|||
| Dates, which are stored internally by Excel as numbers, can be formatted in a wide variety of ways. There are built-in formats for dates, but you can also … |
|||
| http://exceltips.vitalnews.com/Pages/T002179_Understanding_Date_and_Time_For
matting_Codes.html |
|||
| Formatting Subtotal Rows | |||
| Using Data | Subtotals you can add different types of subtotals to a data table. These subtotals are normally shown in bold, but you may want to apply other … |
|||
| http://exceltips.vitalnews.com/Pages/T002984_Formatting_Subtotal_Rows
.html |
|||
| There are multiple ways that you can adjust the margins within a cell to allow more white space. This tip examines the most common methods you can try. |
|||
| http://exceltips.vitalnews.com/Pages/T002762_Adjusting_Cell_Margins_for_More
_White_Space.html |
|||
| Inserting a Radical Symbol | |||
| The radical symbol is used frequently in some branches of mathematics. If you want to insert a radical symbol in a cell, here’s how to go about it. |
|||
| http://exceltips.vitalnews.com/Pages/T002997_Inserting_a_Radical_Symb
ol.html |
|||
| Walk away from your computer, and your work is visible on the screen for all to see. For security purposes you may want a workbook to close automatically if … |
|||
| http://exceltips.vitalnews.com/Pages/T002281_Automatically_Closing_a_
Workbook.html |
|||
| Do you use Paste Special to quickly convert formulas to their values for large amounts of data? If you use this trick quite often, then information in this … |
|||
| http://exceltips.vitalnews.com/Pages/T002657_Shortcut_for_Pasting_Onl
y_Values.html |
|||
| Understanding Phantom Macros | |||
| Knowing how Excel sees macros and how to fix phantom macros. Provided by ExcelTips, a free weekly productivity newsletter: www.VitalNews.com. |
|||
| http://exceltips.vitalnews.com/Pages/T002011_Understanding_Phantom_Ma
cros.html |
|||
| When working with charts and chart objects, Excel is very dependent on the mouse. If you don’t want to use the mouse, but instead want to use the keyboard, … |
|||
| http://exceltips.vitalnews.com/Pages/T002837_Using_the_Keyboard_to_Select_an
d_Resize_a_Chart_Object.html |
|||
| Corruption of a workbook can cause data loss. Sometimes it is a bigger problem to lose macros than it is to lose data. Here’sa couple different ways you can … |
|||
| http://exceltips.vitalnews.com/Pages/T002399_Recovering_Macros_from_Corrupte
d_Workbooks.html |
|||
| You can double-click on a CSV (comma separated values) file in Windows, and Excel obediently opens and loads the file. You may not get the results you … |
|||
| http://exceltips.vitalnews.com/Pages/T003228_Stopping_Date_Parsing_when_Open
ing_a_CSV_File.html |
|||
| Sorting Data Containing Merged Cells | |||
| When formatting the layout of your worksheet, Excel allows you to easily merge adjacent cells together. This can cause havoc later, however, since you can’t … |
|||
| http://exceltips.vitalnews.com/Pages/T002581_Sorting_Data_Containing_Merged_
Cells.html |
|||
| When entering dates into a worksheet, you may want the dates to default to last year instead of this year. Here’sa way you can accomplish this type of data … |
|||
| http://exceltips.vitalnews.com/Pages/T003360_Modifying_Default_Year_f
or_Dates.html |
|||
| Got a huge amount of data in a data table, but you only want to select a single record from the table to create a chart? This tip presents three ways you … |
|||
| http://exceltips.vitalnews.com/Pages/T002377_Automatically_Creating_Charts_f
or_Individual_Rows_in_a_Data_Table.html |
|||
| Part numbers can often be long, made up of other component elements. Breaking up part numbers into individual components is easy if each component is a … |
|||
| http://exceltips.vitalnews.com/Pages/T003141_Breaking_Up_VariableLength_Part
_Numbers.html |
|||
| Calculating Future Workdays | |||
| Excel makes doing math with dates quite easy. This tip starts with the simplest of date math—adding a few days to a date. Things get more interesting when … |
|||
| http://exceltips.vitalnews.com/Pages/T002164_Calculating_Future_Workd
ays.html |
|||
| Non-adjusting References in Formulas | |||
| Insert and delete cells and Excel will routinely modify the formulas affected by those edits. You may not want the references in those formulas to be … |
|||
| http://exceltips.vitalnews.com/Pages/T002876_Nonadjusting_References_in_Form
ulas.html |
|||
| Copying Worksheets in a Macro | |||
| Do you need to write a macro that copies worksheets from one workbook to another, or even within the same workbook? If so, you need to become familiar with … |
|||
| http://exceltips.vitalnews.com/Pages/T002784_Copying_Worksheets_in_a_
Macro.html |
|||
| If you need to exit a macro before it is finished running, you can do it using a brute force method, or you can build in a way to gracefully exit. |
|||
| http://exceltips.vitalnews.com/Pages/T003021_Aborting_a_Macro_and_Retaining_
Control.html |
|||
| Precision in Excel | |||
| The best way to understand why Excel provides the answers it does is to examine how it handles precision. This tip provides a high-level overview of … |
|||
| http://exceltips.vitalnews.com/Pages/T001981_Precision_in_Excel.html | |||
| Every Second Tuesday | |||
| There are multiple ways that you can generate a list of every other Tuesday. This tip explores several of the fastest and easiest ways to do it—all without … |
|||
| http://exceltips.vitalnews.com/Pages/T002428_Every_Second_Tuesday.htm
l |
|||
| Entering Numbers in Excel | |||
| What really happens when you enter information into a cell? Well, Excel tries to make sense of what you entered—it tries to parse the information. |
|||
| http://exceltips.vitalnews.com/Pages/T002090_Entering_Numbers_in_Exce
l.html |
|||
| Excel’s charts are normally created in color, but you can print them in black and white. You may be looking for a way to export the black and white version … |
|||
| http://exceltips.vitalnews.com/Pages/T002204_Exporting_Black_and_Whit
e_Charts.html |
|||
| Excel allows you to configure what happens when you press Enter in a cell. This change, which controls the direction in which the following cell is selected … |
|||
| http://exceltips.vitalnews.com/Pages/T002073_Choosing_Direction_After_Enter_
On_a_Workbook_Basis.html |
|||
| If you use Excel to work with data exported from another program, you might be interested in a way to import a large number of text files into an Excel … |
|||
| http://exceltips.vitalnews.com/Pages/T003148_Importing_Multiple_Files_to_a_S
ingle_Workbook.html |
|||
| Do you want to “condense” how Excel prints a workbook so less paper is used? This tip describes a couple of things you can do to get the desired results. |
|||
| http://exceltips.vitalnews.com/Pages/T003243_Printing_Multiple_Worksheets_on
_a_Single_Page.html |
|||
| Setting Horizontal Alignment | |||
| You can horizontally align the information in a cell in any of eight different ways. This tip explains not only how to do the alignment, … |
|||
| http://exceltips.vitalnews.com/Pages/T002123_Setting_Horizontal_Align
ment.html |
|||
| Zooming With the Keyboard | |||
| Zooming in and out on a workbook allows you to magnify or reduce what you see on the screen, without affecting the printout. This tip provides a couple of … |
|||
| http://exceltips.vitalnews.com/Pages/T002731_Zooming_With_the_Keyboar
d.html |
|||
| Missing Equal Sign Tool | |||
| Excel used to provide, prior to Excel 2002, an equal-sign tool near the Formula bar. If you miss this tool, you may want to add a similar tool back to your … |
|||
| http://exceltips.vitalnews.com/Pages/T003146_Missing_Equal_Sign_Tool.
html |
|||
| Using Data Forms | |||
| Lots of people prefer to enter information directly into Excel, but there is another way that may be helpful: Using data forms. This tip introduces data … |
|||
| http://exceltips.vitalnews.com/Pages/T002973_Using_Data_Forms.html | |||
| Creating a Chart | |||
| Excel goes out of its way to make it very easy to chart your data. In fact, you can create simple charts with just a couple of mouse clicks. |
|||
| http://exceltips.vitalnews.com/Pages/T002839_Creating_a_Chart.html | |||
| Want to jump right to a single piece of data from an index you develop on a worksheet? It’s easy to do if you use the hyperlink method described in this tip … |
|||
| http://exceltips.vitalnews.com/Pages/T003083_Jumping_to_Text_in_Worksheet_fr
om_an_Index.html |
|||
| If you need to add dashes between letters and numbers in a string, the work can quickly get tedious. This tip examines some ways you can make the work … |
|||
| http://exceltips.vitalnews.com/Pages/T002613_Inserting_Dashes_between_Letter
s_and_Numbers.html |
|||
| Default Worksheet when Opening | |||
| When opening a workbook, you may want to make sure that a particular worksheet is always displayed first. The only way to ensure this is through the use of … |
|||
| http://exceltips.vitalnews.com/Pages/T002014_Default_Worksheet_when_O
pening.html |
|||
| Shifting Objects Off a Sheet | |||
| One day you are just editing your worksheet like you normally do, then you see an error that says “Cannot shift object off this sheet. |
|||
| http://exceltips.vitalnews.com/Pages/T003151_Shifting_Objects_Off_a_S
heet.html |
|||
| You can easily copy the contents of one cell to another using a formula. There is no way to similarly copy formatting from one cell to another. |
|||
| http://exceltips.vitalnews.com/Pages/T002769_Automatically_Copying_Fo
rmatting.html |
|||
| When processing workbook information in a macro, you may need to step through each worksheet to make some sort of changes. It is helpful to know what type … |
|||
| http://exceltips.vitalnews.com/Pages/T002538_Detecting_Types_of_Sheet
s_in_VBA.html |
|||
| Transposing and Linking | |||
| Using different ways to paste information with the Paste Special option. Provided by ExcelTips, a free weekly productivity newsletter: www.VitalNews.com. |
|||
| http://exceltips.vitalnews.com/Pages/T002652_Transposing_and_Linking.
html |
|||
| Preserving the Undo List | |||
| The undo list is normally wiped out when you run a macro. This makes it impossible for a user to undo the changes that are wrought by your coding. |
|||
| http://exceltips.vitalnews.com/Pages/T002060_Preserving_the_Undo_List
.html |
|||
| One of the important configuration files for Excel is known as the XLB file. You should periodically make backups of this file, in order to protect the … |
|||
| http://exceltips.vitalnews.com/Pages/T002368_Disappearing_Toolbar_Buttons_fo
r_Macros.html |
|||
| Saving Non-Existent Changes | |||
| Do you get frustrated when Excel asks if you want to save your changes when closing a workbook in which you made no changes? It is helpful to understand not … |
|||
| http://exceltips.vitalnews.com/Pages/T002081_Saving_NonExistent_Chang
es.html |
|||
| Self-Deleting Macros | |||
| Macros eventually lose their purpose and are no longer needed. There can be any number of reasons for them no longer being needed, such as a particular date … |
|||
| http://exceltips.vitalnews.com/Pages/T003123_SelfDeleting_Macros.html | |||
| Sending Single Worksheets via E-mail | |||
| Got a single worksheet that you want to e-mail to someone, but don’t want them to see the rest of the worksheets in the workbook? |
|||
| http://exceltips.vitalnews.com/Pages/T003273_Sending_Single_Worksheets_via_E
mail.html |
|||
| Adjusting Values with Formulas | |||
| You can adjust values by using the Paste Special feature, but you may want to do it by applying a formula. This tip describes the rationale for such an … |
|||
| http://exceltips.vitalnews.com/Pages/T002592_Adjusting_Values_with_Fo
rmulas.html |
|||
| Setting a print range for a worksheet is easy. What is more difficult is if you need to set print ranges for multiple worksheets in the same workbook. |
|||
| http://exceltips.vitalnews.com/Pages/T003157_Setting_Print_Ranges_for_Multip
le_Worksheets.html |
|||
| Getting a Count of Unique Names | |||
| Do you have a list of names in which there may be some duplication? You might want to know how many unique names there are in the list. |
|||
| http://exceltips.vitalnews.com/Pages/T003105_Getting_a_Count_of_Uniqu
e_Names.html |
|||
|
Defining Shortcut Keys for Symbols |
|||
| When you use the Insert Symbol dialog box, you can insert any character or symbol it is possible to access in Excel. If you need to frequently insert a … |
|||
| http://exceltips.vitalnews.com/Pages/T003221_Defining_Shortcut_Keys_f
or_Symbols.html |
|||
| Changing Section Headers | |||
| Excel doesn’t provide a very easy way to vary headers or footers during a printout. This tip explains how you can use a macro to do your printing and make … |
|||
| http://exceltips.vitalnews.com/Pages/T002821_Changing_Section_Headers
.html |
|||
| Deriving Monthly Median Values | |||
| One of the things that Excel is very good at is crunching numbers and processing huge amounts of data. This tip gives step-by-step examples of how you can … |
|||
| http://exceltips.vitalnews.com/Pages/T003086_Deriving_Monthly_Median_
Values.html |
|||
| Sorting by Five Columns | |||
| Excel allows you to sort but up to three columns, but you may want to sort by more than that. This tip provides ways you can get the desired sorted data. |
|||
| http://exceltips.vitalnews.com/Pages/T002255_Sorting_by_Five_Columns.
html |
|||
| Comparing Workbooks | |||
| Do you need to compare two workbooks to each other? While you can use specialized third-party software to do the comparisons, a simple formula or two might … |
|||
| http://exceltips.vitalnews.com/Pages/T002006_Comparing_Workbooks.html | |||
| When importing information into a worksheet, you might be puzzled to see that some of the information ends up being treated as dates, even when it shouldn’t … |
|||
| http://exceltips.vitalnews.com/Pages/T003019_Defeating_Automatic_Date
_Parsing.html |
|||
| Want a quick way to apply superscript and subscript to selected text within a cell? This tip shows how the formatting can be done through a user form and a … |
|||
| http://exceltips.vitalnews.com/Pages/T001945_Creating_Superscript_and_Subscr
ipt_Buttons.html |
|||
| If you have a mixture of numbers and letters in a cell, you may be looking for a way to access and use the numeric portion of the value. |
|||
| http://exceltips.vitalnews.com/Pages/T003185_Using_a_Numeric_Portion_of_a_Ce
ll_in_a_Formula.html |
|||
| Sheets for Days | |||
| Need a quick way to have a worksheet for each day in a month? Here’sa macro that makes the worksheet creation a snap. Provided by ExcelTips, a free weekly … |
|||
| http://exceltips.vitalnews.com/Pages/T002836_Sheets_for_Days.html | |||
| Excel allows you to protect worksheets, individually. Users can still see that the worksheets are they, but they cannot display them if they don’t know the … |
|||
| http://exceltips.vitalnews.com/Pages/T001952_Protecting_Individual_Worksheet
s_by_User.html |
|||
| Those in Europe use a date format that is different than those in the US; this is not news. But what if you need to convert those European dates to US dates … |
|||
| http://exceltips.vitalnews.com/Pages/T003309_Converting_European_Dates_to_US
_Dates.html |
|||
| Resizing a Text Box in a Macro | |||
| Text boxes are easy to add to a document and manually resize, as needed. If you want to resize the text box in a macro, however, the way to do it … |
|||
| http://exceltips.vitalnews.com/Pages/T003143_Resizing_a_Text_Box_in_a
_Macro.html |
|||
| Creating Two-Line Custom Formats | |||
| Formatting cells is easy in Excel; you can even create your own custom formats. Normally those formats result in data that appears on a single line in a … |
|||
| http://exceltips.vitalnews.com/Pages/T002895_Creating_TwoLine_Custom_
Formats.html |
|||
| Recording a Data Entry Time | |||
| When you use Excel for data entry, you might want to keep track of the time when each entry is entered. Coming up with a manual or formulaic way to … |
|||
| http://exceltips.vitalnews.com/Pages/T003116_Recording_a_Data_Entry_T
ime.html |
|||
| Counting Cells with Text Colors | |||
| Excel allows you to specify text colors on a character-by-character basis in your cells, if desired. If you want to count how many cells contain text of a … |
|||
| http://exceltips.vitalnews.com/Pages/T002901_Counting_Cells_with_Text
_Colors.html |
|||
| Adding a File Path and Filename | |||
| Do you need to add a files’ path and name to your workbook? There are a couple of ways you can go about adding the information, either to an existing cell … |
|||
| http://exceltips.vitalnews.com/Pages/T002875_Adding_a_File_Path_and_F
ilename.html |
|||
| Printing Just the Visible Data | |||
| In a large worksheet, you may want to display and print just a portion of the available data. Displaying the desired information is easy; printing it is a … |
|||
| http://exceltips.vitalnews.com/Pages/T002702_Printing_Just_the_Visibl
e_Data.html |
|||
| Weekdays in a Month | |||
| Excel has no intrinsic formulas that allow you to determine how many of a particular weekday occur within a month. You can create such a formula, however, … |
|||
| http://exceltips.vitalnews.com/Pages/T002183_Weekdays_in_a_Month.html | |||
| Numbers Spelled Out | |||
| It is not uncommon to have a need to spell out numbers, such that “123” becomes “one hundred twenty three.” There is no intrinsic way to do this in Excel, … |
|||
| http://exceltips.vitalnews.com/Pages/T002270_Numbers_Spelled_Out.html | |||
| Drawing objects can be layered over each other in almost any manner you desire. If you want to change the order in which those drawing objects are layered, … |
|||
| http://exceltips.vitalnews.com/Pages/T002599_Specifying_an_Order_for_Drawing
_Objects.html |
|||
| Easily add any number of checkboxes to an Exel worksheet. Provided by ExcelTips, a free weekly productivity newsletter: www.VitalNews.com. |
|||
| http://exceltips.vitalnews.com/Pages/T001964_Inserting_Large_Numbers_of_Chec
kboxes.html |
|||
| Printing a Draft Watermark | |||
| Adding a watermark to the background of a printout can be challenging. This tip explains the different ways you can implement your own watermarking … |
|||
| http://exceltips.vitalnews.com/Pages/T002448_Printing_a_Draft_Waterma
rk.html |
|||
| Two-Level Axis Labels | |||
| Need a chart that uses two lines for axis labels? It’s easy to do if you know how to set up your data in the worksheet, before creating the chart. |
|||
| http://exceltips.vitalnews.com/Pages/T003203_TwoLevel_Axis_Labels.htm
l |
|||
| Using AutoSave in Excel | |||
| It is a good precaution to periodically save your work. That way, if you have an unforeseen problem with your computer, you only lose what you were working … |
|||
| http://exceltips.vitalnews.com/Pages/T002777_Using_AutoSave_in_Excel.
html |
|||
| Reorganizing Data | |||
| Do you need to completely reformat the data you import into Excel? This tip shows how you can break a single column of data into multiple columns of input. |
|||
| http://exceltips.vitalnews.com/Pages/T002301_Reorganizing_Data.html | |||
| Weird Mouse Shortcut | |||
| If you like to use the mouse in your worksheet navigation efforts, you’ll want to pay attention to this tip. Here you discover a way you can navigate using … |
|||
| http://exceltips.vitalnews.com/Pages/T002453_Weird_Mouse_Shortcut.htm
l |
|||
| Copying Comments when Filtering | |||
| The advanced filtering feature in Excel allows you to quickly copy unique information from one data list to another. If you want to copy the comments … |
|||
| http://exceltips.vitalnews.com/Pages/T003169_Copying_Comments_when_Fi
ltering.html |
|||
| Merging Many Workbooks | |||
| Got a whole slew of workbooks that you need to merge together? You can do it manually, but it could take you all day. It’s much better to use a macro to do … |
|||
| http://exceltips.vitalnews.com/Pages/T002409_Merging_Many_Workbooks.h
tml |
|||
| Digital Signatures for Macros | |||
| The security features built into Excel 2002 and 2003 allow you to digitally sign your macros so that users can rest assured that they remain unchanged since … |
|||
| http://exceltips.vitalnews.com/Pages/T003107_Digital_Signatures_for_M
acros.html |
|||
| When you copy a formula that contains cell or range references, Excel adjusts the references relative to where you are pasting the formula. |
|||
| http://exceltips.vitalnews.com/Pages/T002946_Relative_Worksheet_References_w
hen_Copying.html |
|||
| Toggling AutoFilter | |||
| Using the menu sequence Data | Filter | AutoFilter, you can toggle AutoFilter on and off for a data table. This tip explains how to create your own toolbar … |
|||
| http://exceltips.vitalnews.com/Pages/T002710_Toggling_AutoFilter.html | |||
| If you’ve got a bunch of phone numbers in a worksheet, you may want to check if they are all within certain bounds. You can use the Conditional Formatting … |
|||
| http://exceltips.vitalnews.com/Pages/T002978_Conditional_Formatting_for_Erra
nt_Phone_Numbers.html |
|||
| Counting with Two Criteria | |||
| Want to count the records in a data table that match two criteria? It’s not as easy or straightforward as you might hope. This tip explores several … |
|||
| http://exceltips.vitalnews.com/Pages/T002815_Counting_with_Two_Criter
ia.html |
|||
| Uncovering and Removing Links | |||
| Data can be linked into a workbook from a variety of external sources. If you get a workbook from a co-worker or friend, those links may not work any more. |
|||
| http://exceltips.vitalnews.com/Pages/T001925_Uncovering_and_Removing_
Links.html |
|||
| Do your arrow and Enter keys not work as you expect them to? Does using the keys result in selecting a cell someplace else in the workbook. |
|||
| http://exceltips.vitalnews.com/Pages/T002071_Weird_Actions_for_Arrow_Keys_an
d_Enter.html |
|||
| Need to figure out the lowest score in a range of scores? Here’s the formulas to get the information you need. Provided by ExcelTips, a free weekly … |
|||
| http://exceltips.vitalnews.com/Pages/T002054_Figuring_Out_the_LowScor
e_Winner.html |
|||
| Counting Groupings Below a Threshold | |||
| When working with some data sets, you may need to know how many groups of values in the data set have elements that fall below a certain threshold. |
|||
| http://exceltips.vitalnews.com/Pages/T003171_Counting_Groupings_Below_a_Thre
shold.html |
|||
| Weighted Averages in a PivotTable | |||
| Weighted averages are very handy when analyzing certain types of data. If you want to add a weighted average to a PivotTable, there are no intrinsic Excel … |
|||
| http://exceltips.vitalnews.com/Pages/T002900_Weighted_Averages_in_a_P
ivotTable.html |
|||
| Conditional Page Breaks | |||
| When printing a report from Excel, it is not unusual to want to start a new page when something changes in your data. Using the Subtotals feature of Excel, … |
|||
| http://exceltips.vitalnews.com/Pages/T002792_Conditional_Page_Breaks.
html |
|||
| Shortcut Key for Format Painter | |||
| The Format Painter tool is very handy when you want to quickly duplicate formatting form one place to another. The tool virtually requires the use of the … |
|||
| http://exceltips.vitalnews.com/Pages/T003225_Shortcut_Key_for_Format_
Painter.html |
|||
| Calculating Week-Ending Dates | |||
| When working with dates, you may need to figure out all the dates on which weeks end in a given year. There are several formulaic ways to accomplish this … |
|||
| http://exceltips.vitalnews.com/Pages/T002444_Calculating_WeekEnding_D
ates.html |
|||
| Displaying the "Last Modified" Date | |||
| One of the properties that Excel maintains for a workbook is the date it was last modified or saved. This tip explains how you can access that date and … |
|||
| http://exceltips.vitalnews.com/Pages/T002285_Displaying_the_Last_Modi
fied_Date.html |
|||
| If you need to get lots of information from Excel to PowerPoint, the task can be daunting. This tip explains different approaches you can take when you need … |
|||
| http://exceltips.vitalnews.com/Pages/T002378_Snapshots_of_Excel_Worksheets_f
or_PowerPoint.html |
|||
| A Ruler in Excel | |||
| A few workarounds for the fact that Excel does not have a built-in ruler. Provided by ExcelTips, a free weekly productivity newsletter: www.VitalNews.com. |
|||
| http://exceltips.vitalnews.com/Pages/T002484_A_Ruler_in_Excel.html | |||
| Copying formulas from one cell to another is quite intuitive in Excel—unless you want the copied formulas to follow some sort of pattern. |
|||
| http://exceltips.vitalnews.com/Pages/T003067_Copying_Formulas_using_a
_Pattern.html |
|||
| Shading Based on Odds and Evens | |||
| Given a series of values in a range of cells, you may want to shade the cells based on whether the values are odd or even. Using Conditional Formatting you … |
|||
| http://exceltips.vitalnews.com/Pages/T002767_Shading_Based_on_Odds_an
d_Evens.html |
|||
| Making an Image Color Transparent | |||
| Excel allows you to put all types of graphics in your workbooks. If you are using bitmapped graphics (a very common type), you can instruct Excel to treat a … |
|||
| http://exceltips.vitalnews.com/Pages/T002200_Making_an_Image_Color_Tr
ansparent.html |
|||
| Handling Leading Zeros in CSV Files | |||
| When dealing with files containing comma-separated values, you want to make sure that what gets imported into Excel reflects what is really in the file. |
|||
| http://exceltips.vitalnews.com/Pages/T002588_Handling_Leading_Zeros_in_CSV_F
iles.html |
|||
| Modifying Axis Scale Labels | |||
| Create a chart in Excel, and the program decides how to scale the chart for best appearance. If you want to override what Excel decides, you can adjust the … |
|||
| http://exceltips.vitalnews.com/Pages/T003180_Modifying_Axis_Scale_Lab
els.html |
|||
| Moving Part of a Footer Down a Line | |||
| When creating a footer for a printout, it is possible for parts of the footer to overwrite other parts. This tip explains how you can use a two-line footer … |
|||
| http://exceltips.vitalnews.com/Pages/T002961_Moving_Part_of_a_Footer_Down_a_
Line.html |
|||
| Faster Text File Conversions | |||
| When importing a text file into Excel, you have the opportunity to define the characteristics of the data being imported. If the data has quite a few fields … |
|||
| http://exceltips.vitalnews.com/Pages/T002235_Faster_Text_File_Convers
ions.html |
|||
| Using Excel for Timing | |||
| Excel allows you to store times in a worksheet. If you want to use Excel to time certain events, there are a couple of ways you can approach the task. |
|||
| http://exceltips.vitalnews.com/Pages/T002037_Using_Excel_for_Timing.h
tml |
|||
| Would you like to develop a worksheet that has two cells that will convert whatever is entered in either of the cells? For instance, you might put a … |
|||
| http://exceltips.vitalnews.com/Pages/T003277_Developing_Reciprocal_Conversio
n_Formulas.html |
|||
| Counting Displayed Cells | |||
| When you filter data, Excel displays only a portion of what is really in a worksheet. If you want to count the number of cells that are displayed after … |
|||
| http://exceltips.vitalnews.com/Pages/T001955_Counting_Displayed_Cells
.html |
|||
| Understanding how Excel uses memory, how much it allows, and how to work around the limitations. Provided by ExcelTips, a free weekly productivity … |
|||
| http://exceltips.vitalnews.com/Pages/T002545_Workbook_Once_Created_is_Too_Bi
g_for_Memory.html |
|||
| Conditional Formatting | |||
| A few ways to use conditional formatting on cells in your Excel worksheet. Provided by ExcelTips, a free weekly productivity newsletter: www.VitalNews.com. |
|||
| http://exceltips.vitalnews.com/Pages/T002665_Conditional_Formatting.h
tml |
|||
| Shading every second, third, or fifth row of a printout can be helpful for reading data. This tip describes how to use a macro to get the formatting you … |
|||
| http://exceltips.vitalnews.com/Pages/T002130_Shading_Rows_for_Ease_in_Readin
g_Output.html |
|||
| Excel can import some types of files automatically, and other types require some intervention on your part. If you are not getting the results you want … |
|||
| http://exceltips.vitalnews.com/Pages/T002426_Avoiding_Scientific_Notation_on
_File_Imports.html |
|||
| Unhiding or Listing All Objects | |||
| In complex workbooks with lots of objects, it is easy to “lose” an object so that you cannot select it. The macro techniques used in this tip allow you to … |
|||
| http://exceltips.vitalnews.com/Pages/T002025_Unhiding_or_Listing_All_
Objects.html |
|||
| If you’ve got a workbook that has dozens of charts in it, you know that it can be a real pain to select each chart and make a common change (such as to a … |
|||
| http://exceltips.vitalnews.com/Pages/T003125_Changing_Elements_in_Lots_of_Ch
arts_at_One_Time.html |
|||
| When you create workbooks for others to use, you might want to make sure that they can’t change the formatting and paper size you’ve specified. |
|||
| http://exceltips.vitalnews.com/Pages/T002559_Preventing_Changes_to_Formattin
g_and_Page_Size.html |
|||
| Using List Box Controls | |||
| One of the form controls you can place in a worksheet is a list box. Once you know how to populate a list box and use it, this control can make a great … |
|||
| http://exceltips.vitalnews.com/Pages/T002918_Using_List_Box_Controls.
html |
|||
| Using WordArt in Excel |
|||
| The WordArt program has been available in Office for a long, long time. It allows you to (dare I say it) create art from words. |
|||
| http://exceltips.vitalnews.com/T003408 | |||
| Tying a Hyperlink to a Specific Cell |
|||
| Make a hyperlink to a cell in your workbook, edit the structure of that workbook a bit, and you may find that the hyperlink no longer targets the original … |
|||
| http://exceltips.vitalnews.com/T003466 | |||
| Modifying Proper Capitalization |
|||
| The PROPER worksheet function is used to change the case of text so that the first letter of each word is capitalized. If there are some words you don’t … |
|||
| http://exceltips.vitalnews.com/T003467 | |||
| Converting UTC Times to Local Times |
|||
| Dates and times are often standardized on UTC time, which is analogous to GMT times. How to convert such times to your local time is a relatively simple … |
|||
| http://exceltips.vitalnews.com/T003440 | |||
| Generating Double-Digit Random Numbers |
|||
| Normally you use the RAND and RANDBETWEEN functions to generate random numbers. What if you want to generate random numbers within a certain range but … |
|||
| http://exceltips.vitalnews.com/T003403 | |||
| Applying Conditional Formatting to Multiple Worksheets |
|||
| If you just switched to Excel 2007, you may have noticed some differences in how the program handles applying conditional formatting to multiple worksheets. |
|||
| http://exceltips.vitalnews.com/T003433 | |||
| Excel Proficiency Testing |
|||
| Need to figure out how good someone is with Excel? You need what is commonly called a “proficiency test.” Here’s the low-down on what is available and what … |
|||
| http://exceltips.vitalnews.com/T003426 | |||
| Calculating the Median Age of a Group of People |
|||
| Suppose you have a worksheet that contains a list of ages and then a count of people who correspond with those ages. You may wonder how you can figure out … |
|||
| http://exceltips.vitalnews.com/T003397 | |||
| Determining Mouse Cursor Coordinates On a Graphic |
|||
| Add a graphic to a worksheet as part of an Image object, and you can use some very handy event handlers to figure out the coordinates of the mouse pointer. |
|||
| http://exceltips.vitalnews.com/T003421 | |||
| Microsoft Excel Tips |
|||
| Welcome to ExcelTips! Microsoft Excel is a powerful program, and you can make it even more powerful by learning the Excel tips, Excel tricks, … |
|||
| http://exceltips.vitalnews.com/ | |||
| Limiting Entry of Prior Dates | |||
| You can use the data validation feature of Excel to limit what is entered in a cell, including the way that dates are entered. This tip provides an advanced … |
|||
| http://exceltips.vitalnews.com/Pages/T002960_Limiting_Entry_of_Prior_
Dates.html |
|||
| Need a list of macro shortcut keys? It’s not as easy in Excel as in some other Office applications, such as Word. It can be done, however, with a little … |
|||
| http://exceltips.vitalnews.com/Pages/T003162_List_of_Macro_Shortcuts_in_All_
Open_Workbooks.html |
|||
| If you have a set of addresses in a worksheet, you may have some addresses that partially match other ones. For instance, a set of addresses where the only … |
|||
| http://exceltips.vitalnews.com/Pages/T002782_Removing_Duplicates_Based_on_a_
Partial_Match.html |
|||
| The Last Business Day | |||
| Many businesses need to know when the last business day of the month occurs. This tip discusses the many ways you can calculate this date in Excel. |
|||
| http://exceltips.vitalnews.com/Pages/T002452_The_Last_Business_Day.ht
ml |
|||
| Conditional Printing | |||
| If you need to make what Excel prints be based upon conditions in a worksheet, you’ll love the information in this tip. There are a variety of ways you can … |
|||
| http://exceltips.vitalnews.com/Pages/T002372_Conditional_Printing.htm
l |
|||
| Library Not Registered Error | |||
| bottom. Great ExcelTips! Your e-mail address is safe! Close Note. Search for answers: Submit search form ExcelTips Site … |
|||
| http://exceltips.vitalnews.com/Pages/T002952_Library_Not_Registered_E
rror.html |
|||
| Conditional formatting does not allow you to change the typeface and font size used in a cell. You can write your own macro to do the formatting change, … |
|||
| http://exceltips.vitalnews.com/Pages/T002380_Changing_Font_Face_and_Size_Con
ditionally.html |
|||
| Saving Common Formulas | |||
| If you’ve got a formula you like to use over and over again, you can assign the formula a name, just like you can with cell ranges. |
|||
| http://exceltips.vitalnews.com/Pages/T002957_Saving_Common_Formulas.h
tml |
|||
| Automatically Converting to GMT | |||
| Greenwich Meridian Time, or GMT, is a common reference time used around the world. If you want to convert a time to GMT, this tip provides the information … |
|||
| http://exceltips.vitalnews.com/Pages/T002185_Automatically_Converting
_to_GMT.html |
|||
| If you have a group of password-protected workbooks linked through a common worksheet, you may get frustrated by the need to continually enter the password … |
|||
| http://exceltips.vitalnews.com/Pages/T002886_Using_a_Single_Password_for_Mul
tiple_Workbooks.html |
|||
| Latitude and longitude are expressed in hours, minutes, and seconds, which means that Excel can store them as time values. This may cause problems when you … |
|||
| http://exceltips.vitalnews.com/Pages/T002770_Exporting_Latitude_and_L
ongitude.html |
|||
| Excel can display fractions just as easily as it can display other types of formatting. When you enter a fraction Excel translates it into a “reduced” … |
|||
| http://exceltips.vitalnews.com/Pages/T002760_Stopping_Fractions_from_
Reducing.html |
|||
| Finding Unknown Links | |||
| There are several things to try when finding unknown links in Excel. Provided by ExcelTips, a free weekly productivity newsletter: www.VitalNews.com. |
|||
| http://exceltips.vitalnews.com/Pages/T002141_Finding_Unknown_Links.ht
ml |
|||
| Finding Differences Between Lists | |||
| If you have two data lists that are related to each other, you can use the techniques in this tip. For instance, you may want to compile a list of the … |
|||
| http://exceltips.vitalnews.com/Pages/T002053_Finding_Differences_Betw
een_Lists.html |
|||
| Do you have a list in which there are duplicates in the key field, and you want to look up non-duplicate values associated with those keys? |
|||
| http://exceltips.vitalnews.com/Pages/T003077_Looking_Up_Names_when_Key_Value
s_are_Identical.html |
|||
| Do you want to include two data labels with your chart? Exactly how you can achieve this feat isn’t intuitive in Excel, but once you learn the trick you’ll … |
|||
| http://exceltips.vitalnews.com/Pages/T002411_Numeric_Value_and_Percentage_Va
lue_in_a_Graph_Column.html |
|||
| Got an add-in that you don’t want loaded during start-up? Here’sa few ways that you can exclude it. Provided by ExcelTips, a free weekly productivity … |
|||
| http://exceltips.vitalnews.com/Pages/T002383_Excluding_a_Specific_AddIn_at_S
tartup.html |
|||
| Copying Headers and Footers | |||
| Got a set of headers and footers in one worksheet that you want copied to another worksheet? This tip explains the various approaches you can use to perform … |
|||
| http://exceltips.vitalnews.com/Pages/T002696_Copying_Headers_and_Foot
ers.html |
|||
| Counting Dates in a Range | |||
| Given a table of dates in a worksheet, you may want to determine how many of those dates are within a certain date range. This tip explains the formulas you … |
|||
| http://exceltips.vitalnews.com/Pages/T002818_Counting_Dates_in_a_Rang
e.html |
|||
| Shared workbooks are, unfortunately, prone to data corruption or loss. This tip examines one such data loss problem and proposes various solutions for the … |
|||
| http://exceltips.vitalnews.com/Pages/T002998_Losing_Data_in_a_Shared_
Workbook.html |
|||
| Referring to the Last Cell | |||
| If you have lots of data in a table, and the length of that table can vary, how do you reference the last cells in each column of the table? |
|||
| http://exceltips.vitalnews.com/Pages/T002170_Referring_to_the_Last_Ce
ll.html |
|||
| Splitting Information into Rows | |||
| If you have data stored in multiple lines in a single cell, you might want to expand that data so that it actually appears on multiple rows. |
|||
| http://exceltips.vitalnews.com/Pages/T003263_Splitting_Information_in
to_Rows.html |
|||
| Engineering Calculations | |||
| In an engineering or scientific environment, it is not unusual to need data. |
|||
| http://exceltips.vitalnews.com/Pages/T002928_Engineering_Calculations
.html |
|||
| Large Numbers in the MOD Function | |||
| There is a known bug in the MOD function that stops it from working with large numbers and small divisors. This tip examines the bug and discusses ways to … |
|||
| http://exceltips.vitalnews.com/Pages/T003302_Large_Numbers_in_the_MOD_Functi
on.html |
|||
| Counting All Characters | |||
| If you need to count the number of text characters in a worksheet, the macro in this tip can come in very handy. It looks at not only the text in cells, … |
|||
| http://exceltips.vitalnews.com/Pages/T002284_Counting_All_Characters.
html |
|||
| Getting Rid of the Office Assistant | |||
| How to rid yourself of the Office Assistant. Provided by ExcelTips, a free weekly productivity newsletter: www.VitalNews.com. |
|||
| http://exceltips.vitalnews.com/Pages/T003193_Getting_Rid_of_the_Offic
e_Assistant.html |
|||
| Drawing Simple Objects | |||
| Want to draw a few simple shapes or lines on your worksheet? Here’s how to do it. Provided by ExcelTips, a free weekly productivity newsletter: www. |
|||
| http://exceltips.vitalnews.com/Pages/T002520_Drawing_Simple_Objects.h
tml |
|||
| Entering Dates Without Separators | |||
| When you enter dates into a cell, Excel pays attention to the separators you use between the month, day, and year. While entering dates could be done a lot … |
|||
| http://exceltips.vitalnews.com/Pages/T002039_Entering_Dates_Without_S
eparators.html |
|||
| You can spice up your bar chart by using a graphic, of your choosing, to construct the bars. This tip shows how easy it is to pick and use your graphic. |
|||
| http://exceltips.vitalnews.com/Pages/T003199_Using_Graphics_to_Represent_Dat
a_Series.html |
|||
| Importing Huge Data Files | |||
| Sometimes, when importing data created by other programs, you may find that there is too much for Excel to handle. Here’s how you can still accomplish the … |
|||
| http://exceltips.vitalnews.com/Pages/T002533_Importing_Huge_Data_File
s.html |
|||
| Changing Fonts in Multiple Workbooks | |||
| If you need to change fonts used in a lot of different workbooks, the task can be daunting—if you need to do it manually. This tip looks at a macro you can … |
|||
| http://exceltips.vitalnews.com/Pages/T002526_Changing_Fonts_in_Multiple_Work
books.html |
|||
| Displaying Images based on a Result | |||
| Got some images that you want to appear in a worksheet based on the result displayed in a cell? Figuring out how to “conditionally display” an image can be … |
|||
| http://exceltips.vitalnews.com/Pages/T003128_Displaying_Images_based_on_a_Re
sult.html |
|||
| When creating a line cart, the line can show values both positive and negative values. This tip explains how you can use different colors to display that … |
|||
| http://exceltips.vitalnews.com/Pages/T001999_Positive_and_Negative_Colors_in
_a_Chart.html |
|||
| Deleting Blank Columns | |||
| Import data from another program, and you could end up with a lot of blank columns in your data. Here’s the quickest way to get rid of these empty columns. |
|||
| http://exceltips.vitalnews.com/Pages/T002660_Deleting_Blank_Columns.h
tml |
|||
|
Is Daylight Savings Time in Effect? |
|||
| Daylight savings time is supported by modern computers, but getting the information into Excel as to whether daylight savings time is in effect can be a … |
|||
| http://exceltips.vitalnews.com/Pages/T003318_Is_Daylight_Savings_Time
_in_Effect.html |
|||
| Exploded Pie Chart Sections | |||
| When you create a pie chart in Excel, you can create it with all the slices together or all the slices spread apart (“exploded”). |
|||
| http://exceltips.vitalnews.com/Pages/T002825_Exploded_Pie_Chart_Secti
ons.html |
|||
| Making All Occurrences Bold | |||
| Do you need to change the formatting of all occurrences of a word in a worksheet? How you approach the task depends on two factors: the version of Excel you … |
|||
| http://exceltips.vitalnews.com/Pages/T002414_Making_All_Occurrences_B
old.html |
|||
| Locking Callouts to a Graph Location | |||
| If you add callouts using the drawing tools in Excel, you may have noticed that they don’t always stay where you expect them to stay. |
|||
| http://exceltips.vitalnews.com/Pages/T003007_Locking_Callouts_to_a_Graph_Loc
ation.html |
|||
| Working with Elapsed Time | |||
| Since Excel stores times as numeric values, you can perform arithmetic operations on those values. This allows you to work with elapsed time, … |
|||
| http://exceltips.vitalnews.com/Pages/T002819_Working_with_Elapsed_Tim
e.html |
|||
| Deleting Worksheets in a Macro | |||
| Do you need to delete a worksheet under the control of a macro you are creating? You’ll love this tip, then. Provided by ExcelTips, a free weekly … |
|||
| http://exceltips.vitalnews.com/Pages/T002293_Deleting_Worksheets_in_a
_Macro.html |
|||
| You can use the Text to Columns wizard to separate names into different cells. The success you have with this, however, depends on the condition of the … |
|||
| http://exceltips.vitalnews.com/Pages/T002789_Separating_Names_into_Individua
l_Columns.html |
|||
| AutoFilling with the Alphabet | |||
| Creating your own custom word search puzzles in Excel can present you with an interesting problem: how to fill a range of empty cells with letters of the … |
|||
| http://exceltips.vitalnews.com/Pages/T003109_AutoFilling_with_the_Alp
habet.html |
|||
| Wildcards in ‘Replace With’ Text | |||
| When doing searches in Excel, you can use wildcard characters in the specification of what you are searching. However, you cannot use them in the … |
|||
| http://exceltips.vitalnews.com/Pages/T003303_Wildcards_in_Replace_Wit
h_Text.html |
|||
| Easily Deploying Customizations | |||
| When you create a whole set of customizations for Excel, you may want to share them with others in your office or workgroup. This tip provides some guidance … |
|||
| http://exceltips.vitalnews.com/Pages/T002083_Easily_Deploying_Customi
zations.html |
|||
| Determining Sorting Criteria | |||
| Excel makes it easy to sort data, but once it is sorted, then determining how the sort was done is not nearly as easy. There are a couple of approaches … |
|||
| http://exceltips.vitalnews.com/Pages/T002395_Determining_Sorting_Crit
eria.html |
|||
| Disabling Moving Between Worksheets | |||
| If you want someone to not be able to move from one worksheet to another in a workbook, you’ve got your work cut out for you. Here are some things you can … |
|||
| Worksheets can be set up so that they are automatically updated with data pulled from a variety of sources. As data changes in this manner, you may want to … |
|||
| http://exceltips.vitalnews.com/Pages/T002906_Displaying_Messages_When_Automa
tic_Data_Changes.html |
|||
| Unprotecting Groups of Worksheets | |||
| Protecting worksheets is a handy way to make your work safe from other users. If your workbook contains quite a few worksheets, unprotecting them … |
|||
| http://exceltips.vitalnews.com/Pages/T002275_Unprotecting_Groups_of_W
orksheets.html |
|||
| You can select non-contiguous ranges of cells and set them as the print area for a worksheet. Getting those ranges on a single sheet of printout, however, … |
|||
| http://exceltips.vitalnews.com/Pages/T002779_Multiple_Print_Areas_on_a_Singl
e_Printed_Page.html |
|||
| Unique Military Date Format | |||
| The military, for some purposes, uses a variation of a Julian date that is not directly supported by Excel. This tip explains various formulas you can use … |
|||
| http://exceltips.vitalnews.com/Pages/T003227_Unique_Military_Date_For
mat.html |
|||
| Converting Forced Text to Numbers | |||
| If you have some numbers stored in cells that are formatted as text, you may get some surprises when you try to use those cells in some of your formulas. |
|||
| http://exceltips.vitalnews.com/Pages/T002670_Converting_Forced_Text_t
o_Numbers.html |
|||
| Excel provides a consolidation tool that allows you to easily combine the data from a bunch of worksheets into a single worksheet. This tip explains how you … |
|||
| http://exceltips.vitalnews.com/Pages/T003005_Condensing_Multiple_Worksheets_
Into_One.html |
|||
| Macros can run differently on different machines. This fact can be painfully obvious if a macro runs slower on a machine that you thought was faster. |
|||
| http://exceltips.vitalnews.com/Pages/T003072_Macro_Slows_Down_on_More_Powerf
ul_Machine.html |
|||
| Using a macro to add the full path name into a header or footer in an Excel worksheet. Provided by ExcelTips, a free weekly productivity newsletter: www. |
|||
| http://exceltips.vitalnews.com/Pages/T002639_Full_Path_Names_in_Headers_or_F
ooters.html |
|||
| Analyzing huge amounts of data presents challenges that are different from those faced with smaller amounts of data. This tip examines how you can analyze … |
|||
| http://exceltips.vitalnews.com/Pages/T002771_Calculating_Statistical_Values_
on_DifferentSized_Subsets_of_Data.html |
|||
| Many people use Excel to track lots and lots of data. Hiding some of that data based upon some criteria, such as whether the data is outside of a date range … |
|||
| http://exceltips.vitalnews.com/Pages/T003261_Hiding_Columns_Not_within_a_Dat
e_Range.html |
|||
| Official Color Names in VBA | |||
| When creating macros, you can use the ColorIndex and Color properties to set or determine the colors in a cell. Excel provides eight constants that can be … |
|||
| http://exceltips.vitalnews.com/Pages/T002778_Official_Color_Names_in_
VBA.html |
|||
| Switching Headers in a Frozen Row | |||
| Excel allows you to “freeze” rows in your worksheet. What if you want the rows that are frozen to change as you scroll through the worksheet? |
|||
| http://exceltips.vitalnews.com/Pages/T003446_Switching_Headers_in_a_F
rozen_Row.html |
|||
| If the information you import from Access into Excel isn’t interpreted just right, it can have consequences on your conditional formatting. |
|||
| http://exceltips.vitalnews.com/Pages/T002418_Conditional_Formatting_with_Dat
a_Imported_from_Access.html |
|||
| Calculating Only the Active Workbook | |||
| When you have automatic calculation turned on, Excel treats all your open workbooks the same and recalculates them all. You may not want this to occur; … |
|||
| http://exceltips.vitalnews.com/Pages/T002877_Calculating_Only_the_Active_Wor
kbook.html |
|||
| Printing to a Disk File | |||
| It never fails—there is one person in every office who has the best printer in the world, while you get the hand-me-downs. You can still take advantage of … |
|||
| http://exceltips.vitalnews.com/Pages/T003240_Printing_to_a_Disk_File.
html |
|||
| Counting Employees in Classes | |||
| Excel is very good at counting things, even when those things need to meet specific criteria. This tip shows how you can do a comparison to individual … |
|||
| http://exceltips.vitalnews.com/Pages/T002552_Counting_Employees_in_Cl
asses.html |
|||
| Checking for Messages in Cells | |||
| If you have a large range of cells that is used to display verification messages for a worksheet, you know that looking through the range to check for … |
|||
| http://exceltips.vitalnews.com/Pages/T002772_Checking_for_Messages_in
_Cells.html |
|||
| Displaying Letter Grades | |||
| Excel is great at working with numbers, but it can be confusing to try to convert some numbers (such as numeric grades) into letters (such as letter grades) … |
|||
| http://exceltips.vitalnews.com/Pages/T002611_Displaying_Letter_Grades
.html |
|||
| Text placed in cells can either be lowercase, uppercase, or a mixture of the two. If you want to count the cells based upon the case of the text within … |
|||
| http://exceltips.vitalnews.com/Pages/T003212_Counting_Cells_According
_to_Case.html |
|||
| Floating Information in a Frozen Row | |||
| You can freeze information in rows or columns using one of the built-in features of Excel. As you move up or down in the worksheet, the information in the … |
|||
| http://exceltips.vitalnews.com/Pages/T003140_Floating_Information_in_a_Froze
n_Row.html |
|||
| If you use Excel’s Web Query tools to grab data from a website, you may run into some problems if the site isn’t available right away. |
|||
| http://exceltips.vitalnews.com/Pages/T003233_Retrieving_Web_Query_Data_witho
ut_Interruption.html |
|||
| How to round values half way between two boundaries is apparently a topic of contention in some sectors of the statistical world. |
|||
| http://exceltips.vitalnews.com/Pages/T002835_Rounding_Religious_Wars_
Take_Two.html |
|||
| Displaying Latitude and Longitude | |||
| If you work with geographic data, you may need a way to display latitude and longitude in a worksheet. This tip examines several different approaches you an … |
|||
| http://exceltips.vitalnews.com/Pages/T003016_Displaying_Latitude_and_
Longitude.html |
|||
| Deleting All Names but a Few | |||
| Excel provides an easy way for you to delete one defined name at a time. If you want to delete most of the names in a workbook, but leave a few still intact … |
|||
| http://exceltips.vitalnews.com/Pages/T002419_Deleting_All_Names_but_a
_Few.html |
|||
| Conditional formatting is a great boon to Excel users. One use is that you can use it to highlight cells that contain zero values. |
|||
| http://exceltips.vitalnews.com/Pages/T002980_Conditional_Formats_that_Distin
guish_Blanks_and_Zeroes.html |
|||
| Hiding and Protecting Columns | |||
| Excel allows you to protect, to a degree, the contents of various cells in your worksheets. If you want to protect an entire column, there are specific … |
|||
| http://exceltips.vitalnews.com/Pages/T002781_Hiding_and_Protecting_Co
lumns.html |
|||
| Comparing Lists for Duplicates | |||
| Do you have two similar data lists that you need to compare to see if there are duplicates in the lists? This can be a daunting task, but you can add some … |
|||
| http://exceltips.vitalnews.com/Pages/T002251_Comparing_Lists_for_Dupl
icates.html |
|||
| Adjusting the VLOOKUP Function | |||
| The VLOOKUP function returns either an exact match with a lookup value or, if one is not available, the first value that is less than the lookup value. |
|||
| http://exceltips.vitalnews.com/Pages/T003090_Adjusting_the_VLOOKUP_Fu
nction.html |
|||
| If you work in the sciences or mathematics, you know that significant digits are important. This tip answers questions about whether you can force Excel to … |
|||
| http://exceltips.vitalnews.com/Pages/T002045_Maintaining_Accuracy_of_Signifi
cant_Digits.html |
|||
| Deleting Duplicate Text Values | |||
| Got a long list from which you need to remove the duplicates? There are a couple of ways you can perform this common task. This tip explains two ways to do … |
|||
| http://exceltips.vitalnews.com/Pages/T002430_Deleting_Duplicate_Text_
Values.html |
|||
| Labeling X-Y Scatter Plots | |||
| Figuring out how to get the data points in an XY scatter plot labeled can be confusing; Excel certainly doesn’t make it easy. Here’s some ideas on how to … |
|||
| http://exceltips.vitalnews.com/Pages/T003503_Labeling_XY_Scatter_Plot
s.html |
|||
| Ignoring N/A Values in a Sum | |||
| You can use some of Excel’s worksheet functions across a range or worksheets, but not all of them. One that has problems with multi-sheet use is SUMIF, … |
|||
| http://exceltips.vitalnews.com/Pages/T003156_Ignoring_NA_Values_in_a_
Sum.html |
|||
| You can use the Zoom feature of Excel to magnify what Excel shows of your workbook, but it affects the entire screen. What if you want to only magnify a … |
|||
| http://exceltips.vitalnews.com/Pages/T003114_Magnifying_Only_the_Curr
ent_Cell.html |
|||
| Pulling Formulas from a Worksheet | |||
| The formulas in your worksheet can be displayed (instead of formula results) by a simple configuration change. You can then easily make a copy of those … |
|||
| http://exceltips.vitalnews.com/Pages/T003190_Pulling_Formulas_from_a_
Worksheet.html |
|||
| Getting Rid of Spaces in Cells | |||
| Getting rid of stray spaces that clutter up otherwise empty cells can be a bother. This tip explains how you can use a couple of different techniques to … |
|||
| http://exceltips.vitalnews.com/Pages/T002883_Getting_Rid_of_Spaces_in
_Cells.html |
|||
| Changing Your Company Name | |||
| The installation process for Office and Excel allows you to specify a company name. If your company name later changes, you may be faced with the task of … |
|||
| http://exceltips.vitalnews.com/Pages/T002908_Changing_Your_Company_Na
me.html |
|||
| Creating Dependent Drop-Lists | |||
| Drop-down lists are handy in an Excel worksheet, and you they can be even more handy if a selection in one drop-down lists controls the options presented in … |
|||
| http://exceltips.vitalnews.com/Pages/T002972_Creating_Dependent_DropL
ists.html |
|||
| AutoFill with Random Numbers | |||
| AutoFilling is a great tool in Excel; it allows you to place a wide range of values into cells. If you want to use it to add random numbers to a worksheet, … |
|||
| http://exceltips.vitalnews.com/Pages/T002964_AutoFill_with_Random_Num
bers.html |
|||
| Default Headers and Footers | |||
| Changing the look of default headers and footers for your Excel workbooks. Provided by ExcelTips, a free weekly productivity newsletter: www.VitalNews.com. |
|||
| http://exceltips.vitalnews.com/Pages/T002635_Default_Headers_and_Foot
ers.html |
|||
| Easily Changing Chart Data Ranges | |||
| Many times you use the same chart format to graph a subset of information from a full data table. This tip presents a detailed tutorial on how to make your … |
|||
| http://exceltips.vitalnews.com/Pages/T002376_Easily_Changing_Chart_Da
ta_Ranges.html |
|||
|
Summing Every Fourth Cell in a Row |
|||
| Excel provides several powerful functions to calculate a sum of values, but how you put those functions to use in non-standard ways can be perplexing. |
|||
| http://exceltips.vitalnews.com/Pages/T003234_Summing_Every_Fourth_Cell_in_a_
Row.html |
|||
| At the heart of any formula in Excel is the ability to reference other cells. Excel supports two types of address: relative and absolute. |
|||
| http://exceltips.vitalnews.com/Pages/T002062_Understanding_Relative_and_Abso
lute_Addressing.html |
|||
| Returning a Blank Value | |||
| Some people want a formula to return a blank value based upon a calculation or comparison. Such a condition is impossible in Excel—formulas can never return … |
|||
| http://exceltips.vitalnews.com/Pages/T002814_Returning_a_Blank_Value.
html |
|||
| Pasting Without Updating References | |||
| Do you need to paste formulas without updating the references in whatever you are pasting? You can accomplish this, depending on what you are pasting and … |
|||
| http://exceltips.vitalnews.com/Pages/T002033_Pasting_Without_Updating_Refere
nces.html |
|||
Leave a Comment
You must be logged in to post a comment.
