We have relaunched: What's new at ZDNet Asia?

ZDNet / Techguide / Microsoft Office Suite / Story

Copy hundreds of rows of Excel formulas in three steps

By Mary Ann Richardson and Susan Harkins, TechRepublic on August 20, 2009

Summary

Copying formulas in Excel is relatively simple, unless you need to copy down through dozens or even hundreds of cells. Here's a foolproof trick to get it done.

Topics

cell, dialog box, microsoft access, microsoft excel, microsoft visual basic, microsoft word, real estate, toolbar, window

Events

Microsoft MSDN/Developer Event
25 Mar 2010

One Marina Boulevard, Microsoft Singapore

IT Architect Regional Conference Singapore 2010
20 - 21 Apr 2010

Singapore Management University, Singapore

The Internet Show 2010
21-22 Apr 2010

Suntec Singapore

Microsoft Excel

Copy hundreds of rows of Excel formulas in three steps

Excel provides a number of ways to copy formulas.

You can use Excel's Auto Fill feature or, as an alternative, select the cells you want to copy to, enter the formula in the active cell, and then press [Ctrl][Enter]. These methods are fine for copying formulas down a column of cells--unless the column extends down hundreds of rows.

For example, say Column A in your worksheet lists 100 products and Column B lists the wholesale price of each. You would like to enter a formula that calculates the retail price for each product and displays the results in Column C. Selecting 100 cells is a time-consuming and error-prone process, but you can copy the formula down Column C without having to select the cells.

Follow these steps for foolproof cell copying:

  1. Click in C2 and enter =B2*300%
  2. Press [Ctrl][Enter].
  3. Double-click the fill handle (the small black square in the lower-right corner of the cell).

Excel will stop copying the formula when it reaches a row with a blank cell in Column B. To ensure that you copied the formula correctly, click in C1 and then press [Ctrl][Shift][End].



Microsoft Word

Create a custom number format in Word

In Word, you can quickly number a list by selecting the items and clicking the Numbering button on Word's Formatting toolbar. Word even offers a selection of predefined formats that are adequate for most uses. However, you're not stuck with just the predefined formats.

For example, you might want to print an underscore in front of a numbered question. You see these types of lines on tests, surveys, and questionnaires.

Word doesn't offer a predefined format for that type of list, but you can create it yourself. First, begin as you would any other list by typing the items.

Once you have your list, you're ready to create and apply the custom formatting as follows:

  1. Select all the items in the list.
  2. From the Format menu, choose Bullets And Numbers.
  3. In the resulting dialog box, click the Numbered tab.
  4. Select a numbering option that you seldom or never use, because you're going to modify it.
  5. Click Customize.
  6. In the Customize Numbered List dialog box, click before the number in the Number Format control.
  7. Type several underscore characters and a space.
  8. Click OK and Word applies the custom numbering scheme to the selected items.

You can rearrange the list and even insert new items; Word will automatically adjust the numbering scheme to accommodate your changes.



Microsoft Access

Redocking the Immediate window in Access

The Visual Basic Editor docks the current module and the Immediate window in a center window. It's a nice arrangement that gives just enough real estate to both windows.

Recently, I lost that configuration and I couldn't get it back. I was constantly restoring, resizing, and rearranging the module and the Immediate window. It was terribly annoying.

Access developer Paul Hartland finally came through with the right trick:

  1. Right-click the Immediate window and make sure Dockable is checked.
  2. Double-click the Immediate window's title bar to maximize it. Doing so will redock the Immediate window to the bottom border.
  3. If the Immediate window is too large, simply reduce it by pulling down the window's top margin a bit. As you do, the VBE will also enlarge the size of the open module window. If no module is open, the VBE will adjust the next module to fit in the allotted space above the Immediate window.

With the Immediate window redocked, it and the module windows fit nicely together.

Talkback

Add your opinion

In order to post a comment, you need to be registered. (Sign In or register below)

Post your comment
Transform your business interactions with real-time voice, video and telepresence solutions.
Tech Vendor: Cisco

ZDNet Asia Live

Zdnetasia.com Estimated Worth $178,365 USD. Daily Ad Revenue:$244 USD, Daily Views:81,445 Pages... - http://www.haplog.com/www.zdneta...

recently estimated website net worth of zdnetasia.com - http://www.haplog.com/www.zdneta...

4 hours 10 minutes ago by haplog on topsy

When I create an event, I click on an approximate time during the day when I want the event to occur, then I click "edit event detail...

18 hours 46 minutes ago by bessellbrowne on Google Calendar gets 'smart' rescheduling

ipads break alott i had one it broke three times in the month i had it so i got rid of the damn thing id just go for the laptop Top Grade...

18 hours 48 minutes ago by bessellbrowne on Report: 'Hundreds of thousands' of iPad preorders

