Create a Personal View of Your Workbooks
Excel enables you to have multiple workbooks showing simultaneously, and to have a customized view of your workbooks arranged in different windows. Then you can save your workspaces as files and use them when it suits you
[Discuss (0) | Link to this hack]

Enter Data into Multiple Worksheets Simultaneously
It's fairly ordinary to have the same data appear in multiple worksheets simultaneously. You can use Excel's tool for grouping so that data in one workbook can be entered into multiple worksheets at the same time. We also have a quicker and more flexible approach that uses a couple of lines of Visual Basic for Applications (VBA) code
[Discuss (0) | Link to this hack]

Prevent Users from Performing Certain Actions
Although Excel provides overall protection for workbooks and worksheets, this blunt instrument doesn't provide limited privileges to users—unless you do some hacking
[Discuss (0) | Link to this hack]

Prevent Seemingly Unnecessary Prompts
Excel's chattiness can get a little old; always prompting you to confirm actions you just asked it to perform. Quit the conversation and let Excel get back to the action
[Discuss (0) | Link to this hack]

Hide Worksheets So That They Cannot Be Unhidden
Sometimes you want a place for information that users can't read or modify. Build a backstage into your workbook, a place to keep data, formulas, and other minutiae consumed by, but not seen in, your sheets
[Discuss (0) | Link to this hack]

Customize the Templates Dialog and Default Workbook
If you tend to perform the same tasks or use the same spreadsheet layouts again and again, you can build your own Template tab into Excel's standard Insert Template dialog to provide a quick starting point
[Discuss (0) | Link to this hack]

Create an Index of Sheets in Your Workbook
If you've spent much time in a workbook with many worksheets, you know how painful it can be to find a particular worksheet. An index sheet available to every worksheet is a navigational must-have
[Discuss (1) | Link to this hack]

Limit the Scrolling Range of Your Worksheet
If you move around your spreadsheet a lot, or if you have data you don't want readers to explore, you might find it convenient to limit the visible area of your spreadsheet to only that which has actual data
[Discuss (0) | Link to this hack]

Lock and Protect Cells Containing Formulas
You may want to let users change cells containg data without providing them access to change formulas. You can keep cells containing formulas under lock and key without having to protect your entire sheet or workbook
[Discuss (1) | Link to this hack]

Find Duplicate Data using Conditional Formatting
Excel's conditional formatting is generally used to identify values in particular ranges, but we can hack it to identify duplicated data within a list or table
[Discuss (0) | Link to this hack]

Tie Custom Toolbars to a Particular Workbook
Although most toolbars you build apply to just about any work you do, sometimes the functionality of a custom toolbar applies to only one workbook in particular. With this hack, you can tie custom toolbars to their respective workbooks
[Discuss (0) | Link to this hack]

Outsmart Excel's Relative Reference Handler
In Excel, a formula reference can be either relative or absolute, but sometimes you want to move cells that use relative references without making the references absolute. Here's how
[Discuss (1) | Link to this hack]

Remove Phantom Workbook Links
Ah, phantom links. You open your workbook and are prompted to "Update Links," but there are no links! How can you update links when they don't exist?
[Discuss (0) | Link to this hack]

Reduce Workbook Bloat
Ever notice that your workbook is increasing in size at an alarming rate for no apparent reason? There are several causes of workbook bloat, and some slimming solutions
[Discuss (0) | Link to this hack]

Extract Data from a Corrupt Workbook
Workbook corruption can mean the loss of vital data, costing you more than just money. This hack explores some methods that might recover your data
[Discuss (0) | Link to this hack]

Validate Data Based on a List on Another Worksheet
Data validation makes it easy to specify rules your data must follow. Unfortunately, Excel insists that lists used in data validation must appear on the same worksheet as the data being validated. Fortunately, there are ways to evade this requirement
[Discuss (0) | Link to this hack]

