Author

Erlinda Scarlet

Browsing

Did you know you can actually show the Sum of Sales in between any month or period? Did you know that the Sum formula is not the only option to calculate the total value? In this article, we will be discussing this exquisite feature in Excel known as the Running Total In. Let’s get started.

Running Total In – A Glance

Using a running total in a pivot table, you can calculate the running total value over a span of time such as days, months, and years. This feature comes as an inbuilt function in Excel and is handy when using large data sets. It adds the past value along with the current value to give an absolute output. It is sometimes known as the year to date analysis. You don’t require any formula to get the output, this function calculates without any formulas.

Lets take a look at the example

Example

In this example, we have a dateset of two customers and their quantity for each month.

Step 1: Drag and drop the required field from the PivotTable fields. In this example, the Date is dragged to the column area, Customer to Rows and Quantity to Values.

running total in

Step 2: Select the value you want to find the running total. In this example, I will select the D5 cell. So, the total of January, February, and March will be shown all together in the Month of March (D5).

Step 3: Right click on the value >> Show Value As >> Running Total In.

How to use Running Total in Pivot table 1

Step 4: The Running Total In dialog box appears, from the drop down select ‘Date’.

How to use Running Total in Pivot table 2

From the below image you can see that all the values before March has been added and and shown as the output.

How to use Running Total in Pivot table 3

To read more related articles click here

To watch more exciting videos on Excel and for more updates visit our You Tube channel

Did you know you can change the sum labels areas in the Pivot table? Did you know you can only press the space key to change the entire format in the label? In this article, we will be discussing how to change Sum views in label areas with a relevant example. Let’s get started.

Once your data is inserted in your Pivot table, the headings are automatically created with the summary function name included, so the default heading for the text vales is Row Labels and for number values, it comes with a summary function Sum of.

Since it is a default label it cannot be changed manually. When you select the cell and type the heading you require, it throws you an error message.

For example, if you change the label name from Sum of Quantity to Quantity, it pops an error message reading “PivotTable field name already exists”.  

sum views

Although, there is no inbuilt option to resolve this problem. It can be achieved by smart use of Excel features. So, here’s how you can do.

Example

Step 1:To change the Sum views, select the headings required in your PivotTable field. In this example, check the Region, Country, Quantity and Sales headings.

How to change Sum views in Label areas 4

Step 2: You can see the default labels, for every text column it is labeled as Row Label and for every number value (Quantity & Sales) it comes with Sum views (Sum of).

Step 3: Now I want to rename Sum of Quantity as Quantity only. And, I want to rename Sum of Sales as Sales only. This is where the Replace option comes in handy.

Step 4: Select the column heads that has the Sum views.

Go to Home tab >> Find & Select >> Replace. Alternatively, you can use the short cut key Ctrl +H.

How to change Sum views in Label areas 5

The Replace dialog box appears.

How to change Sum views in Label areas 6

Step 5: In the Find What box type “Sum of”, and in the Replace With box enter a “Space”.

How to change Sum views in Label areas 7

Click Replace All.

From the below image you can see that the headings having “Sum of” text have been replaced by space. And, the headings have appeared as “Quantity” and “Sales”.

How to change Sum views in Label areas 8

To read more related articles click here.

To watch more exciting videos on Excel and for more updates visit our You tube channel.

Did you know you can highlight cell rules even in pivot table data? In this article, we will be discussing how to apply conditional formatting using the highlight cell rules (greater than & less than) option in the Pivot table. So, Let’s get started.

Highlight Cell Rules – A Glance

Conditional formatting can be applied by selecting a group of cells and highlighting a preferred rule. The ‘highlight cell rule’ option in conditional formatting helps you to highlight cells under a few specific conditions. You can use numeric values, dates, and text values while working with these conditions. In the following example, we will see how to highlight cells that are greater and lesser than a specific value. Here’s how

Example 1 – Using Greater than

Step 1: To find the greater than value, select the value column first.

