How to Sort, Use Formulas, and Analyze Data in Excel: Essential Tips and Tricks

Excel is a powerful tool for data analysis, but it can be overwhelming for beginners. This blog post will provide essential tips and tricks to help you sort data, use formulas effectively, and analyze data like a pro. Whether you’re a student, a business professional, or just someone looking to organize personal data, these insights will enhance your Excel skills.

 

How to Sort, Use Formulas, and Analyze Data in Excel

Step 1: Sorting Data

Sorting data in Excel is a fundamental skill that allows you to organize your data in a meaningful way. Here’s how to do it:

1. Select the range of data you want to sort. If you want to sort the entire spreadsheet, click on any cell within your data set.
2. Go to the ‘Data’ tab on the Ribbon.
3. Click on ‘Sort A to Z’ (ascending order) or ‘Sort Z to A’ (descending order) for quick sorting based on one column.
4. For more advanced sorting, click on the ‘Sort’ button to open the Sort dialog box. Here, you can add multiple levels of sorting criteria.

 

Example:

 

| Name     | Age | City       |
|———-|—–|————|
| John Doe | 28  | New York   |
| Jane Doe | 35  | Los Angeles|
| Bob Smith| 22  | Chicago    |

To sort by age in ascending order:

1. Select the range A1:C4.
2. Go to ‘Data’ > ‘Sort A to Z’.

The sorted table will look like this:

| Name     | Age | City       |
|———-|—–|————|
| Bob Smith| 22  | Chicago    |
| John Doe | 28  | New York   |
| Jane Doe | 35  | Los Angeles|

 

Step 2: Using Formulas

Formulas are what make Excel a powerful tool for calculations. Here are some basic formulas you should know:

SUM: Adds up all the numbers in a range.
AVERAGE: Calculates the average of a group of numbers.
MAX: Returns the largest number in a set of values.
MIN: Returns the smallest number in a set of values.
IF: Performs a logical test and returns one value for a TRUE result, and another for a FALSE result.

 

Example:

To calculate the total of sales in column B:

1. Click on the cell where you want the total to appear.
2. Type `=SUM(B2:B10)` and press Enter.

 

Step 3: Data Analysis

Excel has several features that allow for sophisticated data analysis:

PivotTables: These are interactive tables that quickly summarize large amounts of data. You can use PivotTables to sort, count, and total data from a dataset.
Conditional Formatting: This feature allows you to automatically format cells based on their values, which can help you quickly identify trends and patterns.
Charts and Graphs: Visual representations of data can make it easier to understand. Excel offers a variety of chart types, including line, bar, pie, and scatter plots.

 

Example of Creating a PivotTable In Excel:

 

  1. Select the data you want to analyze.
    2. Go to ‘Insert’ > ‘PivotTable’.
    3. In the Create PivotTable dialog box, choose where you want the PivotTable to be placed.
    4. Drag and drop fields to the Rows, Columns, Values, and Filters areas. 

Conclusion:


Mastering Excel requires practice, but by understanding how to sort data, use formulas, and analyze data, you’ll be well on your way to becoming proficient. Remember to experiment with different features and functions to discover what Excel can do for you. With these essential tips and tricks, you’ll be able to streamline your workflow, make informed decisions based on your data, and unlock the full potential of Excel.

 

Live Excel Support Transcript Example:

Below is a live transcript of one of our Excel experts helping a customer sorting and analyzing data in Excel.

 

Full Conversation:

Customer: Yes, I can hear you. Can you hear me?
Technician: Yes, ma’am. Fantastic. So what can I do?
Customer: Hi. So I have an Excel spreadsheet that has 5000 plus rows of data. And I know enough about sorting to get in trouble, right? And then when I really have to start hacking data, and writing formulas, my brain just doesn’t cut out that way. And no matter how many like YouTube videos I watch, my brain’s like, we’re not doing this. And so I’m hoping I’ve listed like three things that I need help with. And I’m hoping you can walk me through that and help me with the resolution.
Technician: Yeah, share your screen. I can see what you’re working on.
Customer: Yep, absolutely. Um, boop, boop, I think I closed the spreadsheet. Oh, I didn’t. Entire screen. Wait. Window. Oh, there it is. Excel. Share. Am I sharing my screen?
Technician: No. What happened?
Customer: That was weird. Window. Excel. Share. Um, it doesn’t like that. Let me add it to. Yeah, are you trying to do it in Firefox?
Technician: No. Synchrony usually will work. When I mean, it’s open. And so when I click on it, it’s like, No, not really. We don’t want to do that. I’m wondering if I open it in the browser. That will help. Hold on. Let me try that.
Technician: Okay. Okay. I saved it in the cloud. So I just have to remember where to go. Um, I hate this. Government Affairs. Government Affairs. RPAC. Lists. Fourth quarter push. Full list for management. Okay. So then Geeker. Screen. Full list for management. Okay. Now I can see it. So here’s what I run into. This is the full list. And at the end of the year, I like to divvy it up into different categories for some messaging. And so I like to look at like a current year and previous year. So you can see at the bottom, the different tabs for the different sheets, like 23, they’ve invested more than 22. 23 is less than 22. Same amounts. And then first time investing, never invested. So is there a way to write like an if then, if like, if D2 is less than E2, then you move the whole or copy row two to a different tab.
Customer: Okay. So not exactly like that. But we can get the rows where D2 is less than E2 in the other tab. Would that work?
Technician: Okay. Sure. So this is already, so the ones down here are actually populated. But if we want to create another tab so you can show me, that would be super fantastic.
Customer: Okay. Yeah. So you can go ahead and do that.
Technician: Okay. Also, your microphone keeps breaking up and getting feedback. I don’t know if there’s a different microphone that you can use.
Customer: Shoot. Is there like a setting?
Technician: Uh-oh. All right. Let’s see. Is that- Is that help at all?
Customer: No, it’s okay. We’ll just deal with it. Do you want to do phone?
Technician: Actually, yeah, let’s do that because I think it’ll be, it’ll be clear. So you can mute yourself and I will call you.
Customer: Okay. Perfect. Your call has been forwarded to an automatic voice message- Oh my gosh. 7 0 3 5 0 5 0 2 0 is not available. Your call has been forwarded to an automatic voice message system.
Technician: All right. So we’ll just have to deal with the audio being- Well, I forgot that I have silent unknown callers on, so I turned it off. So if you want to try again, I’m happy to.
Customer: See, but now it’s giving me a red X when I try to click call you. So whatever. It’s fine. We’ll just deal with it.
Technician: Okay. So if you can share your screen again, I can walk you through what we need to do.
Customer: Okay. Okay. Awesome. So you can say equals filter and then select the whole set of values that you want to be working with. Everything, right? Because we want to take the whole row, right?
Technician: Oh, wait. Wait, I’m confused. So I’m comparing like D to E. Right. But when E is greater than D, we want to take the whole row, right?
Customer: Correct. Okay. Yeah. So you want to just select from A over to whatever the N column is. Like the whole list? The whole thing. Yep. Oh, okay. Oh, my God, now I’m lost. No, no, no. So up at the top, you can just put, instead of AI 248, you can just do A colon AI. Okay. Okay. And then comma. And then click on the D column here. And then less than, and then click on the E column. And then close parentheses. And then just hit Enter. Oh, and so this is if D is less than E? Yeah. Yeah. Okay. Why, why do I have such trouble with that? And so I can do it for equals or greater than, or, um, equals zero, right? Yeah. Yeah. Okay. And wow, that’s fascinating. So it all over to, sorry, the fact that we just did this like in 10 seconds. Okay, fantastic. Okay. So the second thing is, um, I have this data set and the current year numbers may be different or the current year numbers here, but not everybody. Not everybody on the awards list is on the list. Oh, I want to compare to see if they’re the same. Yeah. Okay. So what you can do is, um, you can we don’t need an issue. You can do it on the on the Ohio awards 1. Okay. Okay. Um, so there’s a unique identifier. That’s what I was going to ask. Okay. Yeah. Where is it? Remember? Yep. Okay. So what you want to do is insert a blank column, like somewhere around there. And then you can say, uh, in E2, you can say equals index. Parentheses, then go select the, um, whatever column it is that has the number we want on the other sheet. Comma. Matt. Then parentheses again. Other left parentheses. Sorry. Okay, and then click back to the Ohio awards 1. And then, uh, click on O2, because that’s where the member ID is. Okay, comma. Click back to full list. That first cell? or the whole? Oh, no the whole thing. Okay, Okay, so you kind of have, um, um, you got only two rows right now. But you don’t want this space. Okay, what does zero mean? Exact match. Okay. And then close parentheses twice. Okay. And then hit enter. So that is pulling the number over. So now that that’s working, uh, we can, you can go back and stick another formula around that. Okay. And so, uh, click on F2 again. Okay. And then at the beginning, so after the equal sign, but before index. Okay. You can say if parentheses. And then all the way at the end. Oh, okay. Equals wherever the current year value is stored here. So I guess D2. Comma. Then whatever you want it to say when it’s when the match works. Um. Okay, you just put it, you have to put it in quotes. Sorry. And then comma, and then whatever you want to say when they don’t. And then close, close, close parentheses. Three. No, I think we just need one this time. Okay. Yeah, and then return. Yeah, just hit. And then if you want to click on the F2 cell, and then the square in the bottom right corner, double click that. And it’ll drop it all the way down. There you go. How fascinating. Why does that say it’s different? Because it’s different. Probably. Let’s check. Wait, so we’re looking at, wait, why is there both say current year? Because there were two columns originally. Yeah, wait, but one previous year. Why did the name? We didn’t change those. Yeah, but watch, watch this. Oh, wait. Um, yeah, this said, this said previous year. Oh, not on this one. Yeah. Okay. So wait, then I screw, I delete. So, um. Just hit, just hit, uh, control Y to redo. Oh, I never knew that. Okay. Wait. There you go. Okay. So, okay. So let’s, let’s double check that it’s working, right? Because it might not be. So, uh, this says row nine. So come over to oh nine and get that member ID number. Wait. The member. Oh, this year. Right. So copy that. Okay. And then come over to full list and then do control F and we’ll find that and we’ll see what, uh, it’s, what it should. Not, not, not, yeah. Oh, for, hold on. Oh, no. I don’t even know. I’m having one of those days. Me too. Okay. Copy list, control F. Why, what am I doing wrong? Okay. Let me find the next. So there you go. It’s, it says 420585, which is different than the Ohio award number. Yeah. So. Why would that be an NA? NA means that that member ID number does not appear on the full list. Oh, fascinating. Right. Because NA means there’s an error. And the only reason it’s going to have an error from this formula is if it, when it tries to find the match, it doesn’t find anything. So those should be the only three possible outcomes. Okay. So then I just sort. Right. Yeah. Okay. Or actually I probably wouldn’t do a sort. That’s going to be, I would just filter it. Right. That’ll probably be easier. Right. Because then if you just click on compare. Wait, I see just mine. That’s fine. And so you can see. That’s dating. Okay. Okay. Perfect. Okay. Let me look at my to-do list. Okay. So. So we did the sort thingy my Bob, we did the comparison thingy my Bob. And then the last thing was the chart. So what I’ve done. So these are all the folks first time investing. And I wanted to be able to. So I have, I have it. Like, like a list of. The people, is there a way to show the list of the people under here? Sure. If you want to, oh, you need that back. Okay. So. Oh, my God. Um, yeah, pick a table. Oh, field field list. Yeah. Okay. So we don’t have a single name column. But. Oh, so what I did was I did at one point when I tried to do this, I tried to do the whole. I merged the first and last name into one column. And then try to pull it over. And then I got totally confused by what I was doing. Gotcha. So what’s the source for that pivot table? The first time stuff. Uh, yeah. First time investing. Yeah. So then you can just insert a new column. Okay. And we’ll just call it full name. And then you can say equals D2 ampersand. Is that no? What’s an ampersand is the and sign. So shift 7. Oh, okay. A double quote space, double quote. Wait, double. No, no the double quote and then a space and then another double quote to show a blank. Gotcha. Okay. And then ampersand E2. Okay. And then hit enter and then do the same. Trick to go back to the pivot table. You’re going to have to go to pivot table. Not don’t don’t don’t close that. We need that. Click pivot table at the top. Um, and then refresh all. Cool. Now grab full name and drag it as a row under office. Fantastic. Okay. Is there a way to like throw an email or no? Um, yeah, you can drag that as another row underneath. Let’s see it. Oh, here we go. Okay. That doesn’t. I’m not sure I could try dragging it to. No, I’m going to summarize that. So pivot tables don’t really play that nice with, um, uh, text data. Yeah. So you’re not, it’s not going to be a great way to display the email separately from the name. Yeah, yeah. Um, okay. So then. Okay, let me just make sure there was like another thing I was really struggling with. Let me see if I can remember. Um, oh, I hate like cloud version of Excel. It’s annoying for sure. Um, jog is parking at the kids next door. Hey, come on. Sorry. No, it’s all right. Yeah. Oh, three huskies. Oh my gosh. When you get it. Yeah. Wow. So you’re full of sacks at your house. Okay. Thank you. Sorry, Excel guy. I, you know. The pool guy apparently needs to tell me about his stocks. Um, let’s see. Okay. Uh, let’s see. So if I was zero and I said, okay, star one real estate, we have 35 people. These are the people who have not given, um, I guess I can pull. Oh, but now I know how to do like the indexy thing. If, um, so I think I’m good, but now I know where you are. I can call you guys again when I get stuck. Exactly. Perfect. Okay. Um, so I think we’re, I think we’re good. All right. Um, yeah. So if you need help with anything else in the future, uh, don’t hesitate to get in touch. Hesitate to get in touch. Um, and yeah, I hope the rest of your day is easier and, uh, good luck. Awesome. Thank you so much. I totally appreciate it. Uh, no trouble at all. Have a good night. You too. Bye. Bye.