There are a number of websites that still require Internet Explorer to view and IE for Mac Stinks (it is really ies4osx which is the Wind...

18 hours 50 minutes ago by bessellbrowne on Microsoft: Only minor tweaks in Windows 7 SP1

The receivers don't transmit back to the satellite. Unless there is a phone line attached to the receiver, they don't have any wa...

18 hours 52 minutes ago by bessellbrowne on Apple to join the geolocation craze?

What to expect from open source Symbian http://is.gd/aPIGL

19 hours 7 minutes ago by rebelk0de on topsy

"Lead Cognos BI Developer Insurance in New South Wales , Australian ..." http://bit.ly/ayy19L

19 hours 47 minutes ago by rhrcognos on topsy

whatever little understanding I have we 'll only progress toward end of the world if we use HPCs to lenthen life of human being. Huma...

1 day 59 minutes ago by abhi32002@gmail.com on High computing promises elixir of life

Thanks for the knowledgeable article on SDDs. Allas...when all this reasearch will happen in Indian Universities. Hope the new bill on Fo...

1 day 12 minutes ago by abhi32002@gmail.com on APAC HPC users eye solid-state drives

It was a good article. This brings a good opportunity for Indian IT firms to come up with new solutions in this field. HPC can become a b...

1 day 30 minutes ago by abhi32002@gmail.com on High computing most-wanted job in Asia

COL KR DHARMADHIKARY(RETD) its very late to reply the link, but if it is still alive and looking for opportunity, i would like to know th...

1 day 28 minutes ago by deb021280 on Education takes off in rural India, helped by PCs

It was just a matter of time until google was marginalised anyway. I'm afraid this will be forgotten in China very quickly. Still, it...

1 day 33 minutes ago by robinsmith on Report: Google to leave China on April 10

High performance computing (HPC) most-wanted job in Asia http://bit.ly/9vFC3i (via @zdnetasia) #singapore

He doesn't care if her shoes are of glass, All he wants to see is a huge rack and nice a*s. Sleeping beauty's not awoken by true ...

1 day 1 minute ago by warlowdavies on One pair of 3D glasses to rule them all

RT @zdnetasia: EMC COO, Pat Gelsinger, on bridging gaps in the organization and its cloud ambitions in Asia. (cont) http://tl.gd/i5jjd

EMC COO, Pat Gelsinger, on bridging gaps in the organization and its cloud ambitions in Asia. http://bit.ly/9etOZW

Asian SMBs need to pay more attention to disaster recovery planning http://bit.ly/bDet08 via @zdnetasia

Asian SMBs need to pay more attention to disaster recovery planning http://bit.ly/bDet08

[TECH] URL Shorteners slow Web redirection. - http://bit.ly/bySnWK @zdnetasia

URL shorteners are great but they can slow web redirection & you pray it would never go down http://bit.ly/bySnWK via @zdnetasia

Temasek Holdings eyeing tech stocks, indicating optimistic outlook on IT sector. http://bit.ly/aM7VwU

URL shorteners slow Web redirection. http://bit.ly/bySnWK

Chinese agencies cry foul over Google. http://bit.ly/by6rwV

Philippine antipiracy drive focuses on enterprises. http://bit.ly/aWryDC

Gartner: China to become world's fastest-growing enterprise software market. http://bit.ly/bqJTtb

all of sg's isps have been practising compulsory invisible proxy for all home subscribers at their backend since many years back alre...

2 days 11 minutes ago by melvinchia on Web filters mean bad news for business

it is not to good for china.
Proactol

2 days 56 minutes ago by nathonastle on Chinese ad partners beg Google for information

RT @zdnetasia: HP touts new products and management and productivity tools to address business computing pain points. http://bit.ly/dudgA6

For those with a computer science background, or interested in the high performance computing scene: http://bit.ly/9vFC3i

IT security insiders rob casinos of $50K http://is.gd/aPIKR

3 days 28 minutes ago by rebelk0de on topsy

Very good explanation of JMX

3 days 1 minute ago by Babith B on Managing applications with JMX

The reaction to a report issued Tuesday by Flurry Analytics managed to completely overlook some interesting news--the Android-based Motorola Droid outsold the original iPhone over the same period of time following their respective launches--to focus instead on the sales numbers for the Nexus One.

3 days 5 minutes ago by lonemavericks on diggs

Another ZTE story....

3 days 7 minutes ago by Moderate Your Greed on Philippines opens bid for final 3G license

We at www.fifosys.com have also seen a growth in IT outsourcing and anticipate it as a growing field.

4 days 40 minutes ago by sarah Jane on Companies' outsourcing spend to increase

Keep up with ZDNet Asia

Linkedin Join our network

RSS Feeds Subscribe now

Newsletters Subscribe today

Twitter Follow us

Inside ZDNet Asia

Special Report: Tech Focus in 2010

Latest Videos on Tech Industry

Insight: Interviews with Newsmakers

Search for over 15,000 Tech Jobs in Asia

Find your Tech answers in TechGuides

IT Library: Over 60,000 IT papers at your fingertips

Download IT Priorities 2010 Report