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.

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

  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.

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.

New Call-to-Action


Source link

Keep an eye on what we are doing
Be the first to get latest updates and exclusive content straight to your email inbox.
We promise not to spam you. You can unsubscribe at any time.
Invalid email address

MARKETING

7 Things Creators Should Know About Marketing Their Book

Published

on

7 Things Creators Should Know About Marketing Their Book

Writing a book is a gargantuan task, and reaching the finish line is a feat equal to summiting a mountain.

(more…)

Keep an eye on what we are doing
Be the first to get latest updates and exclusive content straight to your email inbox.
We promise not to spam you. You can unsubscribe at any time.
Invalid email address
Continue Reading

MARKETING

Being position-less secures a marketer’s position for a lifetime

Published

on

Optimove Positionless Marketer Optimove

On March 20, 2024, the Position-less Marketer was introduced on MarTech.org and my keynote address at Optimove’s user conference.

Since that initial announcement, we have introduced the term “Position-less Marketer” to hundreds of leading marketing executives and learned that readers and the audience interpreted it in several ways. This article will document a few of those interpretations and clarify what “position-less” means regarding marketing prowess.

As a reminder, data analytics and AI, integrated marketing platforms, automation and more make the Position-less Marketer possible. Plus, new generative AI tools like ChatGPT, Canna-GPT, Github, Copilot and DALL-E offer human access to powerful new capabilities that generate computer code, images, songs and videos, respectively, with human guidance.

Position-less Marketer does not mean a marketer without a role; quite the opposite

Speaking with a senior-level marketer at a global retailer, their first interpretation may be a marketer without a role/position. This was a first-glance definition from more than 60% of the marketers who first heard the term. But on hearing the story and relating it to “be position-less” in other professions, including music and sports, most understood it as a multidimensional marketer — or, as we noted, realizing your multipotentiality. 

One executive said, phrasing position-less in a way that clarified it for me was “unlocking your multidimensionality.” She said, “I like this phrase immensely.” In reality, the word we used was “multipotentiality,” and the fact that she landed on multidimensionality is correct. As we noted, you can do more than one thing.

The other 40% of marketing executives did think of the “Position-less Marketer” as a marketing professional who is not confined or defined by traditional marketing roles or boundaries. In that sense, they are not focused only on branding or digital marketing; instead, they are versatile and agile enough to adjust to the new conditions created by the tools that new technology has to offer. As a result, the Position-less Marketer should be comfortable working across channels, platforms and strategies, integrating different approaches to achieve marketing goals effectively.

Navigating the spectrum: Balancing specialization and Position-less Marketing

Some of the most in-depth feedback came from data analytic experts from consulting firms and Chief Marketing Officers who took a more holistic view.

Most discussions of the “Position-less Marketer” concept began with a nuanced perspective on the dichotomy between entrepreneurial companies and large enterprises.

They noted that entrepreneurial companies are agile and innovative, but lack scalability and efficiency. Conversely, large enterprises excel at execution but struggle with innovation due to rigid processes.

Drawing parallels, many related this to marketing functionality, with specialists excelling in their domain, but needing a more holistic perspective and Position-less Marketers having a broader understanding but needing deep expertise.

Some argued that neither extreme is ideal and emphasized the importance of balancing specialization and generalization based on the company’s growth stage and competitive landscape.

They highlight the need for leaders to protect processes while fostering innovation, citing Steve Jobs’ approach of creating separate teams to drive innovation within Apple. They stress the significance of breaking down silos and encouraging collaboration across functions, even if it means challenging existing paradigms.

Ultimately, these experts recommended adopting a Position-less Marketing approach as a competitive advantage in today’s landscape, where tight specialization is common. They suggest that by connecting dots across different functions, companies can offer unique value to customers. However, they caution against viewing generalization as an absolute solution, emphasizing the importance of context and competitive positioning.

These marketing leaders advocate for a balanced marketing approach that leverages specialization and generalization to drive innovation and competitive advantage while acknowledging the need to adapt strategies based on industry dynamics and competitive positioning.

Be position-less, but not too position-less — realize your multipotentiality

This supports what was noted in the March 20th article: to be position-less, but not too position-less. When we realize our multipotentiality and multidimensionality, we excel as humans. AI becomes an augmentation.

But just because you can individually execute on all cylinders in marketing and perform data analytics, writing, graphics and more from your desktop does not mean you should.

Learn when being position-less is best for the organization and when it isn’t. Just because you can write copy with ChatGPT does not mean you will write with the same skill and finesse as a professional copywriter. So be position-less, but not too position-less.

Position-less vs. being pigeonholed

At the same time, if you are a manager, do not pigeonhole people. Let them spread their wings using today’s latest AI tools for human augmentation.

