Author

Aravindh Ravichandran

Browsing

Want to Modify/ Edit a calculated item? Don’t know how do do it? You are in the right place in this article we are going to discuss how to modify/edit a calculated item.

Following are the steps to Edit a Calculated Item

To know how to create Calculated Item Click Here.

Problem: I have created a calculated column clubbing all the expenses to Printing & stationery expenses, Office expenses, and maintenance expenses. But, by mistake, I’ve added Bolts, Nuts, screws & spares cost to Printing & stationery which increased the total cost of expenses, So let us see how we can edit the calculated item.

Edit a Calculated Item in the Pivot Table

Step 1: Be on the data Go to Analyze tab >> under calculation tools >> find Field, Items &select option.

Edit a Calculated Item in the Pivot Table
Calculated Item in Pivot Table

Step 2: A drop down appears on selecting Field, Items &select option, From that select Calculated Item.

Edit a Calculated Item in the Pivot Table

Step 3: A Insert Calculated Calculated Item dialogue box Appears.

Calculated Item in Pivot Table

Step 4: Under the name option there is an small drop down icon click on it which displays the list of calculated items you have created. From that select the item which you want to edit/modify. In this x=case am selecting Printing & Stationery.

How to Edit a Calculated Item in the Pivot Table? 1

Step 5: Now Under the formula option you can add or remove any item. In this case am going to remove Nuts, screws & spares cost which I.ve added by mistake. And click on modify to modify the data as per the change

How to Edit a Calculated Item in the Pivot Table? 2

Step 6: Now you can see the difference of before and after editing the data. Look at the printing and stationery expenses 150 rs is reduced.

How to Edit a Calculated Item in the Pivot Table? 3
How to Edit a Calculated Item in the Pivot Table? 4

Delete a Calculated Item

You also have the option the delete a calculated item, let us try to delete a calculated column from the above data.

Step1: Follow the above steps till step 3.

Step 2: From the name drop down select the item which you want to delete, In this case am selecting Office Expences.

How to Edit a Calculated Item in the Pivot Table? 5

Step 3: After selecting the calculated item click on Delete and press OK.

How to Edit a Calculated Item in the Pivot Table? 6

Step 4: Now you can see that the selected item got deleted from the data.

How to Edit a Calculated Item in the Pivot Table? 7

Hope you got clarity on How to edit a Calculated item in Pivot Table, Don’t forget to try it out with your data if there are any suggestions drop in the comment box.

To know more about Excel go through our Formulas.

Do follow our YouTube channel for regular updates.

Heard about Calculated item in Pivot Table? Curious to know about it? Don’t wory you’re on the right place, In this article you are going to see about creating a calculated item in pivot table.
Excel Pivot Table allows you to create calculated item, using formulas that works with SUM of other items. Though calculated items has some limitations to what they can do, It lets you add more power to your pivot table.

Steps to create calculated Item in pivot table

The following data is used for the example:

Calculated Item in Pivot Table

Example: From the above data I want to add all the expenses to their respective heads like Printing & Stationery expenses, Office expenses, etc let us see how we can do it.

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

Step 2: Drag the fields as per your requirement, In this example am dragging Expenses to rows column and Cost to the Values column.

Calculated Item in Pivot Table

Step 3: Be on the data Go to Analyze tab >> under calculation tools >> find Field, Items &select option.

Calculated Item in Pivot Table
Calculated Item in Pivot Table

Step 4: A drop down appears on selecting Field, Items &select option, From that select Calculated Item.

Calculated Item in Pivot Table

Step 5: A Insert Calculated Calculated Item dialogue box Appears.

Calculated Item in Pivot Table

Step 6: Name is the heading of the calculated item, For this example, it is Printing & Stationery.
There is an option Fields in the dialog box that contains all the heads of the data which allows you to select the field for which you want to add calculated Item here, am selecting Expences. to the right side of the dialogue box, there will be the items of the field for the selection
In this example, I want the expences to be clubbed in their respective Heads so I simply put the heading as Printing & Stationery and add field Maths add a (+) plus sign, and the items which come under printing and Stationery and press the add option. Similarly, prepare for office and maintenance expenses.

Calculated Item in Pivot Table

Step 7: Now you can see 3 calculated items are added in the data along with all the items use the filter option to filter required data.
To know how to filter data in pivot table Click Here
To have a clarity look on to the Image.

Calculated Item in Pivot Table

Hope you got clarity on How to create a Calculated item in Pivot Table, Don’t forget to try it out with your data if there are any suggestions drop in the comment box.
To know how to Edit calculated column Click Here

.Do follow our YouTube channel for regular updates.


