Excel: Managing Worksheet Content

  • Updated

A Worksheet filled with data doesn't have to be boring! Here are some ways to manage your data and create some visual interest in the bargain. 

Formatting Text & Numbers

Font and Font Size

  1. Select the cell(s) you want to format
  2. Select the Home tab
  3. Navigate to the Font group and change the font type or font size by clicking the dropdown arrow and selecting the desired option

Excel_Fonts.png

Font Color and Emphasis

The Font group under the Home tab enables bolding, italicizing, underlining and setting the color of your font. 

Excel_Font_Group.png

Wrap Text

Wrapping text allows you to keep the contents within the boundaries of a specific cell.   

  1. Select the cell, column or row you want to wrap
  2. Select the Home tab, Alignment group
  3. Click Wrap Text 

NOTE: Row height or Column width may need to be adjusted to get the desired appearance

Excel_Wrap_Text.png

Center Text

  1. Select the cell in which you want to center the text
  2. Under the Home tab, in the Alignment group, select the Center icons for vertical and/or horizontal orientation

Excel_Center_Text.png

Indent Text

  1. Select the cell you want to indent
  2. Under the Home tab, in the Alignment group, select the Increase Indent button
  3. To reverse, select the Decrease Indent button

Excel_Indent.png

Clear Formatting

Formatting stays with the cell.  Deleting or editing the cell does not delete the formatting, you must clear the formatting.

  1. Select cells to clear (or entire workbook)
  2. Under the Home tab in the Editing group, click the Clear dropdown arrow
  3. Select Clear Formats to remove cell formatting while leaving contents intact
  4. Clear All removes formatting AND contents of cell(s)

Excel_Clear_Formats.png

Format Painter

The Format Painter copies the formatting from selected portions of the Worksheet and applies it to other portions.

  1. Select the section of the Worksheet from which you wish to copy the Formatting
  2. Under the Home tab, in the Clipboard group, select the Format Painter button 
  3. Select the portion of the Worksheet to which you wish to apply the Formatting; it will apply automatically

Excel_Format_Painter.png

Conditional Formatting

Conditional Formatting helps you visually answer specific questions about your data. You can apply Conditional Formatting to a range of cells.

  1. Highlight the Column or cell range to which you wish to apply Conditional Formatting
  2. Navigate to the Home tab, Styles group, and click the dropdown arrow for Conditional Formatting
  3. Expand the menu for the desired Formatting Rule and select appropriately, or create your own Formatting Rule by selecting New Rule

Excel_Conditional_Formatting.png     Excel_Formatting_Rules.png

Merging Cells

When you merge two or more adjacent, horizontal or vertical cells, the cells display across multiple columns or rows.

  1. Select the cells you wish to merge
  2. Under the Home tab, in the Alignment group, select the dropdown for Merge & Center
  3. Select the desired Merge option

NOTE: If you want to Merge and Center, simply click that option; no need for the menu.

Excel_Merge_Cells.png

Cell Styles

A Style is a set of formatting characteristics that you can apply to text, tables and lists in your document to quickly change their appearance.

Quick Styles

  1. Select the cells you want to format.
  2. Under the Home tab, in the Styles group, select a Style in the Styles box (use the navigation arrows to view all Styles)

Excel_Styles.png

NOTE: To create your own Style, click the "all" dropdown arrow at the bottom right of the Styles box and select New Cell Style. Name your custom Style and click Format to set desired options. Your custom Style will appear as an option in the Styles. 

Excel_New_Cell_Style.png

Excel_Format_Style.png

Themes

A Theme can be applied to change the visual appearance of your worksheet. Themes combine Color, Fonts & Effects.

NOTE: A Theme will only apply to those cells/areas in which Styles have been used. 

  1. Navigate to Page Layout tab
  2. Under the Themes group, select dropdown arrow under Themes button to display options
  3. Select desired Theme 

Excel_Themes.png

NOTE: To create a custom Theme, select the dropdown arrow for Colors, Fonts, and Effects individually, choose the "Customize" option and name your Theme. Your customized Theme will then appear as an option in the Themes list. 

Splitting Columns

This function allows you to split data in one cell into two separate cells/columns. It's particularly helpful in separating things like first and last names, sections of an address, etc. 

  1. Select the Column of data you'd like to split
  2. Navigate to the Data tab, the Data Tools group and select Text to Columns
  3. Follow the steps in the Convert Text to Columns Wizard
  4. When all options have been set, click Finish
  5. Initial Column will now be split into two Columns; adjust headings as necessary

Excel_Text_to_Columns.png

Excel_Convert_Text_to_Columns_Wizard.png

Excel_Convert_Text_Finish.png

Excel_Split_Columns.png

Hide Rows & Columns

If your Worksheet contains data you may not wish others to see or that is not pertinent to the end result, you can hide it temporarily.

  1. Select the Column(s) or Row(s) you wish to Hide
  2. Right click and select Hide from the menu
  3. Hidden Columns/Rows are indicated by a white "bar", and the absence of the sequential Column letter or Row number

Excel_Hide.png   Excel_Hidden_Column.png

To reveal Columns or Rows, right click on "hidden" section (white bar) and select Unhide

Excel_Unhide.png

 

Setting a Print Area

When preparing to print your Worksheet, you can define a Print Area that will print only that area. 

  1. Highlight the desired Print Area on your Worksheet
  2. Under the Page Layout tab, Page Setup group, click the Print Area button
  3. Select Set Print Area; section to be printed will be outlined with a thin border

To remove the Print Area, repeat the process and select Clear Print Area

Excel_Print_Area.png

Grouping Worksheets

Grouping Worksheets enables you to enter a single value into multiple Worksheets simultaneously. *NOTE: the Worksheets must be arranged in the same manner on each Worksheet.

  1. Select the first tab for the Worksheets you wish to group together
  2. Holding down the Ctrl key on your keyboard, select the tabs for the additional Worksheets to group; all tabs will appear "active"
  3. Enter or change data in the appropriate cell on ONE of the grouped Worksheets
  4. The data will automatically be entered in the same location on all grouped Worksheets 

Excel_Grouped_Worksheets.png

To ungroup the Worksheets, right click any tab and select Ungroup Sheets from the menu

*NOTE: Ungroup Worksheets prior to entering data that applies to an individual Worksheet

Excel_Ungroup_Sheets.png