Tom’s Tutorials For Excel: Copying Formulas While Keeping Their Relative and Absolute References
Here’s how you can copy a set of formulas and paste them elsewhere, while keeping the original formulas unaffected and keeping the relative and absolute references unchanged.
Before the copy and paste.
…
Tom’s Tutorials For Excel: Pasting a Formulas Static Value in Cell Below
You probably know that if you select a cell below a cell that contains a value or formula, when you press Ctrl+’ (the Ctrl and apostrophe keys), you can replicate that value or formula.
…
Tom’s Tutorials For Excel: “Did You Know…”
Did you know:
You can insert a new worksheet by pressing Shift+F11. Immediately thereafter, to continue inserting as many new worksheets as you want, press the F4 key.
Did you know:
You can enter any value in a cell,
…
Tom’s Tutorials For Excel: Converting a trailing negative sign to a real negative number
As any Excel developer or workplace guru knows, raw downloads from external storage warehouses can throw some strange data at you. An example is a list of numbers, some of which are meant to be negative but are actually text values because of a trailing negative sign,
…
Tom’s Tutorials For Excel: Finding and Replacing on All Worksheets
Sometimes overlooked is Excel’s ability to Find and Replace across all worksheets in a workbook, without needing to select all the worksheets.
• Select any cell in any sheet.
…
Tom’s Tutorials For Excel: Filling Across Worksheets
I previously posted this example for making changes on all sheets at once.
Here’s a quick and easy solution when the changes you want to put on all worksheets have already been made to one worksheet.
…
Tom’s Tutorials For Excel: Stripping the Last Nth Characters From a String
Here are formulas to strip away the last Nth characters from a string, where N is the count of characters you want to strip. Notice that if you have a list of items with varying locations of the character of interest,
…
Tom’s Tutorials For Excel: Filling Series For Linear and Growth
Here is an example of building a table of projected sales and income, using three Fill methods of Series, Linear, and Growth.
TIP:
First Example: Fill Series (add a year number to each filled cell)
• Enter the first year of your data,
…
Tom’s Tutorials For Excel: Shortcutting a Cell for PasteSpecial to Values
Here’s a quick way to use your keyboard for achieving the effect of copying a cell, and pasting it special for values.
Instead of mouse clicks and menus that are commonly used,
…
Tom’s Tutorials For Excel: AutoFilling Dates For Weekdays Only
Here’s how you can fill in a range of dates that only includes traditional workdays of Monday through Friday, while bypassing the weekend dates for Saturdays and Sundays.
Step 1
You start by entering your beginning date into a cell.
…
Tom’s Tutorials For Excel: Doing Math Without Formulas or Programming
You can copy a helper cell and perform a mathematical operation in the Paste Special dialog box to work with numbers in Excel. There are no formulas or programming code involved — just a few simple mouse clicks.
…
Tom’s Tutorials For Excel: Using an InputBox and Looping Dates for Copy and Sum
In January I posted this example for using AutoFilter for dates. AutoFilter is usually quicker and more efficient but I wanted to show how an example of looping through dates by their year.
…
Tom’s Tips For Excel Videos: Filling Empty Cells With Value From Above
Sometimes you get data in a column which has a lot of blank cells that represent whatever text was in the last filled cell above them.
When you need to fill in hundreds or thousands of empty cells,
…
Tom’s Tutorials For Excel: *Really* Clear the Clipboard
The Windows Clipboard is a temporary storage area for information that you have copied or moved from one place and plan to use somewhere else. You cannot see or touch the Clipboard but you can work with it to copy,
…
Tom’s Tutorials For Excel: Fill Formula to Last Used Row
The picture on the left shows a list of numbers in column A starting in cell A2. You have a formula in cell B2 which you want to quickly copy down to the last used cell in column A.
…
Tom’s Tutorials For Excel: Transpose a Table Range
In three fast, easy steps you can transpose a multi-row and multi-column table to gain a fresh analytical look at your data. The result is similar to an effect with a pivot table, but with the advantage that you can see both views of the data at the same time,
…
San Francisco, California
1-925-310-4170
tom@atlaspm.com