Step 2: Go to Home tab >> Conditional Formatting.

highlight cell rules

Step 3: Select Highlight Cell Rules >> Greater than.

How to Highlight Cell rules based on Values in Pivot Table 9

The Greater than dialog box appears.

How to Highlight Cell rules based on Values in Pivot Table 10

Step 4: In the dialog box specify the greater than value. In this example, I will enter the value as 100000.

How to Highlight Cell rules based on Values in Pivot Table 11

Step 5: Specify the color of the output you want to get in the dropdown. In this example, I will enter the color as ‘Green fill with dark green text’.

How to Highlight Cell rules based on Values in Pivot Table 12

Click OK.

From the below image you can see that all the values greater than 100000 have been highlighted in green fill with dark green text.

How to Highlight Cell rules based on Values in Pivot Table 13

Example 2 – Using Less than

Step 1: To find the less than value, select the values in your pivot data.

Step 2: Go to Home tab >> Conditional Formatting.

How to Highlight Cell rules based on Values in Pivot Table 14

Step 3: Select the Highlight Cell Rules >> Less than.

How to Highlight Cell rules based on Values in Pivot Table 15

The Less than dialog box appears.

How to Highlight Cell rules based on Values in Pivot Table 16

Step 4: In the dialog box, specify the less than value. In this example, I will enter the value as 75000. And, I will enter the color of my output as ‘Light Red Fill with Dark Red Text’. Click OK.

How to Highlight Cell rules based on Values in Pivot Table 17

From the below image you can see that all the values less than 75000 are being shown is Red Fill with Dark Red Text.

How to Highlight Cell rules based on Values in Pivot Table 18

Example 3: Using Between Option

Step 1: To find the between value, select the value column first.

Step 2: Go to Home tab >> Conditional Formatting.

highlight cell rules

Step 3: Select Highlight Cell Rules >> Between.

How to Highlight Cell rules based on Values in Pivot Table 19

The Between box appears.

How to Highlight Cell rules based on Values in Pivot Table 20

Step 4: In the dialog box, specify the between value. In this example, I will enter the between value as 50000 in the first box as my starting number and 100000 in the second box.

How to Highlight Cell rules based on Values in Pivot Table 21

Step 5: In the third box specify the color you want the output in, in the drop down. In this example, I will select the color as Light Red Fill.

How to Highlight Cell rules based on Values in Pivot Table 22

Click OK.

From the below image you can see that all the values between ‘50000 and 100000’ have been highlighted in light red fill.

How to Highlight Cell rules based on Values in Pivot Table 23

Example 4: Using Equal to option.

Step 1: To find the equal to value, select the value column first.

Step 2: Go to Home tab >> Conditional Formatting.

How to Highlight Cell rules based on Values in Pivot Table 24

Step 3: Select Highlight Cell Rules >> Equal to.

How to Highlight Cell rules based on Values in Pivot Table 25

The Equal to dialog box appears.

How to Highlight Cell rules based on Values in Pivot Table 26

Step 4: Enter the Equal to value. In this example, I have entered the value as 412589.

How to Highlight Cell rules based on Values in Pivot Table 27

Step 5: Specify the color of the output in the dropdown. In this example, I will select the color as Green Fill with dark green text.

How to Highlight Cell rules based on Values in Pivot Table 28

Click OK.

From the below image you can see that all the values that are equal to 412589 have been highlighted in Green fill with dark green text.

How to Highlight Cell rules based on Values in Pivot Table 29

Example 5: Using Text that Contains option

Step 1: Select the text column where you want to find the text.

Step 2: Go to Home tab >> Conditional Formatting >> Highlight Cell Rules >> Text that Contains.

highlight cell rules

The Text that Contains dialog box appears.

How to Highlight Cell rules based on Values in Pivot Table 30

Step 3: In the dialog box specify the text you want to find. In this example, I have entered the text as ‘Kart’.

