Learning Objectives
Show
This section addresses formatting commands that can be used to enhance the visual appearance of a worksheet. It also provides an introduction to mathematical calculations. The skills introduced in this section will give you powerful tools for analyzing the data that we have been working with in this workbook and will highlight how Excel is used to make key decisions in virtually any career. Additionally, Excel Spreadsheet Guidelines for format and appearance will be introduced as a format for the course and spreadsheets submitted. Formatting Data and CellsEnhancing the visual appearance of a worksheet is a critical step in creating a valuable tool for you or your coworkers when making key decisions. There are accepted professional formatting standards when spreadsheets contain only currency data. For this course, we will use the following Excel Guidelines for Formatting. The first figure displays how to use Accounting number format when ALL figures are currency. Only the first row of data and the totals should be formatted with the Accounting format. The other data should be formatted with Comma style. There also needs to be a Top Border above the numbers in the total row. If any of the numbers have cents, you need to format all of the data with two decimal places. Figure 1.31aOften, your Excel spreadsheet will contain values that are both currency and non-currency in nature. When that is the case, you’ll want to use the guidelines in the following figure: Figure 1.31bThe following steps demonstrate several fundamental formatting skills that will be applied to the workbook that we are developing for this chapter. Several of these formatting skills are identical to ones that you may have already used in other Microsoft applications such as Microsoft® Word® or Microsoft® PowerPoint®.
Why?Pound Signs (####) Appear in Columns When a column is too narrow for a long number, Excel will automatically convert the number to a series of pound signs (####). In the case of words or text data, Excel will only show the characters that fit in the column. However, this is not the case with numeric data because it can give the appearance of a number that is much smaller than what is actually in the cell. To remove the pound signs, increase the width of the column. Figure 1.35 shows how the Sheet1 worksheet should appear after the formatting techniques are applied. Figure 1.35 Formatting Techniques AppliedData Alignment (Wrap Text, Merge Cells, and Center)The skills presented in this segment show how data are aligned within cell locations. For example, text and numbers can be centered in a cell location, left justified, right justified, and so on. In some cases you may want to stack multiword text entries vertically in a cell instead of expanding the width of a column. This is referred to as wrapping text. These skills are demonstrated in the following steps:
Keyboard ShortcutsMerge Commands
Why?Merge & Center One of the most common reasons the Merge & Center command is used is to center the title of a worksheet directly above the columns of data. Once the cells above the column headings are merged, a title can be centered above the columns of data. It is very difficult to center the title over the columns of data if the cells are not merged. Figure 1.38 shows the Sheet1 worksheet with the data alignment commands applied. The reason for merging the cells in the range A1:D1 will become apparent in the next segment. Figure 1.38 Sheet1 with Data Alignment Features AddedSkill RefresherWrap Text
Skill RefresherMerge Cells
Entering Multiple Lines of TextIn the Sheet1 worksheet, the cells in the range A1:D1 were merged for the purposes of adding a title to the worksheet. This worksheet will contain both a title and a subtitle. The following steps explain how you can enter text into a cell and determine where you want the second line of text to begin:
Skill RefresherEntering Multiple Lines of Text
Borders (Adding Lines to a Worksheet)In Excel, adding custom lines to a worksheet is known as adding borders. Borders are different from the grid lines that appear on a worksheet and that define the perimeter of the cell locations. The Borders command lets you add a variety of line styles to a worksheet that can make reading the worksheet much easier. The following steps illustrate methods for adding preset borders and custom borders to a worksheet:
Skill RefresherPreset Borders
Custom Borders
AutoSumYou will see at the bottom of Figure 1.42 that Row 15 is intended to show the totals for the data in this worksheet. Applying mathematical computations to a range of cells is accomplished through functions in Excel. Chapter 2 “Mathematical Computations” will review mathematical formulas and functions in detail. However, the following steps will demonstrate how you can quickly sum the values in a column of data using the AutoSum command:
Skill RefresherAutoSum
Moving, Renaming, Inserting, and Deleting WorksheetsThe default names for the worksheet tabs at the bottom of workbook are Sheet1, Sheet2, and so on. However, you can change the worksheet tab names to identify the data you are using in a workbook. Additionally, you can change the order in which the worksheet tabs appear in the workbook. The following steps explain how to rename and move the worksheets in a workbook:
Integrity CheckDeleting Worksheets Be very cautious when deleting worksheets that contain data. Once a worksheet is deleted, you cannot use the Undo command to bring the sheet back. Deleting a worksheet is a permanent command. Keyboard ShortcutsInserting New Worksheets
Figure 1.46 shows the final appearance of the General Merchandise World Workbook. Figure 1.46 Final Appearance of the General Merchandise World WorkbookSkill RefresherRenaming Worksheets
Moving Worksheets
Deleting Worksheets
Key Takeaways
AttributionAdapted by Barbara Lave from How to Use Microsoft Excel: The Careers in Practice Series, adapted by The Saylor Foundation without attribution as requested by the work’s original creator or licensee, and licensed under CC BY-NC-SA 3.0. Which is used to copy cell format from one cell to another?Copy cell formatting. Select the cell with the formatting you want to copy.. Select Home > Format Painter.. Drag to select the cell or range you want to apply the formatting to.. Release the mouse button and the formatting should now be applied.. Which command is used to copy formatting?You can use Ctrl+Shift+Cto copy a format, and Ctrl+Shift+Vto paste a format. While the cursor does not change to a paintbrush, you can repeatedly paste formatting to multiple areas without re-copying.
What is the shortcut to copy cell format in Excel?Select the cell from which you want to copy the format. Press Ctrl + C to copy the selected cell to the Clipboard.. Shift + F10 displays the context menu.. Shift + S selects the Paste Special command.. Shift + R chooses to paste only formatting.. How to copy data from one cell to another in Excel automatically?Use Copy and Paste Link to automatically transfer data from one Excel worksheet to another. Open two spreadsheets containing the same, simple dataset.. In sheet 1, select a cell and type Ctrl + C / Cmd + C to copy it.. In sheet 2, right-click on the equivalent cell and go to the Paste > Link.. |