Ta kontakt med oss

MARKNADSFÖRING

How to Create a Pivot Table in Excel: A Step-by-Step Tutorial

Publicerad

How to Create a Pivot Table in Excel: A Step-by-Step Tutorial


The pivot table is one of Microsoft Excel’s most powerful — and intimidating — functions. Powerful because it can help you summarize and make sense of large data sets. Intimidating because you’re not exactly an Excel expert, and pivot tables have always had a reputation for being complicated.

The good news: Learning how to create a pivot table in Excel is much easier than you might’ve been led to believe.

But before we walk you through the process of creating one, let’s take a step back and make sure you understand exactly what a pivot table is, and why you might need to use one.

In other words, pivot tables extract meaning from that seemingly endless jumble of numbers on your screen. And more specifically, it lets you group your data in different ways so you can draw helpful conclusions more easily.

The “pivot” part of a pivot table stems from the fact that you can rotate (or pivot) the data in the table to view it from a different perspective. To be clear, you’re not adding to, subtracting from, or otherwise changing your data when you make a pivot. Instead, you’re simply reorganizing the data so you can reveal useful information from it.

What are pivot tables used for?

If you’re still feeling a bit confused about what pivot tables actually do, don’t worry. This is one of those technologies that are much easier to understand once you’ve seen it in action.

The purpose of pivot tables is to offer user-friendly ways to quickly summarize large amounts of data. They can be used to better understand, display, and analyze numerical data in detail — and can help identify and answer unanticipated questions surrounding it.

Here are seven hypothetical scenarios where a pivot table could be a solution:

1. Comparing sales totals of different products.

Say you have a worksheet that contains monthly sales data for three different products — product 1, product 2, and product 3 — and you want to figure out which of the three has been bringing in the most bucks. You could, of course, look through the worksheet and manually add the corresponding sales figure to a running total every time product 1 appears. You could then do the same for product 2, and product 3 until you have totals for all of them. Piece of cake, right?

Now, imagine your monthly sales worksheet has thousands and thousands of rows. Manually sorting through them all could take a lifetime. Using a pivot table, you can automatically aggregate all of the sales figures for product 1, product 2, and product 3 — and calculate their respective sums — in less than a minute.

Annons

2. Showing product sales as percentages of total sales.

Pivot tables naturally show the totals of each row or column when you create them. But that’s not the only figure you can automatically produce.

Let’s say you entered quarterly sales numbers for three separate products into an Excel sheet and turned this data into a pivot table. The table would automatically give you three totals at the bottom of each column — having added up each product’s quarterly sales. But what if you wanted to find the percentage these product sales contributed to all company sales, rather than just those products’ sales totals?

With a pivot table, you can configure each column to give you the column’s percentage of all three column totals, instead of just the column total. If three product sales totaled $200,000 in sales, for example, and the first product made $45,000, you can edit a pivot table to instead say this product contributed 22.5% of all company sales.

To show product sales as percentages of total sales in a pivot table, simply right-click the cell carrying a sales total and select Show Values As > % of Grand Total.

3. Combining duplicate data.

In this scenario, you’ve just completed a blog redesign and had to update a bunch of URLs. Unfortunately, your blog reporting software didn’t handle it very well and ended up splitting the “view” metrics for single posts between two different URLs. So in your spreadsheet, you have two separate instances of each individual blog post. To get accurate data, you need to combine the view totals for each of these duplicates.

That’s where the pivot table comes into play. Instead of having to manually search for and combine all the metrics from the duplicates, you can summarize your data (via pivot table) by blog post title, and voilà: the view metrics from those duplicate posts will be aggregated automatically.

4. Getting an employee headcount for separate departments.

Pivot tables are helpful for automatically calculating things that you can’t easily find in a basic Excel table. One of those things is counting rows that all have something in common.

If you have a list of employees in an Excel sheet, for instance, and next to the employees’ names are the respective departments they belong to, you can create a pivot table from this data that shows you each department name and the number of employees that belong to those departments. The pivot table effectively eliminates your task of sorting the Excel sheet by department name and counting each row manually.

Annons

5. Adding default values to empty cells.

Not every dataset you enter into Excel will populate every cell. If you’re waiting for new data to come in before entering it into Excel, you might have lots of empty cells that look confusing or need further explanation when showing this data to your manager. That’s where pivot tables come in.

You can easily customize a pivot table to fill empty cells with a default value, such as $0, or TBD (for “to be determined”). For large tables of data, being able to tag these cells quickly is a useful feature when many people are reviewing the same sheet.