How to Highlight Cell rules based on Values in Pivot Table 31

Step 4: Specify the color of the output in the dropdown. In this example, I will select the Yellow Fill with Dark Yellow Text.

How to Highlight Cell rules based on Values in Pivot Table 32

Click OK.

From the below image you can see that all the texts that has the word Kart is highlighted in yellow fill with dark yellow text.

How to Highlight Cell rules based on Values in Pivot Table 33

Example 6: Using A date Occurring option

To Highlight cell rules using a data occurring

Step 1: Select the columns that have the dates.

Step 2: Go to Home tab >> Conditional Formatting >> Highlight cell rules >> A date Occurring.

How to Highlight Cell rules based on Values in Pivot Table 34

A date Occurring dialog box appears.

How to Highlight Cell rules based on Values in Pivot Table 35

Step 3: From the dropdown, select the option you prefer to find dates. In this example, I have entered ‘This Week’.

How to Highlight Cell rules based on Values in Pivot Table 36

Step 4: From the next dropdown, select the color you want your output in. In this example, I have selected green fill with dark green text.

How to Highlight Cell rules based on Values in Pivot Table 37

Click OK.

From the below example you can see that all the dates for this week has been highlighted in green fill with dark green text.

How to Highlight Cell rules based on Values in Pivot Table 38

Example 7: Using the Duplicate Values option

To highlight cell rules using duplicate option

Step 1: Select the data to find the duplicate values.

Step 2: Go to Home tab >> Conditional Formatting >> Highlight Cell Rules >> Duplicate Values.

How to Highlight Cell rules based on Values in Pivot Table 39

The Duplicate dialog box appears.

How to Highlight Cell rules based on Values in Pivot Table 40

Step 3: By default it is set ad Duplicate Values in the dialog box.

Note: You can find find the unique values by selecting Unique option.

How to Highlight Cell rules based on Values in Pivot Table 41

Step 4: In the next dropdown select the color you want your output to be in. In this example, I have selected Light Red Fill.

How to Highlight Cell rules based on Values in Pivot Table 42

Click OK.

From the below image you can see that all the duplicate values in the data have been highlighted in light red color.

How to Highlight Cell rules based on Values in Pivot Table 43

To read more relate articles click here

To watch more exciting videos on Excel Visit our You tube channel.

Did you know you can list out the top or bottom value by percentage in your data? In this article we will be discussing this exquisite feature in Excel that will help you to filter top 10 percent (or any other number) of items by value.

Top or Bottom values by Percent – A Glance

When analyzing data’s in a Pivot table you can normally use filters to focus on a specific part of the data. In the similar way, you can use the top 10 option to filter and list out the top company’s based on a value or a percent. This feature comes in handy when working with large data’s, and it serves as a huge time saver.

Lets take a look at the example

Example

Let’s say from the below pivot data I want to get the list of the top 10 companies that have 40% as their Sales. Here’s how

Step 1: Click on the drop-down from the Row Label >> Value Filters >> Top 10.

top or bottom

Step 2: The Top 10 dialog box pops up, there are four categories to be specified.

  • Top or bottom – In the first category select the top or bottom option based on your preference.
How to filter values - Top or Bottom Percent 44
  • In the second category mention the number of the percentage you want the data filtered. In this example, I will enter 40.
How to filter values - Top or Bottom Percent 45
  • In the third category mention the type of value you need. In this example, I will select the percent value since I wanted the companies whose Sales is 40%.
How to filter values - Top or Bottom Percent 46
  • In the fourth category lists all the values listed in the value area. Since we have only one value field, only that field is shown in the dropdown. Click OK.
How to filter values - Top or Bottom Percent 47

As you can see from the below image Excel has given the records of the companies whose sales in 40% .

How to filter values - Top or Bottom Percent 48

To read more related articles click here

To watch exciting videos on Excel visit our You tube channel

Did you know you can create your own format and can also customize the styles? In this article, we will get to know about how to create and customize Pivot Table styles.

