Microsoft Office Q&A: Ask anything (Word, Excel, PowerPoint, etc.)

Thanks, Kyle Johnson2, it worked perfectly!

You could also try this approach:
=COUNTIF(INDIRECT(G10):C:C;1)

I’m running into a massive headache with my RAM—specifically, I need to figure out how to make this computer actually finish calculating formulas in Excel. Right now, it’s taking hours upon hours, and I have quite a few columns packed with data...

So, I went ahead and asked an IT guy how to speed things up, and his big piece of advice was that I simply don't have enough memory. Fine, whatever—but what does that actually mean in practice? Do I just buy more RAM and slap it in there? And more importantly, will that even touch the problem of these agonizingly slow Excel calculations?

To give you the specs: I’ve got 2 GB of RAM and an AMD Athlon IIx2 250 3.00 GHz processor. If you need any other technical details, just ask.

Or am I better off just biting the bullet and buying a completely new computer? Any advice?

Look, if you were actually running out of RAM, Excel would just hit you over the head with an "out of memory" error message and call it a day. Most likely, your processor is just struggling to keep up because the calculation logic in your sheet is poorly optimized—it’s a classic case of working harder, not smarter. How massive are these tables we're talking about, and what kind of functions are you throwing at them? Excel can crunch millions of formulas in a heartbeat, but things fall apart when you use the wrong functions in a bad sequence, forcing the system to waste energy recalculating a mountain of data unnecessarily. It isn't a hardware issue; it's a structural one.

That’s an excellent point—honestly, I was thinking the exact same thing, that maybe memory isn't even the culprit here. Thank you so much for posting that comment; seeing only one reply after 70 views had me starting to get a little anxious.

So, looking at the first six columns, those are just raw data,

then in the next six columns, I'm generating indices using a formula like LEFT(A2,2)&LEFT(B2,2)... and so on. These indices end up being about eight digits long,

but then in the following six columns, I use the MATCH function (searching the index against a second list from row 2 down to row 500,000—which is locked in with $ signs;0). The formula *should* return the specific row number where the indices match, but instead, it just keeps throwing this #N/A error at me.

It is entirely possible I botched the formula itself—not that copying the formula down to the XY row is the issue—but rather what you pointed out: the fact that it has to constantly scan through every single row of that second list, which just drags on and on forever.

