Hi, I hope you love the previous exercise where we had practice with a data spanning over 60,000 lines with more than a dozen columns. Well, I'm using the same case study to ask another question. We had the sales data working for us whereby in 2000 to 2003 and 2000 for the three years, how much sales this particular company had made to its various customers globally. Now, let's say I want to find a trend whether amongst the seven days, how have the sales been affected, although this is a seven days running company, I want to see whether the maximum sales has been dispatched on Sunday, or Wednesday, or Thursday or Friday. Now, to be able to do that, first you need to extract the day, which is Sunday, Monday, Tuesday, Wednesday. Now to be able to do that.
I will be taking help of one formula called text. I'm going to give the heading as day but the formula that I'll be using is called text. Let me optimize the window before I start Start working on it. There you go. Once I put the column in front of me clearly, now I'm going to write a formula in the yellow cell. It says equal to text.
It asks for the date value, which I'm going to pick from the sales date, comma, and within a pair of double quotation, I'm going to put four times DD. And that's it. double quotation Close, close the brackets into. There's a formula which has been discussed in much detail in another lecture. But let me see the application in context of this data. As I copy paste the entire set of formula in the column n, I get the information at this first of Jan 2002 is actually falling on a Tuesday.
Now with this data in hand, let me quickly prepare a pivot table Ctrl A Insert, Pivot Table, okay. And as usual, I'm going to right click on the grid area activate one of the pivot table options, which allows me to go back to the classic pivot table layout, once having done so now. Let me put the date in the roofie. And thereafter, let me put sales amount in the main action area. Now, let me first optimize the format. And what I get is more or less, the trend is consistent.
So if you want to present this data in a very clear graphical format, you would use something called sparkline. Something I already discussed in one of another video lecture sessions. So what I'm going to do is, I'm going to place my cursor in any one cell, okay. And after having done so, I go to Insert tab. Then I go to the section with scald spark lines. I'm going to click on line, I choose line and I choose the entire set of numbers.
Please note, I'm just using only seven numbers right now, not the text, not the total. As I press OK, you get a basic trend, whereby the second month you have slightly higher sales compared to other sales of different days. So this is a sparkline based Inside shell shocked so what we saw was using a formula text, how to extract the day and then properly also using sparklines to visually present the data