• Products
    • Sage Intelligence Reporting
      • Enrolment
    • Velixo
      • Enrolment
        • Velixo for Acumatica
        • Velixo for Sage Intacct
  • Services
    • Consulting
    • Training
    • Development
  • Support
    • Support Overview
    • Create Support Ticket
    • Book A Session
    • Knowledgebase
  • About
    • About Us
  • Contact
  • Products
    • Sage Intelligence Reporting
      • Enrolment
    • Velixo
      • Enrolment
        • Velixo for Acumatica
        • Velixo for Sage Intacct
  • Services
    • Consulting
    • Training
    • Development
  • Support
    • Support Overview
    • Create Support Ticket
    • Book A Session
    • Knowledgebase
  • About
    • About Us
  • Contact
Quick Support
Book Demo

How to reduce the size of a workbook using a single formula

By admin on May 30, 2018April 19, 2019

Sometimes, large Microsoft Excel workbooks can be slow and inefficient—which could cause frustration.  To reduce the size of a workbook, consider replacing several formulas with a single array formula. Array formulas can perform multiple calculations and they also ensure consistency, which can lead to greater accuracy.  Furthermore, array formulas provide for more security, since a cell within an array formula data range cannot be deleted.

In this tip, we show you how to do this.

You are welcome to download the sample workbook to practice this exercise.

1. Select the data range D2:D21.

2. Type the formula =B2:B21*C2:C21 in the formula bar.

Note: The data range D2:D21 should remain highlighted as you enter the formula in the formula bar.

Refer to the screen shot below:

tip-1

3. Press CTRL + SHIFT + ENTER.

The result will be as below:

tip-2

4. When you select any cell within the data range D2:D21, you will see the same formula {=B2:B21*C2:C21}.

  • There is only a single formula in the entire data range, instead of 19 different formulas; which would have been the case had we multiplied B2*C2 and copied the formula down.
  • The file size is thus reduced and data processing in the workbook will be faster.

5. Select any cell within the data range D2:D21.

6. Press Delete.

A message dialog like the below will be displayed:

tip-3

7. For normal formulas any cells within the data range D2:D21 can be deleted.

8. To delete an array formula, the entire data range will have to be selected and then you press the ‘Delete’ key.

9. To edit the formula, select the data range D2:D21. Edit from the formula bar then press CTRL + SHIFT + ENTER.

By doing this, you’ll be reducing the size of your workbook and save on the amount of time it takes to run it out.

The post How to reduce the size of a workbook using a single formula appeared first on Sage Intelligence.

Source: Excel on Steroids

Post navigation

How to create a Hierarchy SmartArt Graphic in Excel
How to calculate the average of the top 5 values

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • Why Working In Excel Works
  • Alchemex Welcomes Velixo 6 For MYOB Advanced
  • Welcome to Velixo Reports Version 4.2
  • Part 3 of 3: Learn how to create a Management Pack in Sage 50cloud Intelligence
  • Part 3 of 3: Create and customize reports easily with the Financial Report Designer

Search

Contact Us

Phone: +61 2 9890 2339
Email: sales@alchemex.com.au
Address: PO Box 212, Deaken West, Canberra, Australia, 2600

Products

  • Intelligence Reporting
  • Velixo Reports

Services

  • Consulting
  • Training
  • Development

About

  • About Us
  • Partners
  • Blog

Support

  • New Ticket
  • Community (Coming Soon)
  • Knowledgebase

Phone: +61 2 9890 2339
Sales: sales@alchemex.com.au
Support: support@alchemex.com.au

Facebook-f Linkedin

© All rights reserved Alchemex Pty Ltd 2020

Made with ❤ by Unfazed Ltd