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

I could use some help with this:

I’ve got a spreadsheet with these headers:
Client Type Service Price

My goal is to pull specific clients based on certain price brackets—basically everyone under $333 for a specific service (there are about 50 of them). It would be great if I could filter by Service Type too, though I could just manually sort the types since there are only four.
The sheet has over 100K rows.

I thought I could knock this out using a VLOOKUP combined with an IF statement, but I definitely bit off more than I can chew here. 😁
It’s just not working. Help.

I tried messing around with a Pivot Table, but the layout gets messy because of the sheer number of services. Plus, it sorts everything alphabetically, which makes the numerical order a total headache.

Thanks in advance.

Honestly, you might be better off just using a standard filter for that—you can set specific criteria for both the price range and the type all at once.
Once you've got that sorted, you can just do a quick copy and paste values.

Andrew Cruz3 said:I could really use some help with this:

I've got a spreadsheet filled with these values
Client Type Service Price

I'm trying to pull out specific clients based on price categories—like everyone with a price under $333 for a specific service (there are about 50 of them). It would be awesome if I could also filter by Service Type, though I can just manually split up the types since there are only four.
The table has over 100K rows

I thought I could knock this out using a VLOOKUP formula combined with an IF statement, but I definitely bit off more than I could chew 😁
It's just not working. Help!

I messed around with a Pivot Table too, but it makes the layout a mess because of the huge number of services. Plus, it sorts everything alphabetically, which totally messes up my numerical flow.

Thanks in advance!

Try using Advanced Filter (Microsoft Office 2003). If you're using LibreOffice Calc, the Standard filter works perfectly for this.

I don't think filters are going to cut it here.😬

I'm looking at a dataset with about 4,000 entries, and I need to pull out specific ones where the price for a particular service stays under a certain threshold, X. The problem is, I can't figure out how to set up the filters to isolate data based on three different parameters:
price
service category
service type

It gets messy because more than 50 different services—each with its own price tag—are all lumped into a single column.
Some services consistently dip below my target price, but those aren't the ones I'm actually hunting for.

I can't just paste the spreadsheet here since it contains sensitive information.

You apply a specific filter condition to every single column individually.

Take the price column, for example:
You click that drop-down menu that pops up once you've enabled the filter, navigate to Number Filters -> Less than..., and type in 1000. You'll see just how many combinations you can pull off—different ranges, all sorts of things. Now, everything under $333 is filtered out. Moving right along.

Now, for the service category:
You can either just check the box for the specific category you want, or you can go to Text Filters -> Equals... and manually type in the service name. At this point, you're looking at only that specific service, and everything below is $333.

As for the service type:
It works exactly the same way as the category. So now, you have everything filtered by category, type, and price simultaneously.

The bottom line is that you can set whatever criteria you want for every individual column, and in the end, you’ll land exactly on what you were looking for:
the specific service categories, a certain type, and ensuring they fall under $333

Andrew Cruz3 said:I honestly don't think filters will work here 😬
...
since I have over 50 different services—each with its own price point—all crammed into a single column.
Some services consistently stay under a certain price, but those aren't the ones I'm looking for right now.

I can't really upload the spreadsheet here because it's got some pretty sensitive data in it.

You actually can make it work using Advanced Filters. Take a quick look at the tutorials on this page. Basically, you can set multiple criteria for the same column using "OR" logic, or specify exactly which services you want to include or exclude, along with price conditions for specific items. Just remember the golden rule: items on the same row follow "AND" logic, while items on different rows act as "OR" logic.

Andrew Cruz3 said:I'm a bit uneasy about posting the spreadsheet since it's got sensitive info

Why? Just take my advice.
Just leave maybe 10-20... 50 rows, then wipe out all the extra junk you don't need.
Swap out anything sensitive. Use Find/Replace on names or company titles to just turn them into random letters, round those values down to some two or three-digit numbers, or just rename the columns to something super basic. As long as the structure stays the same, you're good.

Set up a few different versions of the services or whatever key elements you're trying to troubleshoot. Put the raw data you need help with on one sheet, then make a copy of that sheet to show what the results *should* look like—maybe throw in a comment or some text in a cell here and there. That’s it. It’ll be way easier for people to help you, and you'll get an answer faster. Otherwise, we could be posting back and forth forever and still might not crack the problem.

Hey everyone, I could really use a hand here... It's regarding Word. I'm working on my thesis and need to insert this massive table, but I need it to be in Landscape orientation. Basically, I need to flip just two specific pages within my Word document to Landscape so the table actually looks readable. I know I've done this before, but it totally slipped my mind!

It's not about the actual topic, just a technical question about Word, so I didn't want to start a whole new thread. I'm using Word 2007...

Joshua Wells3 said:So, we're talking about a thesis here. I need to drop a massive table into Word, but it needs to be in Landscape mode.

Look, since this is for a thesis, you really ought to be using Word styles. It makes building an automatic TOC or table of contents way easier, plus keeps your page numbering from turning into a mess. To mix orientations in one file, you've gotta use section breaks to separate a portrait of landscape. Some of these links point to Microsoft Office 2003, but they work just fine for Word 2007—just look for the commands in a different spot. You could also just build the table normally and rotate it 90 degrees. If you hit a wall, just post here. 😉

frozenheron7 said:If your new price is just "price times area," then you should probably just use this formula:
=IF(A2<>0;A2*B2;"")

