Author

Praveen Kumar

Browsing

Highlight the Cells using Colour Scales in Pivot Table

Colour Scales are basically used features for heating maps. This feature helps you in colouring the cells in different gradients to show the differences between the cells. Then this article will help you with conditional formatting options like Colour Scales in pivot table. Let’s get started now.

Example: Here I have a list of collective data and now I want to highlight the background to show the differences between the sales in 2019 and 2020 columns. Follow the below steps to represent the cells using colours

Step 1: The first step is to create a pivot table for the data.
To know how to create a Pivot table please Click Here.

How to Apply Colour Scales in Pivot Table 1

Step 2: To add a field, Tick the checkbox before the field name in the Pivot Table Fields panel. When you select the field name, the selected field name will be inserted into the pivot table

Apply Color Scales in Pivot Table

Step 2: Select the range of cells where you want to apply the formatting.

Data Bars

Step 3: Then go to Home > Conditional Formatting > Colour Scales and choose the appropriate format for your data.

Here I have selected Blue – White – Red Colour Scale to show the differences in highest, lowest, and middle values by colours.

Data Bars

Pro Tip. However, if you want to more innovative. You can select More Rules from Colour Scales option and choose either choose 2 – colour scale or 3 – colour and then customize your colours.

Step 4: Once you select the colour scale, the formatting will be applied to the selected data.

Data Bars

Do follow our YouTube channel for regular updates.

Highlight the Cells using Icon Sets in Pivot Table

Usually, you can find icon set cells in project management reports. The cells will be visualized in icons, and each would vary by the change in range. You can categorize your data using icon sets, which provides the icons for the cells denoted by its range. you can also merge more than one conditional format in icon sets. Then this article will help you with conditional formatting options like icon sets in pivot table. Let’s get started now.

Example: Here I have a list of collective data and now I want to set the icon for the sales in 2019 and 2020 columns. Follow the below steps to highlight the cells using icon sets.

Step 1: The first step is to create a pivot table for the data.
To know how to create a Pivot table please Click Here.

How to Apply Icon Sets in Pivot Table 2

Step 2: To add a field, Tick the checkbox before the field name in the PivotTable Fields panel. When you select the field name, the selected field name will be inserted into the pivot table

Icon Sets in Pivot Table

Step 2: Select the range of cells where you want to apply the formatting.

Data Bars

Step 3: Then go to Home > Conditional Formatting > Icon sets and select the icon from the drop – down list.

Here I have selected Red to Black icon to set the icon for the sales in 2019 and 2020

Data Bars

Pro Tip. You can also customize your icon if you want. To do this, select the range of cells and go to Conditional Formatting > Icon Sets > More Rules and apply your changes in it.

Step 4: Once you set the icon, the formatting will be applied to the selected data.

Data Bars

The Below image will help you to comprehend better by combining the data bars, color scales and icon sets altogether.

Data Bars

Pro Tip. To clear the formatting, you can go to Home > Conditional Formatting > Clear Rules and select the option from the drop-down list. Once you select the option, the formatting will be cleared.

Data Bars

Do follow our YouTube channel for regular updates.

Do you know how to create dropdown list? Did you know it is commonly used by Data validation? Did you know you can restrict the data you want through Data validation? Excel can not only help you know but also makes you understand the use of it.

Data validation is a function that is found in Excel 2010, 2013, 2019 and Microsoft 365. It is a function that helps you restrict the data you want. It makes sure you have entered the correct text and value. It also restricts value entry from the cell that is unnecessary.
Yet, the common function of Data validation is creating a drop-down list. It helps you in creating a list of names or things or any data you want to make your work easier.

Step to be followed for Create Dropdown Lists– Data Validation

Step 1: Be on the blank cell where you want Dropdown Lists for Data Validation Lists to appears. And then Go to Data tab > Data Validation under data tools option.

Create Dropdown List

Pro tip:
You can use also keyboard shortcut Alt +A+V+V for Create Dropdown Lists.

Step 2: You will see Data Validation dialogue box pops on the screen, then under Allow field click the drop-down and select List from the options.

How to Create Dropdown List-Data Validation 4

Step 3: In the Source field you can either type the order or List from a range of values.When you click to List from a range of values.