Control Conditional Formatting with Checkboxes
Although conditional formatting is one of Excel's most powerful features, it's a nuisance to turn it on and off through the menus and dialog boxes of the GUI. Adding checkboxes to your worksheet that turn formatting on and off makes it much easier to read data in any way you want, whenever you want
[Discuss (0) | Link to this hack]

Identify Formulas with Conditional Formatting
Excel does not have a built-in function that identifies formulas. Once a formula is entered into a cell, you can tell whether the cell is a static value or a value derived from a formula only by clicking in each cell and looking in the Formula bar. This hack fills that gap with a custom function
[Discuss (0) | Link to this hack]

Count or Sum Cells That Meet Conditional Formatting Criteria
Once you can see the results of conditional formatting, you might want to create formulas that reference only the data that was conditionally formatted. Excel doesn't quite understand this in its calculations, but it can learn
[Discuss (0) | Link to this hack]

Highlight Every Other Row or Column
You've surely seen Excel spreadsheets that have alternating row colors. . Conditional formatting makes this easy
[Discuss (0) | Link to this hack]

Create 3D Effects in Tables or Cells
Whenever you see a nifty 3D effect in a program or application such as Excel, you are actually seeing an illusion created by specific formatting. It is easy to create this illusion yourself by applying formatting to a cell or range of cells
[Discuss (0) | Link to this hack]

Turn Conditional Formatting and Data Validation On and Off with a Checkbox
Data validation can make it far less likely that a user accidentally will enter incorrect data. Sometimes, however, you might need to make it easier to enter data that otherwise would be flagged as incorrect by conditional formatting or blocked completely by the validator
[Discuss (1) | Link to this hack]

Support Multiple Lists in a ComboBox
When working with multiple lists, you can force a list to change by using a combination of option buttons plus a ComboBox
[Discuss (4) | Link to this hack]

Create Validation Lists That Change Based on a Selection from Another List
Validation needs can vary depending on the context in which the validation is used. However, you can create a spreadsheet in which one validation list changes depending on what you select in another
[Discuss (4) | Link to this hack]

Force Data Validation to Reference a List on Another Worksheet
One of the options available in the data validation feature is the List option, which provides a nice drop-down list of specific items from which the user can choose. One drawback with data validation is that the moment you try to reference a list that resides on another worksheet, you will be told this is not possible. Luckily, you can make it possible by using this hack
[Discuss (0) | Link to this hack]

Use Replace... to Remove Unwanted Characters
When importing data or copying and pasting data from other sources into Excel, unwanted characters appear throughout your spreadsheet. Using this hack, you can spare yourself the trouble of removing them by hand
[Discuss (0) | Link to this hack]

Convert Text Numbers to Real Numbers
The contents of a cell might look like numbers, especially in imported data, but it still might be impossible to use these numbers in calculations. Here are a few ways in which you easily can convert these "text" numbers to true numbers
[Discuss (0) | Link to this hack]

Customize Cell Comments
Cell comments enable you to place the electronic equivalent of a sticky note to any specified cell in a worksheet. Although many people use cell comments, many don't know that cell comments are customizable
[Discuss (1) | Link to this hack]

Sort by More Than Three Columns
Excel's Sort feature is limited in that it enables you to nominate no more than three data fields by which to sort. In most cases, this is enough, but sometimes it can be handy to sort by more than three columns of data. Here is how you can get around this limitation
[Discuss (0) | Link to this hack]

Random Sorting
You can use Excel to pick three winners—1, 2, and 3chosen at random from a list in your spreadsheet. The easiest and fairest way to do this is to use Excel's function in combination with its sorting capabilities
[Discuss (0) | Link to this hack]

Manipulate Data with the Advanced Filter
If you are familiar with Excel's AutoFilter tool, you also are familiar with its limitations. If you require extensive data manipulation, using Excel's Advanced Filter tool is the way to go
[Discuss (0) | Link to this hack]

Create Custom Number Formats
Excel comes with built-in number formats, but sometimes you need to use a number format that is not built into Excel. create number formats that you can customize to meet your needs
[Discuss (0) | Link to this hack]

