You are reading the article Using Slicers In Excel Pivot Table – A Beginner’s Guide updated in February 2024 on the website Daihoichemgio.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested March 2024 Using Slicers In Excel Pivot Table – A Beginner’s Guide
A Pivot Table Slicer enables you to filter the data when you select one or more than one options in the Slicer box (as shown below).
Let’s get started.
Suppose you have a dataset as shown below:
This is a dummy data set (US retail sales) and spans across 1000 rows. Using this data, we have created a Pivot Table that shows the total sales for the four regions.Read More: How to Create a Pivot Table from Scratch.
Once you have the Pivot Table in place, you can insert Slicers.
One may ask – Why do I need Slicers?
You may need slicers when you don’t want the entire Pivot Table, but only a part of it. For example, if you don’t want to see the sales for all the regions, but only for South, or South and West, then you can insert the slicer and quickly select the desired region(s) for which you want to get the sales data.
Slicers are a more visual way that allows you to filter the Pivot Table data based on the selection.
Here are the steps to insert a Slicer for this Pivot Table:
Select any cell in the Pivot Table.
In the Insert Slicers dialog box, select the dimension for which you the ability to filter the data. The Slicer Box would list all the available dimensions and you can select one or more than one dimensions at once. For example, if I only select Region, it will insert the Region Slicer box only, and if I select Region and Retailer Type both, then it’ll insert two Slicers.
Note that Slicer would automatically identify all the unique items of the selected dimension and list it in the slicer box.
You can also insert multiple slicers by selecting more than one dimension in the Insert Slicers dialog box.
To insert multiple slicers:
Select any cell in the Pivot Table.
In the Insert Slicers dialog box, select all the dimensions for which you want to get the Slicers.
This will insert all the selected Slicers in the worksheet.
Note that these slicers are linked to each other. For example, If I select ‘Mid West’ in the Region filter and ‘Multiline’ in the Retailer Type filter, then it will show the sales for all the Multiline retailers in Mid West region only.
Also, if I select Mid West, note that the Specialty option in the second filter gets a lighter shade of blue (as shown below). This indicates that there is no data for Specialty retailer in the Mid West region.
What’s the difference between Slicers and Report Filters?
Here are some key differences between Slicers and Report Filters:
Slicers don’t occupy a fixed cell in the worksheet. You can move these like any other object or shape. Report Filters are tied to a cell.
Report filters are linked to a specific Pivot Table. Slicers, on the other hand, can be linked to multiple Pivot Tables (as we will see later in this tutorial).
Since a report filter occupies a fixed cell, it’s easier to automate it via VBA. On the other hand, a slicer is an object and would need a more complex code.
A Slicer comes with a lot of flexibility when it comes to formatting.
Here are the things that you can customize in a slicer.
If you don’t like the default colors of a slicer, you can easily modify it.
Select the slicer.
If you don’t like the default styles, you can create you own. To do this, select the New Slicer Style option and specify your own formatting.
By default, a Slicer has one column and all the items of the selected dimension are listed in it. In case you have many items, Slicer shows a scroll bar that you can use to go through all the items.
You may want to have all the items visible without the hassle of scrolling. You can do that by creating multiple column Slicer.
To do this:
Select the Slicer.
Change the Columns value to 2.
This will instantly split the items in the Slicer into two column. However, you may get something looking as awful as shown below:
This looks cluttered and the full names are not displayed. To make it look better, you change the size of the slicer and even the buttons within it.
To do this:
Select the Slicer.
Change Height and Width of the Buttons and the Slicer. (Note that you can also change the size of the slicer by simply selecting it and using the mouse to adjust the edges. However, to change the button size, you need to make the changes in the Options only).
By default, a Slicer picks the field name from the data. For example, if I create a slicer for Regions, the header would automatically be ‘Region’.
You may want to change the header or completely remove it.
Here are the steps:
In the Slicer Settings dialog box, change the header caption to what you want.
This would change the header in the slicer.
If you don’t want to see the header, uncheck the Display Header option in the dialog box.
By default, the items in a Slicer are sorted in an ascending order in case of text and Older to Newer in the case of numbers/dates.
You can change the default setting and even use your own custom sort criteria.
Here is how to do this:
In the Slicer Settings dialog box, you can change the sorting criteria, or use your own custom sorting criteria.Read More: How to create custom lists in Excel (to create your own sorting criteria)
It may happen that some of the items in the Pivot Table have no data in it. In such cases, you can make the Slicers hide that item.
In such cases, you can choose not display it at all.
Here are the steps to do this:
In the Slicer Settings dialog box, with the ‘Item Sorting and Filtering’ options, check the option ‘Hide items with no data’.
A slicer can be connected to multiple Pivot Tables. Once connected, you can use a single Slicer to filter all the connected Pivot Tables simultaneously.
Remember, to connect different Pivot Tables to a Slicer, the Pivot Tables need to share the same Pivot Cache. This means that these are either created using the same data, or one of the Pivot Table has been copied and pasted as a separate Pivot Table.Read More: What is Pivot Table Cache and how to use it?
Below is an example of two different Pivot tables. Note that the Slicer in this case only works for the Pivot Table on the left (and has no effect on the one on the right).
To connect this Slicer to both the Pivot Tables:
In the Report Connections dialog box, you will see all the Pivot Table names that share the same Pivot Cache. Select the ones you want to connect to the Slicer. In this case, I only have two Pivot Tables and I’ve connected both with the Slicer.
Now your Slicer is connected to both the Pivot Tables. When you make a selection in the Slicer, the filtering would happen in both the Pivot Tables (as shown below).
Just as you use a Slicer with a Pivot Table, you can also use it with Pivot Charts.
Something as shown below:
Here is how you can create this dynamic chart:
Make the fields selections (or drag and drop fields into the area section) to get the Pivot chart you want. In this example, we have the chart that shows sales by region for four quarters. (Read here on how to group dates as quarters).
Select the Slicer dimension you want with the Chart. In this case, I want the retailer types so I check that dimension.
Format the Chart and the Slicer and you’re done.
Note that you can connect multiple Slicers to the same Pivot Chart and you can also connect multiple charts to the same Slicer (the same way we connected multiple Pivot Tables to the same Slicer).
You May Also Like the Following Pivot Table Tutorials:
You're reading Using Slicers In Excel Pivot Table – A Beginner’s Guide
Learn web scraping in Python using the BeautifulSoup library
Web Scraping is a useful technique to convert unstructured data on the web to structured data
BeautifulSoup is an efficient library available in Python to perform web scraping other than urllib
A basic knowledge of HTML and HTML tags is necessary to do web scraping in PythonIntroduction
The need and importance of extracting data from the web is becoming increasingly loud and clear. Every few weeks, I find myself in a situation where we need to extract data from the web to build a machine learning model.
For example, last week we were thinking of creating an index of hotness and sentiment about various data science courses available on the internet. This would not only require finding new courses, but also scraping the web for their reviews and then summarizing them in a few metrics!
This is one of the problems / products whose efficacy depends more on web scraping and information extraction (data collection) than the techniques used to summarize the data.
Note: We have also created a free course for this article – Introduction to Web Scraping using Python. This structured format will help you learn better.Ways to extract information from web
There are several ways to extract information from the web. Use of APIs being probably the best way to extract data from a website. Almost all large websites like Twitter, Facebook, Google, Twitter, StackOverflow provide APIs to access their data in a more structured manner. If you can get what you need through an API, it is almost always preferred approach over web scraping. This is because if you are getting access to structured data from the provider, why would you want to create an engine to extract the same information.
Sadly, not all websites provide an API. Some do it because they do not want the readers to extract huge information in a structured way, while others don’t provide APIs due to lack of technical knowledge. What do you do in these cases? Well, we need to scrape the website to fetch the information.
There might be a few other ways like RSS feeds, but they are limited in their use and hence I am not including them in the discussion here.What is Web Scraping?
You can perform web scraping in various ways, including use of Google Docs to almost every programming language. I would resort to Python because of its ease and rich ecosystem. It has a library known as ‘BeautifulSoup’ which assists this task. In this article, I’ll show you the easiest way to learn web scraping using python programming.
For those of you, who need a non-programming way to extract information out of web pages, you can also look at import.io . It provides a GUI driven interface to perform all basic web scraping operations. The hackers can continue to read this article!Libraries required for web scraping
As we know, Python is an open source programming language. You may find many libraries to perform one function. Hence, it is necessary to find the best to use library. I prefer BeautifulSoup (Python library), since it is easy and intuitive to work on. Precisely, I’ll use two Python modules for scraping data:
Urllib2: It is a Python module which can be used for fetching URLs. It defines functions and classes to help with URL actions (basic and digest authentication, redirections, cookies, etc). For more detail refer to the documentation page. Note: urllib2 is the name of the library included in Python 2. You can use the urllib.request library included with Python 3, instead. The urllib.request library works the same way urllib.request works in Python 2. Because it is already included you don’t need to install it.
BeautifulSoup: It is an incredible tool for pulling out information from a webpage. You can use it to extract tables, lists, paragraph and you can also put filters to extract information from web pages. In this article, we will use latest version BeautifulSoup 4. You can look at the installation instruction in its documentation page.
BeautifulSoup does not fetch the web page for us. That’s why, I use urllib2 in combination with the BeautifulSoup library.
Python has several other options for HTML scraping in addition to BeatifulSoup. Here are some others:Basics – Get familiar with HTML (Tags)
While performing web scarping, we deal with html tags. Thus, we must have good understanding of them. If you already know basics of HTML, you can skip this section. Below is the basic syntax of HTML:This syntax has various tags as elaborated below:
Other useful HTML tags are:
If you are new to this HTML tags, I would also recommend you to refer HTML tutorial from W3schools. This will give you a clear understanding about HTML tags.Scraping a web page using BeautifulSoup
Here, I am scraping data from a Wikipedia page. Our final goal is to extract list of state, union territory capitals in India. And some basic detail like establishment, former capital and others form this wikipedia page. Let’s learn with doing this project step wise step:
#import the library used to query a website import urllib2 #if you are using python3+ version, import urllib.request #specify the url #Query the website and return the html to the variable 'page' page = urllib2.urlopen(wiki) #For python 3 use urllib.request.urlopen(wiki) #import the Beautiful soup functions to parse the data returned from the website from bs4 import BeautifulSoup #Parse the html in the 'page' variable, and store it in Beautiful Soup format soup = BeautifulSoup(page) Above, you can see that structure of the HTML tags. This will help you to know about different available tags and how can you play with these to extract information.
Work with HTML tagsIn:soup.title In :
stringOut:u'List of state and union territory capitals in India - Wikipedia, the free encyclopedia' In :
Above, it is showing all links including titles, links and other information. Now to show only links, we need to iterate over each a tag and then return the link using attribute “href” with get.
Find the right table: As we are seeking a table to extract information about state capitals, we should identify the right table first. Let’s write the command to extract information within all table tags. all_tables=soup.find_all('table') right_table=soup.find('table', class_='wikitable sortable plainrowheaders') right_table Above, we are able to identify right table.#Generate lists A= B= C= D= E= F= G= for row in right_table.findAll("tr"): cells = row.findAll('td') states=row.findAll('th') #To store second column data if len(cells)==6: #Only extract table body not heading A.append(cells.find(text=True)) B.append(states.find(text=True)) C.append(cells.find(text=True)) D.append(cells.find(text=True)) E.append(cells.find(text=True)) F.append(cells.find(text=True)) G.append(cells.find(text=True)) #import pandas to convert list to data frame import pandas as pd df=pd.DataFrame(A,columns=['Number']) df['State/UT']=B df['Admin_Capital']=C df['Legislative_Capital']=D df['Judiciary_Capital']=E df['Year_Capital']=F df['Former_Capital']=G df
Similarly, you can perform various other types of web scraping using “BeautifulSoup“. This will reduce your manual efforts to collect data from web pages. You can also look at the other attributes like .parent, .contents, .descendants and .next_sibling, .prev_sibling and various attributes to navigate using tag name. These will help you to scrap the web pages effectively.-But, why can’t I just use Regular Expressions?
Now, if you know regular expressions, you might be thinking that you can write code using regular expression which can do the same thing for you. I definitely had this question. In my experience with BeautifulSoup and Regular expressions to do same thing I found out:
Code written in BeautifulSoup is usually more robust than the one written using regular expressions. Codes written with regular expressions need to be altered with any changes in pages. Even BeautifulSoup needs that in some cases, it is just that BeautifulSoup is relatively better.
Regular expressions are much faster than BeautifulSoup, usually by a factor of 100 in giving the same outcome.
So, it boils down to speed vs. robustness of the code and there is no universal winner here. If the information you are looking for can be extracted with simple regex statements, you should go ahead and use them. For almost any complex work, I usually recommend BeautifulSoup more than regex.End Note
In this article, we looked at web scraping methods using “BeautifulSoup” and “urllib2” in Python. We also looked at the basics of HTML and perform the web scraping step by step while solving a challenge. I’d recommend you to practice this and use it for collecting data from web pages.
Note: We have also created a free course for this article – Introduction to Web Scraping using Python. This structured format will help you learn better.If you like what you just read & want to continue your analytics learning, subscribe to our emails, follow us on twitter or like our facebook page.
Why C++ for Machine Learning?
The applications of machine learning transcend boundaries and industries so why should we let tools and languages hold us back? Yes, Python is the language of choice in the industry right now but a lot of us come from a background where Python isn’t taught!
The computer science faculty in universities are still teaching programming in C++ – so that’s what most of us end up learning first. I understand why you should learn Python – it’s the primary language in the industry and it has all the libraries you need to get started with machine learning.
But what if your university doesn’t teach it? Well – that’s what inspired me to dig deeper and use C++ for building machine learning algorithms. So if you’re a college student, a fresher in the industry, or someone who’s just curious about picking up a different language for machine learning – this tutorial is for you!
In this first article of my series on machine learning using C++, we will start with the basics. We’ll understand how to implement linear regression and logistic regression using C++!
Note: If you’re a beginner in machine learning, I recommend taking the comprehensive Applied Machine Learning course.Linear Regression using C++
Let’s first get a brief idea about what linear regression is and how it works before we implement it using C++.
Linear regression models are used to predict the value of one factor based on the value of another factor. The value being predicted is called the dependent variable and the value that is used to predict the dependent variable is called an independent variable. The mathematical equation of linear regression is:Y=B0+B1 X
X: Independent variable
Y: Dependent variable
B0: Represents the value of Y when X=0
B1: Regression Coefficient (this represents the change in the dependent variable based on the unit change in the independent variable)
For example, we can use linear regression to understand whether cigarette consumption can be predicted based on smoking duration. Here, your dependent variable would be “cigarette consumption”, measured in terms of the number of cigarettes consumed daily, and your independent variable would be “smoking duration”, measured in days.Loss Function
The loss is the error in our predicted value of B0 and B1. Our goal is to minimize this error to obtain the most accurate value of B0 and B1 so that we can get the best fit line for future predictions.
For simplicity, we will use the below loss function:e(i) = p(i) - y(i)
e(i) : error of ith training example
p(i) : predicted value of ith training example
y(i): actual value of ith training exampleOverview of the Gradient Descent Algorithm
Gradient descent is an iterative optimization algorithm to find the minimum of a function. In our case here, that function is our Loss Function.
Here, our goal is to find the minimum value of the loss function (that is quite close to zero in our case). Gradient descent is an effective algorithm to achieve this. We start with random initial values of our coefficients B0 and B1 and based on the error on each instance, we’ll update their values.
Here’s how it works:
Initially, let B1 = 0 and B0 = 0. Let L be our learning rate. This controls how much the value of B1 changes with each step. L could be a small value like 0.01 for good accuracy
We calculate the error for the first point: e(1) = p(1) – y(1)
We’ll update B0 and B1 according to the following equation:b0(t+1) = b0(t) - L * error b1(t+1) = b1(t) - L * error
We’ll do this for each instance of a training set. This completes one epoch. We’ll repeat this for more epochs to get more accurate predictions.
You can refer to these comprehensive guides to get a more in-depth intuition of linear regression and gradient descent:Implementing Linear Regression in C++ Initialization phase:
We’ll start by defining our dataset. For the scope of this tutorial, we’ll use this dataset:
We’ll train our dataset on the first 5 values and test on the last value:
View the code on Gist.
Next, we’ll define our variables:
View the code on Gist.Training Phase
Our next step is the gradient descent algorithm:
View the code on Gist.
Since there are 5 values and we are running the whole algorithm for 4 epochs, hence 20 times our iterative function works. The variable p calculates the predicted value of each instance. The variable err is used for calculating the error of each instance. We then update the values of b0 and b1 as explained above in the gradient descent section above. We finally push the error in the error vector.
As you will notice, B0 does not have any input. This coefficient is often called the bias or the intercept and we can assume it always has an input value of 1.0. This assumption can help when implementing the algorithm using vectors or arrays.
Finally, we’ll sort the error vector to get the minimum value of error and corresponding values of b0 and b1. At last, we’ll print the values:
View the code on Gist.Testing Phase:
View the code on Gist.
We’ll enter the test value which is 6. The answer we get is 4.9753 which is quite close to 5. Congratulations! We just completed building a linear regression model with C++, and that too with good parameters.Full Code for final implementation:
View the code on Gist.Logistic Regression with C++
Logistic Regression is one of the most famous machine learning algorithms for binary classification. This is because it is a simple algorithm that performs very well on a wide range of problems.
The name of this algorithm is logistic regression because of the logistic function that we use in this algorithm. This logistic function is defined as:predicted = 1 / (1 + e^-x) Gradient Descent for Logistic Regression
We can apply stochastic gradient descent to the problem of finding the coefficients for the logistic regression model as follows:
Let us suppose for the example dataset, the logistic regression has three coefficients just like linear regression:output = b0 + b1*x1 + b2*x2
The job of the learning algorithm will be to discover the best values for the coefficients (b0, b1, and b2) based on the training data.
Given each training instance:
Calculate a prediction using the current values of the coefficients. prediction = 1 / (1 + e^(-(b0 + b1*x1 + b2*x2)).
Calculate new coefficient values based on the error in the prediction. The values are updated according to the below equation: b = b + alpha * (y – prediction) * prediction * (1 – prediction) * x
Where b is the coefficient we are updating and prediction is the output of making a prediction using the model. Alpha is a parameter that you must specify at the beginning of the training run. This is the learning rate and controls how much the coefficients (and therefore the model) changes or learns each time it is updated.
Like we saw earlier when talking about linear regression, B0 does not have any input. This coefficient is called the bias or the intercept and we can assume it always has an input value of 1.0. So while updating, we’ll multiply with 1.0.
The process is repeated until the model is accurate enough (e.g. error drops to some desirable level) or for a fixed number of iterations.
For a beginner’s guide to logistic regression, check this out – Simple Guide to Logistic Regression.Implementing Logistic Regression in C++ Initialization phase
We’ll start by defining the dataset:
We’ll train on the first 10 values and test on the last value:
View the code on Gist.
Next, we’ll initialize the variables:
View the code on Gist.Training Phase
View the code on Gist.
Since there are 10 values, we’ll run one epoch that takes 10 steps. We’ll calculate the predicted value according to the equation as described above in the gradient descent section:prediction = 1 / (1 + e^(-(b0 + b1*x1 + b2*x2)))
Next, we’ll update the variables according to the similar equation described above:b = b + alpha * (y – prediction) * prediction * (1 – prediction) * x
Finally, we’ll sort the error vector to get the minimum value of error and corresponding values of b0, b1, and b2. And finally, we’ll print the values:
View the code on Gist.Testing phase:
View the code on Gist.
When we enter x1=7.673756466 and x2= 3.508563011, we get pred = 0.59985. So finally we’ll print the class:
View the code on Gist.
So the class printed by the model is 1. Yes! We got the prediction right!Final Code for full implementation
View the code on Gist.
One of the more important steps, in order to learn machine learning, is to implement algorithms from scratch. The simple truth is that if we are not familiar with the basics of the algorithm, we can’t implement that in C++.
Excel Slicers are a professional way to enable users to easily and intuitively interact with your reports, filtering data in PivotTables, Pivot Charts, Excel Tables and CUBE functions. They’re available in Excel 2010 onwards for PivotTables, and for Excel Tables from Excel 2013. In this comprehensive tutorial I cover EVERYTHING you need to know about Excel Slicers.
Table of Contents
1. What are Excel Slicers? 2. Inserting Slicers 3. Using Slicers 4. Formatting Slicers 5. Custom Slicer Styles 6. Copy and Modify Slicers 7. Connect Slicers to Multiple PivotTables 8. PivotTables not Listed in Report ConnectionsWatch the Excel Slicers Video
Download the Workbook
Enter your email address below to download the sample workbook.
By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.What are Excel Slicers?
Now I know you can already filter using the PivotTable or Excel Table filter tools, but Slicers are better for two reasons:
They can control the filtering of multiple PivotTables/Charts (but only one Table)
They look professional and are more intuitive to use
Have a go yourself using the interactive workbook below.
Warning: don’t go silly and choose too many areas though, or the PivotChart might implode….. what am I thinking? That’s like a red rag to a bull, of course you’re going to try it now that I mention it but at least I warned you!
Data used in chart above is from Greater London Authority (Microsoft Azure Marketplace).
Pretty cool, eh? I’m sure you’re now itching to get started with your own Slicers, so here’s how:
Inserting Excel Slicers
Using Excel Slicers
Slicers are intuitive to use, and they allow us to easily filter one or multiple items:
The Slicer displays the selected items in a different colour giving a visual indicator to the user:
One of the gripes I had with Slicers in the early days was that they were a bit chunky. Since then, I’ve found some of the formatting tricks hidden deep down in the menus that allow you to make them a more manageable size.
I’ll take you through the obvious ones first and then I’ll show you the secret ones 😉
Excel Slicer Settings
Change the Slicer name
Turn the Slicer header off/on, or give it a different caption
Choose how to sort the Slicer
Choose how the Slicer should handle items with no data. Note: in Excel 2010 you don’t have the option to ‘Hide items with no data’.
Excel Slicer Styles
In the Slicer Styles group we can choose the colour and style, or create a new style (note: the colour options will be based on the Theme/Colors you have selected for the workbook in the Page Layout tab of the ribbon, mine is ‘Paper’):
The Arrange group of tools allow you to quickly align your Slicers or move them behind or in front of other objects like charts, shapes, images etc.:
Buttons and Size
I generally find anything smaller than 0.5cm for the button height is as small as you can go.
If the overall height of your Slicer is too small to display all of the values a scroll bar will be inserted:
Custom Excel Slicer Styles
This will open the ‘New Slicer Style’ dialog box where you can format each of the 10 Slicer elements exactly as you want:
You can even check the ‘Set as default slicer style for this document’ box and use it over and over again, but wait right there because I have a quicker way.
Copy and Modify Excel Slicers
This will open the Modify Slicer Style dialog box:
Then go to the Border tab and remove the border:
Note: While removing the border serves to make the Slicer appear smaller it is actually still the same size. However, now you can squeeze it into a smaller space by placing the edges of the Slicer underneath other objects, like charts, without it being noticeable.
Ok, now that your Slicer is more compact, you’ll be able to squeeze it into your report.
If you want more customisation, check out this dedicated tutorial on Excel Slicer formatting for further tips on how to tweak them to your liking.
Connecting Excel Slicers to Multiple PivotTables/Charts
Once you’ve inserted your Slicer you can go about choosing which PivotTables/Charts you want it to control.
PivotTable not Listed in Slicer Report Connections Area
An Excel Slicer can only control PivotTables which share the same Pivot Cache. Typically, PivotTables which reference the same data source share a Pivot Cache, but not always. If the PivotTables you want to connect to don’t appear in the list, then you’ll know the cause is separate Pivot Caches
The easiest way to fix PivotTables that aren’t appearing in the Slicer Report Connections area is to copy one of the PivotTables that is in the list, and modify it to replicate the missing PivotTable. This way you’ll ensure the PivotTable is sharing the same Pivot Cache.
Note: If you copy a PivotTable that is already connected to one or more Slicers, the copied PivotTable will also be connected to those Slicers, so you may need to edit the Slicer Report Connections.
Besides finding gifts for our friends and family, this is also the season of giving. Team up with a non-profit and donate a percentage of sales to that charity. It will make people feel better about purchasing something from your site since you’re giving back.13. Bundle Products
Think of what Amazon does here. When you search for one product, Amazon will offer a couple of other suggestions that can be bundled together at a discount. You could also offer gift sets to stand out from other retailers. A nice move if you want to increase sales.12. Email Previous Customers 11. Offer Holiday Bonuses/Coupons/Upsells
People enjoy being rewarded for being a customer. Offer a complimentary gift when you purchase a certain amount, coupons or something extra like free gift-wrapping to attract repeat customers.10. Great Customer Service
People want to know that if there is a problem with their order or if they have a question regarding a product that they can reach a real, live person. Place your customer service number prominently throughout your site and have a live person on the other end of the phone. This is how Zappos became such a juggernaut.9. Free Shipping
We all love freebies. And, does it get any better when you notice that a site offers free shipping? An essential move for people who are most-likely on a budget throughout the holidays.8. Personal Suggestions/Experiences
Customers are constantly searching for gift recommendations or what the must-have item is this year. Offer consumers gift ideas through a list that was personally written by you or a ghost writer. Go the extra mile and share your own personal experiences with that product as well. Besides providing some personality, people may be more inclined to purchase a product if they know it’s been recommended by a real person.7. Offer One Day Sale or Discounts
Pretty much every online retailer will be offering some sort of sale or discount during the holidays. So, this should be a no-brainer. If you don’t offer a one-day sale or end of year discounts, people will move on to a site that has the better deal.6. Host Contests
Contests are a proven technique to gain attention. Whether it’s a wish list or caption contest, they are a simple and effective way to draw visitors to your site, which will hopefully result in more sales.5. Use Social Media
There’s so much more you can do with social media than just informing customers that you have a website where they can buy stuff. Have a holiday flash sale via Facebook. Post hashtags on Twitter that link to discounts or coupons. Conduct a Pinterest holiday board contest. There a number of ways that you can use social media networks to bring people to your site for the final sale.4. Make Sure You’re Smartphone Ready
The Google survey we mentioned earlier also discovered that three-quarters of smart phone owners will browse on their phones this season. Common sense, right here. You need to make sure that your site is compatible with smartphones. Whether that’s by having the correct size images or making sure that your checkout works properly, your site must be effective on these devices.3. Offer Gift Cards/Certificates
Gift cards and gift certificates are big business, which is why your site must absolutely offer one. Be certain that your site has a section devoted to gift cards that is prominently displayed on the homepage. It also wouldn’t hurt for the card to be enclosed in a decorated box or envelope.2. Feature Holiday Themes
Since everyone is in the holiday spirit, make sure that the graphics on your site are just as festive – just to remind people it is indeed the holidays! This could also be good chance to highlight some of your products, if you sell goods, right on the homepage. Also, make sure that all your social networks have holiday themed content. Tis’ the season.1. Follow Social Media
Keep up with trends via social media. We’re not just talking about only Facebook and Twitter, but also Instagram, Tumblr, and Pinterest. By following trends on social networks you’ll be aware of what items shoppers are searching for this season so you know what to push.
Excel already has a couple of ways to sort data quickly.
You can easily sort a data set by using the sort icons in the ribbon or the sort dialog box.
Then why do you need to know how to do this using VBA?
Knowing how to sort data using VBA can be helpful when included as a part of your code. For example, suppose you get a data set daily/weekly that you need to format and sort in a specific order.
I will cover how to create this later in this tutorial. Let’s first quickly get the basics straight.
When sorting using VBA, you need to use the chúng tôi method in your code.
The ‘Range’ would be the data that you’re trying to sort. For example, if you’re sorting the data in A1:A10, then ‘Range’ would be Range(“A1:A10”).
You can also create a named range and use it instead of the cell references. For example, if I create a named range ‘DataRange’ for the cells A1:A10, then I can also use Range(“DataRange”)
With the sort method, you need to provide some additional information through parameters. Below are the key parameters you need to know:
Key – here you need to specify the column that you want to sort. For example, if you want to sort column A, you need to use key:=Range(“A1”)
Order – here you specify whether you want the sorting in an ascending order or the descending order. For example, if you want the sorting in ascending order, you will use Order:=xlAscending
Header – here you specify whether your data set has headers or not. If it has headers, the sorting starts from the second row of the data set, else it starts from the first row. To specify that your data has headers, you will use Header:=xlYes
While these three suffices in most of the cases, you can read more about the parameters in this article.
Now let’s see how to use the chúng tôi method in VBA to sort data in Excel.
Suppose you have a single column without header (as shown below).
You can use the below code to sort it in ascending order.Sub SortDataWithoutHeader() Range("A1:A12").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo End Sub
Note that I have specified the data range manually as Range(“A1:A12”).
In case there might be changes in the data and values might be added/deleted, you can use the below code that automatically adjusts based on the filled cells in the dataset.Sub SortDataWithoutHeader() Range("A1", Range("A1").End(xlDown)).Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo End Sub
Note that instead of Range(“A1:A12”), I have used, Range(“A1”, Range(“A1”).End(xlDown)).
This will check the last consecutively filled cell in the column and include it in sorting. In case there are blanks, it will only consider data till the first blank cell.
You can also create a named range and use that named range instead of the cell references. For example, if the named range is DataSet, your code would now be as shown below.Sub SortDataWithoutHeader() Range("DataRange").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo End Sub
Now let me quickly explain the parameters used in the above examples:
Key1:=Range(“A1”) – Specified A1 so that the code would know which column to sort.
Order1:=xlAscending – Specified the order as xlAscending. If you want it to be in the descending order, use xlDescending.
Header:= xlNo – Specified that there are no headers. This is also the default value. So even if you omit this, your data will be sorted considering it has no headers.
Wondering where to put this VBA code and how to run the macro? Read this tutorial!
In the previous example, the data set did not have a header.
When your data has headers, you need to specify that in the code so that the sorting can start from the second row of the dataset.
Suppose you have a dataset as shown below:
Below is the code that will sort the data in descending order based on the sales of the stores.Sub SortDataWithHeader() Range("DataRange").Sort Key1:=Range("C1"), Order1:=xlDescending End Sub
Note that I have created a named range – ‘DataRange’, and used this named range in the code.
So far in this tutorial, we have seen how to sort a single column (with and without headers).
Now, what if you want to sort based on multiple columns.
For example, in the below data set, what if I want to first sort by the state code, and then by the store.
Here is the code that will sort multiple columns at one go.Sub SortMultipleColumns() With ActiveSheet.Sort .SortFields.Add Key:=Range("A1"), Order:=xlAscending .SortFields.Add Key:=Range("B1"), Order:=xlAscending .SetRange Range("A1:C13") .Header = xlYes .Apply End With End Sub
Below is the result that you will get.
In the above example, the data is first sorted by the state code (column A). Then within the state code data, it is again sorted by the Store (Column B). This order is determined by the code in which you mention it.
Something as shown below:
Below is the code that will allow you to do this:
Dim KeyRange As Range Dim ColumnCount As Integer ColumnCount = Range(“DataRange”).Columns.Count Cancel = False If chúng tôi = 1 And Target.Column <= ColumnCount Then Cancel = True Set KeyRange = Range(Target.Address) Range(“DataRange”).Sort Key1:=KeyRange, Header:=xlYes End If End Sub
Note that I have created a named range (“DataRange”) and have used it in the code instead of using the cell references.
Also note that as of now, this code will sort all the columns in ascending order only.
To do this:
Paste the code in the code window of the sheet in which your data resides.
Now what if you want to sort the first two columns (‘State’ and ‘Store’) in ascending order, but ‘Sales’ column in descending order.
Here is the code that will do it:
Dim KeyRange As Range Dim ColumnCount As Integer ColumnCount = Range(“DataRange”).Columns.Count Cancel = False If chúng tôi = 1 And Target.Column <= ColumnCount Then Cancel = True Set KeyRange = Range(Target.Address) If Target.Value = “Sales” Then SortOrder = xlDescending Else SortOrder = xlAscending End If Range(“DataRange”).Sort Key1:=KeyRange, Header:=xlYes, Order1:=SortOrder End If End Sub
Now let’s take this a notch further and show a visual Marker (arrow and colored cell) in the header when it is sorted.
Something as shown below:
To get this, I have added a new worksheet and made the following changes in it (you can download the example file and follow along):
Changed the name of the new sheet to ‘BackEnd’.
Copy and paste the headers from the data set to cell A3:C3 in the ‘Backend’ sheet.
Use the following function in cell A4:AC4: =IF(A3=$C$1,A3&" "&$B$1,A3)
Your backend sheet would look something as shown below:
Dim KeyRange As Range Dim ColumnCount As Integer ColumnCount = Range(“DataRange”).Columns.Count Cancel = False If chúng tôi = 1 And Target.Column <= ColumnCount Then Cancel = True Worksheets(“Backend”).Range(“C1”) = Target.Value Set KeyRange = Range(Target.Address) Range(“DataRange”).Sort Key1:=KeyRange, Header:=xlYes Worksheets(“BackEnd”).Range(“A1”) = Target.Column For i = 1 To ColumnCount Range(“DataRange”).Cells(1, i).Value = Worksheets(“Backend”).Range(“A4”).Offset(0, i – 1).Value Next i End If End Sub
Note that this code works well for the way my data and workbook is constructed. If you change the structure of the data, you will have to modify the code accordingly.
Download the Example File
You May Also Like the Following Excel Tutorials:
Update the detailed information about Using Slicers In Excel Pivot Table – A Beginner’s Guide on the Daihoichemgio.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!