Customize Pivot Table Styles – A Glance

There are no options to change any in-built pivot table styles. Although, you can create your own custom pivot table style in your workbook in a format you prefer. You can also apply the customized pivot style to one or more pivot tables. A convenient way to customize the style is by first applying any in-built style and then duplicate it.

Here’s how

Example:

Sometimes, we may want to show important information in our pivot data. In this example, we will see how to add colors to our pivot table.

Step 1: Select any cell on the data. You can see that the Analyze and Design tab pops up.

Step 2: Go to the design tab and you can see different styles in the ‘Pivot Table styles’ group.
Under the Pivot Table Styles option you have a list of colors, from light colors to dark ones.

customize pivot table styles

Step 3: Select the drop down from the pivot table styles group, and select New Pivot Table Style option below.

How to Customize Pivot Table styles 49

The New Pivot Table Style option appears.

How to Customize Pivot Table styles 50

Step 4: From the Table Element head, you can select any row or column in the list which you want to highlight in a different color. In this example, I will be highlighting all of the Company’s name.

Step 5: From the Table Element I will be selecting ‘Row subheading 1’. Click the Format option below.

How to Customize Pivot Table styles 51

Step 6: The format cells dialog box appears. Select the Fill tab and click on any color you prefer to apply. Click Ok.

How to Customize Pivot Table styles 52

Step 7: You can customize pivot table styles by setting a new name in the New Pivot Table box.

In this example, the new name entered is ‘My_pivot table02’. Click OK.
Note: To set this style as a default PivotTable style, check the ‘Set as default PivotTable style for this document’ chekbox.

How to Customize Pivot Table styles 53

Step 8: You can now go back to the Pivot table style group and click the drop down. You can now see from the Custom head, that you have customize Pivot table styles.

How to Customize Pivot Table styles 54

After selecting the custom style, you can see that the style of your pivot data is changed as you set. You can apply the customize Pivot table style in any data in that particular document.

How to Customize Pivot Table styles 55

To read more related articles click here

To watch more exciting videos on Excel visit our You Tube channel.

Did you know you can name cells based on your preference? Did you know you can set meaningful names as your formulas? Do you want your formulas to be all the more descriptive? Well, then you are looking at the right article. Here’s everything you will know about how to create a Named Range. So, Let’s get started.

Named Range – A Glance

A named range shows a descriptive name for a cell or a range of cells in a worksheet. It is an easy and speedy process to create a named range. It serves as a huge time saver and is efficient to use. Instead of using the cell reference, you can simply use the name allocated to the cells.

Lets take a look at some examples to create a named range.

Example: 1

Step 1: Select the cell range, in this example I will be creating a named range for cell C5: C13.

Step 2: An easy way to create a named range is by selecting and cells and naming them in the Name box. Alternatively, you can also press Alt+F3.

Step 3: In this example, I have named the cells as Revenue_2016 in the name box.

create a named range

Step 4: Hit Enter, you can now see the name of the cell in the drop down of the name box.

create a named range

Step 5: As you can see from the below image, the cell name has been added in the formula list and therefore you can directly apply it by pressing the tab key or by double-clicking.

How to create a Named Range in Excel 56

From the below image you can see the result.

How to create a Named Range in Excel 57

Example: 2

You can also create a name range using the name manager option.

Step 1: Go to the Formulas tab >> select Name manager. Alternatively, you can use the shortcut key Ctrl+F3.

How to create a Named Range in Excel 58

Step 2: The New Manager dialog box appears. Since we are going to create a new name select the New tab.

How to create a Named Range in Excel 59

Step 3: A New Name dialog box appears.

How to create a Named Range in Excel 60

Step 4: Enter the name you want to set in the Name box, if you want the name to be available in your entire workbook select the workbook in the scope box, to show the name in a single worksheet select the sheet name. Click Ok.

How to create a Named Range in Excel 61