Add More Levels of Undo to Excel for Windows
We all are familiar with Excel's fabulous Undo feature, which enables a user to undo his mistakes. Unfortunately, the default level for this is a mere 16 changes. With the hack in this section, you can change the registry so that you can undo up to 100 mistakes
[Discuss (0) | Link to this hack]

Create Custom Lists
By adding a custom list to Excel, you can type the first item in the list, drag it down using the fill handle, and watch the list fill automatically
[Discuss (0) | Link to this hack]

Boldface Excel Subtotals
Wouldn't it be great if you could identify the subtotals in your worksheets so that you can find them easily? With the hacks in this section, you can
[Discuss (0) | Link to this hack]

Convert Excel Formulas and Functions to Values
Most Excel spreadsheets contain formulas. Sometimes you may want to force only the result of a formula to occupy a cell, instead of leaving the formula in place, where it will change if/when the data it references changes
[Discuss (0) | Link to this hack]

Automatically Add Data to a Validation List
The validation feature in Excel is great, but there is one key thing it cannot do (without the following hack): automatically add a new entry to the list being used as the source for the validation list
[Discuss (0) | Link to this hack]

Hack Excel's Date and Time Features
Excel's date and time feature is great if you're creating simple spreadsheets, but they can cause problems for more advanced projects. Fortunately, there are ways to get around Excel's assumptions when they don't meet your needs
[Discuss (0) | Link to this hack]

Address Data by Name
Although cell numbers are at the foundation of everything Excel does, it's much easier to remember names, such as Item Number and Quantity, than it is to remember cell numbers, such as A1:A100. Excel makes this easy
[Discuss (0) | Link to this hack]

Use the Same Name for Ranges on Different Worksheets
Sometimes it would be convenient to use the same name for data in the same place on multiple worksheets within the same workbook. Excel requires a few extra steps to make this work
[Discuss (0) | Link to this hack]

Create Custom Functions Using Names
Although referencing data by name is convenient, it's sometimes more helpful to store a constant value or even a formula, especially if you've been creating custom functions in VBA.
[Discuss (0) | Link to this hack]

Create Ranges That Expand and Contract
If you need to constantly update and add to your data, or if you work with charts and PivotTables, you'll want to create dynamic named ranges, which expand and contract relative to your data
[Discuss (0) | Link to this hack]

Nest Dynamic Ranges for Maximum Flexibility
A dynamic named range that resides within another dynamic named range can be very useful for things such as long lists of names.
[Discuss (0) | Link to this hack]

Identify Named Ranges on a Worksheet
Excel enables users to give meaningful names to specific ranges in their worksheets. As the number of different named ranges on a worksheet grows, you will need tools for identifying the areas referenced by your named ranges
[Discuss (0) | Link to this hack]

PivotTables: A Hack in Themselves
PivotTables are one of the wildest but most powerful features of Excel, an ingenious hack themselves that may take some experimentation to figure out
[Discuss (1) | Link to this hack]

Share PivotTables but Not Their Data
Create a snapshot of your PivotTable that no longer needs the underlying data structures
[Discuss (0) | Link to this hack]

Automate PivotTable Creation
The steps you need to follow to create a PivotTable require some effort, and that effort often is redundant. With a small bit of VBA, you can create simple PivotTables automatically
[Discuss (0) | Link to this hack]

Move PivotTable Grand Totals
One of the most annoying things about PivotTables is that the Grand Total that summarizes your data always ends up at the bottom of the table, meaning you have to scroll down just to see the figures. Move your Grand Total up to the top where it's easier to find
[Discuss (0) | Link to this hack]

Efficiently Pivot Another Workbook's Data
Use data residing in another workbook as the source for your PivotTable
[Discuss (0) | Link to this hack]