-> This works for your second row—assuming, of course, your headers are in the first row. Just drag that formula down through all 100 rows by grabbing the little black square in the bottom-right corner of the selected cell.

Bradley Murphy56 said:Here’s what I’m looking at:
Column B contains prices, while column A holds the area. Column B is filled with values from row one to row one hundred, and there's a total price at the bottom. Column A isn't fully populated because some rows have a value of zero. Now, I want to know how to make a specific cell (let's say in column C) calculate only those prices where an area is actually entered—not the grand total, since the total is already sitting at the bottom.

Is that even possible?

Well, since that answer didn't exactly hit the mark, let's try to simplify things a bit.

Suppose we put these numbers in column A: row one is 1, row two is 0, row three is 3, row four is 0, row five is 5, row six is 0, row seven is 7, row eight is 0, row nine is 9, and row ten is 0.
In column B, let's put 10, 20, 30, 40, 50, 60, 70, 80, 90, and 100 for the first ten rows.
What I need is to sum only the values where the corresponding entry isn't zero. In this case, the result should be 250. But how on earth do I get the software to actually calculate that?

Bradley Murphy56 said:Well, that last answer didn't quite hit the mark, so let me try breaking it down a bit more simply.

If I put some numbers in column A—say, row 1 is 1, row 2 is 0, row 3 is 3, row 4 is 0, row 5 is 5, row 6 is 0, row 7 is 7, row 8 is 0, row 9 is 9, and row 10 is 0...
And then in column B, from rows 1 through 10, I have 10, 20, 30, 40, 50, 60, 70, 80, 90, and 100.
What I really need to do is sum up only the values in column B where the neighbor in column A isn't zero. So, looking at those numbers, the total should be 250. How do I get Excel to actually do that calculation for me?

Formula:

Well, there you have it. That's the whole story.

I'm trying to get my footnotes to appear all together at the very end of my entire document—rather than having them tucked away at the bottom of every single page where the number actually shows up... Does anyone know how to pull that off? I'm working in Word 2010.

I’m hoping I can manage to get my point across clearly enough this time... 🙂

So, here’s the situation—I have an Excel spreadsheet, just a standard one, and I want to add a little shortcut right next to a specific name—you know, like those blue hyperlinked web addresses www.... My goal is to click that link and have it immediately open a specific Word document for me.
For instance: Name: John Doe (in Excel) — Shortcut: Contract

Essentially, I have the names listed in the table, and right next to them, I want a clickable link that jumps straight to the corresponding contract saved in Word...
Is this actually doable, and if so, what’s the best way to go about it? 🙂

Thanks in advance...

Daniel Cook4 said:I'm really hoping I can finally manage to get my point across clearly this time. 🙂

So, I've got this classic Excel spreadsheet going, and I'm looking to add a little shortcut right next to a specific name—you know, kind of like those blue little icons or abbreviations you see sometimes. Any tips? www..I'm trying to get it set up so that when I click on them—you know, those shortcuts—it just opens up a specific Word document right away.
Example: Name: Joe Smith (Excel) - Shortcut: Contract

So, I've got this spreadsheet where I have names listed, and right next to them, I want a shortcut. Basically, when I click that shortcut, I want it to jump me straight over to the specific contract saved in Word. Any tips on how to set that up?
Is that actually possible? And if so, how do I go about doing it? 🙂

Thanks in advance!

To insert a hyperlink, just hit Ctrl+K. You can type out whatever text you want people to see, then simply pick the specific file you want to pop up when someone clicks that link.

Jack Hayes said:Just go to Insert -> Hyperlink, or hit Ctrl+K... type out whatever text you want people to actually see, then just pick the specific file you want to pop up when they click that link.

It really is that straightforward... 🙄

Thanks, this is going to be a huge help for me...🙂

I just set up a new Windows 7 machine along with Office 7.

The issue is that Word won't run any grammar or spell checks for me. I've tried everything under the sun—searching online, downloading all sorts of service packs—but nothing seems to stick.

I keep getting this error message saying, "Cannot find proofing tools for English."

Is there a kind soul out there who might be able to point me in the right direction? If you can't share it here publicly, maybe you could just send me a link via PM. I've been searching through forums and old threads about this exact headache, and I know the moderators get prickly about sharing software links, but I'm honestly at my wit's end because I'm practically living in Word to get my work done. 🙂

I'm messing around with a presentation in PowerPoint 2003 and for some reason—I have zero clue why—the accented characters just vanish whenever I switch the font over to Times New Roman. Anyone got a clue what's going on?

Question: Is there a way to align numbers by the decimal point in Excel? I have a list of values stacked vertically—each one sitting in its own merged cell—and currently, they are just centered.
Aligning everything to the right doesn't work for me because it ruins the aesthetic layout of this specific template I'm building. 🙂

Thanks in advance. 🙂

graniteranger81 said:Question: Is there any way to align numbers by the decimal point in Excel? I have a list of numbers stacked vertically, each sitting in its own merged cell, and they're currently centered.
Right-aligning them just doesn't work for the specific look and feel I need for this form template. 🙂

Thanks in advance. 🙂

Yes 🙂
Use Yes format Cells -> Accounting. By default, I think it’ll slap a currency symbol in there too, but you can just set that to "None" if you don't want it.