Batch-change value of thousands of cells in Excel sheet

adminMicrosoft0 Comment
6 min. reading

There are milion of ways on modifying many cells’ value in single pass using… formula. But, what about a situation, when you need to perform only a single one-time bulk-change of value in many Excel’s cells?

I had a situation when I was preparing some IT project’s budget. Since I am an IT geek then writing amounts in thousands like 1,30k USD was no problem at all. However, this solution had two falls:

  • Some accounting freak, having nothing to do with IT, had problems understanding that “k” means thousands
  • Cells itself had to use some custom formatting of ##0,00k USD in order to add that damn “k”

Thus, I had to do a one-time recalculation of most of cells in my sheet to multiply their value by 1000.

Overview

There are many ways of doing this in Excel, including:

  • Using Flash Fill (Excel 2013 and above)
  • Using some formula: one or another
  • Using helper column
  • Using VBA script

To my somewhat surprise the fastest and the cleanest way was to use VBA. Just for one time shot.

Preparation

Since this was a one-time dealer, I had to:

  • Temporarily changing type of my file int macro-enabled *.xlsm file
  • Enable Developer tab in Customize the Ribbon dialog
  • Adding and executing simple macro
  • Saving modified file again in macro-free sheet of *.xlsx

In this particular task I had to change (recalculate) 400 cells’ value on two sheets in a single workbook. The entire task took me less than 10 minutes (less than 20 minutes, including writing this post in middle, as usually :). Far, far less in my opinion than doing this 400-cells change manually.

Adding macro

For some reason I hadn’t got Developer tab enabled, so enabling it was my first step:

Then it was as simple as:

  1. Clicking Visual Basic (first button) in Developer tab
  2. Double-clicking ThisWorkbook in VBAProject > Microsoft Excel Objects in the Visual Basic for Applications window
  3. Pasting the below simple macro into opened window
  4. Saving changes and closing the Visual Basic for Applications window

Macro

The macro itself is a very simple modification of this Stack Overflow’s answer and it looks like:

Sub MultiplyByThousand()
    Dim r As Range
    For Each r In Selection
        r.Value = r.Value * 1000
    Next r
End Sub

All I had to do now was:

  1. Select some range in sheet
  2. Click Macro (second button) in Developer tab
  3. Select ThisWorkbook.MultiplyByThousand macro
  4. Hit Run

Over and over again until all required cells were recalculated.

Of course that I could select them all with holding Ctrl button and creating many non-standard selection areas. But, since I don’t have too much experience with VBS, I wanted to do this step-by-step.

Let’s just say that I am cautious.

Things to remember

If your range contains empty cells then it will be threaded as 0 and displayed as 0 after recalculation.

Modify the code to avoid this:

Sub MultiplyByThousand()
    Dim r As Range
    For Each r In Selection
        r.Value = r.Value * 1000
    Next r
End Sub

If your range contains any formula then it will be stripped-off, taken as number, recalculated and lost!

Of course, if you run this particular macro more than one time then the value in cell will be consecutively multiplied by thousand! :) There is not checking to assure that macro will pass over a single cell only once. Use with caution.

Clean-up

Since this was just a one-time dealer and I had no intention in keeping this macro alive, after all the dirty work was done, all I had to do to “clean-up” was to save modified workbook back under *.xlsx extension, effectively stripping off this macro and making it macro-free again.

An alternative

My friend, who read this post, told me about easier and ultra-fast way of using Paste Special + Multiply.

It goes like this:

  1. Type 1000 in any empty temporal cell
  2. Copy it (Ctrl+C)
  3. Select range, in which you want to change values
  4. Select Paste Special / Values / Multiply, optionally checking Skip blanks
  5. Click OK to confirm
  6. Watch the magic happening
  7. Remove 1000 from temporal cell

And… you’re done.

You can do this even faster, with your eyes closed, by using keyboard:

  • Ctrl+Alt+V
  • V, M and optionally B
  • Enter

This way is not only thousand times faster, but it is also fully reversible by Undo.

You can, of course, always use the macro-way when you need to batch-modify many cells while doing some string-level changes, as mentioned in the original Stack Overflow question.

Leave a Reply