Explode a Single Slice from a Pie Chart
Although pie charts are excellent visual aids, sometimes you want to emphasize a particular piece of the pie. Separating it from the rest gives it more attention
[Discuss (0) | Link to this hack]

Create Two Sets of Slices in One Pie Chart
Most people think pie charts are limited to a single set of values, but here is a way to create a pie chart based on two columns of values
[Discuss (0) | Link to this hack]

Create Charts That Adjust to Data
Your charts can include and plot new data automatically, the moment you add the data to your spreadsheet
[Discuss (1) | Link to this hack]

Interact with Your Charts Using Custom Controls
To make your chart truly interactive, you can use one or more dynamic ranges in your chart and then use either a scrollbar or a drop-down list from the Forms toolbar to reveal the figures your readers want to peruse
[Discuss (1) | Link to this hack]

Three Quick Ways to Update Your Charts
Although creating new charts is wonderful, updating them to reflect new circumstances can take a lot of effort. You can reduce the amount of work needed to change the data used by a chart in a number of ways
[Discuss (0) | Link to this hack]

Hack Together a Simple Thermometer Chart
Excel doesn't provide a thermometer chart. If you want one, you'll have to construct it.
[Discuss (0) | Link to this hack]

Create a Column Chart with Variable Widths and Heights
Wouldn't it be nice to create a column chart whose columns can vary in width and height? Then, as you plot your data into the columns, the columns' width and height cleverly adjust themselves simultaneously
[Discuss (0) | Link to this hack]

Create a Speedometer Chart
You can create a really impressive, workable speedometer (or "speedo") chart, complete with moving needle, by using a combination of doughnuts and pie charts. The added touch is that you can control the speedometer via a scrollbar
[Discuss (0) | Link to this hack]

Link Chart Text Elements to a Cell
When creating and using charts repetitively, it can be handy to know how to link some of your chart text elements, such as titles and labels, directly to a cell. This means that if and when your underlying data changes, your chart data and its text elements will always be in harmony
[Discuss (0) | Link to this hack]

Hack Chart Data So That Blank Cells Are Not Plotted
Excel treats blank cells as having a value of 0, which can result in some ugly charts. Your chart can suddenly drop off, leaving you with a chart that no longer accurately paints the picture you are trying to convey. With the hacks in this section, you can ensure that blank cells are not plotted
[Discuss (0) | Link to this hack]

Add Descriptive Text to Your Formulas
Excel's named ranges and cell comments can help clarify formulas, but sometimes you want to put documentation into the formula itself. With the hacks in this section, you can add descriptive text to your formulas quickly and easily
[Discuss (0) | Link to this hack]

Move Relative Formulas Without Changing References
In Excel, a formula reference can be either relative or absolute. Sometimes, however, you might want to reproduce the same formulas somewhere else in your worksheet or workbook, or on another sheet
[Discuss (0) | Link to this hack]

Compare Two Excel Ranges
Spotting the differences between two large tables of data can be a very time-consuming task. Fortunately, there are at least two ways in which you can automate what would otherwise be a very tedious manual process
[Discuss (0) | Link to this hack]

Fill All Blank Cells in a List
Often, many people will leave a blank cell if the data for that cell is the same as the cell above it. Visually this makes lists easy to read, but structurally it is not a good idea. With the hacks in this section, you can fill all blank cells in a list quickly and easily
[Discuss (0) | Link to this hack]

Make Your Formulas Increment by Rows When You Copy Across Columns
Excel's automatic incrementing of cell references works well most of the time, but sometimes you might want to override how it works
[Discuss (1) | Link to this hack]

Convert Dates to Excel Formatted Dates
Dates imported from other programs frequently cause problems in Excel. Many people manually retype them, but there are easier ways to solve the problem
[Discuss (0) | Link to this hack]

Sum or Counting Cells While Avoiding Error Values
Error values are useful warnings, but sometimes you need to do calculations despite the errors. Choosing functions that tolerate errors will let you do this
[Discuss (0) | Link to this hack]

