Excel Experts, change numbers automatically

Factual Questions
1

I am working on a database project for a client.

They have a pricing list that we need to increase by 10% across the board.

Is there a way i can highlight all the prices and increase them all at once without having to change every one of the 2000+ items individually.

Thanks!

2

Is the list in an Excel sheet, or a database?

3

In an excel sheet. it is basically like this:

Canned Corn $0.56
Canned Yellow Corn $0.55
Canned White Corn $0.57
What i want to do is just highlight the pricing column and multiply it by 1.1 to add the 10%

4

If in a database, run this:
UPDATE [table_name]
SET [price_field] = [price_field] * 1.1
Replace names in brackets, of course.

If in Excel, set up a blank column next to the price, and enter “=A2*1.1” where A2 is the address of the adjacent price cell. Then drag the formula to the bottom of the sheet.

5

Then you can copy all of the cells your formula produced and paste them on top of the original if you’d like.

6

You can do this by Copy and Paste:

  1. Type 1.1 in an empty cell, then Ctrl+C to copy it to the clipboard
  2. Select all the cells you want to modify
  3. Do Paste Special -> Multiply
7

You can create a macro. Press Alt & F11, Select Insert=>Module. Copy and paste the below code there.

Sub Increase()
Dim r As Range
Dim cell As Range
Dim s As String
Dim t As String
Application.ScreenUpdating = False
Set r = Range(“A1:A3000”)
s = 1.1
For Each cell In r.Cells
cell = cell * s
Next cell
End Sub
Obviously you can change the range and multiple if you need to. Then go to the view tab=>macros=>view macros. Select the macro called “Increase” that you just created. It will multiple everything in the range you selected by 1.1 (adding too the number 10%).

8

You guys are amazing thanks!

Saved me hours of work!

9

Anytime you’re looking at hours of repetitive computer tasks, there’s always a better way.

10

I consider myself a pretty advanced Excel user, but this trick is new to me. Thanks!

11

Yes! this is THE answer

12

New to me too, and I teach Excel. I’m adding this to my class topic on Monday. Thanks!

13

Thank you! There have been so many times when I’ve needed this. I knew there had to be a way!

14

I have to use Excel, OpenOffice, LibreOffice and Google Drive and this is also the first time I hear about this.

I can confirm that that works also in LibreOffice and OpenOffice Calc, but not in Google Drive Spreadsheet.

15

This also works beautifully when numbers stored as text need to be converted to numbers. Just use 1 instead of 1.1

16

Also, I’d urge the OP to take an Excel class at some point. There are about 10 different ways I can think of off the top of my head that would do what he’s looking for, quite easily - and none of them involve hours of work. Spreadsheets are set up for this type of thing. Just from the OP’s description, I’d guess that even simple dragging of a cell to populate cells below/across from it would be new material.