Heard about Calculated field in Pivot Table? Curious to know about it? Don’t wory you’re on the right place, In this article you are going to see about creating a calculated field in pivot table.

Excel Pivot Table allows you to create calculated fields, using formulas that works with SUM of other fields. Though calculated fields has some limitations to what they can do, It lets you add more power to your pivot table.

Steps to create calculated Field in pivot table

The following students data is used for the example:

Calculated Field in Pivot Table

Example: From the above data I want to add a calculated field i.e total marks of each person let us see how we can do it.

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.

Step 2: Drag the fields as per your requirement, In this example am dragging students name to rows column.

Calculated Field in Pivot Table

Step 3: Be on the data Go to Analyze tab >> under calculation tools >> find Field, Items &select option.

Calculated Field in Pivot Table

Step 4: A drop down appears on selecting Field, Items &select option, From that select Calculated Field.

Calculated Field in Pivot Table

Step 5: A Insert Calculated Field dialogue box Appears.

Calculated Field in Pivot Table

Step 6: Name is the heading of the calculated field, For this example, it is Total marks.
There is an option Fields in the dialog box that contains all the heads of the data which allows you to add the particular field/head in the formula with the help of Insert Field option.
In this example, I want the total of all the subjects so I simply add field Maths add a (+) plus sign, and then again add field physics add a (+) plus sign and add chemistry and click on ok as shown in the image.
Formula Used: = Maths+ ‘Physics ‘+ Chemistry

Calculated Field in Pivot Table

Step 7: Now you can see that a Total marks field is added in the Pivot Table fields as well as in the data.
To have a clarity look on to the Image.

Calculated Field in Pivot Table

Bonus Tip: Using Complex formula to create calculated Field in pivot table.

In this step i want to add 15 marks those who scored above 200 let us see how to get it.

Step 1: Follow the above steps till Step 4.

Step 2: For this scenario, you can use IF Function, Under the Insert calculated field you can name as bonus marks, and as we have added a field called total marks it also appears in the field list with the help of it you can achieve this.
Formula Used: =if(‘Total Marks’>200,15,0)

Calculated Field in Pivot Table

Step 3: Now you can see that person who scored above 200 have got a bonus of 15 marks.

Calculated Field in Pivot Table

Step 5: Here you can create another Calculated field called Final marks adding the Total marks and bonus.

Calculated Field in Pivot Table

Hope you got clarity on How to create a Calculated Field in Pivot Table, Don’t forget to try it out with your data if there are any suggestions drop in the comment box.

To know How to Edit a Calculated Field in the Pivot Table Click Here

To know more about Excel go through our Formulas.

Do follow our YouTube channel for regular updates.


Dot function is used to retrieve the required information from a data type in a simplified manner. It basically used to find some data without infusing any complex conditional calculations and it helps the user to find the information spontaneously.

Syntax

Basically a dot function contains two elements which includes a ” . ” notation.

Arguments

  • Cell generated with data type: This refers to the country/city/company name. probably you can give a reference.

Field name: This refers to the required data field. for example: Population, area etc.

Example using (.)Dot Function

While extracting the required data using field values, this is an alternative way of retrieving data. A simple self-constructed formula can be inserted in the formula tab to get the required data. This feature can be applied by following the steps as listed below:

To retrieve the data, the formula must be applied on the formula tab. If the user needs the population/area of the countries in the provided list.

  • Step 1: Select the cells in the worksheet for which you want to return the area/population then the user must enter ” . ” along with the data field, a drop-down will be listed from which you can select the list of field you want to return. In this case, it is the population
    Formula used,
    =B5:B9.Population
Dot function_1

The result will be projected as shown in the image.

Dot function_2

Dot function is available only in Microsoft 365.

Note: In case there exists data in place of spilled range, a #SPILL error will appear. In simple terms, when the resultant area is not empty or consists of any value a #SPILL error is returned. This indicates that the range where the result needs to spill down is not completely blank. 

To more on New Dynamic Array Formulas – click here

To know more about Rich Data Types in Excel please click here and don’t forget to subscribe to our youtube channel to get regular updates.

Ever wondered that Excel file can be converted to CSV file? yes, In this article we are going to see how to convert Excel to CSV File.

What is CSV

COMMA-SEPARATED VALUES (CSV) name itself says that it separates the values with a comma, and it is a widely used file format that stores numbers and text as plain text. Moreover, the CSV format allows users to glance at the file and immediately diagnose the problems with data, if any, to change the CSV  quoting rules, etc. All this is possible because a CSV file is plain text and an average user or even a novice can easily understand it without any learning curve.

