MS Excel for Beginners – Data Entry and Formatting

Proper data entry is crucial for effective analysis in Excel. Ensuring your data is entered correctly and formatted appropriately will not only improve readability but also enhance your ability to analyze the data effectively.

Entering Data

When working with Excel, you can input various types of data:

• Text: Enter any alphanumeric characters directly into a cell. For instance, you can input names or labels.
• Numbers: Input numerical values, which can be used in calculations. Ensure you enter them without any additional characters (like commas or dollar signs) unless you want them formatted as such.
• Dates: Excel recognizes dates when entered in standard formats, such as MM/DD/YYYY or DD/MM/YYYY, depending on your regional settings.

Exercise: Create a workbook and enter the data shown in the image. Try to add more data (atleast 10 rows) for good hands on experience.

Formatting

Formatting is essential for enhancing the presentation of your data. Properly formatted cells can make your spreadsheet more readable and visually appealing. You can adjust various aspects of cell formatting:

• Font Styles: Change the font type, size, and style (bold, italic, underline) to highlight important data or differentiate sections. To do this, select the cell or range of cells, then go to Home > Font. You can also use shortcuts: Ctrl + B for bold, Ctrl + I for italic, and Ctrl + U for underline.
• Colors: Use fill colors to highlight cells or change the text color to make it stand out. Select the cell(s) you want to format, then navigate to Home > Font or Home > Fill Color. You can choose from various color options to enhance visibility.
• Borders: Add borders to cells to create a structured appearance, which is especially helpful when presenting data in tables. To add borders, select the desired cells, then go to Home > Borders. You can choose different border styles to customize your layout.

Formatting Numbers in Excel

The Number tab in the Format Cells dialog is essential for controlling how numerical data is displayed in your Excel worksheets. Proper number formatting enhances readability and ensures that your data conveys the intended meaning. Here’s how to use it effectively:

• Accessing Number Formatting: To access the Format Cells dialog, right-click on a cell or range of cells and select Format Cells, or press Ctrl + 1 to open the dialog directly. Then, click on the Number tab.
• Categories: The Number tab offers various formatting categories:
  • General: The default format, suitable for most data. Numbers are displayed without any specific formatting.
  • Number: Allows you to specify the number of decimal places, use thousand separators, and decide how negative numbers are displayed (e.g., in red, with parentheses, etc.).
  • Currency: Displays numbers as currency, with options for selecting currency symbols and decimal places. This is ideal for financial data.
  • Accounting: Similar to Currency but aligns currency symbols and decimal points in a column, providing a cleaner look for financial reports.
  • Percentage: Converts the number to a percentage format, multiplying it by 100 and adding a percentage sign. You can also set the number of decimal places.
  • Fraction: Displays numbers as fractions. You can choose how detailed the fraction should be (e.g., halves, quarters).
  • Scientific: Formats numbers in exponential notation, which is useful for very large or small numbers.
• Custom Formats: If the predefined formats do not meet your needs, you can create a Custom Format. In the Number tab, select Custom and enter your format code (e.g., “0.00” for two decimal places).

Exercise. Perform following formatting options.

• Make the Header row distinct by providing color and borders.
• Create a new row to group the header columns.
• Format the Sales, Discount, and Profit columns. Display Sales and Profit prefixed with a $ sign and two places of decimals). Display Discount as a percentage.

Conditional Formatting

Conditional formatting in Excel is a powerful feature that allows you to highlight important data points, making trends and insights more visible.

• Applying Rules: To apply conditional formatting, select your data and navigate to Home > Conditional Formatting. You can also use the shortcut Alt + H + L to open the conditional formatting menu. From here, choose various rules, such as color scales or icon sets, to visualize trends and identify significant values. This helps in quickly spotting outliers or patterns in your data.

Exercise: Perform following formatting options in the worksheet data.

1. Display the Sales amount in Green where Sales is greater than $500.
2. Display negative Profits in red.
3. Display Top 20% of Profit in Green.
4. Display data bars to display higher and lower quantities sold.