To automatically format the empty cells of your pivot table, right-click your table and click PivotTable Options. In the window that appears, check the box labeled Empty Cells As and enter what you’d like displayed when a cell has no other value.

How to Create a Pivot Table

  1. Enter your data into a range of rows and columns.
  2. Sort your data by a specific attribute.
  3. Highlight your cells to create your pivot table.
  4. Drag and drop a field into the “Row Labels” area.
  5. Drag and drop a field into the “Values” area.
  6. Fine-tune your calculations.

Now that you have a better sense of what pivot tables can be used for, let’s get into the nitty-gritty of how to actually create one.

Step 1. Enter your data into a range of rows and columns.

Every pivot table in Excel starts with a basic Excel table, where all your data is housed. To create this table, simply enter your values into a specific set of rows and columns. Use the topmost row or the topmost column to categorize your values by what they represent.

For example, to create an Excel table of blog post performance data, you might have a column listing each “Top Pages,” a column listing each URL’s “Clicks,” a column listing each post’s “Impressions,” and so on. (We’ll be using that example in the steps that follow.)

how to create a pivot table step 1: enter your data into a range of rows and columns

Step 2. Sort your data by a specific attribute.

When you have all the data you want entered into your Excel sheet, you’ll want to sort this data in some way so it’s easier to manage once you turn it into a pivot table.

To sort your data, click the Data tab in the top navigation bar and select the Sort icon underneath it. In the window that appears, you can opt to sort your data by any column you want and in any order.

For example, to sort your Excel sheet by “Views to Date,” select this column title under Kolumn and then select whether you want to order your posts from smallest to largest, or from largest to smallest.

Annons

Select OK on the bottom-right of the Sort window, and you’ll successfully reorder each row of your Excel sheet by the number of views each blog post has received.

how to create a pivot table step 2: sort your data by a specific attribute

Step 3. Highlight your cells to create your pivot table.

Once you’ve entered data into your Excel worksheet, and sorted it to your liking, highlight the cells you’d like to summarize in a pivot table. Click Insert along the top navigation, and select the PivotTable icon. You can also click anywhere in your worksheet, select “PivotTable,” and manually enter the range of cells you’d like included in the PivotTable.

This will open an option box where, in addition to setting your cell range, you can select whether or not to launch this pivot table in a new worksheet or keep it in the existing worksheet. If you open a new sheet, you can navigate to and away from it at the bottom of your Excel workbook. Once you’ve chosen, click OK.

Alternatively, you can highlight your cells, select Recommended PivotTables to the right of the PivotTable icon, and open a pivot table with pre-set suggestions for how to organize each row and column.

how to create a pivot table step 3: highlight your cells to create your pivot table

Notera: If you’re using an earlier version of Excel, “PivotTables” may be under Tables eller Data along the top navigation, rather than “Insert.” In Google Sheets, you can create pivot tables from the Data dropdown along the top navigation.

Step 4. Drag and drop a field into the “Row Labels” area.

After you’ve completed Step 3, Excel will create a blank pivot table for you. Your next step is to drag and drop a field — labeled according to the names of the columns in your spreadsheet — into the Row Labels area. This will determine what unique identifier — blog post title, product name, and so on — the pivot table will organize your data by.

For example, let’s say you want to organize a bunch of blogging data by post title. To do that, you’d simply click and drag the “Top pages” field to the “Row Labels” area.

how to create a pivot table step 4: drag and drop a field into the rows label area

Notera: Your pivot table may look different depending on which version of Excel you’re working with. However, the general principles remain the same.

Annons

Step 5. Drag and drop a field into the “Values” area.

Once you’ve established what you’re going to organize your data by, your next step is to add in some values by dragging a field into the Values area.

Sticking with the blogging data example, let’s say you want to summarize blog post views by title. To do this, you’d simply drag the “Views” field into the Values area.

how to create a pivot table step 5: drag and drop a field into the values area

Step 6. Fine-tune your calculations.

The sum of a particular value will be calculated by default, but you can easily change this to something like average, maximum, or minimum depending on what you want to calculate.

On a Mac, you can do this by clicking on the small i next to a value in the “Values” area, selecting the option you want, and clicking “OK.” Once you’ve made your selection, your pivot table will be updated accordingly.

If you’re using a PC, you’ll need to click on the small upside-down triangle next to your value and select Value Field Settings to access the menu.