Following are to steps to convert Excel to CSV file:

Step 1: In your Excel workbook, switch to the File tab, and then click Save As. Alternatively, you can press F12 to open the same Save As dialogue.

Convert Excel To CSV File

Step 2: Under the Save As dialogue box click on Save as type and select CSV coma delimitted.

Convert Excel To CSV File

Step 3: Click on Save the file will be saved in CSV file type.

Convert Excel To CSV File

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

Excel stores date in numerical format but in many cases, we need to change it in text format, So in this article, we are going to see how to convert date to text. One of the simplest ways is to use the TEXT function

The Excel TEXT Function is specially designed to convert a numeric value to a text string and display it in the format you specify.

Following are the steps to convert date to text:

Following Data is used in this example.

Convert Date To Text

Step 1: Besides to date cell enter the following formula
=TEXT(B7,”DD-MM-YYYY”)

Convert Date To Text

Step 2: After entering the formula click on Ok, Now you will see that date is aligned to the left side which means that date is in text format.

How To Convert Date To Text in Excel 8

Bonus Step: In Excel, there is an option where you can align the text format on the right side to get is type “–” before to your formula. Your text will be aligned to the right side.

How To Convert Date To Text in Excel 9

Step 3: Now drag down the formula. Your dates will be converted to text. As shown in the image.

How To Convert Date To Text in Excel 10

How to convert text strings in different format

Text function can display the text in different formattings with the help of the following date codes.

Months:

  • m – month number without a leading zero.
  • mm – month number with a leading zero.
  • mmm – short form of the month name, for example, Jan, Feb, Mar
  • mmmm – a long form of the month name
  • mmmmm – month as the first letter, for example, M (stands for March and May)

Days:

  • d – days number without a leading zero.
  • dd – day number with a leading zero.
  • ddd – the abbreviated day of the week
  • dddd – full name of the day of the week

Years:

  • yy – two-digit year.
  • Yyyy – four-digit year.

Refer the below image

How To Convert Date To Text in Excel 11

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

Generally, we maintain numeric values in Excel, because they can be used in formulas with other numbers. There are some situations where converting numbers to text makes sense. There are three simple ways to convert numbers to the text as listed below:

  • Using the Text function
  • Using the Format Cell Option
  • Using Apostrophe

Note: Since number values is converted to text, the result cannot be used for calculations.

Steps to convert numbers to text in excel

Using the Text function

First let us see how to convert numbers to text using TEXT Function.In the below data there are list of students and there marks in percentage(%) lets see how to convert them into text.

Converting numbers to text

Step 1: Create a column words, and enter the following formula
=B7 & ” Secured” & TEXT(C7,” 0%”)

How To Convert Numbers To Text 12

Text Function helps to convert the given numerical data to text.

Step 3: Drag the formula till down to get the other outputs
Note: Number format will be aligned to the right side and text format will be aligned on the left side.
The final output is as follows.

How To Convert Numbers To Text 13

Using the Format Cell Option

Now let us see how to convert numbers to text using format cell option.

Step 1: Select the data

Converting Numbers To Text

Step 2: Right-click in your Mouse/Touchpad and select Format cell options.
Shortcut Key: Ctrl >> 1

Converting Numbers to text

Step 3: Format cells dialog box will appear, Under that find text and click on OK.

How To Convert Numbers To Text 14

Step 4: All the Numbers will be converted to text.

How To Convert Numbers To Text 15

Using Apostrophe

Adding an apostrophe() before the number value changes the value into text format. This feature is convenient to use when there is a limited number of cells like 2-3 needed to be changed. This will instantly change the value into text.

Step 1: Double click on the cell value and add an apostrophe.

Converting numbers to text

Note: This feature cannot be used when there are huge datasets.

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

Crazy about Flash Fill? and want to know how you can save your time? so next time you ever see the list of names that you need to split in two columns, get them in the proper case or even get their initials no need to search for crazy solutions or complex formulas just use FlashFill.

What is FlashFill?

Flash Fill is a tool in excel that automatically fills your data when it senses a pattern nearby row/column. For example, you have a data with names in A column and you want to separate the first name and last name in column B and C, you need to just type the first name in B column and select flashfill it automatically senses the data from column A and fills the data. The same way you go to column C and enter the last name and use flash fill it senses the data from column A and auto-fills the data.

The following are the different instances where flash fill helps you a lot.

Below are the few examples where you can make use of flash fill in different situations.

Instance 1: To separate first name and last name.

Step 1 – Be on the column where you want to apply flash fill

Flash Fill 1

