02 Fitur Home

Summary

The video demonstrates how to use Excel’s Home tab to clean up and format a sample store-sales dataset, then apply conditional formatting to identify higher- and lower-value entries. The presenter also shares keyboard shortcuts for many of these tasks.

Data cleanup and formatting

• Copy the practice data: Select the data with Ctrl+A, copy with Ctrl+C, and paste it into a practice sheet with Ctrl+V.
• Wrap text: Choose Home → Alignment → Wrap Text to display longer cell contents on multiple lines. Toggle it off with Alt+H, W.
• Adjust column widths: Choose Home → Format → AutoFit Column Width to fit columns to their contents. Shortcut: Alt+H, O, I.
• Distinguish the header:
  • Select the header row and apply a dark-blue fill using Home → Font → Fill Color.
  • Change the font color to white and make it bold with Ctrl+B.
  • To extend a selection across adjacent data, use Ctrl+Shift+Left/Right.
  • Open the fill-color menu with Alt+H, H; use Alt+H, F, C for font color.
• Add borders: Choose All Borders from the Font group. Shortcut: Alt+H, B, A.
• Format numerical values:
  • Apply Comma Style to the unit-price column. Shortcut: Alt+H, K.
  • Remove unwanted decimal places using the decrease-decimal control. Shortcut: Alt+H, 9, repeated as needed.
  • Currency or Accounting format can also be used. The currency symbol depends on the computer’s regional settings.
• Format transaction dates: Choose a date format from the Number group. To use the keyboard, open Format Cells with Ctrl+1, navigate to the date options, and select a format.
• Align values: Center a row of values using Home → Alignment → Center.

Conditional formatting for analysis

The presenter uses conditional formatting to identify values in the sales data:

• To highlight values greater than 500, choose Home → Conditional Formatting → Highlight Cells Rules → Greater Than, enter 500, and select a green fill with dark-green text.
• To highlight values less than 200, choose Less Than, enter 200, and apply a red-text or light-red style.
• The keyboard sequences shown are Alt+H, L, H, followed by G for Greater Than or L for Less Than. Enter the threshold, then use the dropdown to choose a formatting style.

Main lesson

The Home tab provides tools to make a dataset easier to read and analyze through text wrapping, column sizing, header styling, borders, number and date formats, alignment, and conditional formatting. Many of these tasks can also be completed using Excel keyboard shortcuts.

Speakers and sources featured

• Speaker: One presenter/instructor; no name is given in the subtitles.
• Data source: Dummy store-sales data used as the practice dataset.
• Additional resource: An ebook titled 100 Excel Techniques Without a Mouse, described by the presenter as a source for more shortcuts.