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

Wait, you're telling me this column actually has 110,000 rows? Seriously?

Hmm... If you're looking to fill an entire column like that,
just right-click the cell you want to duplicate > copy > then right-click the header of the column itself > paste.

Ethan Miller3 said:how to apply this to the whole column

First, just click on cell D1 where you've already got your formula set up.

You should see this tiny little black square sitting right in the bottom-right corner of the cell border for D1.

Just double-click that little box.

The formula ought to automatically fill down through all the rows—well, as far down as there is actual data present in the adjacent columns.

It worked! Finally fixed it.
Man, what can I even say?
YOU ARE A TOTAL LEGEND, MAN!!! Seriously, you crushed it. 👋

Thanks a million, everyone. 😳

Ethan Miller3 said:BRAVO MASTER, YOU ABSOLUTE LEGEND!!!👋

Thanks, though I think you’re laying it on a bit thick 🙂.

The thing about Excel is—well, it's almost too easy—just a couple of clicks and suddenly it looks like magic... 😁

Ethan Miller3 said:I tried typing the formula directly into the row meant for formulas... but it won't take it.
By the way, I used column C for those dates (I swapped C for A just like you suggested, unless there's something else I should change)

Just plug in this formula: =DATE(MID(C1; 7; 4); MID(C1; 4; 2); MID(C1; 1; 2)) and it should convert to a date. If you don't like the default look, you can always change the cell formatting.

Oh, looks like this one's already sorted out...
Quick question though, what's up with that date format yyyy.mm.dd. using dots?!

Jack Hayes said:By the way, what kind of date format even is yyyy.mm.dd. using dots like that?!

That’s actually the kind of format I—well, let's just say it works for me. It seems to be a bit of a compromise between how Excel handles dates internally and how we typically write them out here in the States (with that period after the year). I don't see why anyone would have an issue with that specific notation.

The way a cell is displayed doesn't change what's actually inside it. If I feel like being eccentric, I could set a date cell to a format like yyyy,,,mm---dd///, which would make October 12, 1980, look like 1980,,,10---12///, yet I could still run every single date function perfectly fine.

I try to be extremely careful when I tell others that "this isn't how you do things"—caution, after all, is the mother of wisdom.

Exactly. At its core, a date is just a number—specifically, the count of days elapsed since January 1, 1900, if my memory serves me right. How we actually choose to display that value within a cell is entirely up to us. Personally, I tend to use the date format exactly the way Nicholas King suggested; it’s the most reliable way to get everything back into a standard American date format that actually makes sense to the eye.

frozenheron7 said:Exactly. A date is really just a number—it’s actually the count of days since January 1, 1900, if I remember correctly—so how we choose to display it in a cell is entirely up to us. Personally, I usually stick to the method Nicholas King described to get the date back into a "standard" American format.

Since a date is fundamentally just a number, it's probably smarter to avoid using punctuation (like periods) within your formulas. Excel might handle it, but it doesn't always play nice.
I used this formula =DATE(MID(C1; 7; 4); MID(C1; 4; 2); MID(C1; 1; 2)) which is a surefire way to return a valid date. Once it's done, it'll show up using whatever the default Excel setting happens to be. No extra formatting steps required before or after.
Anyone can customize their date display, but what's actually standard practice? In formal American writing, you'd typically see July 17, 2012, or 07/17/2012 without those extra leading zeros or weird spacing. But there's a big difference between how you write a date in a document versus how it lives in an Excel column. I don't think seeing dates formatted like yyyy.mm.dd is common in Excel at all; that style feels much more suited for text files where you need things to sort correctly.

Yeah, you’ve got a point there, but I think there’s been a massive misunderstanding because Nicholas King was talking about the formatting, not how you actually input the data. And what you said regarding the grammarians? Spot on. They insist on certain grammatical cases for dates, which is a total nightmare to handle in Excel. You can easily set a format to show "July 17," but if you wanted it to read "July 17th" in a specific linguistic sense, you'd have to dive straight into VBA code just to make it work properly.

Hey everyone! Thanks so much for all the help lately, you guys are honestly lifesavers..
I’m reaching out because I’ve been wrestling with an Excel issue for weeks now, and I was hoping a kind soul might be able to take a look at it for me..
So, here is the situation:

I have 10 different subjects and about 200 students.
I’ve laid out all ten subjects in columns, with the students listed in rows.
The tricky part is that not every student has to pass all ten subjects; some only need to pass 5, others 4, some 8, some 7, and so on... Basically, each student has a specific number of required subjects out of my total of 10.
Whenever a student passes an exam, I enter the date they took it (using this format: 1-Jan-13) into the cell under that subject's column. Exams a student hasn't taken yet but still needs to complete are marked as "undefined date." Subjects that a student isn't required to take at all are marked as "N/A."
So, this table with 10 columns and 200 rows is filled with these different markers (dates, "undefined date," and "N/A"), and every student has a different set of mandatory subjects.
What I really need is a formula that can calculate the percentage of exams passed (relative to the total number of exams that specific student is actually required to pass—meaning I need to ignore any cells labeled "N/A")—calculated for each row per student.
Naturally, I want this percentage to update automatically every single time I input a new date..

For instance, if someone has passed three exams (three cells containing dates) and still has three more to go (three cells saying "undefined date"), that means they have 6 total exams to pass, making their pass rate 50%. Essentially, Excel needs to completely ignore those 4 cells in the row that are marked "N/A."
What is the simplest way to pull this off (ideally using just one formula)?
Does anyone know how?😕

I’ve uploaded a sample file so you can see exactly what I’m working with: http://www.sendspace.com/file/2o4wqm

Carol Rivera32 said:Hey everyone! Thanks so much for all the help, you guys are lifesavers..
I'm reaching out because I've been stuck on an Excel issue for weeks now, so if there's a kind soul out there who can take a look..
So, here's the deal:

I have 10 different subjects and, say, 200 students.
I've laid out all ten subjects in columns, and students in rows.
The thing is, not every student has to pass all ten subjects—some just need to pass five, some four, others eight or seven, and so on. Basically, each student has a specific number of subjects they are required to pass (out of my total of 10).
Whenever a student passes an exam, I enter the date they took it (using this format: 1-Jan-13) into the cell under that subject's column. Exams a student still needs to take (but is required to) are marked with: "undefined date." Subjects a student isn't required to take at all are marked as "N/A."
So, the table with 10 columns and 200 rows is full of these labels (dates, "undefined date," and "N/A"), and every student has a different number of mandatory subjects.
What I really need is a formula that calculates the percentage of passed exams (relative to the total number of exams the student is actually required to pass—meaning I want to ignore the "N/A" cells)—for each row per student.
Because, naturally, I want that percentage to update instantly every time I punch in a new date..

For example, if someone has passed three exams (three cells with dates) and still has three left to go (three cells saying "undefined date"), that means they have 6 exams total to pass, making their pass rate 50%. So, Excel needs to completely ignore those 4 cells in the row that say "N/A."
What's the easiest way to do this (ideally in just one formula)?
Does anyone know?😕

I uploaded a sample file so you can see exactly what I'm working with: http://www.sendspace.com/file/2o4wqm

Since you have fixed text for all the columns—"N/A" means they don't take it, "undefined date" means they must take it, and then there's the actual date when it's passed—you can use a formula that just checks for those specific text strings and ignores the actual dates. Here's the formula:
Code:

Personally, I'd probably use something else instead of the phrase 'undefined date'—maybe just a dash, or 'xxx,' or something short. It would make things a lot cleaner.
Basically, this formula takes the total possible exams (which is 10), subtracts the ones that aren't required ("N/A") to find how many *must* be taken, then subtracts the number of exams not yet completed to get the passed count. Finally, it divides that by the number of required exams. Just make sure the column containing the formula is formatted as a percentage.

Carol Rivera32 said:What’s the easiest way to pull this off? Ideally, just one formula if possible.

I don't think you gave me the "full table." I'm guessing this N/A thing of yours is actually what you meant. #N/A error. Great. Just what I needed. What did you even get out of that VLOOKUP?
Look, if you actually want that formula from the last post to work, you gotta fix that #N/A error first. It’s a dead end until you do. Maybe try throwing an IFERROR or something on it? ISNA or ISERROR.

Jack Hayes said:Since you're using specific text strings for all the columns—like "N/A" for things that don't need testing, "undefined date" for things that still need to be taken, and then just the date once they pass—you can actually just write a formula that looks specifically for those fixed labels and ignores the actual dates. Here’s how you do it:
Code:

=((10-COUNTIF(B2:K2;"N/A"))-COUNTIF(B2:K2; "undefined date"))/(10-COUNTIF(B2:K2;"N/A"))

Personally, I’d swap out "undefined date" for something a bit shorter, maybe just an "x" or a dash. It keeps the spreadsheet looking much cleaner and less cluttered.
Basically, this formula takes the total number of tests required (which is 10), subtracts the ones that were failed, which gives you the number of passed tests, and then divides that by the total number of tests that actually had to be completed. Just make sure you set the column format to Percentage.

🙏🙏
👋
🎉
🙂

🙂 👏

🙂 🙂

Thanks so much, Jack Hayes and Kyle Johnson2!! Drinks are on me next time we're out!
Works like a charm!!

Kyle Johnson2, that's not an error, "N/A" just stands for "not applicable."
But thanks anyway!
I really hope you land a great job soon, you definitely deserve it! Fingers crossed for you! 👍

How do I get my text to line up perfectly in Word?
For example, I have my chapters and subchapters listed, and I want to put the page number at the end of each line, but right now the numbers aren't lining up vertically. How do I fix that? 😁

If you’re attempting to build a Table of Contents "by hand," you really ought to be using tabs—rather than the spacebar, which I suspect is what you're currently doing.

Otherwise, with just a bit of effort—which is honestly more than worth the trouble—you could actually set up the document so that Word generates the entire thing automatically for you.

Hey everyone, what's up?

So, I've run into a bit of a headache... it's an issue with Office 2007 and Outlook
Basically, whenever a new email hits my inbox, instead of showing me who actually sent the message, it just lists out the names of every single recipient on the thread. Hopefully, that makes sense—I know it sounds a little weird when you say it out loud. 🤔
If anyone can help me troubleshoot this, I'd really appreciate it... it's becoming a pretty big deal for me.

Justin Garcia7 said:like, I've got my chapters and subchapters all typed out, and I put the page numbers at the end of each line, but they aren't lined up right... how do I fix that?

Look, if you want a Table of Contents that actually looks decent in Word, you've gotta use Word styles—basically, define your headings and subheadings (Heading1, Hedaing2, etc.). For Word 2007 info, check this link:

Justin Gray81 said:When the emails actually arrive, the sender's name isn't showing up in the inbox at all—instead, it just lists the names of everyone who was CC'd or included as a recipient. I'm hoping that makes sense enough 🤔

Not really, it doesn't🙂.

A screenshot would be worth its weight in gold

So... here are the emails hitting the inbox, and I went ahead and crossed out all the sender names. My colleague’s setup is acting weird—instead of showing her own name as the sender, it’s actually listing the names of the people she sent the email to.
Basically, if she sends a message to three different people, her name doesn't show up at all; instead, you just see the names (or email addresses) of everyone on the recipient list. 🙂

Hopefully, that clears things up for you. 🙂

Justin Gray81 said:So, if she sent it to three different people, it’s not actually showing her name—like, the sender's name—it's just listing out the email addresses for everyone who received the message. 🙂

Man, I totally overcomplicated this thing 😁

Basically... she gets the email and the sender's name isn't even visible (that part was blurred out in the photo), it's just showing the email addresses of all the recipients instead..