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.

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.

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

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

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”.

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.

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.

The Replace dialog box appears.

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

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”.

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.

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

The Greater than dialog box appears.

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

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’.

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.

## 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.

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

The Less than dialog box appears.

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.

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

## 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.

Step 3: Select Highlight Cell Rules >> Between.

The Between box appears.

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.

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.

Click OK.

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

## 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.

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

The Equal to dialog box appears.

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

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.

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.

## 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.

The Text that Contains dialog box appears.

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

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

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.

## 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.

A date Occurring dialog box appears.

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

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.

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.

## 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.

The Duplicate dialog box appears.

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.

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.

Click OK.

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

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.

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.
• In the second category mention the number of the percentage you want the data filtered. In this example, I will enter 40.
• 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%.
• 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.

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

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.

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

The New Pivot Table Style option appears.

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.

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

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.

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.

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.

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.

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

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.

From the below image you can see the result.

## 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.

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

Step 3: A New Name dialog box appears.

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.

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

From the below image you can see the result.

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 have a clear vision on Named ranges in Excel – Click here

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.

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.

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

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

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

## 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.

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

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

## 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.

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

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.

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.

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

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.

The data validation box appears.

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.

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.’

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.

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

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

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.

## Syntax

The Syntax of the COUNTIF function is

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.

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)

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

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.

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:

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