Reduce the Impact of Volatile Functions on Recalculation
Volatile functions, which must be recalculated almost every time the user performs an action in Excel, can waste an enormous amount of time. Although volatile functions are too useful to discard entirely, there are ways to reduce the delays they create
[Discuss (0) | Link to this hack]

Count Only One Instance of Each Entry in a List
When you have a large list of items, you might want to perform a count on the items without counting entries that appear multiple times. With this hack, you can count each unique entry only once
[Discuss (0) | Link to this hack]

Sum Every Second, Third, or nth Row or Cell
Every now and then you might want to sum every second, third, fourth, etc., cell in a spreadsheet. Now you can, with the following hack
[Discuss (0) | Link to this hack]

Find the nth Occurrence of a Value
Excel's built-in lookup functions can do some pretty clever stuff, but unfortunately Excel has no single function that will return the occurrence of specified data. Fortunately, there are ways to make Excel do this
[Discuss (0) | Link to this hack]

Make the Excel Subtotal Function Dynamic
Although is one of Excel's most convenient functions, you sometimes want to choose the function it uses, or apply it to data that can expand and contract
[Discuss (0) | Link to this hack]

Add Date Extensions
Excel's date formats consist of many different formats that you can use to display a date. However, one format that has always been lacking in Excel—and still does not exist—is the ability to display a date as 15th October 2003. You can make Excel do this if you need it
[Discuss (0) | Link to this hack]

Convert Numbers with the Negative Sign on the Right to Excel Numbers
Have you ever had to work with imported negative numbers that have the negative sign on the right? SAP is one such program that does this with negative numbers—e.g., 200- instead of -200. Changing these by hand so that Excel understands them can be a hassle, but it doesn't need to be
[Discuss (0) | Link to this hack]

Display Negative Time Values
Sometimes you want to display negative time values, but Excel will only display . There are several ways to escape this problem
[Discuss (0) | Link to this hack]

Use the VLOOKUP Function Across Multiple Tables
Although itself is very handy, it is restricted to looking in a specified table to return a result, which sometimes is not enough. You can escape this limitation with the function
[Discuss (2) | Link to this hack]

Show Total Time as Days, Hours, and Minutes
When you add hours in Excel, you can have the result return as total hours and minutes, but unfortunately, not as days, hours, and minutes. Displaying that will take some extra work
[Discuss (0) | Link to this hack]

Determine the Number of Specified Days in Any Month
When you're creating calendar-related applications, especially payroll applications, you sometimes need to know how many times a given day of the week appears in a particular month
[Discuss (0) | Link to this hack]

Construct Mega-Formulas
Mega-formulas—a formula within a formula within a formula—are enough to send even the most seasoned Excel veteran running for the hills. With a little forethought and by working step by step toward the formula you need, however, you can tame those complex mega-formulas without fear
[Discuss (0) | Link to this hack]

Hack Mega-Formulas that Reference Other Workbooks
Excel formulas get pretty complicated when a mega-formula references another workbook. Not only do you need to include cell references, but also you must include workbook names or sheet names, and even the full path if the referenced workbook is closed. There are several ways to simplfiy what can be a complex process
[Discuss (0) | Link to this hack]

Hack One of Excel's Database Functions to Take the Place of Many Functions
Excel's database functions—, , etc.—can take the place of potentially thousands of functions, thereby reducing both recalculation time and workbook space
[Discuss (0) | Link to this hack]

Speed Up Code While Halting Screen Flicker
When you record macros from within Excel, the code it generates often produces screen flicker, which not only slows down your macro, but also makes the macro's activity look very disorganized. Fortunately, you can eliminate screen flicker while at the same time speeding up your code
[Discuss (0) | Link to this hack]

Run a Macro at a Set Time
Many times it would be great to run a macro at a predetermined time or at specified intervals. Fortunately, Excel provides a VBA method that makes this possible
[Discuss (0) | Link to this hack]