How to Create Dropdown List-Data Validation 5

Step 4: Now an open up a small window that will auto-fill cell references when you select a range of cells on the worksheet. And click OK.

How to Create Dropdown List-Data Validation 6

Step 5: After completed the above steps Dropdown Lists has been created successfully.

How to Create Dropdown List-Data Validation 7

To know more about data validation click here

For more information visit our YouTube channel.

Have you ever found it hard to get a particular year from large data? Did you know group dates in the pivot table is an extremely helpful function?  Do you need a quick report? Pivot table in Excel has the features to group your dates.


Suppose, we have a data set of sales in a few years, as shown below. And we want them to Group by Quarters and Years.

The following steps help to do that.

Step 1The first step is to create a pivot table for the data.
To know how to create a Pivot table please Click Here.

Group by Quarters and Years

Step 2: To add a field, Tick the checkbox before the field name in the PivotTable Fields panel. Here I have drag Date to Rows field ,Sales person to columns and Sales to Values Fields in PivotTable.

How to Group by Quarters and Years in Pivot Table 8

Step 3: Be on the date columns and Go to Pivot Table Analyze >>Group selection under group option.

How to Group by Quarters and Years in Pivot Table 9

Step 4: Grouping dialogue box appears on your window.Select Quarters and Years then click OK

How to Group by Quarters and Years in Pivot Table 10

The below image represents the data Group by Quarters and Years.

How to Group by Quarters and Years in Pivot Table 11

Note:
For Excel 2016 and above version if you drag and drop date to row field it automatically groups the date to (Years, Quarters, Months,Date).

To know more about Excel go through our Formulas.

Do follow our YouTube channel for regular updates.

Did you know can count values in Pivot Table? Do you know you can also count your text in this data set? Do you want to know how to read it? Find how you can count in the Pivot Table here.

Do you know what is a pivot table?

A pivot table is generally demography of data from large data. A pivot table is considered as one of the most important and redoubtable features in Microsoft Excel. It is a strong function because it helps you to understand large and extensive data files.

How to count values in pivot table

If you are managing a big sales company all over the world. And you have large data, you might find it difficult to find the number of sales from a large datasheet. In such cases, count function gives you the specified number you need easily.

For example, from the below data we want to return the count of the companies under each Stock classification group (SC_Group).  The following are the steps

Step 1The first step is to create a pivot table for the data.
To know how to create a Pivot table please Click Here.

count values in pivot table

Step 2: To add a field, Tick the checkbox before the field name in the PivotTable Fields panel. Here I have drag SC_group to Rows field and again drag SC_group to Values Fields in PivotTable.

Note:

  • If all the cells in the column are numerical, then the function default will be the sum.
  • If any cells in the column are either blanks, texts, or errors, then the function default will be the count.
How to Count values in Pivot Table 12

Pro tip :
Right-click the cell and select Summarize Values By Count from the drop-down menu.

Alternatively :
Step 1: Select any cell within the column and right-click and select Value Field Settings.

How to Count values in Pivot Table 13

Step 2 :Once you click Value Field Settings, a dialog box appears in the window.In a dialog box, under Summarize Values By choose the option Count from the list and click OK.

How to Count values in Pivot Table 14

To know more about Excel go through our Formulas.

Do follow our YouTube channel for regular updates.

There are a lot of options in Excel when it comes to Print. There are multiple ways to print worksheets. In this article, we are going to see the different ways in which we can print more than one worksheet.

Following are the different ways to print all worksheets:

1 Print all worksheets:

Step 1: To print multiple worksheets in Excel, select the required sheets at the bottom of the spreadsheet.
1. Click right using mouse/touchpad in the sheet 1 and select all sheets.

Print All Worksheets

Step 2: Now click Ctrl >> P or Go to File >> Print to print all worksheets.

2. Print only selected worksheets.

Step 1: Select the work sheets you want to print.
Hold Shift key and select the sheets with the help of mouse/touchpad.

Print All Worksheets

Step 2: Now click Ctrl >> P or Go to File >> Print.
Under print Go to settings and select print active worksheets.

Print All Worksheets

Step 3: Click on print.