Step 2 – Go to C3 and type “Praveen” and in C4 “Neha”. You will see a flash fill list displayed on your sheet.
As shown in the below image.

Flash Fill 2

Step 3 – Now Press “Enter” key that autofills the name.

Flash Fill 3

Step 4 – Now let us fill the last name using a different method.
Enter “Kumar” in D3 cell and go to Home ribbon tab >> under editing tools select Fill >> FlashFill
It automatically fills the data.
or Shortcut Key: Ctrl + E

Flash Fill

Instance 2: Create an Email Id for the given names.

Step 1: Have your data on a sheet and in C1 enter the email id for the first person.
In this case, it is “praveen.kumar@excelinexcel.in”

Flash Fill 4

Step 2 – Now press Ctrl + E, fills the data automatically with the given id.
Note: It applies the format from the cell above.

Flash Fill 5

Instance 3: Get initials from the name.

Step 1 – Have the required data on the sheet, and enter the first initial in C3 and select flash fill.
Note: In the below image if you notice Excel has not recognized the correct initial.

How to use Flash Fill in Excel 16

Step 2 – In this case, you have to give Excel more information so enter the value of C4 and then select flash fill now you will get the correct information.

Flash Fill 7

Instance 4: Extract year from the date.

Step 1 – Have the required data on the sheet, and enter the year in C3 i.e 2018, and then select flash fill.
Excel automatically senses the data and auto-fills.

How to use Flash Fill in Excel 17

Things to remember

  • Flash Fill is not dynamic. So if you make any changes in the source data it doesn’t update automatically. If you had made any changes in the source data you have to reapply the flashfill.
  • Flash Fill needs to be applied close to your source data otherwise Excel cannot recognize the patterns. For example, your source data is in column B and you apply flash fill in column E Excel will throw an error. So ensure that you have to apply flash fill close to your source data.

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

Ever wondered what is 3D referencing? Did you ever need to reference the same cell or the same range on multiple worksheets? Say you are summing up sales for different products and each product has its tab. One way of writing formula is to reference each tab separately, but that can get annoying, there is a more convenient way of writing formula and that’s to use 3D referencing.

What is 3D Referencing?

A reference that refers to the same cell or range that includes multiple worksheets is called 3D referencing. It is the most elegant way to analyze a large number of identical worksheets with simple formulas.

How to use 3D Referencing?

3D referencing is very simple to use, have summarized data on multiple sheets, create a new sheet go to a cell enter the following =(a function name, reference sheets, reference cell).

Steps to create 3D Referencing

Assume that you are the sales manager of a pillow manufacturing company that sells pillow in north, east, west, and south region from 2016 onwards, suddenly your manager asked a summarized sheet from 2016 to 2019. You can make it simple with the help of 3D referencing.

Below image represents sales data from the year 2016 To 2019.

Data

Step 1 – Create a new sheet with region wise total and quarter wise total.

3D Referencing

Step 2 – Go to a cell beside to north region total and type “=Sum” and press “Tab”. It activates the SUM function

SUM Function

Step 3 – Under the arugument select all 4 sheets i.e2016, 2017, 2018, 2019.
Select the “2016” worksheet tab with the mouse/Touchpad.
Hold down the Shift key and select the “2019” worksheet tab with the mouse/touchpad.
Note: You no need to select each sheet, holding the shift key helps to select all the sheets.

Referencing

Step 4 – Next step is to select the cell you want to sum in this case cell “G3”.
Enter a close bracket “)” to complete the formula.
Formula used: =SUM(‘2016:2019’!G3)

3D Referencing In Excel 18

Step 5 – Drag the formula to other cells and change the reference cell according to it.
Refer below image.

3D Referencing

Subscribe to our YouTube channel for regular updates.

Excel Charts are a fun way to visualize your data. But normal charts restrict the user to select what they wish to see. However, interactive charts in Excel enables you to change the visual according to the choice of the user. This means you only have to build the report once and then the user can effortlessly create their own view instead of you having to create multiple permutations of the same report.

interactive charts in excel

Where can it be used?

Many times while presenting data, you might encounter a situation wherein, there is a lot of data to be presented. On the contrary, the user would want to examine the detailing one by one and not as a whole. A solution would be enabling the user to communicate and pick the desired information within the chart. Interactive charts in Excel perform the same.

For example, let’s say you are the product manager of a company. You are looking at historical data of order quantity for various products. Making a chart with the entire data can result in a chaotic experience for the user. Moreover, it would make it hard to interpret. But with the help of an interactive chart, you can choose a particular product and analyze its performance and trend.

Steps to create interactive charts

