MARKETING
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.)
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.
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.
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.
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.
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.
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.
Source link
![How to Create One in 2024 [+ Examples]](https://articles.entireweb.com/wp-content/uploads/2024/03/How-to-Create-One-in-2024-Examples.jpgkeepProtocol-400x240.jpeg "How to Create One in 2024 [+ Examples]")
![How to Create One in 2024 [+ Examples]](https://articles.entireweb.com/wp-content/uploads/2024/03/How-to-Create-One-in-2024-Examples.jpgkeepProtocol-80x80.jpeg "How to Create One in 2024 [+ Examples]")
MARKETING
Tinuiti Marketing Analytics Recognized by Forrester
Rapid Media Mix Modeling and Proprietary Tech Transform Brand Performance
Tinuiti, the largest independent full-funnel performance marketing agency, has been included in a recent Forrester Research report titled, “The Marketing Analytics Landscape, Q2 2024.” This report comprehensively overviews marketing analytics markets, use cases, and capabilities. B2C marketing leaders can use this research by Principal Analyst Tina Moffett to understand the intersection of marketing analytics capabilities and use cases to determine the vendor or service provider best positioned for their analytics and insights needs. Moffett describes the top marketing analytics markets as advertising agencies, marketing dashboards and business intelligence tools, marketing measurement and optimization platforms and service providers, and media analytics tools.
As an advertising agency, we believe Tinuiti is uniquely positioned to manage advertising campaigns for brands including buying, targeting, and measurement. Our proprietary measurement technology, Bliss Point by Tinuiti, allows us to measure the optimal level of investment to maximize impact and efficiency. According to the Forrester report, “only 30% of B2C marketing decision-makers say their organization uses marketing or media mix modeling (MMM),” so having a partner that knows, embraces, and utilizes MMM is important. As Tina astutely explains, data-driven agencies have amplified their marketing analytics competencies with data science expertise; and proprietary tools; and tailored their marketing analytics techniques based on industry, business, and data challenges.
Our Rapid Media Mix Modeling sets a new standard in the market with its exceptional speed, precision, and transparency. Our patented tech includes Rapid Media Mix Modeling, Always-on Incrementality, Brand Equity, Creative Insights, and Forecasting – it will get you to your Marketing Bliss Point in each channel, across your entire media mix, and your overall brand performance.
As a marketing leader you may ask yourself:
- How much of our marketing budget should we allocate to driving store traffic versus e-commerce traffic?
- How should we allocate our budget by channel to generate the most traffic and revenue possible?
- How many customers did we acquire in a specific region with our media spend?
- What is the impact of seasonality on our media mix?
- How should we adjust our budget accordingly?
- What is the optimal marketing channel mix to maximize brand awareness?
These are just a few of the questions that Bliss Point by Tinuiti can help you answer.
Learn more about our customer-obsessed, product-enabled, and fully integrated approach and how we’ve helped fuel full-funnel outcomes for the world’s most digital-forward brands like Poppi & Toms.
The Landscape report is available online to Forrester customers or for purchase here.
MARKETING
Ecommerce evolution: Blurring the lines between B2B and B2C
Understanding convergence
B2B and B2C ecommerce are two distinct models of online selling. B2B ecommerce is between businesses, such as wholesalers, distributors, and manufacturers. B2C ecommerce refers to transactions between businesses like retailers and consumer brands, directly to individual shoppers.
However, in recent years, the boundaries between these two models have started to fade. This is known as the convergence between B2B and B2C ecommerce and how they are becoming more similar and integrated.
Source: White Paper: The evolution of the B2B Consumer Buyer (ClientPoint, Jan 2024)
What’s driving this change?
Ever increasing customer expectations
Customers today expect the same level of convenience, speed, and personalization in their B2B transactions as they do in their B2C interactions. B2B buyers are increasingly influenced by their B2C experiences. They want research, compare, and purchase products online, seamlessly transitioning between devices and channels. They also prefer to research and purchase online, using multiple devices and channels.
Forrester, 68% of buyers prefer to research on their own, online . Customers today expect the same level of convenience, speed, and personalization in their B2B transactions as they do in their B2C interactions. B2B buyers are increasingly influenced by their B2C experiences. They want research, compare, and purchase products online, seamlessly transitioning between devices and channels. They also prefer to research and purchase online, using multiple devices and channels
Technology and omnichannel strategies
Technology enables B2B and B2C ecommerce platforms to offer more features and functionalities, such as mobile optimization, chatbots, AI, and augmented reality. Omnichannel strategies allow B2B and B2C ecommerce businesses to provide a seamless and consistent customer experience across different touchpoints, such as websites, social media, email, and physical stores.
However, with every great leap forward comes its own set of challenges. The convergence of B2B and B2C markets means increased competition. Businesses now not only have to compete with their traditional rivals, but also with new entrants and disruptors from different sectors. For example, Amazon Business, a B2B ecommerce platform, has become a major threat to many B2B ecommerce businesses, as it offers a wide range of products, low prices, and fast delivery
“Amazon Business has proven that B2B ecommerce can leverage popular B2C-like functionality” argues Joe Albrecht, CEO / Managing Partner, Xngage. . With features like Subscribe-and-Save (auto-replenishment), one-click buying, and curated assortments by job role or work location, they make it easy for B2B buyers to go to their website and never leave. Plus, with exceptional customer service and promotional incentives like Amazon Business Prime Days, they have created a reinforcing loyalty loop.
And yet, according to Barron’s, Amazon Business is only expected to capture 1.5% of the $5.7 Trillion addressable business market by 2025. If other B2B companies can truly become digital-first organizations, they can compete and win in this fragmented space, too.”
If other B2B companies can truly become digital-first organizations, they can also compete and win in this fragmented space
Joe AlbrechtCEO/Managing Partner, XNGAGE
Increasing complexity
Another challenge is the increased complexity and cost of managing a converging ecommerce business. Businesses have to deal with different customer segments, requirements, and expectations, which may require different strategies, processes, and systems. For instance, B2B ecommerce businesses may have to handle more complex transactions, such as bulk orders, contract negotiations, and invoicing, while B2C ecommerce businesses may have to handle more customer service, returns, and loyalty programs. Moreover, B2B and B2C ecommerce businesses must invest in technology and infrastructure to support their convergence efforts, which may increase their operational and maintenance costs.
How to win
Here are a few ways companies can get ahead of the game:
Adopt B2C-like features in B2B platforms
User-friendly design, easy navigation, product reviews, personalization, recommendations, and ratings can help B2B ecommerce businesses to attract and retain more customers, as well as to increase their conversion and retention rates.
According to McKinsey, ecommerce businesses that offer B2C-like features like personalization can increase their revenues by 15% and reduce their costs by 20%. You can do this through personalization of your website with tools like Product Recommendations that help suggest related products to increase sales.
Focus on personalization and customer experience
B2B and B2C ecommerce businesses need to understand their customers’ needs, preferences, and behaviors, and tailor their offerings and interactions accordingly. Personalization and customer experience can help B2B and B2C ecommerce businesses to increase customer satisfaction, loyalty, and advocacy, as well as to improve their brand reputation and competitive advantage. According to a Salesforce report, 88% of customers say that the experience a company provides is as important as its products or services.
Market based on customer insights
Data and analytics can help B2B and B2C ecommerce businesses to gain insights into their customers, markets, competitors, and performance, and to optimize their strategies and operations accordingly. Data and analytics can also help B2B and B2C ecommerce businesses to identify new opportunities, trends, and innovations, and to anticipate and respond to customer needs and expectations. According to McKinsey, data-driven organizations are 23 times more likely to acquire customers, six times more likely to retain customers, and 19 times more likely to be profitable.
What’s next?
The convergence of B2B and B2C ecommerce is not a temporary phenomenon, but a long-term trend that will continue to shape the future of ecommerce. According to Statista, the global B2B ecommerce market is expected to reach $20.9 trillion by 2027, surpassing the B2C ecommerce market, which is expected to reach $10.5 trillion by 2027. Moreover, the report predicts that the convergence of B2B and B2C ecommerce will create new business models, such as B2B2C, B2A (business to anyone), and C2B (consumer to business).
Therefore, B2B and B2C ecommerce businesses need to prepare for the converging ecommerce landscape and take advantage of the opportunities and challenges it presents. Here are some recommendations for B2B and B2C ecommerce businesses to navigate the converging landscape:
- Conduct a thorough analysis of your customers, competitors, and market, and identify the gaps and opportunities for convergence.
- Develop a clear vision and strategy for convergence, and align your goals, objectives, and metrics with it.
- Invest in technology and infrastructure that can support your convergence efforts, such as cloud, mobile, AI, and omnichannel platforms.
- Implement B2C-like features in your B2B platforms, and vice versa, to enhance your customer experience and satisfaction.
- Personalize your offerings and interactions with your customers, and provide them with relevant and valuable content and solutions.
- Leverage data and analytics to optimize your performance and decision making, and to innovate and differentiate your business.
- Collaborate and partner with other B2B and B2C ecommerce businesses, as well as with other stakeholders, such as suppliers, distributors, and customers, to create value and synergy.
- Monitor and evaluate your convergence efforts, and adapt and improve them as needed.
By following these recommendations, B2B and B2C ecommerce businesses can bridge the gap between their models and create a more integrated and seamless ecommerce experience for their customers and themselves.
MARKETING
Streamlining Processes for Increased Efficiency and Results
How can businesses succeed nowadays when technology rules? With competition getting tougher and customers changing their preferences often, it’s a challenge. But using marketing automation can help make things easier and get better results. And in the future, it’s going to be even more important for all kinds of businesses.
So, let’s discuss how businesses can leverage marketing automation to stay ahead and thrive.
Benefits of automation marketing automation to boost your efforts
First, let’s explore the benefits of marketing automation to supercharge your efforts:
Marketing automation simplifies repetitive tasks, saving time and effort.
With automated workflows, processes become more efficient, leading to better productivity. For instance, automation not only streamlines tasks like email campaigns but also optimizes website speed, ensuring a seamless user experience. A faster website not only enhances customer satisfaction but also positively impacts search engine rankings, driving more organic traffic and ultimately boosting conversions.
Automation allows for precise targeting, reaching the right audience with personalized messages.
With automated workflows, processes become more efficient, leading to better productivity. A great example of automated workflow is Pipedrive & WhatsApp Integration in which an automated welcome message pops up on their WhatsApp
within seconds once a potential customer expresses interest in your business.
Increases ROI
By optimizing campaigns and reducing manual labor, automation can significantly improve return on investment.
Leveraging automation enables businesses to scale their marketing efforts effectively, driving growth and success. Additionally, incorporating lead scoring into automated marketing processes can streamline the identification of high-potential prospects, further optimizing resource allocation and maximizing conversion rates.
Harnessing the power of marketing automation can revolutionize your marketing strategy, leading to increased efficiency, higher returns, and sustainable growth in today’s competitive market. So, why wait? Start automating your marketing efforts today and propel your business to new heights, moreover if you have just learned ways on how to create an online business
How marketing automation can simplify operations and increase efficiency
Understanding the Change
Marketing automation has evolved significantly over time, from basic email marketing campaigns to sophisticated platforms that can manage entire marketing strategies. This progress has been fueled by advances in technology, particularly artificial intelligence (AI) and machine learning, making automation smarter and more adaptable.
One of the main reasons for this shift is the vast amount of data available to marketers today. From understanding customer demographics to analyzing behavior, the sheer volume of data is staggering. Marketing automation platforms use this data to create highly personalized and targeted campaigns, allowing businesses to connect with their audience on a deeper level.
The Emergence of AI-Powered Automation
In the future, AI-powered automation will play an even bigger role in marketing strategies. AI algorithms can analyze huge amounts of data in real-time, helping marketers identify trends, predict consumer behavior, and optimize campaigns as they go. This agility and responsiveness are crucial in today’s fast-moving digital world, where opportunities come and go in the blink of an eye. For example, we’re witnessing the rise of AI-based tools from AI website builders, to AI logo generators and even more, showing that we’re competing with time and efficiency.
Combining AI-powered automation with WordPress management services streamlines marketing efforts, enabling quick adaptation to changing trends and efficient management of online presence.
Moreover, AI can take care of routine tasks like content creation, scheduling, and testing, giving marketers more time to focus on strategic activities. By automating these repetitive tasks, businesses can work more efficiently, leading to better outcomes. AI can create social media ads tailored to specific demographics and preferences, ensuring that the content resonates with the target audience. With the help of an AI ad maker tool, businesses can efficiently produce high-quality advertisements that drive engagement and conversions across various social media platforms.
Personalization on a Large Scale
Personalization has always been important in marketing, and automation is making it possible on a larger scale. By using AI and machine learning, marketers can create tailored experiences for each customer based on their preferences, behaviors, and past interactions with the brand.
This level of personalization not only boosts customer satisfaction but also increases engagement and loyalty. When consumers feel understood and valued, they are more likely to become loyal customers and brand advocates. As automation technology continues to evolve, we can expect personalization to become even more advanced, enabling businesses to forge deeper connections with their audience. As your company has tiny homes for sale California, personalized experiences will ensure each customer finds their perfect fit, fostering lasting connections.
Integration Across Channels
Another trend shaping the future of marketing automation is the integration of multiple channels into a cohesive strategy. Today’s consumers interact with brands across various touchpoints, from social media and email to websites and mobile apps. Marketing automation platforms that can seamlessly integrate these channels and deliver consistent messaging will have a competitive edge. When creating a comparison website it’s important to ensure that the platform effectively aggregates data from diverse sources and presents it in a user-friendly manner, empowering consumers to make informed decisions.
Omni-channel integration not only betters the customer experience but also provides marketers with a comprehensive view of the customer journey. By tracking interactions across channels, businesses can gain valuable insights into how consumers engage with their brand, allowing them to refine their marketing strategies for maximum impact. Lastly, integrating SEO services into omni-channel strategies boosts visibility and helps businesses better understand and engage with their customers across different platforms.
The Human Element
While automation offers many benefits, it’s crucial not to overlook the human aspect of marketing. Despite advances in AI and machine learning, there are still elements of marketing that require human creativity, empathy, and strategic thinking.
Successful marketing automation strikes a balance between technology and human expertise. By using automation to handle routine tasks and data analysis, marketers can focus on what they do best – storytelling, building relationships, and driving innovation.
Conclusion
The future of marketing automation looks promising, offering improved efficiency and results for businesses of all sizes.
As AI continues to advance and consumer expectations change, automation will play an increasingly vital role in keeping businesses competitive.
By embracing automation technologies, marketers can simplify processes, deliver more personalized experiences, and ultimately, achieve their business goals more effectively than ever before.
Google Limits News Links In California Over Proposed ‘Link Tax’ Law
Google Core Update Volatility, Helpful Content Update Gone, Dangerous Google Search Results & Google Ads Confusion
Daily Search Forum Recap: April 12, 2024
10 Paid Search & PPC Planning Best Practices
2 Ways to Take Back the Power in Your Business: Part 2
5 Psychological Tactics to Write Better Emails
Weekend Google Core Ranking Volatility
Critical Display Error in Brand Safety Metrics On Twitter/X Corrected
The power of program management in martech
WordPress Releases A Performance Plugin For “Near-Instant Load Times”
-
SEO7 days agoGoogle Limits News Links In California Over Proposed ‘Link Tax’ Law
-
SEARCHENGINES6 days agoGoogle Core Update Volatility, Helpful Content Update Gone, Dangerous Google Search Results & Google Ads Confusion
-
SEARCHENGINES7 days ago
Daily Search Forum Recap: April 12, 2024
-
SEO6 days ago10 Paid Search & PPC Planning Best Practices
-
MARKETING6 days ago2 Ways to Take Back the Power in Your Business: Part 2
-
MARKETING5 days ago5 Psychological Tactics to Write Better Emails
-
SEARCHENGINES5 days agoWeekend Google Core Ranking Volatility
-
PPC6 days agoCritical Display Error in Brand Safety Metrics On Twitter/X Corrected
You must be logged in to post a comment Login