Connect with us

MARKETING

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

Published

on

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.

Advertisement

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.

Advertisement

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 Column and then select whether you want to order your posts from smallest to largest, or from largest to smallest.

Advertisement

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

Note: If you’re using an earlier version of Excel, “PivotTables” may be under Tables or 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

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

Advertisement

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.

Advertisement

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

New Call-to-Action



Source link

MARKETING

8 major email marketing mistakes and how to avoid them

Published

on

8 major email marketing mistakes and how to avoid them

As email marketers, we know we need to personalize the messages we send to subscribers and customers. I can’t think of a single statistic, case study or survey claiming an email program of one-to-everyone campaigns outperforms personalization.

Instead, you’ll find statistics like these:

  • 72% of customers will engage only with personalized messages (Wunderkind Audiences, formerly SmarterHQ)
  • 70% of consumers say that how well a company understands their individual needs affects their loyalty (Salesforce)
  • 71% of customers are frustrated by impersonal shopping experiences (Segment)

But what marketers often don’t understand, especially if they’re new to personalization, is that personalization is not an end in itself. Your objective is not to personalize your email campaigns and lifecycle messages. 

Rather, your objective is to enhance your customer’s experience with your brand. Personalization is one method that can do that, but it’s more than just another tactic. 

It is both an art and a science. The science is having the data and automations to create personalized, one-to-one messages at scale. The art is knowing when and how to use it.

We run into trouble when we think of personalization as the goal instead of the means to achieve a goal. In my work consulting with marketers for both business and consumer brands, I find this misunderstanding leads to eight major marketing mistakes – any of which can prevent you from realizing the immense benefits of personalization.

Mistake #1. Operating without an overall personalization strategy

I see this all too often: marketers find themselves overwhelmed by all the choices they face: 

  • Which personalization technologies to use
  • What to do with all the data they have
  • How to use their data and technology effectively
  • Whether their personalization efforts are paying off

This stems from jumping headfirst into personalization without thinking about how to use it to meet customers’ needs or help them solve problems. 

To avoid being overwhelmed with the mechanics of personalization, follow this three-step process:

Advertisement
  • Start small. If you aren’t using personalization now, don’t try to set up a full-fledged program right away. Instead, look for quick wins – small areas where you can use basic personalized data to begin creating one-to-one messages. That will get you into the swing of things quickly, without significant investment in time and money. Adding personal data to the body of an email is about as basic as you’ll get, but it can be a start.
  • Test each tactic. See whether that new tactic helps or hurts your work toward your goal. Does adding personal data to each message correlate with higher clicks to your landing page, more conversion or whatever success metric you have chosen?
  • Optimize and move on. Use your testing results to improve each tactic. Then, take what you learned to select and add another personalization tactic, such as adding a module of dynamic content to a broadcast (one to everyone) campaign. 

Mistake #2. Not using both overt and covert personalization

Up to now, you might have thought of in specific terms: personalized subject lines, data reflecting specific actions in the email copy, triggered messages that launch when a customer’s behavior matches your automation settings and other “overt” (or visible) personalization tactics.

“Covert” personalization also employs customer preference or behavior data but doesn’t draw attention to it. Instead of sending an abandoned-browse message that says “We noticed you were viewing this item on our website,” you could add a content module in your next campaign that features those browsed items as recommended purchases, without calling attention to their behavior. It’s a great tactic to use to avoid being seen as creepy.

Think back to my opening statement that personalization is both an art and a science. Here, the art of personalization is knowing when to use overt personalization – purchase and shipping confirmations come to mind – and when you want to take a more covert route. 

Mistake #3. Not maximizing lifecycle automations

Lifecycle automations such as onboarding/first-purchase programs, win-back and reactivation campaigns and other programs tied to the customer lifecycle are innately personalized. 

The copy will be highly personal and the timing spot-on because they are based on customer actions (opting in, purchases, downloads) or inactions (not opening emails, not buying for the first time or showing signs of lapsing after purchasing). 

Better yet, these emails launch automatically – you don’t have to create, schedule or send any of these emails because your marketing automation platform does that for you after you set it up. 

You squander these opportunities if you don’t do everything you can to understand your customer lifecycle and then create automated messaging that reaches out to your customers at these crucial points. This can cost you the customers you worked so hard to acquire, along with their revenue potential.

Mistake #4. Not testing effectively or for long-term gain

Testing helps you discover whether your personalization efforts are bearing fruit. But all too often, marketers test only individual elements of a specific campaign – subject lines, calls to action, images versus no images, personalization versus no personalization  – without looking at whether personalization enhances the customer experience in the long term.

