• About Geek Beat
  • Newsletter
  • Advertise
  • Contact
  • Login
  • Facebook
  • Google+
  • Instagram
  • RSS
  • Twitter
  • YouTube

Geek Beat

One of the world’s most recognizable technology news brands, delivering daily tech news, tips, and reviews.

  • News
    • Apple
    • CES
    • Editor’s Choice
    • Google
    • How To’s
    • Microsoft
    • Music
    • Mobility
    • NABShow
    • Photography
  • Episodes
  • Reviews
  • Videos
  • Connect With Us
    • Chat Rooms
    • DropCams
    • Geek Beat Live!
    • Product Review Submissions
Home > News > How To Keep A Constant In Excel And Other Spreadsheets

How To Keep A Constant In Excel And Other Spreadsheets

April 19, 2012 By Scott Ellis

When creating a formula in Excel or most other spreadsheet programs we sometimes need one of the values to be dynamic (e.g. we can change it in one place and effect everywhere it’s referenced) but stay constant when we replicate that formula across rows or columns.

To keep a constant value in Excel use the following steps:

  1. Create a cell with the constant value you want to reference
  2. Create a formula in a cell that performs your calculation
  3. In the formula where you reference the value you created in step 1, add a “$” before the letter (representing the column) and number (representing the row).

Example of Keeping A Constant Value in Excel

We have a cell, let’s say cell A1 which just contains the text “Interest Rate” which is simply a label so we know what we’re dealing with.

Next, we have a cell B1 which contains the actual interest rate, we’ll represent our percentage as a decimal so 10% becomes .1.

Now below that we have three columns:

  1. Principal Amount
  2. Simple Interest
  3. Total (Principal Amount + Simple Interest)

in A3 we put $1000, in A4 we put $2000 then highlight both cells and grab the bottom right corner and drag down for 8 more rows so you now have values $1000 – $10,000.

Next in B3 we enter a formula to calculate interest by multiplying our first value ($1000 in cell A3) by the interest rate (.1 or 10% in B1).

=(A3*B1)

The cell should show $100.


Now, as with creating the 10 rows in increments of $1000, we can grab the bottom right corner and drag down. The problem is that both references will change cells but we only want the first one (principal amount) to calculate from the next cell down (A4, A5 and so on). we want the interest rate cell to always be calculated from B1 (not B2, B3, etc… as the spreadsheet will naturally assume).

So to fix this we add the “$” before the Column letter and Row number for our interest rate cell changing the formula in B3 to now look like this:

=(A3*$B$1)

Now when we grab and drag the interest rate will always be calculated from the value in B1.

Finally, just to round things out, lets do another forumla in C3 to add Principal to Interest using the simple formula =(A3+B3) in C3.

Now grab and drag C3 down and you’ll see the increments increase and we now know how much simple interest we’ll make in a year on $1000 – $10,000.

Want to see things change? Modify the .1 in cell B1 to .05 (5%) and everything will change across the board.


What are your favorite excel tips and tricks? Need help figuring out something in a spreadsheet? Hit me up on twitter @vsellis or google+ at gplus.to/scottellis.

Learn something new everyday!


Filed Under: News, Tutorial Tagged With: how-to, Productivity, Tip a Day, tips

About Scott Ellis

Scott Ellis: is the founder of vsellis.com web design, and hyperlocal publisher of the leading online media resource for Frisco, Tx. Connect with Scott on Instagram, Google+, or twitter @vsellis.

Comments

  1. Matthew McGarity says

    April 24, 2012 at 4:37 pm

    This stuff is slick — I could spend *all* day talking about cool Excel stuff. Keep it up!

  2. Tom says

    April 20, 2012 at 12:52 pm

    Didn’t even notice you were on a Mac… I believe the Mac Shortcut for F4 is Cmd + T but I don’t do Excel on a Mac 😉

  3. Daniel says

    April 19, 2012 at 4:49 pm

    You could also name the cell.
    In your exemple, if you change the name of the cell from B1 to INTEREST
    Your formula would then be =(A3*INTEREST) wich has the same effect.

    • Scott Ellis says

      April 19, 2012 at 4:53 pm

      Daniel,
      Excellent tip! Thank you!

  4. Tom says

    April 19, 2012 at 1:14 pm

    No mention of the F4 key to toggle the $ sign, what’s up with that?

    • Scott Ellis says

      April 19, 2012 at 4:52 pm

      Tom, I’m guessing that’s PC only. Doesn’t seem to work on my Mac unless it’s a different key stroke. Good suggestion though.

Popular Posts

  • How To Keep A Constant In Excel And Other Spreadsheets by Scott Ellis | posted on April 19, 2012 | under News, Tutorial
  • How to Use Your Own Router with AT&T U-verse by Chris Bramble | posted on February 17, 2014 | under News, Tutorial
  • The Complete Guide to Installing A PV (Photovoltaic) Solar Panel System by John P. | posted on September 9, 2013 | under Episodes
  • How to Access Advanced Boot Options in Windows XP, Windows Vista, Windows 7, and Windows 8 by Robert Albury | posted on September 10, 2014 | under Tutorial
  • GSM, CDMA and LTE: A Guide to Mobile Network Standards by Benjamin J. Roethig | posted on March 4, 2013 | under News
  • How To Add Sound and Voice to Your Photos by giovanni | posted on July 22, 2013 | under Episodes
  • Tutorial: Configuring a QNAP Multimedia Station by Dexter | posted on March 28, 2014 | under Tutorial
  • Resetting the QNAP TVS-463 NAS by Robert Albury | posted on November 5, 2015 | under Tutorial
  • How To Redirect Windows User Profile Data to a Different Location by Robert Albury | posted on November 14, 2014 | under Tutorial
  • How Much Money Will Fit in a Briefcase? by John P. | posted on September 26, 2013 | under Episodes

Recent Posts

  • Go Mic Mobile Wireless System for Mobile Filmmakers
  • A RAY OF LIGHT
  • Keep Devices Charged Anywhere in the World with the Twist Plus World Charging Station
  • Final Leaked iPhone 7 Details Before The Big Event
  • Should Snapchat Be A Part of Your Digital Strategy?

Recent Comments

  • Ricardo Garza on Streambox Announces 360 Live Video Streaming to YouTube
  • Sunbya on Review: Blue Lola Headphones bring unparalleled quality
  • Jay Anderson on CES2016 – Autel Robotics X-Star Quadcopters
  • Scott Ellis on CES2016 – The Parrot DISCO – The Worlds First Fixed Wing Consumer Drone
  • Ricardo Garza on CES2016 – The Parrot DISCO – The Worlds First Fixed Wing Consumer Drone

Tags

3D Amazon Apple apps audio bluetooth business CE Line Shows 2013 CES computers fitness gadgets games gaming geek geek beat GeekBeat geek house Google health how-to ios john p. kickstarter Microsoft mobile Mobility Music nabshow new building Other photography Reviews robots Samsung social media speakers storage tech technology travel Tutorial vehicles video vlog

Copyright Livid Lobster, LLC · website design by @vsellis
search & social media consultant, giovanni gallucci