Ask Your Question

Efficacy of storing text or referring to text

asked 2021-04-24 16:09:52 +0200

White Knight gravatar image

I was pondering upon the relative merits of filling cells with repeating text as opposed to referring to single instances of the text in multiple cells.

My structure requires a sequence of 8 pre-defined (up to 15 characters) text cells to be repeated in a column. The pattern is then replicated for hundreds of sets.

Set 1 event 1, event 2, event 3 ~ event 8

Set 2 event 1, event 2, event 3 ~ event 8

Each event has 16 Calculated cells on its row.

Is there a noticeable difference between the text being physically present in the cell as opposed to having the cell series refer to a single instance of the text series? viz. =A$1, =A$2, =A$3 - =A$8 then repeating =A$1, =A$2, =A$3 - =A$8

In terms of volume, with just 2000 rows, the text requires 20000 bytes whereas the formulae characters require only 8000 bytes.

Is there a trade-off between the volume of data in memory and the processing of calculations/referrals?

Is a VLOOKUP() more efficient than a direct reference or is the lookup array too small to be significant?

Is the relatively small number of rows from the potential 1048576 rows too small to be significant?

Version: (x64) Build ID: 64390860c6cd0aca4beafafcfd84613dd9dfb63a CPU threads: 4; OS: Windows 10.0 Build 19042; UI render: Skia/Raster; VCL: win Locale: sv-SE (en_GB); UI: en-GB Calc: threaded

8GB Ram, i5 3.4GHz

edit retag flag offensive close merge delete


Are you interested in research? Read this book. For LO Calc, it will also work. There's a lot of optimization and timing. The book can be found in the public domain.

eeigor gravatar imageeeigor ( 2021-04-24 18:34:16 +0200 )edit

Spot on - Many thanks for your efforts

White Knight gravatar imageWhite Knight ( 2021-04-25 10:17:14 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2021-04-24 16:44:59 +0200

erAck gravatar image

Identical strings in text cells are implemented as shared strings with the actual string being only one copy, and text cells have less overhead than formula cells and formula cells may need to be recalculated, so in general using literal text is better in this constellation.

I don't understand your question regarding VLOOKUP() here, lacking context.

edit flag offensive delete link more


VLOOKUP() was simply a reference to looking the text strings up in a 2x8 array where 1 = "key" to text 1, 2 = "key" to text 2, etc., as opposed to the direct references =A$1, = A$2. I believe I read somewhere that a defined table array with VLOOKUP() is more efficient than individual cell references but I wasn't convinced a 2x8 array would offer anything significant even if I had remembered correctly. Your explanation concerning the string being only one copy clearly defines the VLOOKUP() potential as a novice's pipe-dream.

White Knight gravatar imageWhite Knight ( 2021-04-24 17:05:15 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2021-04-24 16:09:52 +0200

Seen: 24 times

Last updated: Apr 24