3. To print sheets with particular print area.

Step 1: Select the area you want to print and Go to Page layout >> set print area.

Print All Worksheets

Step 2: After setting the print area Go to File >> Print to print.

Subscribe to our YouTube channel for regular updates.
To learn more Excel Formulas Click Here.

We all face a issue while printing an Excel document which is too long, where you might notice that the first page of your data contain header columns but remaining pages don’t have it. In this post, we will address the problem with the help of the Excel page setup option.

The following are the step to add header columns for all pages in the Excel.

Step 1: Open the worksheet you are going to print.
print_1
Step 2: Be on the data Go to page layout tab >>and click on print tiles under page setup options.
print_2
Step 3: Displays page setup dialogue box, find Rows to repeat at top in the Print titles section.
print_3
Step 4: Click the Collapse Dialog icon next to “Rows to repeat at top” field. The Page Setup dialogue window is minimised, and you get back to the worksheet. Select a row or several rows that you want to print on every page.
print_4
Step 6: Click Enter or the Collapse Dialogue button again to return to the Page Setup dialog box.
print_5
Step 7: Click on Print Preview to see the result.
Or press Ctrl+F2
print_6

Bonus tip: Let us see how to add page numbers for all the pages.

Step 1: Be on the data go to Insert tab >> under Text >> select Header&Footer option.
print_7
Step 2: Displays header and footer tools option, under header&footer tools select page number and number of pages option. &[Page] of &[Pages]will be displayed on header and footer section.
print_8
Step 3: Click anywhere outside the footer to see the page number.
print_9

Subscribe to our YouTube channel for regular updates.
To learn more Excel Formulas Click Here.

Generally, a watermark is a logo, stamp or signature superimposed onto an image or a document which is a more or less transparent image or text that has been applied to a piece of paper, another image to either protect the original image or to make it harder to copy the item.

Note: Excel doesn’t come with a built-in watermark option. But you can use an image, like a logo, to create a watermark.

Let’s say a financial manager wants to send a report to the board of directors with a watermark of the company. There are three methods to add a watermark in excel file.

  1. By using header and footer.
  2. By using an image as a watermark.
  3. By using WordArt as a watermark.

By using header and footer.

Step-1: Be on the data, Go to Insert >>Header & Footer under text option.

How to add watermark_1

Step-2: Select Header & Footer enables header and footer option on your excel file.

How to add watermark_2

Step-3: Select Picture from the Header & Footer Elements.

How to add watermark_3

Step-4: Displays insert picture dialogue box, select the picture you want to add as watermark and click Insert.

How to add watermark_4

Step-5:  In the header, you’ll see & [Picture],click anywhere outside the header to see the watermark.

How to add watermark_5

Step-6: To customise your watermark,be on the header and Go to Header & Footer tab, click Format Picture.

How to add watermark_6

By using an image as a watermark.

Step-1: Go to Insert tab>>Pictures under illustrations option.

How to add watermark_7

Step-2: Displays insert picture dialogue box, select the picture you want to add as watermark and click Insert.

How to add watermark_8

Step-3:To customise your watermark,Go to Format Picture tab and edit the picture as your requirement.

How to add watermark_9

By using WordArt as a watermark.

Step-1: Go to Insert tab, click on the Word Art menu.

How to add watermark_10

Step-2: Enter your text option pop-ups, type your watermark text.

How to add watermark_11

Step-3: To customise your watermark text,you can either edit in Shape Format or right-click>>format shape.

How to add watermark_12

To know more on Excel go through our Articles

Don’t miss out on free session on Excel and more. To register Click here.

Subscribe to our YouTube channel for regular updates.

Do you know that the data can be filtered based on font colour or cell colour? The filter option will enable you to display only font colour or cell colour for an individual column and hide the remaining rows temporarily from our view. This article will explain how to Filter by font colour, cell colour.

Filter based on Cell Colour

Below is a data set as an example we have one column with coloured cells i.e. the Product name. In this example, we will filter the cell representing a green colour.

Filter by font colour_2

Step-1:Be on the data set,click the drop-down in the column header, and point to Filter by colour.

Filter by cell colour_2

Step-2:Select the colour to be filtered under Filter by Cell Colour.