how to create a pivot table step 6: fine tune your calculations

When you’ve categorized your data to your liking, save your work and use it as you please.

Digging Deeper With Pivot Tables

You’ve now learned the basics of pivot table creation in Excel. With this understanding, you can figure out what you need from your pivot table and find the solutions you’re looking for.

For example, you may notice that the data in your pivot table isn’t sorted the way you’d like. If this is the case, Excel’s Sort function can help you out. Alternatively, you may need to incorporate data from another source into your reporting, in which case the VLOOKUP function could come in handy.

Annons

Editor’s note: This post was originally published in December 2018 and has been updated for comprehensiveness.

New Call-to-Action



Källlänk

MARKNADSFÖRING

Community Building for Retention, Awareness, Loyalty, Content, & Member Advocacy

Publicerad

Community Building for Retention, Awareness, Loyalty, Content, & Member Advocacy

A little birdy told me you want to know what this “Community” stuff is you keep hearing about. I promise it’s not scary, at least not as frightening as Data Tracking and Analytics. 

Ahh, Numbers!

No need to worry, you’re safe here, and the data can’t get you. At least, not in this particular post. 

Community is a tale as old as time and is simply evolving along with humanity; perhaps it’s time you join the party! 

I like curiosity so allow me to be your guide through the magical and underrated world of Community Building. By the time you finish reading, you’ll know what a Community is, why you should want one, and what a Community Builder can do for you.

What is Community, and why is it important?

If you ask the peeps at Merriam-Webster, the TL;DR version is that a community is people with common interests living in a particular area, or a group of people with a common characteristic or interest living together within a larger society. That’s not a bad definition if you ask me, but I think we can do better in this case.

Community is not a place—not even that arcade you and your friends used to frequent—and despite the common misconception, it’s not an exchange of information over the internet. Community is about a feeling and relationships built among people. As DigitalMarketer says, it’s “a segment of people who form relationships due to shared goals, experiences, and interests”. 

Become a Certified Community Specialist

Learn how to develop meaningful relationships with your customers and automate the customer acquisition process.

Click here

Annons

Community members will have built a sense of trust, belonging, and caring for each other. 

That warm, fuzzy feeling of community comes from shared experiences and shared history… uncommon commonalities, you could say. 

Like I said, a tale as old as time. We’ve all been a member of communities in one way or another, even if it wasn’t in a platform or forum.

How can this benefit your business?

When done right, the community can most commonly decrease costs and increase revenue through higher retention, brand awareness, brand loyalty, ticket deflection, content development, and member advocacy. 

When a sense of belonging is created, a relationship is built between your members and each other. Even better, one between you and your members. We’re all partying together!

A Community can be the most potent customer feedback loop you’ve ever seen! In our largest Community, DM Engage (for our DM Lab members), I know I can always count on honest and constructive feedback from our members, and they’re not shy about asking for what they want. 

The power of user-generated content? Unmatched. Imagine seeing this testimonial on a landing page.

I don’t mean to toot our horn, but you can bet that after an experience like this, Michael “Buzz” Buzinski will be a lifetime DigitalMarketer member. With the right environment, you can grab tons of screenshots like this and, even better, videos! 

As a bonus, Buzz and I will be buddies for life!

Annons

What is a Community Builder?

This one is a doozy, not because it’s difficult to define, but because there can be so many definitions! 

For me, it’s someone who nurtures connections and relationships on a small or large scale. It can be one to one or one to many. They’re strategic, semi-organized, unafraid to be the bad guy, and empathetic. They create a “home” for people to gather.

If you ask one of my favorite Twitter people to follow, it’s…

“A community builder can be someone who works to create a structure that will hopefully enable a community to thrive. The platform, the processes, and the important, sometimes difficult choices.” Patrick O’Keefe, Community Lead at CNN

A Community Builder is an architect of experiences and relationships, as cheesy as that may sound. Without one, you’re probably not achieving what you set out to do. 

A Gatekeeper, a People Manager, a Content Moderator, a Ring Master in your circus…whatever you call them, are the ones building the house your members will live in and that your members will help decorate to their needs and tastes. 

What does a Community Builder do?

A better question is ‘What don’t they do’?

Your ironing, probably. Their own ironing, maybe. (I am both of these people.)

Annons

They plan, write, structure, promote, burn out, create momentum, are really in their feelings, and don’t do anything without a reason. 

No matter how silly or unnecessary something might seem, there is a reason behind the madness.