Step 5: You can see that the name range is given in the formula list.

How to create a Named Range in Excel 62

From the below image you can see the result.

How to create a Named Range in Excel 63

These are two ways in which you can create a named range easily and efficiently.

Points to remember:

  • Names cannot contain spaces or any special characters, instead, they can be replaced by an underscore (_), dot (.), and backslash (\).
  • Names cannot begin with numbers.
  • Names cannot coincide with the cell references (Eg. ‘A1’ or ‘M100’)
  • Names are not case sensitive.

To read more related articles click here.
To have a clear vision on Named ranges in Excel – Click here

To have Hands on experience download the working file attached below.

Practice Files

Did you know you can analyze your data by years, months, or even by weeks? In this article, we will be discussing on how to group by dates in Pivot Table. Group by dates includes grouping by years, months, and weeks. So, let’s see how this exquisite feature works.

Group by Dates in Pivot Table- A Glance

Pivot table comes with a feature of grouping dates, numbers, and even text fields. In this article, we are going to see how to group dates by year, month, and weeks. This can be done manually by selecting the fields and in the pivot table and grouping them according to your preference. This a highly recommended feature that helps you to analyze the results between different fields when using large datasets.

Group by Years

Step 1: Be on the data set and Go to the Insert tab >> Pivot table.

Group by Dates in Pivot Table

Step 2: In the Pivot table fields you will find all the headings of your data.
In this example, we are going to group the data by years. So, drag and drop the ‘Date’ heading to Rows, and Sales to Values. You can see the values displayed in the worksheet in the below image.

group by dates

Step 3: To group by dates in years, Go to the Analyze tab >> Group Selection.

How to use Group by Dates in Pivot Table 64

Step 4: In the Group Selection box, select ‘Years’ from the ‘By’ list. Click Ok.

group by dates

You can see from the below image that the pivot table has listed the data grouped by years.

group by dates

Group by Months

Similar to the way we grouped Years, we can also group by Months.

Step 1: To group by dates in Months, select any cell on the data in the Pivot table.

Step 2: Go to the Analyse tab >> under Group section click Group selection.

How to use Group by Dates in Pivot Table 64

Step 3: In the dialog box, select Months as well as Years. You can select more than one options by pressing Ctrl key.

How to use Group by Dates in Pivot Table 66

From the below image you can see that the Pivot table has summarized the data by Months along with their Years.

How to use Group by Dates in Pivot Table 67

Group by Weeks

For some data’s it makes sense to analyze on a weekly basis. For example, any customer services related data. You can use the group by dates in week option to analyze any sales that happened within a week or between any specific days.

Step 1: Select any cell on the data in the pivot table.
To know how to create a Pivot table please Click Here.

Step 2: Go to the Analyze tab >> select the Group Selection.

How to use Group by Dates in Pivot Table 68

Step 3: In the dialog box, select the Days option. After selecting you will notice that the Number of Days option becomes available. Group by days and specify the number of days to be used while grouping. In this case, I will group the number to a week that is 7 days.

How to use Group by Dates in Pivot Table 69

Pro Tip: To change the grouping and to begin the week from Monday, you need to change the start date from the Auto tab in the box. (The starting and Ending dates are taken by default based on the dates given in the data).

You can see from the below image that the the pivot table has grouped all the weekly sales.

How to use Group by Dates in Pivot Table 70

Note: When you use the Day option in the group by dates method, you cannot group it using more than one option.

Hope this article contains a precise outcome to group by dates in pivot table,want to add multiple subtotals in Pivot table in Excel- Click here

To watch more exciting videos on Excel and to get our regular updates visit our You tube channel

Ever thought of creating your own Error message? Do you want your error messages to alert exactly where you went wrong? Well, then you are looking at the right article. Here we will be discussing how to create your own Custom Error in data validation. So, let’s get underway.

Custom Error – A Glance