Filter by font colour, cell colour 15

The below image is output of Filter by Cell Colour.

Filter by font colour, cell colour 16

Filter based on Font Colour

Below is a data set. We have used different font colours for each product. In this example, we aim to filter the cell containing a blue font colour.

Filter by font colour_3

Step-1:Be on the data set,click the drop-down in the column header, and point to Filter by Colour.

Filter by cell colour_1

Step-2:Select the colour to be filtered under Filter by Font Colour.

Filter by cell colour_2

In the below image,the home theatre which in blue font colour has been filtered.

Filter by cell colour_3

Filter based on Selected cell

Another way to filter data in Excel, to filter-based on the selected cell. we are various criteria to filter selected cell i,e. selected cell’s value, colour, font colour, or icon. The following are the given steps to execute filter based on selected cell value.

  • select on the cell containing the value, colour or icon you want to be filtered. In the given example we are going to filter the cell containing a green font colour.
Filter by font colour, cell colour 17
  • Right-click >> Filter>>Filter by Selected cell’s font colour
    You can filter by selected cell’s value, colour, font colour, or icon.
Filter by font colour, cell colour 18

The below image represents the data filter based on Selected cell font colour.

Filter by font colour, cell colour 19

To know more on Filter in Excel- Click here

Subscribe to our YouTube channel for regular updates.

Looking for specific information? Are large datasets making it difficult to find the information? Thankfully, Excel made it easy for us to narrow down the search with the help of a massive tool Filter or AutoFilter. This article will help you understand the role of Filter in Excel.

tutorial of filer in excel

What is Filter in Excel?

  • Filter in excel is the easiest way to display the information which is relevant to us from the large dataset and hides all other data from our view.
  • Excel can filter rows by format, value, or based on given criteria.
  • By applying a filter, we can even copy, edit, print only the visible cell /rows without changing the entire list.

Key Difference between Excel Filter and Excel Sort –

Generally, many of us think filter and sort are the same, but there is an immense difference between them. The following are the key difference

  1. Excel Filter – when we filter our data it shows only the data which we need, remaining data are temporarily hidden from our view. For example to display only “alphabets” from the list (4,2,8, a, b,1,9) to (a,b).
  2. Excel Sort – when we sort our data, the entire array /table is rearranged, for example arranging a list in ascending order (8, 2, 3) to (2, 3, 8).

Common ways to add a filter in Excel:

  1. Be on the data, Go to Data Tab>> Click Filter under Sort & Filter option.
filter button on data tab
     2.   Be on the data, Go to Home Tab >> Click Sort & Filter > Filter.
shot & filter option

   3.   Press Ctrl +Shift +L key to add or remove the filter.

Pro tip: Ensure that datasets have a header row with the column names. A filter cannot be applied without a heading.

How to apply a filter in Excel?

  1. Be on the column where you want to filter and click, the drop-down arrow.
    Shortcut Key: Press Alt+ Down arrow key to display the drop-down list.
  2. Uncheck the Select all box.
  3. Check the boxes for filtering data and click OK.
filter in excel process

Note: It also allows you to filter multiple columns.

How to use a filter in excel?

Excel Filter provides a bundle of advanced tools that helps to filter specific data types such as text, numbers, and dates.
Let’s have a quick look at each option and see the steps to create a filter that suits your data type.

Text filters –

To filter a column based on the text-value, Excel filter provides plenty of advanced options such as

  1. Text filters that begins with or ends with a specific character(s).
  2. Text filters that contains or does not contain a given character or word.
  3. Text filters that equal or does not equal a specified character that exactly matches.

Excel enables the Text Filters option only when your column contains text values. The below image shows the entire text filter option.

text filters in excel

For example, let’s filter the rows which contain the “Keyboard” word.

  1. In the drop-down menu, select the text filters that Contains.
    Note:
    you can select any option as per requirement.
contains option in text filters

2.   A Custom AutoFilter dialogue box pops-up on your screen. In the box to the right of the filter, you can either enter the text, copy-paste text, or select the text from the dropdown lists and click OK.

custom autofilter dialogue box with contains option

3.   This Feature display cells which contains “Keyboards”.

custom autofilter result box

