MARKETING

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

Published

2 years ago

January 3, 2022

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.

What is a pivot table?

A pivot table is a summary of your data, packaged in a chart that lets you report on and explore trends based on your information. Pivot tables are particularly useful if you have long rows or columns that hold values you need to track the sums of and easily compare to one another.

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.

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.

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

Enter your data into a range of rows and columns.
Sort your data by a specific attribute.
Highlight your cells to create your pivot table.
Drag and drop a field into the “Row Labels” area.
Drag and drop a field into the “Values” area.
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.

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.

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.

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

Related Topics:Create Excel Pivot StepbyStep Table Tutorial

Up Next

How To Use Cybersecurity As An Effective Marketing Tool

Don't Miss

How to Build a Marketplace Platform

Click to comment

You must be logged in to post a comment Login

MARKETING

Generative Engine Optimization Framework Introduced in New Research

Published

12 hours ago

May 2, 2024

Entireweb News Bot

Generative Engine Optimization Framework Introduced in New Research

There are several AI chatbot-like features available in the current search engines, including Bing Copilot, Google, Bard, and Gemini. They help to optimize the content visibility in the search results with the help of an AI-powered Search engine known as a Generative engine or AI Search.

A traditional search engine like Bing, Google, or Yahoo ranks and displays information in the SERPs based on the search terms a user inputs. 🔎

The generative engine, on the other hand, generates comprehensive, accurate, and relevant results and information with the help of Generative AI or Large Language Models (LLMs) such as chatGPT, Gemini, and Claude. They understand and integrate information from various sources for the user’s queries.

In this blog, We will discuss the GEO that is introduced in the new research, its framework, and how it can change traditional Search engine optimization (SEO) practices and optimize content for visibility.

The Key Components of the GEO Framework and How They Transform Traditional SEO Practices

GEO is described in the research paper as: “A novel paradigm to aid content creators in improving the visibility of their content in Generative Engine responses through a black-box optimization framework for optimizing and defining visibility metrics. We facilitate systematic evaluation in this new paradigm by introducing GEO-bench, a benchmark of diverse user queries across multiple domains, coupled with sources required to answer these queries.”

Traditional SEO depends upon the keyword volume, difficulty, and optimization for the specific search terms, which focus less on an interpretation relationship between the concepts of keywords or user queries. SEO practices prioritize text-based source content over other sources of content format where regular updating of fresh content is not a primary focus. Also, metrics like impression and click rates affect ranking system results in traditional methods.

GEO encourages detailed information over just the keyword, addressing the related main queries by creating depth content and potential subtopics with the understanding of concept and relationship, encouraging the other formats, such as visual, audio, and images, not just text-based. Moreover, it emphasizes the latest updated content information with continuous accuracy and relevance to provide the most accurate and up-to-date details.

The Impact of Introducing GEO on Website Ranking and Content Relevancy

A generative engine relies on traditional SEO practices like user intent and algorithms for ranking to a degree, such as keyword stuffing. Although it focuses on keywords, it tries to find connections and meanings beyond the keywords in order to create high-quality content.

GEO doesn’t directly indicate the web visibility or page ranking in the Search Engine Result Page. However, it can optimize the overall website visibility and indirectly drive user traffic to your websites through generated responsive data and information.

GEO-optimized content provides the AI Search or a Large Language Model (LLM) with reliable and completely detailed information, enabling them to generate the most accurate and relevant information for responses to user questions or inputs.

These AI-powered engines can deliver a vibrant user experience using optimized content for user engagement and interactive experiences. Furthermore, It also builds trust with a user as it relies on renowned and credible sources, which enhances the effectiveness and reliability of the generated response data and provides synthesizing information.

Comparison with Existing SEO Models: Why GEO Stands Out in Enhancing Search Engine Performance

GEO utilizes auto-generative algorithms for content generation based on predetermined objectives and standards where generated content can cover a broader range of keywords and related topics in various formats like image and visual.

A generative search engine uses modern optimization techniques that involve cognitive SEO, NLP (natural language processing), and structured data markup to maintain and improve content leverage, relevancy, and search engine visibility. In addition, it introduces new methods for determining citations’ importance and website visibility, as well as improving user-centric content by using impression metrics.

Traditional SEO models rely upon and use specific keywords to optimize and rank manually in search results. It uses traditional optimization techniques like link building, meta tags, and URLs.