This feature in Excel is exclusively used to create your own error message. Data Validation Error Messages, let’s you know if what you typed does not coincide with the rule you set. The following steps will guide you to create your own custom error message.

Lets take a look at the example.

Step 1: Prepare a data set in your worksheet,
In this example, I have listed down few employees whose salary should be between 10,000 – 30,000 not more or less than that.

Step 2: Apply the data validation, Go to the Data tab >> Data Validation. Alternatively, you can use the shortcut key Alt+D+L.

How to use Custom Error in Data Validation 71

The data validation box appears.

custom error

Step 3: Select the cells you prefer.
Go to the Settings tab >> in the Allow box select Whole number >> in the Minimum box enter 10,000 and in the Maximum box enter 30,000.

custom error

Step 4: Select the Input Message tab, enter the Title and the Input Message.
In this example, the title is entered as ‘Salary restriction applied’, and in the Input Message enter ‘Salary limit is between 10,000 – 30,000.’

custom error

Step 5: Last but not the least, apply the Error Alert.
Select the Error Alert tab, select any relevant style based on your preference. Give an appropriate Title and Error message, as this message will be used as the Error box.

How to use Custom Error in Data Validation 72

Lets take a look at the result. In this example, the value is entered as 35,000 which is above the limit set.

How to use Custom Error in Data Validation 73

Now, you can see your custom error box popping up.

How to use Custom Error in Data Validation 74

To read more related articles click here

To watch more exciting videos on Excel visit our You tube channel

It is always a task to count or number specific details when working with large data sets. In this case, there is only one option that comes in handy, which is called the COUNTIF function. In this article, we will be discussing the function along with relevant examples. So, let us get started.

COUNTIF Function – A Glance

This function counts the value or the number of cells in a range when a given condition is met. The count includes dates, numbers, and texts. Logical operators such as (<,>,<>,=) and wildcard characters like (*,? ~) can also be supported in the COUNTIF function.

Lets take a look at some examples.

Step 1: Be on the data where you want to get the count and select the COUNTIF function.

How to use COUNTIF function in Excel 75

Syntax

The Syntax of the COUNTIF function is

How to use COUNTIF function in Excel 76

Step 2: Select the range from where you want to get the count. In this case, I want to know the total number of sales done by each person. So am selecting the Salesman column i.e. C5: C14.

How to use COUNTIF function in Excel 77

Step 3: In this step, we have to give the criteria i.e of whom you want to get the count, In this case, I want to know the total sales done by Pinky. You can either write the name in the formula itself using double quotes (“Pinky”) or give a reference. In this case, am going to give a reference.
Note: It is suggested to give a reference because In case if you want to know the count of sales of another person you can just change it in the referred cell rather disturbing the formula.
The formula used:=COUNTIF(C5: C14, E16)

COUNTIF FUNCTION

Step 3: From the below image you can see that the result = 4.

How to use COUNTIF function in Excel 78

Points to remember:

  1. COUNTIF only supports one condition.
  2. COUNTIF is not case sensitive.
  3. Text values in criteria should be enclosed with double quotes (” “)
  4. COUNTIF cannot match longer than 255 characters.
  5. Referencing any workbook that is closed will result in a #VALUE error in COUNTIF.

To read more related articles click here.

To watch exciting videos on Excel and for regular updates visit our You tube channel.

Download the Practice files for better hands on experience

Practice File

Did you know you can check the day of a date in Excel? In this article, we will be discussing how to use the Day function in Excel. So, here we go.

DAY Function – Introduction

This function takes dates or serial numbers as an input and it gives you the day in the given month. In simple words, it returns the day of the month. There is only one argument in this function that is serial_number. It is the date corresponding to the day which you will find. You can also use this option to extract and input a day value to another function.

Note: Date‘s are also serial numbers, the output will be the same irrespective of the format.

Example:

dayfunction

In the above image you can clearly see how the function is used.

To read more related articles click here

To watch exciting videos on Excel visit our You tube channel.