Filter column with Multiple criteria 

1. Goto text Filters >> Does not contain

does not contain option in text filters

2. A custom filter dialogue box pops up on your screen under that select And/Or button depending on your need, whether both or either criterion should be true
3. Select the comparison operator from the drop-down for the second criteria, type or select the value text from the drop-down lists. In this example, we will filter rows which “Does not contains” Keyboard and Home theatre.

custom autofilter with does not contain and does not end with

4. This function applies a filter by specified criteria.
The output looks like the below image, if you see clearly under the rows section there will be double lines which indicate some of the rows are hidden due to the applied filter.

result of custom autofilter

How to create a filter with wildcard characters?

The following are the wildcard characters that can be used for creating a filter.

Wild characters Description Example
? (Question mark) Used to match the single Character ?one filters “done” and “gone”
* (asterisk) Used to match any sequence of characters Mobile* filters “Mobile phone” and “Mobile charger”
~(tilde)
followed by *, ?,or ~
Used to get the cell which contains a question mark, asterisk, or tilde. When~? filters “When?”

Number Filter 

Number filter helps to manipulate numeric data in a variety of ways as follows:

  1. Filters a number i.e. equal or not equal to the given number.
  2. Filters a number which is greater than, less than, or between the specified numbers.
  3. Filters top 10 or bottom 10 values.
  4. Filters above average or below average values.

The below image is the overview of the Number filter.

number filters option

For example, to filter the data that contains the amount less than 2,000.

  1. Be on the numerical data to enable number filter, then click the dropdown in the column header, and point to number filters.
  2. In the drop-down menu, select the Number filters >> less than.
number filters option with less than

3. A Custom AutoFilter dialogue box pops-up on your screen. In the box to the right of the filter, you can either enter the number, copy-paste number, or select the number from the dropdown list and click OK.

custom autofilter dialogue box for less than

4. With the help of the specified criteria filter results in the output as below.

custom autofilter output

Date Filters 

Excel date filter provides a bunch of choices that allows you to filter records for a certain period.

By default, Excel data filter will group all dates in a given column with a hierarchy of years, months, and days. Date filters allow to display or hide data for a day, week, month, quarter, year, before or after a specified date, or between two dates. The Below image represents the entire date filter options.

date filter option

Generally, Excel filter by date works with a single click. For instance, to filter rows containing records for the current month, click Date filter>>This Month.

Assume that you own a computer accessories shop in the center of the city and you want to know the items sold between 1 st to 5th April of 2019. This can be done by applying a date filter, below are the steps to be followed

  1. Be on the date columns to enable date filter, then click the dropdown in the column header, and point to date filters.
  2. In the drop-down menu, select the Date filters >> Between.
between option in date filter

3. A Custom AutoFilter dialogue box pops-up on your screen. By default, Excel suggests using “is after or equal to” and ” is before or equal to” comparison operators. (Here, I have given 1-apr-19 to 5-apr-19)and click OK.

custom dialogue box for date filter
date filter output

Filter by colour

Excel can filter your data if it is formatted manually or through conditional formatting. The following are the most commonly used format in excel.

  1. Filter based on cell colour.
  2. Filter based on font colour.
  3. Filter based on cell icon.

How to copy data after applying filter?

  1. Select any filtered Column or cell and then press Ctrl + A to which selects filtered data including column header.
  2. Press Alt +; to select only visible cells ignoring hidden rows or Go to Home tab >> Editing group >> Find & Select > > Go to Special> Visible Cells only.
  3. Now you can copy and paste the visible data.

How to remove the filter?

To remove all filters from the worksheet.

Go to the Data tab > Sort & Filter group and click Clear or press Shift+ Ctrl+ L to apply or remove the filter.

filter removing option

Filter Function 

If you are quite familiar with Excel functions, you can use the filter function to filter the data based on the criteria.
In the following example, we use the formula = FILTER(B4:D18,C4:C18=G3,) to return all records for a wireless keyboard.
You can FILTER based on multiple criteria with the help of (*) operator.

filter function

To know more on Excel go through our Articles

Don’t miss out on free sessions on Excel and more. To register Click here.

Subscribe to our YouTube channel for regular updates.