In traditional search optimization, content creation and optimization can be slow and have low content scalability compared to AI-powered, requiring manual effort for generation and optimization. Constant monitoring and adaptation to platform algorithms are needed to produce the latest and updated information for dynamic user behavior.

Both are equally responsible for improving the brand or website’s online visibility; traditional SEO models require the manual touch for content creation and optimization. GEO tends to use generative responses automatically for content generation as per user queries, making it more effective for user-centric content creation, optimization, and stability in related topics or keywords.

9 Test research findings to improve the website content in GEO

The researchers from Princeton University, Georgia Tech, Allen Institute for AI, and IIT Delhi tested nine various GEO approaches to improve site content for generative engines. Techniques that have been tried and tested over 10,000 search queries, nine optimization strategies were tested on something that “closely resembles the design of BingChat”:

1714643800 803 Generative Engine Optimization Framework Introduced in New Research

Authoritative: The content was altered to be more compelling while conveying definitive claims.
Keyword Stuffing: More keywords were added to match the query.
Statistics Addition: Instead of a qualitative conversation, quantitative statistics were included.
Sources: Relevant citations have been added. Like quotes statistics
Quotation Addition: Quotations from reliable sources have been included.
Easy-to-understand: Simplified the language.
Fluency Optimisation: Improved fluency.
Unique Words: Used in the text whenever possible.
Technical terms: Technical terms have been incorporated into the content.

The data set for search queries was obtained from Google, Microsoft Bing, and Perplexity. Sources include AI Discover, GPT-4, and others.

So, focus on creating detailed and comprehensive blogs or articles by defining the relation and highlighting the context for deeper meaning. Utilize the various formats for content creation to enrich information and diversify the learning perspective.

Also, update your content with the latest information and trends to maintain regular effectiveness and relevancy in the generative engines.

Conclusion:

In the end, Generative Engine Optimization (GEO) provides a more automated, scalable, and adaptive method of content creation and optimization than traditional Search Engine Optimization (SEO) approaches, which need manual and constant work for the optimization and ranking. Compared to traditional search engines, generative engines give instant and detailed personalized information to users’ queries for improved engagement.

Conventional SEO uses metrics like impression, session duration, and click-through rate (CTR), whereas GEO proposes new metrics to measure the relevance and visibility of citations within generative engine responses, making users eliminate the need to visit individual websites for information as it generates the responses on users queries from the reliable, relevant, and various sources.

AI-powered search optimization is still developing and becoming popular since most users and business owners are using generative AI as their source of information and improved visibility with universally applicable diverse content formats.

MARKETING

How To Develop a Great Creative Brief and Get On-Target Content

Published

1 day ago

May 1, 2024

Entireweb News Bot

How To Develop a Great Creative Brief and Get On-Target Content

Every editor knows what it feels like to sit exasperated in front of the computer, screaming internally, “It would have been easier if I’d done it myself.”

If your role involves commissioning and approving content, you know that sinking feeling: Ten seconds into reviewing a piece, it’s obvious the creator hasn’t understood (or never bothered to listen to) a damn thing you told them. As you go deeper, your fingertips switch gears from polite tapping to a digital Riverdance as your annoyance spews onto the keyboard. We’ve all been there. It’s why we drink. Or do yoga. Or practice voodoo.

In truth, even your best writer, designer, or audiovisual content creator can turn in a bad job. Maybe they had an off day. Perhaps they rushed to meet a deadline. Or maybe they just didn’t understand the brief.

The first two excuses go to the content creator’s professionalism. You’re allowed to get grumpy about that. But if your content creator didn’t understand the brief, then you, as the editor, are at least partly to blame.

Taking the time to create a thorough but concise brief is the single greatest investment you can make in your work efficiency and sanity. The contrast in emotions when a perfectly constructed piece of content lands in your inbox could not be starker. It’s like the sun has burst through the clouds, someone has released a dozen white doves, and that orchestra that follows you around has started playing the lovely bit from Madame Butterfly — all at once.

Here’s what a good brief does:

It clearly and concisely sets out your expectations (so be specific).
It focuses the content creator’s mind on the areas of most importance.
It encourages the content creator to do a thorough job rather than an “it’ll-do” job.
It results in more accurate and more effective content (content that hits the mark).
It saves hours of unnecessary labor and stress in the editing process.
It can make all the difference between profit and loss.