For managers, finding the right balance between guiding marketing pros to be position-less and, at other times, holding their position as specialists and bringing in specialists from different marketing disciplines will take a lot of work. We are at the beginning of this new era. However, working toward the right balance is a step forward in a new world where humans and AI work hand-in-hand to optimize marketing teams.

We are at a pivot point for the marketing profession. Those who can be position-less and managers who can optimize teams with flawless position-less execution will secure their position for a lifetime.

Source link

Keep an eye on what we are doing
Be the first to get latest updates and exclusive content straight to your email inbox.
We promise not to spam you. You can unsubscribe at any time.
Invalid email address
Continue Reading

MARKETING

Profit More, Work Less: 4 Steps to Niching Down For Your Agency

Published

on

Profit More, Work Less: 4 Steps to Niching Down For Your Agency

Profit More Work Less 4 Steps to Niching Down For

Ever wonder what the most successful agencies did differently than everyone else?

Was it luck, skill, hard work, the industry they chose, or something else?

Through my consulting work at Revenue Boost, I’ve worked with and taught over 400+ agencies how to scale their business.

From this, I’ve seen consistent patterns & traits in the ones who grow effortlessly…

Versus the ones who stay stuck for years – no matter how hard they work.

One key difference in approach stuck out to me.

I’ll illustrate what this one difference was with a story.

Once upon a time…

Two marketers graduated from business school with big plans to start their own agency. 

Ready to conquer the world, they started cold calling, cold emailing, and doing everything under the sun to get clients.

And although they had the SAME levels of work ethic and talent…

One of them now has an 8-figure agency.

The other one of them is still freelancing odd jobs, barely making ends meet.

What did the successful one do differently?

He took a big risk and started turning down clients and projects.

Instead of offering everything to everyone, like most agency owners…

And being a jack of all trades but a master of none…

He decided only to serve Plumbers and be the best dang’ plumbing marketer on the planet.

With a goal to make their pipeline fuller than a broken toilet pipe.

1716128762 859 Profit More Work Less 4 Steps to Niching Down For1716128762 859 Profit More Work Less 4 Steps to Niching Down For

He mastered the art of niching down and realized it would be easier to be the biggest fish in a small pond.

And you should too – and in this article, you’ll learn exactly how to define your own niche.

Now it may seem scary to turn down clients…and it may feel like you’re limiting yourself by focusing on only one client-type.

But it’s exactly the opposite. You’re actually limiting yourself by being everything for everybody.

Niching Down Can Help 2x-3x Your Revenues

One of my clients Lauren ran a digital agency offering everything under the sun.

Social media, paid ads, web dev, SEO, and she offered it to clients from many different industries.

Because of this, her agency stayed stuck at $25,000 a month and she couldn’t break through.

On top of that, she and her team worked so much harder than they had to and operations were messy.

Every client needed different things, required customization, and nothing was standardized.

We sat together to audit all her past clients, and we found that Medical practices were her best clients.

They were easy to sell, stayed the longest, and gave her the least amount of headaches and complaints.

So, she changed her entire business model to ONLY service this industry.

Then, she developed a standardized offer for that industry, rather than customizing everything.

One offer, to one target market. Afterwards, she started cold emailing businesses in her niche with her new offer.

The Results?

 She 2X’d her revenues and grew to $52,000 in monthly revenue in not even four months time.

All from making one simple shift. One decision that can make everything easier, and you can do the same.

See, most agency owners and marketers start out with one or two clients, and then they get referred new clients from various industries.

Before they know it, they’re marketing everything for everyone and have NO idea who their ideal client is.

The Problem with Running a Business This Way Is That It Becomes Impossible to Scale.

Every single new client requires a ton of research, thought, and brainpower.

1716128762 609 Profit More Work Less 4 Steps to Niching Down For1716128762 609 Profit More Work Less 4 Steps to Niching Down For

Because each new client has different needs, it leads to having no standardized processes and systems.

Which keeps the founder stuck in the business and unable to hire a team.

The other problem that arises is acquisition.

There are hundreds of thousands of agencies on the planet, and it’s really hard to stand out.

UNLESS you specialize.

When you specialize in a niche – let’s say, SEO for plumbers…

Then you aren’t competing with every other agency on the planet. You don’t look and sound just like them anymore.

Now, you’ve created your own tiny pond in which you can be a big fish.

There are way fewer agencies that specialize in plumbers or SEO, let alone both. So, you’ve eliminated the competition with one decision.

If a plumber was looking at two agencies – one that was a general digital agency and one that specializes in helping plumbers…

They almost always choose the agency that specializes in their industry and has testimonials from people just like them.

Not to mention, it’s easier to market when you have a clear niche in mind.

You know who you’re writing your content for…

