Blog Archives

Tom’s Tutorials For Excel: Copying Formulas While Keeping Their Relative and Absolute References

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.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , ,

Tom’s Tutorials For Excel: Pasting a Formulas Static Value in Cell Below

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.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , ,

Tom’s Tutorials For Excel: “Did You Know…”

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,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Converting a trailing negative sign to a real negative number

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,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Finding and Replacing on All Worksheets

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.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Filling Across Worksheets

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.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Stripping the Last Nth Characters From a String

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,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Filling Series For Linear and Growth

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,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Shortcutting a Cell for PasteSpecial to Values

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,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: AutoFilling Dates For Weekdays Only

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.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,