How you measure success is a key part of this equation. The metrics you choose must line up with your objectives. That’s one reason I’ve warned marketers for years against relying on the open rate to measure campaign success. A 50% open rate might be fantastic, but if you didn’t make your goal for sales, revenue, downloads or other conversions, you can’t consider your campaign a success.

Advertisement

As the objective of personalizing is to enhance the customer journey, it makes sense then that customer lifetime value is a valid metric to measure success on.  To measure how effective your personalization use is, use customer lifetime value over a long time period – months, even years – and compare the results with those from a control group, which receives no personalization. Don’t ignore campaign-level results, but log them and view them over time.

(For more detailed information on testing mistakes and how to avoid them, see my MarTech column 7 Common Problems that Derail A/B/N Email Testing Success.)

Mistake #5. Over-segmenting your customer base

Segmentation is a valuable form of personalization, but it’s easy to go too far with it. If you send only highly segmented campaigns, you could be exclude – and end up losing because of failure to contact – many customers who don’t fit your segmentation criteria. That costs you customers, their potential revenue and the data they would have generated to help you better understand your customer base.

You can avoid this problem with a data-guided segmentation plan that you review and test frequently, a set of automated triggers to enhance the customer’s lifecycle and a well-thought-out program of default or catch-all campaigns for subscribers who don’t meet your other criteria. 

Mistake #6. Not including dynamic content in general email campaigns

We usually think of personalized email as messages in which all the content lines up with customer behavior or preference data, whether overt, as in an abandoned-cart message, or covert, where the content is subtly relevant.

That’s one highly sophisticated approach. It incorporates real-time messaging driven by artificial intelligence and complex integrations with your ecommerce or CRM platforms. But a simple dynamic content module can help you achieve a similar result. I call that “serendipity.”  

When you weave this dynamic content into your general message, it can be a pleasant surprise for your customers and make your relevant content stand out even more. 

Let’s say your company is a cruise line. Customer A opens your emails from time to time but hasn’t booked a cruise yet or browsed different tours on your website. Your next email campaign to this customer – and to everyone else on whom you have little or no data – promotes discounted trips to Hawaii, Fiji and the Mediterranean.

Advertisement

Customer B hasn’t booked a cruise either, but your data tells you she has browsed your Iceland-Denmark-Greenland cruise recently. With a dynamic content module, her email could show her your Hawaii and Mediterranean cruise offers – and a great price on a trip to Iceland, Denmark and Greenland. Fancy that! 

An email like this conveys the impression that your brand offers exactly what your customers are looking for (covert personalization) without the overt approach of an abandoned-browse email.

Mistake #7. Not using a personal tone in your copy

You can personalize your email copy without a single data point, simply by writing as if you were speaking to your customer face to face. Use a warm, human tone of voice, which ideally should reflect your brand voice. Write copy that sounds like a one-to-one conversation instead of a sales pitch. 

This is where my concept of “helpful marketing” comes into play. How does your brand help your customers achieve their own goals, solve their problems or make them understand you know them as people, not just data points?  

Mistake #8. Not personalizing the entire journey

Once again, this is a scenario in which you take a short-sighted view of personalization – “How do I add personalization to this email campaign?” – instead of looking at the long-term gain: “How can I use personalization to enhance my customer’s experience?”

Personalization doesn’t stop when your customer clicks on your email. It should continue on to your landing page and even be reflected in the website content your customer views. Remember, it’s all about enhancing your customer’s experience.

What happens when your customers click on a personalized offer? Does your landing page greet your customers by name? Show the items they clicked? Present copy that reflects their interests, their loyalty program standing or any other data that’s unique to them?  

Personalization is worth the effort

Yes, personalization takes both art and science into account. You need to handle it carefully so your messages come off as helpful and relevant without veering into creepy territory through data overreaches. But this strategic effort pays off when you can use the power of personalized email to reach out, connect with and retain customers – achieving your goal of enhancing the customer experience.

Advertisement

Opinions expressed in this article are those of the guest author and not necessarily MarTech. Staff authors are listed here.


About The Author

Kath Pay is CEO at Holistic Email Marketing and the author of the award-winning Amazon #1 best-seller “Holistic Email Marketing: A practical philosophy to revolutionise your business and delight your customers.”

Advertisement

Source link

Continue Reading

DON'T MISS ANY IMPORTANT NEWS!
Subscribe To our Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

Trending

en_USEnglish