Tutorials References Exercises Videos Menu
Free Website Get Certified Pro

Excel Tutorial

Excel HOME Excel Introduction Excel Get Started Excel Overview Excel Syntax Excel Ranges Excel Fill Excel Move Cells Excel Add Cells Excel Delete Cells Excel Undo Redo Excel Formulas Excel Relative Reference Excel Absolute Reference Excel Arithmetic Operators Excel Parentheses Excel Functions

Excel Formatting

Excel Formatting Excel Format Painter Excel Format Colors Excel Format Fonts Excel Format Borders Excel Format Numbers Excel Format Grids Excel Format Settings

Excel Data Analysis

Excel Sort Excel Filter Excel Tables Excel Conditional Format Excel Highlight Cell Rules Excel Top Bottom Rules Excel Data Bars Excel Color Scales Excel Icon Sets Excel Manage Rules (CF) Excel Charts

Table Pivot

Table Pivot Intro

Excel Case

Case: Poke Mart Case: Poke Mart, Styling

Excel Functions

AND AVERAGE AVERAGEIF AVERAGEIFS CONCAT COUNT COUNTA COUNTBLANK COUNTIF COUNTIFS IF IFS LEFT LOWER MAX MEDIAN MIN MODE NPV OR RAND RIGHT STDEV.P STDEV.S SUM SUMIF SUMIFS TRIM VLOOKUP XOR

Excel How To

Convert Time to Seconds Difference Between Times NPV (Net Present Value) Remove Duplicates

Guided Projects

Introduction to Excel Learn Data Calculations Learn Data Visualization Learn to Create a Budget Learn to Create a Timeline Learn to Style in Excel

Excel Examples

Excel Exercises Excel Certificate

Excel References

Excel Keyboard Shortcuts


Excel Case: Poke Mart, Styling


Case: Poke Mart, Styling

This case is about helping the Poke Mart merchant to style the shop cart overview. You will practice the skills that you have learned in the chapters about formatting and styling.

Did you solve the first case about the Shop Cart? We will reuse that data. Check it out here.

You do not need the calculations to complete the case. Type the following data:

Copy Values

Merchant: Oh boy! I am glad that you are here to help. This colorless Shop Cart has been declining our sales. Let's give it a lift!

To complete this case you need to:

  • Hide grids
  • Add colors
  • Change fonts
  • Format numbers
  • Finish the last calculation

Ready?

Start by removing the grids

  1. Click view
  2. Uncheck gridlines

Like this:

Make space for the heading by creating a new row 1.

  1. Right click row 1
  2. Insert new row

That's a start! You have created a new row 1.

Make space for the header, increase the row height to 40 pixels.

Note: You will see the border size box which indicates the pixels when you start to drag the border.

  1. Drag Row 1 border to 40 pixels

Merge A1:D1 to create one big cell for the header.

  1. Select A1:D1
  2. Click the Merge button in the Ribbon

Great job! Now there is a big merged cell ready for the header.

Type Poke Mart in the merged range A1:D1

  1. Select A1:D1(click the merged range)
  2. Type Poke Mart
  3. Hit enter

The header font is a bit small, huh?

Change the header font to size 20 and make it Bold

  1. Select A1:D1 (the merged range)
  2. Change to font size 20
  3. Click the Bold command or use keyboard shortcut CTRL + B or Command + B

That's the way! The header looks better now.

For all cells, change the font to Arial and align text for A2:F14 to Center.

  1. Select all cells by clicking the angle icon in the top left corner of the sheet
  2. Change all fonts to Arial
  3. Mark A2:F14
  4. Click the Align button in the Ribbon
  5. Click the Align Center command

Good job! You have changed the fonts for all text to Arial and aligned A2:F14 to the Center.

It's time to get some colors in there.

  1. Select range A2:D4 (the merged cells)
  2. Apply standard Red color
  3. Select D2:D4
  4. Apply HEX code 2a75bb
  5. Select D3:D9
  6. Apply RGB code 255 203 5
  7. Select F12
  8. Apply HEX code ffcb05

The two last colors used are the same. Same color, HEX and RGB are different.

Note: Coloring the input fields can be helpful for those who will use the spreadsheet. In this case we marked the input fields with the yellow color: ffcb05.

Next, change text colors

  1. Select A1:D4
  2. Change text color to white
  3. Select A2:A4
  4. Change text color to white

That is great! You got this.

Change the Number formats for the prices to Currency and remove the decimals.

  1. Select B3:C14
  2. Change Number format to Currency
  3. Click decrease decimals two times (2)

The final touch!

Add borders to make the numbers more readable.

  1. B11: Underline
  2. B14: Double bottom border
  3. E12:F12: Thick outside borders
  1. Select B11
  2. Add Underline border
  3. Select B14
  4. Add Double bottom border
  5. Select E12:F12
  6. Add Thick outside borders

Almost there!

Do the final calculation for total and add the final Bold fonts.

  1. Select B14
  2. Type =
  3. Select B11
  4. Type -
  5. Select B12
  6. Hit enter
  7. Select A14:B14
  8. Make the range bold
  9. Select A2:D2
  10. Make the range bold

Add test values:

  1. Type D3(5)
  2. Type D4(2)
  3. Type D6(5)
  4. Type D9(10)

Congratulations! You have successfully completed all tasks for the Poke Mart merchant.

  1. Hide grids - Check
  2. Add colors - Check
  3. Change fonts - Check
  4. Format numbers - Check
  5. Finish the last calculation - Check

Before and After

Before styling:

After styling:

Well done!