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.

  1. Create the sheet with formulas.
  2. Select the formula cells only, and format them.

You can directly skip to step 2 if you like.

Create the sheet with formulas

Data structure for sales data

Data structure for sales data

Formulas created for analysis

Formulas created for analysis

  • 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
    =365*H4/(TODAY()-IF(MONTH(TODAY())<=3,DATE(YEAR(TODAY())-1,4,1),DATE(YEAR(TODAY()),4,1)))

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

  1. Go to the Home tab, Find & Select, click on Go To Special…

    Go To Special

    Go To Special

  2. Select Formulas and click Ok.

    Formulas only

    Formulas only

  3. Only the cells with formulas are selected.

    Formula cells selected

    Formula cells selected

  4. Apply Red font color & Bold (or whatever formatting you want to apply).
    Formatting applied - red font & Bold

    Formatting applied – red font & Bold

    Keep Excel-ing!