..I’m at a total loss for a faster way to calculate this, because the whole objective is simply to identify matching indices... any ideas for a different approach? For context, one of my sheets is sitting at roughly 46 MB... (though sometimes I just copy and paste values once I don't need the formula anymore to save my sanity)

The number of columns isn't actually the biggest issue here; it's more about how many rows you're dealing with and whether you've got a bunch of empty ones cluttering things up while functions run for no reason. If your spreadsheet is just a messy pile of overlapping formulas, then yeah, it’s no wonder everything is dragging. String functions aren't exactly known for being lightning-fast either.
If you're looking at massive datasets, this kind of task really screams for an SQL database. Honestly, any database would crush this job way faster. That's where the logic comes in—Excel acts like a giant collection of tiny little calculators, one for every single cell. On the other hand, actual code running through a loop over indexed key fields would breeze through those tables step by step.
I think you might have hit that wall where you need to stop using "quick fix" user workarounds and start moving toward actual programming solutions. At that point, RAM isn't even the main concern; databases use indexes and load data in "pages," so the code itself stays pretty lightweight on memory.
I'm guessing you handled this the "manual" way, just dragging and dropping formulas down a mountain of cells? I wonder how much of a boost you'd get if you swapped those worksheet functions for some VBA code instead. There are people on Reddit who are pros at this stuff—maybe check in with Kyle Johnson2 over on the Microsoft Office thread...

Angela Bailey7 said:In the first six columns, there’s data,

then in the next 6 columns, I've got indices created using formulas like LEFT(A2,2)&LEFT(B2,2)...and so on—these indices end up being about 8 digits long.

Then, in the following 6 columns, I’m using the MATCH function (matching the index against indices from another list—rows 2 through 500,000, fixed with $)—the formula should return the row number where they match, but instead, I just get this annoying #N/A error.

Look, you really need to write out the formula you're using in those other 6 columns—I'm not quite following how you're generating that 8-digit number or where exactly the data is pulling from.

MATCH is actually a pretty lightweight function—way lighter than something like VLOOKUP, for instance—so combining it with the INDEX function might actually speed up your searches. Just paste the exact formula you've got in there.

As for that #N/A error—that usually pops up when you use quotation marks for a lookup value in a MATCH syntax but the system is actually looking for a numeric value. Check your formula: if you're searching for a number, don't wrap the lookup value in quotes—those are strictly for text strings.

Match(4295;...
vs
Match("Peter";...

We really need to pin down where the bottleneck is—maybe try calculating it step-by-step to see exactly where things fall apart when the input data changes.

Benjamin King2 said:Come on, just write out the formula from those other six columns—I’m not quite following how you’re generating that 8-digit number or where exactly the data is being pulled from.

The data in the first six columns (separated by commas here):

1,2,3,4,5,6
10,11,12,13,14,15,

It’s using a LEFT join to merge them into a single index like 123456 or 101112131415—and since the index isn't always a single digit, that 8-digit figure can actually swing anywhere from a minimum of 6 to a maximum of 12 digits, though I suppose that isn't strictly relevant... LEFT(A2,2)&LEFT(B2,2)&LEFT(C2,2)&LEFT(D2,2)&LEFT(E2,2)&LEFT(F2,2)...and then you just drag that formula all the way to the end.

Now, MATCH is a pretty lightweight function—certainly more so than something like a VLOOKUP—so it might even be worth pairing it with an INDEX function to speed up the search process. Go ahead and provide the exact formula being used there.

Fine, here is the specific formula: MATCH(J2,(Data_Sheet)!$J$2:$J$449999,0)...which basically means I want the index from cell J2 to find a match within a massive range on another sheet, stretching from row 2 all the way down to 449,999...it works, sure, but updating this thing takes a literal eternity...by "Data_Sheet" I mean the name of the second document, and note that I'm using square brackets there, not my standard parentheses.

As for that #N/A error, it tends to pop up when you use quotation marks for the lookup value in a MATCH syntax while searching for a numeric value. Take a close look at the formula—if you are looking for a number, the lookup value shouldn't be wrapped in quotes; those are strictly for text strings.

....I am not using quotation marks anywhere in the formula.

Match(4295;...
vs
Match("Peter";...

We really need to pinpoint where the bottleneck is occurring—perhaps we should break the calculation down into steps to identify exactly where the lag happens whenever the input data changes.

...the primary data in those first six columns remains constant.

I'm designing some flyers, and I set everything up in Microsoft Word so the contact info sits right at the bottom edge. But every time I hit print, the software pushes those lines up, leaving this massive gap of dead space at the bottom.

How do I fix this glitch?

James Thomas80 said:I'm working on some flyers and set everything up in Word so the contact info sits right at the bottom edge. But every time I hit print, it pushes those lines up, leaving this weird gap at the bottom of the page.

How do I fix this glitch?

Fixed it.

Excel 2007, 2013.
I'm dealing with a measurement issue involving millimeters. If the value is under 2000 mm, I need it to round up to 2000 mm. If it's over 2000 mm, it should round up to 4000 mm. Essentially, I want everything rounded up to the nearest 2000 mm increment once it passes that first threshold.
Any help would be appreciated.

restlessheron19 said:If the value is under 2000 mm, round it up to 2000mm. If it's over 2000 mm, round it up to the next 4000 mm increment. Basically, just keep rounding by increments of 2000 mm from there.
Need some help here

If your data is sitting in column A1:A??, try dropping this formula into B1
Code:

Kyle Johnson2 said:If your data is sitting in range A1:A??, give this formula a shot in cell B1:
Code:

=IF(A1="";"";IF(A1<2000;2000;IF(A1>=2000;(ROUNDDOWN(A1+2000;-3));"")))

Thanks, John
🙂

Why doesn't microsoftstore.com show that green indicator in the address bar to prove the site's identity is verified?

Hey there
I could really use some help with Access

Rebecca Chase62 said:Hey there,
I could really use some help with Microsoft Access

Man, you’re being so vague I don't even know where to start helping you. 🙂

Give me some actual details. What's the specific issue? Walk me through it, describe the error, maybe throw a screenshot my way or something. 😉

Hey everyone, I am seriously struggling here—I can't for the life of me figure out how to embed a song into my PowerPoint presentation so that it plays continuously across all the slides while the slides transition automatically. 😢 I can manage to get one or the other working, but getting them to play together is proving impossible.
What am I doing wrong?

Hey there,
I could really use a hand here.
Does anyone happen to know how I can take something from a cell—say, cell A has 111/222/333—and then somehow split it up so cell B gets 111, cell C gets 222, and cell D gets 333, all while keeping the original info in cell A just the way it is?

thanks!

Can you do it like this?

In B1: =LEFT(A1,3)
In C1: =MID(A1,5,3)
In D1: =RIGHT(A1,3)

Does anyone know if there's an option in Word 2007 where a little pop-up box appears with a text note whenever you highlight a specific section of text?

I'm looking for something similar to how a footnote works—where the text from the footnote pops up when your cursor hovers over the insertion point—but I want to achieve this without actually using a formal footnote, if that's even possible.