You are working on sales data and need some analysis, so you enter the data as below and make some formulas.
After you enter all your formulas, you realize you had to format all those cells which have a formula, no other cells need to formatted.
Let’s go step-by-step.
- Create the sheet with formulas.
- Select the formula cells only, and format them.
You can directly skip to step 2 if you like.
Create the sheet with formulas
- Formula 1 = Multiplies the Price with the Quantity for each order.
- Formula 2 = Sum of all Sales
- Formula 3 = Customer-wise Sales for each customer. =SUMIF has been used for this.
To learn =SUMIF in-depth, click here.
- Formula 4 = Product-wise Sales for each product. =SUMIF has been used for this too.
- Formula 5 = At the current rate at which sales are happening, how much would the total sales be by the end of the year? So if today’s (current) date is 20th October 2015, by the end of the current financial year (date 31st March 2016), the total sales will be $19,777.
The formula used to derive this is
If you want to understand the Formula 5 in-depth, please leave a comment here, alongwith your email id.
Select the formula cells only, and format them
- Go to the Home tab, Find & Select, click on Go To Special…
- Select Formulas and click Ok.
- Only the cells with formulas are selected.
- Apply Red font color & Bold (or whatever formatting you want to apply).