Arming content creators with a thorough brief gives them the best possible chance of at least creating something fit for purpose — even if it’s not quite how you would have done it. Give them too little information, and there’s almost no hope they’ll deliver what you need.

On the flip side, overloading your content creators with more information than they need can be counterproductive. I know a writer who was given a 65-page sales deck to read as background for a 500-word blog post. Do that, and you risk several things happening:

It’s not worth the content creator’s time reading it, so they don’t.
Even if they do read it, you risk them missing out on the key points.
They’ll charge you a fortune because they’re losing money doing that amount of preparation.
They’re never going to work with you again.

There’s a balance to strike.

There’s a balance to be struck.

Knowing how to give useful and concise briefs is something I’ve learned the hard way over 20 years as a journalist and editor. What follows is some of what I’ve found works well. Some of this might read like I’m teaching grandma to suck eggs, but I’m surprised how many of these points often get forgotten.

Who is the client?

Provide your content creator with a half- or one-page summary of the business:

Who it is
What it does
Whom it services
What its story is
Details about any relevant products and services

Include the elevator pitch and other key messaging so your content creator understands how the company positions itself and what kind of language to weave into the piece.

Who is the audience?

Include a paragraph or two about the intended audience. If a company has more than one audience (for example, a recruitment company might have job candidates and recruiters), then be specific. Even a sentence will do, but don’t leave your content creator guessing. They need to know who the content is for.

What needs to be known?

This is the bit where you tell your content creator what you want them to create. Be sure to include three things:

The purpose of the piece
The angle to lead with
The message the audience should leave with

I find it helps to provide links to relevant background information if you have it available, particularly if the information inspired or contributed to the content idea, rather than rely on content creators to find their own. It can be frustrating when their research doesn’t match or is inferior to your own.

How does the brand communicate?

Include any information the content creators need to ensure that they’re communicating in an authentic voice of the brand.

Tone of voice: The easiest way to provide guidance on tone of voice is to provide one or two examples that demonstrate it well. It’s much easier for your content creators to mimic a specific example they’ve seen, read, or heard than it is to interpret vague terms like “formal,” “casual,” or “informative but friendly.”
Style guide: Giving your content creator a style guide can save you a lot of tinkering. This is essential for visuals but also important for written content if you don’t want to spend a lot of time changing “%” to “percent” or uncapitalizing job titles. Summarize the key points or most common errors.
Examples: Examples aren’t just good for tone of voice; they’re also handy for layout and design to demonstrate how you expect a piece of content to be submitted. This is especially handy if your template includes social media posts, meta descriptions, and so on.

All the elements in a documented brief

Here are nine basic things every single brief requires:

Title: What are we calling this thing? (A working title is fine so that everyone knows how to refer to this project.)
Client: Who is it for, and what do they do?
Deadline: When is the final content due?
The brief itself: What is the angle, the message, and the editorial purpose of the content? Include here who the audience is.
Specifications: What is the word count, format, aspect ratio, or run time?
Submission: How and where should the content be filed? To whom?
Contact information: Who is the commissioning editor, the client (if appropriate), and the talent?
Resources: What blogging template, style guide, key messaging, access to image libraries, and other elements are required to create and deliver the content?
Fee: What is the agreed price/rate? Not everyone includes this in the brief, but it should be included if appropriate.

Depending on your business or the kind of content involved, you might have other important information to include here, too. Put it all in a template and make it the front page of your brief.

Prepare your briefs early

It’s entirely possible you’re reading this, screaming internally, “By the time I’ve done all that, I could have written the damn thing myself.”

But much of this information doesn’t change. Well in advance, you can document the background about a company, its audience, and how it speaks doesn’t change. You can pull all those resources into a one- or two-page document, add some high-quality previous examples, throw in the templates they’ll need, and bam! You’ve created a short, useful briefing package you can provide to any new content creator whenever it is needed. You can do this well ahead of time.

I expect these tips will save you a lot of internal screaming in the future. Not to mention drink, yoga, and voodoo.

This is an update of a January 2019 CCO article.

Get more advice from Chief Content Officer, a monthly publication for content leaders. Subscribe today to get it in your inbox.

HANDPICKED RELATED CONTENT:

Cover image by Joseph Kalinowski/Content Marketing Institute