You know who to send emails and social media DMs too…

You know exactly who to target in your ads….

You know what podcasts you should get booked on

And so on and so on.

Plus, you can charge whatever prices you want. Because you aren’t compared to the hundreds of thousands of agencies out there – you have a unique offer now.

Committing to one niche makes marketing easier, it makes selling easier, and it makes scaling easier.

You only have to be good at doing 1 thing for 1 person, and you can build systems and processes around it. This way, you can hire a team to take it over and be able to work less.

Profit More Work Less 4 Steps to Niching Down ForProfit More Work Less 4 Steps to Niching Down For

Become a Certified Analytics & Data Master

At Last, You’ll Have A Powerful Analytics Dashboard That Will Help You Make Smart Business Decisions

Click here

Now how do you do it? What if you don’t know who your ideal client is?

Step 1: Audit Your Current + Past Client List.

Write down every single client you’ve ever served, and group them by niche. Industry, location, size and so on.

Once you group them together, one niche might stick out for you already as your favorite type of client.

If it doesn’t, use my 7-Point checklist and rank each niche on a 1-5 scale.

These 7 criteria points are what makes a great niche.

#1 – Total Addressable Market:

How many businesses are in this market? Is it large enough to support your bigger goals? Is the market shrinking or growing? Make sure the niche is big enough for you and that it’s not declining.

#2 – Purchasing Power

Is this market (or at least a segment of it) able to afford what you want to charge?

Think back to if you’ve received a lot of pricing objections when you’ve sold to these people in the past.

#3 – Lifetime Value

How long did these clients stay? Were they one-and-done projects or did they stay with me for eternity?

The bigger the life-time value, the more money and time you can spend to acquire a client.

If the niche typically churns in a few months or only works with you for quick, one-off projects…

Then you’ll have to spend so much energy on sales and marketing to keep the business alive.

#4 – Strong Need & Pain

Does this market have an important problem to solve, one that they have to fix? Or, is what you sell just a “nice to have”?

If the latter, it’s going to be very hard to get clients.

If they can’t live without your solution, then getting clients will be a breeze.

#5 – Desire to Solve that Pain

It’s one thing for a market to have a problem, but they must also have a desire to solve that problem.

Even if they have the need that you fulfill, that’s not enough – they also have to care about fulfilling that need.

#6 – Easy to Reach

Is the market fairly easy to find online? Can you reach them via most advertising platforms and social channels? Are their groups and communities online?

If you’re targeting businesses that are hard to reach online, you’re creating one extra barrier to your success.

Step 2: Choose 1 Niche After Ranking Each of Your Past Clients.

1716128763 995 Profit More Work Less 4 Steps to Niching Down For1716128763 995 Profit More Work Less 4 Steps to Niching Down For

Tally up all the rankings and pick the 1 with the highest score.

Don’t worry about making the wrong decision.

Consider this an experiment.

You aren’t married to your new niche, you can always change back in a few months if it doesn’t work out.

Step 3: Create a Pre-Packaged Offer for Your New Niche

The whole point of niching down is to create more focus and simplicity in your business

Part of this is about WHO you sell, part of this is about WHAT you sell them.

Start out by choosing 1 problem to solve for them, and 1 solution to that problem.

List out what the deliverables will be and what you want to charge.

Keep it simple! You can build upon this later.

Step 4: Test the Waters and Go Land 5 New Clients.

Before you make any drastic changes to your business, such as letting go of clients, changing your branding and website…

Test the waters first, and verify if this new niche is the direction you want to go.

Go land another 5 clients or so, and that’ll be enough to identify if these are really our ideal clients or not.

You might think they are at first but you’ll know for sure once you serve more of them.

Wrapping Up…

You know now the problems of being a jack-of-all-trades with no clear focus.

Every new client is a ton of work and requires customization…

And getting new clients is difficult because there’s nothing that stands out about your agency. You’ll look and sound like everyone else.

This means when you do niche down, and sell 1 offer to 1 target market…

Your workload will decrease. Each new client will be easier to serve than the previous one.

You’ll become world-class at helping your clients from all the focused repetition

You’ll quickly develop a reputation and become a big fish in a small pond.

In every way, it’ll become easier to grow, scale, attract, and retain clients.

Plus, you’ll have more fun and the business will be simpler & easier to run.

And with this knowledge…

You’ve learned the 5 simple steps to niching down.

So…

Time to get to work!

Put this into practice and watch it transform your business.


Disruptive Design Raising the Bar of Content Marketing with Graphic

Source link

Keep an eye on what we are doing
Be the first to get latest updates and exclusive content straight to your email inbox.
We promise not to spam you. You can unsubscribe at any time.
Invalid email address
Continue Reading

Trending