Note: Don’t talk to your Community person when they’ve got that look on their face, they’re plotting, they’re in the zone, and something amazing or horrific is about to happen. You’ll love it. 

The big thing here is that everything in Community is about intention. It’s in how your members choose to show up and interact, and how your Community Builder architects the conversations, events, and overall experience. They’re like mad scientists, only they’re not angry, just lots of heart and not enough caffeine yet.

In Community, some things happen by chance… or do they? If you intended to start a conversation that ended up being a meaningful moment of connection between your members… is it really just luck? This is what I call ✨ vibing ✨ together.

This is where the magic happens; your Community person sets the stage for the right conversations. How? Well, with a sprinkle of inviting copy, a dash of one-on-one chats, a pinch of puppy posts (because puppy posts always get the job done), and a whole bunch of strategic content that guides your members to complete the actions you intend them to… 

…Just call me Community Witch because that’s a potion that will provide.

What skills or traits does a Community Builder need?

If you’d like to replicate yourself a Michelle, it’s about: 40% irreverence, 40% hard work, 10% wanting to show the haters they’re wrong, and another 10% of hard work (just not on Friday afternoons). 

Annons

What you’re looking for is a people person who enjoys the freedom of creativity, has a curious streak, and knows how to get shi*t done. Imagine a customer service professional with project management and content skills. Sounds cool, right? That’s because it is. 

Let’s talk about skills.

This may sound like an oxymoron, but it takes strong soft skills to make a great Community Professional. Let’s start with some of the more obvious ones. 

  • Organized. Community can be messy. You’re in twenty different tabs, three different platforms, with multiple conversations running, and Slack pinging all at once. You’ve got to be organized enough to know what is going on at any moment. Sure it can be exhausting, but boy, oh boy, is it fun!
  • Communication. How can you build relationships with someone if you can’t communicate? I’m sure it’s possible, but imagine the difficulty! Excellent written and verbal communication is essential when you’re the mouthpiece for the brand. Let’s not accidentally promise 3k worth of bonuses when it was actually 1k. 
  • Empathetic. It’s similar to Customer Service; you’re not always hearing from people on their best day. You must be able to take in what the other person is saying, listen, and understand their point of view. That way, you can provide them with honest response to their issue. Often in Community, the bond and relationship become so strong you deal with things you wouldn’t expect. You’re an advocate for members and an advocate for the brand. It’s a balancing act; the base is your ability to empathize and communicate. 
  • Leadership. As a Community Professional, you’re building paths for your members to take, and you’re leading by example. Members look to you to calm the chaos, enforce the Guidelines, and to learn how to interact in the beginning. 
  • Boundary Setting. Because Community roles are so heavy on emotion, we also need to be fully aware and able to set boundaries with not just members but also our coworkers and ourselves. It’s okay to be the bad guy once in a while if you’re protecting what has been built. While the community is for the members, it’s your house, and they’re just living in it. Your Community Professional should know when to advocate for the community and when to advocate for the brand. 
  • Creativity. You’ve got all this feedback, so now what? Time to get creative and put that feedback to work! There is no one-size-fits-all solution to Community, and they should be able to whip up some short-form copy and think up new opportunities when needed.
  • Curiosity. One of our old core values here at DigitalMarketer was to “know the why,” and I think that applies to Community. Adaptability is the game’s name, so when you see something wonky with the Community, your KPIs, or member interaction, you have to figure it out ASAP. Not only that, but the world of Community is developing at a break-neck pace. You have to be able to keep up with the progress and roll with it. 
  • Storytelling. You may not be able to tell from this post, but I can spin up a mean story here and there. You want someone who can paint a picture, set the stage, and control the narrative. You want all the things that require wordsmithing so they can tell a story that brings forth action. The other important part of storytelling is how your Community person will bring stories from the community that leadership and stakeholders care about. Testimonials, feedback, content ideas, etc..… You know, all the good stuff. 

Where can I find one?

While this one might have found her forever home, many Community Professionals are available to be adopted hired!

While I’m sure there are more, these are some of my favorites! Now go out there and find yourself a perfect match for your brand and your members.



Källlänk

Fortsätt läsa

MISSA INTE NÅGRA VIKTIGA NYHETER!
Prenumerera på vårt nyhetsbrev
Vi lovar att inte spamma dig. Avsluta prenumerationen när som helst.
Ogiltig e-postadress

Trendigt

Entireweb
sv_SESvenska