Use COUNTIF function to make Excel count words or numbers in any range of cells. Instead of manually counting how often a certain value or number appears, let Excel do the work for you. With the COUNTIF function, Excel can count the number of times a word or number appears in any range of cells. Microsoft Excel is an electronic spreadsheet program that runs on a personal computer. As with a paper spreadsheet, you can use Excel to organize your data into rows and columns and to perform mathematical calculations. What is Microsoft Office? The term Microsoft Office refers Microsofts entire suite of office productivity applications.
Picture this: Your boss just sent over a massive Excel file. It has hundreds—or maybe even thousands—of rows of data. And, to make things worse, within all of those digits he would like you to find a very specific trend or piece of information.
Is your head spinning? Are your eyes glazing over? You aren’t alone. Staring at all of those rows, columns, and numbers is overwhelming.
Fortunately, Excel is incredibly powerful and literally exists to take the elbow grease out of the very data analysis that’s being requested of you.
In this Pivot Table tutorial, we’re going to show you how to use this awesome feature, in five easy steps.
Practice makes perfect!
Download your free excel spreadsheets to practice
What is a pivot table?
Don’t run for the hills just yet. You’ve likely heard of pivot tables before. And, if you’re like most people, you immediately wrote them off as yet another Excel tool that was too complicated for you to master.
But, they can actually be surprisingly simple to utilize—and, they’re sure to remove a lot of headaches from your data management duties.
Put simply, a pivot table summarizes your data. It empowers you to extract significant trends or findings from what is otherwise a totally overwhelming spreadsheet.
Curious? Check out this video to see all of the different capabilities of an Excel pivot table:
But basically, all you need to know is that something that would typically take a long time can be done quickly and painlessly when you build a pivot table. And don't worry, this pivot table tutorial will guide you!How to build a pivot table: A case study
You get it—pivot tables are awesome. However, that doesn’t change the fact that you have no clue how to build one.
Well, have no fear! We’ll walk you through it step by step. And, since there’s nothing like an example to add some clarity, let’s look at a specific scenario when a pivot table could be helpful.
Meet Jason. Jason brews and sells craft beer in a quaint brewery in his hometown. In order to better manage his inventory and brewing schedule, he wants to see if there are any trends in terms of the type of beer that sells most each quarter.
For example, do people drink more dark beer in the wintertime? Getting a better grasp on any seasonality would help him a lot, but to start he only has a spreadsheet that breaks down his sales of each type of beer (stout, pilsner, IPA, and an amber) per quarter in 2016 and 2017.
So, to make this easier, Jason has decided he’s going to build a pivot table to see which beer he sold the most of during each quarter.
Let’s walk through the steps along with Jason.
1. Clean your data
Before you start building anything in Excel, it’s smart to take a quick look at your data to ensure that everything looks correct.
Spelling is something that you’ll want to pay especially close attention to when building a pivot table. For example, if Jason had a typo that said “Sout” instead of “Stout” somewhere in his spreadsheet, the pivot table would pull both of those into the data summary.
Want a quick way to check if there are any spelling errors? Highlight only the first row of your data, click on the “Data” tab of your ribbon, click “Filter,” and then click the arrow that appears on the column header to see all of the different items that are listed within that column. Obviously, this tip works best for more manageable data sets.
Finally, while we’re talking about column headers, it’s important to note that Excel will not create a pivot table unless each of your columns has a heading. So, make sure those are in place before you get started! Try using PowerPivot to clean large data sets quickly and easily.
2. Insert pivot table
Believe it or not, we’re already to the point in the process when you can insert a pivot table into your workbook.
To do so, highlight your entire data set (including the column headers), click “Insert” on the ribbon, and then click the “Pivot Table” button.
3. Choose where to place your pivot table
After clicking that “Pivot Table” button, you’ll be met with a popup that asks where you’d like to place your pivot table. You have two options:
- New Worksheet: Meaning it’ll appear on a separate tab from where your raw data lives
- Existing Worksheet: Meaning it’ll be placed right within that same tab with your raw data
Ultimately, this comes down to personal preference. So, Jason is going to go ahead and choose to put his pivot table in a new worksheet. He selects that option, clicks “OK,” and then his pivot table and another popup will appear.
4. Select what your pivot table should include
This is the part that gets the most confusing for people. In this step, you need to select the data that you want to pull into your pivot table.
So, in Jason’s case, he wants to see the different quarters, the types of beer, and the gallons sold of each during those quarters.
To pull them into the pivot table, Jason will simply drag them into the appropriate spots for rows, columns, and values.
There are obviously exceptions to this rule, but when you’re just getting started, this is a good way to think about each of these things:
- Rows: Will be your time increments, whether that’s quarters (like Jason), months, years, etc.
- Columns: Will be your identifiers—the different ways that you could ID the data. In Jason’s case, that’s the types of beers.
- Values: Will be the metric that you’re measuring. Again, in Jason’s case, it’s gallons of beer sold.
After you’ve dragged those into the appropriate spots, close out of that pop-up and you’ll see your pivot table.
5. Do your analysis
Now, your pivot table will be populated. Before rolling up your sleeves and diving into the analysis, this is a good opportunity to double-check things. Are you seeing all of the elements that you selected? Does anything look off to you?
If not, you’re good to go—you’re ready to use your pivot table to identify trends!
For example, Jason can see that he sells far more stouts (which is a darker, heavier beer) in the first and fourth quarters—which makes sense, as those are the colder months. In contrast, he sells way more pilsners (a lighter, crisper beer) in the second and third quarters—which are the warmer months.
Now, he can leverage the information he discovered through his pivot table to better map out his brewing schedule and manage his inventory of craft beer.
Is your thirst not quenched with our beer example? Here's another real-world case study from Microsoft on how to manage household expenses with pivot tables.
Ready to get started?
We’ve only started with the basics with this example. There’s so much more you can do to slice your data in different ways and really zone in on different pieces. Check out our advanced pivot table techniques article to find out more!
Eager to learn more? Sign up to our Pivot Tables Course to discover even more tips and tricks that you can use to leverage this powerful tool to your advantage. We promise—once you master them, you’ll be hooked!
Level up your Excel skills
Take the Pivot Tables course today!
Start free trialMost people try to limit their Excel exposure to the bare minimum (and even actively avoid it like that one weird relative we all have). But whether it be a project at work or just a personal budget, you need to know basic Excel to get things done right and done quickly. To help you get the most out of Excel, we’ve put together a batch of the best Excel tips for beginners.
Download your free Excel practice workbook
Follow along with this sample workbook.
1. Adding frequent actions to quick access toolbar
Looking around any version of Excel you’ll notice there’s an endless array of tools at your fingertips. But most beginners use just a few of these repetitively. Instead of switching between the different tabs of the ribbon every time, you can add your personal favorites to what’s known as the Quick Access Toolbar.
Microsoft offers several ways to make this happen but the easiest way is to right-click on what you want to add and select “Add to Quick Access Toolbar”. One of my personal favorites in my QAT is the Sort & Filter function as I usually work with a lot of data.
Before:
After:
Once you have your favorites added, you can also rearrange your QAT shortcuts by right-clicking on it and then clicking “Customize Quick Access Toolbar...” in the menu. With your customized QAT you’ll be blazing through your next spreadsheet with ease.
2. Filtering data
Speaking of a lot of data, Microsoft has amazing tools to handle it all, which probably explains why it became the most important workplace computer program around the world. Excel’s basic function for handling all this information is the Sort & Filter function. This is so useful because it allows you to rearrange and summarize data in a way that’s actually useful.
To do this, highlight all the data you want included in the filter (a quick way to do this is Ctrl + A). Then, click the funnel icon in the top left of the Home ribbon.
Excel For Beginners Free Tutorials
Now there will be a drop-down arrow at the top of each column in the filter. Here, you can select different values in the table and sort them. For example, let's say you wanted to see how many orders were made in a certain time frame. Just filter on the date column and select your desired time frame.
From our example, you can quickly see there were only two orders in September 2014.
3. Adding dynamic header/footers
While it seems everything is going paperless, sometimes there’s still a need to print. One of the best ways to keep track of what's printed out of Excel is by adding page numbers, timestamps and file paths to the header or footer. To make sure you don’t have to update these values every time you need to print a spreadsheet, you can add formulas that update automatically.
First, change your view of Excel so you can see the header and footer.
Next, add the following text to your header footer:
| File name | &[File] |
| Sheet name | &[Tab] |
| Page number | &[Page] |
| Date | &[Date] |
Before:
After:
4. Setting print areas on sheets
Now that you know how to automatically update what prints in the margin, another time-saving trick is set up print areas for your tabs to update what prints within the margins. If you like to keep all your work on one spreadsheet but only need to print out a certain portion, setting the print area is a great way to save time when you hit Ctrl + P.
To set your print area, highlight the cells you want printed. On the Page Layout ribbon, click the drop-down under Print Area and select Set Print Area.
In the same drop-down menu, there’s the option to clear the print area. This is useful if you’ve changed your spreadsheet and want to add more to printable area.
5. Paste Special settings
There are so many different ways to accomplish tasks in Excel. Consider the simple copy and paste functions. As if Ctrl + C and Ctrl + V didn’t make copying easy enough, Microsoft built Paste Special.
Here’s how it works. Say you want to copy and paste something but not everything like a just a number or format. Instead of copying everything and removing what you don’t want, you can use Paste Special. To do this, copy your information as usual, but instead of using Ctrl + V, right click and select Paste Special from the menu.
Some of the more popular Paste Special options include:
- Values - use this option if you only want to paste the text you see in the cells. It will leave all formatting unchanged.
- Formulas - Need to keep that formula but don’t need the formatting? Formulas allow you to do just that.
- Formats - Use this option to duplicate formats while leaving existing values and formulas.
- Column Widths - For when all your columns need to be a uniform width, this option saves a ton of time instead of adjusting them manually.
Excel For Beginners Powerpoint
6. Group/ungroup columns to hide detail data
Spreadsheets with a bunch of complicated and detailed information can be hard to read and analyze. Luckily, Excel provides an easy way to collapse and expand the messy details to create a more compact and legible view.
Grouping in Excel works best for structured worksheets that have column headings, no blank rows or columns, and data is sorted by at least one column.
Select all the data you want to summarize in the group. Then, go to the Data tab > Subtotal. This will bring up a pop-up in which you can select how the data should be grouped and summarized. In the example below, we grouped by the change in OrderYear and summed on Total. This will show us total sales for each year and in total.
Here is the result:
Now with just a few clicks, you can go from meaningful summaries to fine detail on the same spreadsheet.
7. Protect sheets and workbook
When it comes time to send your Excel spreadsheet, it's important to protect the data that you're sharing. You might want to share your data, but that doesn't mean it should be changed by someone else. Luckily, Excel has built-in features to protect your spreadsheets.
To protect a sheet, click on the Review tab in the ribbon then Protect Sheet. This will bring up a pop-up box where you’ll add the unlock password and what functions users can still perform while the sheet is protected.
Once you click Ok, you are prompted to confirm the password and save the workbook. Now, if anyone tries to alter the information they will need that password. If you have a bunch of sheets you want protected, click on Protect Workbook follow the same procedure.
9. Trace precedent/dependent formulas
Have you ever used a worksheet that someone else set up? If you need to update the formulas and functions but do not know what other calculations will be affected you can spend a lot of time clicking around in the workbook with nothing to show for. Or you’re looking for a miscalculation and need to see where the data is flowing from.
Excel offers a simple way to review which cells are dependent upon others with Trace Precedents, and which cells contribute to others using Trace Dependents.
Both functions only work on the active cell selected so, unfortunately, it's one cell at a time. Click the Trace Precedents or Trace Dependents button in the Formula Auditing group of the Formulas tab to generate the blue arrows. Data flow follows the direction of the arrow with the blue dot being the precedent and the arrow being the dependent.
Below, Trace Dependents for cell E2 shows it only flows to cell G2.
And Trace Precedents for cell I4 shows cells E2 and F2 are the only cells flowing to it.
These functions work across tabs of the same workbook and different workbooks with one exception. Trace Dependents won’t work for external links for workbooks unless they are open.
9. Data validation for drop-down menus in cells
Using the drop-down list is a great way to impress your co-workers and boss with your Excel skills. At the same time, it’s a very user-friendly way to make sure custom Excel sheets function properly.
This tool is used to enter data in a spreadsheet from a predefined list of criteria. The main purpose of using drop-down lists in Excel is to limit the number of choices available for the user. Apart from that, a dropdown prevents spelling mistakes and makes data input faster.
This also gives you the possibility of controlling exactly what can be entered into a cell. This makes it perfect at validating inputs. First Select Data on the Ribbon and the Data Validation.
Next, select your parameters. Below we used the months of the year to populate OrderMonth.
Once you click Ok, click on the drop-down arrow next to the cell and select from the list.
Note: now that one cell is set up, it can be copy and pasted in the remaining cells below.
10. Text-to-column - splitting data in one cell to multiple cells
Have you seen data in excel that you need but there’s other information in those cells that won’t work with your formulas? While there are some complex formulas that will help you split your text into new columns, it can consume a lot of valuable time. The speedy way to split this up is Text to Columns which splits all the selected cells at exactly the same point and puts the results in separate columns.
You can use Text to Columns in two different modes: fixed-width and delimited. Fixed-width is useful if can separate your data using a straight line through all the rows while delimited splits the text based upon the text such as every comma, tab, or space.
For example, let’s look at a delimited Text to Columns to remove cents from our total column.
Like most functions on the Data Ribbon, first, highlight your data and then click Text to Columns. Once here you’ll choose between fixed-width or delimited.
On the next screen, set your splitting criteria. In our example, we used the period.
On the final screen, you have the option to exclude sections and change the formatting. This will save you crucial steps later. Then click Finish.
And finally the results! All of the cents were placed in the column just to the right.
11. Creating simple graphs
Excel For Beginners Neeraj Arora
One of the best ways to improve your Excel skills is being able to communicate your results to others. Graphics, images, and charts are great ways to visualize and represent your data to end users, and Excel does a great job of automating this process. Let’s take a look at how to set up a simple chart using our sample sales data.
A simple graph starts with two sets of data, your independent (date) and dependent (results) variables. Below we have selected OrderDate and Total.
Clicking on Insert in the Ribbon you’ll see all the graphing options. For this example, a simple line graph will show us the total sales for each date.
Excel makes graphing so simple the results even include the title! So the next time you’re called into a last minute meeting you can create a simple graph on the fly in the meeting.
Bonus: Check out this lesson on how to create column charts in Excel:
Conclusion
Now that you’ve learned more about Excel it's time to put that knowledge into action. These helpful tricks will make your work so much faster you’ll wish you knew about them so much sooner.
For more helpful Excel tips for beginners, check out our articles on the best Excel hacks, Excel essentials, and the best Excel shortcuts. You can even take our free crash course to quickly cover some Excel basics.
Want to improve your Excel skills even more? Don’t be the last person in your office to learn Excel - sign up for the comprehensive GoSkills Basic Excel course today!
Ready to start learning? Become an Excel ninja today with GoSkills
Level up your Excel skills
Become a certified Excel ninja with GoSkills bite-sized courses
Start free trial