Use CodeName to Reference Sheets in Excel Workbooks
Sometimes you need to create a macro that will work even if the sheet names it references change
[Discuss (0) | Link to this hack]

Connect Buttons to Macros Easily
Instead of giving every button its own macro, it's sometimes more convenient to create a single macro that manages all the buttons
[Discuss (0) | Link to this hack]

Create a Workbook Splash Screen
Splash screens provide that extra bit of polish to an application—not to mention that they keep you entertained while the application loads. Why shouldn't a spreadsheet do the same?
[Discuss (0) | Link to this hack]

Display a "Please Wait" Message
Have you ever had one of those macros that seem to take forever to complete? If this is a problem with your macro, you can have Excel display a "Please Wait" message to the user
[Discuss (0) | Link to this hack]

Have a Cell Ticked or Unticked upon Selection
Sometimes it's difficult to make choices with checkboxes. Fortunately, you can simplify this process using a basic bit of code
[Discuss (0) | Link to this hack]

Count or Sum Cells That Have a Specified Fill Color
Using a bit of code, you can easily or cells whose fill color was specified manually
[Discuss (0) | Link to this hack]

Add the Microsoft Excel Calendar Control to Any Excel Workbook
If you want to ensure that users enter dates correctly, the Excel Calendar Control can make things easier for both you and the users of the spreadsheet. With this hack, you can add the Calendar Control to any Excel workbook
[Discuss (0) | Link to this hack]

Password-Protect and Unprotect All Excel Worksheets in One Fell Swoop
Sadly, there is no standard feature in Excel that will enable you to protect and unprotect all worksheets in one go; however, some simple code can make it happen
[Discuss (0) | Link to this hack]

Retrieve a Workbook's Name and Path
Every now and then you might want a cell to return the name of a workbook, or even the workbook's filename and path. With this hack, it's easy to retrieve a workbook's name and path
[Discuss (0) | Link to this hack]

Get Around Excel's Three-Criteria Limit for Conditional Formatting
You can use VBA to hack conditional formatting to use more than three criteria on your data. In fact, you can use the code to apply virtually an unlimited number of criteria
[Discuss (0) | Link to this hack]

Run Procedures on Protected Worksheets
Excel macros are a great way to save time and eliminate errors. However, sooner or later you might try to run your favorite Excel macro on a worksheet that has been protected, with or without a password, resulting in a runtime error. Avoid that problem with the following hack
[Discuss (0) | Link to this hack]

Distribute Macros
Although you can distribute a macro along with a workbook, if you want to distribute only the macro's functionality, an Excel add-in is the way to go
[Discuss (0) | Link to this hack]

Load an XML Document into Excel
If someone sends you an XML file containing data that fits into tables, you don't need to read the text and all its angle brackets. You can load the document into Excel directly, tell Excel how you want to present it, and work with the data through maps
[Discuss (0) | Link to this hack]

Save to SpreadsheetML and Extracting Data
Since Excel XP, Excel has included an XML export option. SpreadsheetML provides an XML representation of your spreadsheets, complete with formatting and formula information
[Discuss (0) | Link to this hack]

Create Spreadsheets using SpreadsheetML
While exporting spreadsheets as XML is useful, Excel also lets you import information this way, letting you create spreadsheets using SpreadsheetML
[Discuss (0) | Link to this hack]

Import Data Directly into Excel
Using Amazon's Web Services as a source of data, you can easily integrate live information about books into Excel spreadsheets. This example imports sales rank data for particular books and calculates the average rank
[Discuss (0) | Link to this hack]

Access SOAP Web Services from Excel
If your spreadsheet needs to access constantly updated data, or if you need to access services hosted on another computer, Excel's Web Services support will enable you to get connected
[Discuss (7) | Link to this hack]

Create Excel Spreadsheets Using Other Environments
Although Excel and other spreadsheet programs are the traditional interfaces for creating and reading spreadsheets, sometimes you might need to create files directly from other programs
[Discuss (0) | Link to this hack]