Let’s say you own an automobile shop, and you want to know the sale value of each product at once and not all in one chart. With the help of the following data let us try to create interactive charts to understand the sales value of each product.

excel data to understand interactive charts

Step 1 Copy data in a new spreadsheet, avoid working on the source data.

Data

Step 2 – Select the entire data excepting the total section, and create a line chart. To select a line chart
Manually – Go to the insert tab and select the line chart.
Shortcut Key Alt + N + N
The result will be as below.

line chart data result

The chart looks incredibly hard to study and understand as there are many lines. It is hard to focus on one single product and causes a chaotic experience. But with the help on interactive charts, we will be able to highlight or show the specific line’s information. This will be easier for the user to understand the data. Let’s continue

Step 3 Instead of having different colours for each line, we make all the lines to a dull grey coloured line. The reason behind this is while selecting a specific product, the respective line would be highlighted making it easier to comprehend.
1. Double click on the chart, which enables the “Format data series” to the right side of the screen.
2. Then select a single line, go to the colour option and select a colour, in this case, I am selecting a grey colour.
By doing this the colour of a line will be changed as shown in the picture.

formating data series

Tip: Under step 3 you changed the colour of a single line. Now to change the colour for all the lines, it can be time-consuming. In Excel, we have many shortcut keys. There is a shortcut key which redoes the last action “F4“. We can make use of it. So once done you can then use “F4” key. Below is the result.

highlighted in line chart

Step 4 Let’s reduce the thickness of the line to make it look better for visualization.
1. Double click on the chart, which enables the “Format data series” to the right side of the screen.
2. Then select a single line, go to the width option and change the width to 0.25 pt.
3. Then select each line and press “F4” to repeat the last formatting to the current selection.
By doing this the width of a line will be changed as shown in the image below.

less thick lines in chart

Step 5 – Here, we need a selection mechanism that helps to highlight a particular product. To do so we make use of a pivot table.
1. To create a pivot table, select entire data and go to Insert >> Pivot table or press Alt + N + V
2. Then drag and drop product line to the rows field as we require only the products.

selecting data for pivot table
Pivot table fields box

Step 6 – In this step, we add a slicer. Why slicer? A slicer allows a user to filter data in an interactive manner. Here, we want to highlight a particular line so adding a slicer helps to do that.
1. So, go to insert and add Slicer. Select Product Line.

inserting slicer

3. Copy the slicer and paste it near to the line chart.

pasting slicer near to the line chart

But in this step, when you select any product from the slicer the changes will affect only the pivot. As of now, there will be no changes in the chart. Therefore to make it work first we will link the pivot and then use a formula which will be linked to the chart.

Step 7 – In an empty cell nearby link the cell to the item in the pivot table. Simply type = and select the cell within the pivot table. Now when you filter based on the slicer the pivot table is affected and so is the linked cell.

linking cell to pivot table
cell linked to pivot table

 

Step 8 Extract the periodical sales data of the selected product using the cell which was recently linked to the pivot table. Here we use the VLOOKUP Function.
Formula: =VLOOKUP($B$16,$B$3:$J$10,2,)
The following picture extracts the data of 01-Oct-2019, copy the formula to the right side, and change the column number to get the data of other months.

extracting the data by date

After dragging and changing the column headers the data would look similar to the below image.

result after extracting the data

Step 9 Now is the part where we link the above-ascertained values to the chart. Copy the above data and paste it into the chart. It automatically highlights the selected product line as shown in the picture.

linking the ascertained values to the chart

Step 10 The final step is to modify your chart. Add a Chart title, remove legends, add data labels to the endpoint of the line. These are minor tweaks done to make your chart look attractive.
The final output looks as follows

interactive charts final output

Benefits of Interactive Charts in Excel

  • It enables the user to interact with the data provided.
  • A large amount of information can be presented in an easy way to analyze and understand the format.
  • Fits to present accurate data as expected by the user.
  • An interactive chart is also used to make a comparison of trends.
  • It helps to focus on one thing at a time.
  • No need to prepare static charts with different time zones.

Limitations of Interactive Charts in Excel

  • Sometimes people skip core messages.
  • It could reveal the data in an oversimplified way.
  • It is insufficient to add more colours to differentiate categories.

Conclusion

Interactive charts can help the user to focus on one thing at a time. The process to prepare could be lengthy, but the result is worth it. It also acts as a boon for marketing purposes and for tracking views of social network sites, as it provides an eloquent look for the data derived, it is a bane for enterprises which expects an elite representation of data using standard formatting styles. Nevertheless, interactive charts remove the need to create multiple charts.

To know more on interactive charts click here

Check out our Youtube channel for more videos.