UNIT-3

TABLES AND FORMATTING

Excel is a powerful spreadsheet program that allows you to create tables and format them in a
variety of ways. Here are some tips for creating tables and formatting in Excel:

1. Creating a table: To create a table, select the cells that you want to include in the table
and click the "Insert" tab on the ribbon. Then, click the "Table" button and select the
range of cells that you want to include in the table. Excel will automatically apply a
default table style.
2. Applying table styles: You can change the table style by selecting the table and
clicking the "Design" tab on the ribbon. There, you can choose from a variety of
built-in styles or create your own.
3. Adding borders: To add borders to your table, select the cells that you want to apply
the borders to and click the "Borders" button on the "Home" tab. There, you can
choose from a variety of border styles and thicknesses.
4. Merging cells: To merge cells in a table, select the cells that you want to merge and
click the "Merge & Center" button on the "Home" tab. This is useful for creating
header rows or for combining cells into a larger cell.
5. Formatting numbers: You can format numbers in your table by selecting the cells that
you want to format and clicking the "Number" button on the "Home" tab. There, you
can choose from a variety of number formats, such as currency, percentage, or
date/time.
6. Sorting data: To sort data in a table, select the column that you want to sort by and
click the "Sort A-Z" or "Sort Z-A" button on the "Data" tab. This is useful for
organizing your data in a meaningful way.
7. Filtering data: You can filter data in a table by clicking the "Filter" button on the
"Data" tab. This allows you to hide rows that don't meet certain criteria, making it
easier to focus on the data that is relevant to you.

Changing the look of a table

Excel provides numerous predefined table styles that you can use to quickly
format a table. If the predefined table styles don't meet your needs, you can
create and apply a custom table style. Although you can delete only custom
table styles, you can remove any predefined table style so that it is no longer
applied to a table.

You can further adjust the table formatting by choosing Quick Styles options for table
elements, such as Header and Total Rows, First and Last Columns, Banded Rows and
Columns, as well as Auto Filtering.
Navigating a table

Using arrow keys: You can use the arrow keys on your keyboard to move around the cells in
the table. Use the up and down arrow keys to move vertically, and use the left and right arrow
keys to move horizontally.

1. Using the Tab key: Pressing the Tab key will move you to the next cell to the right. If
you are at the last column of a row, pressing Tab will move you to the first cell of the
next row.
2. Using the Shift+Tab keys: Pressing Shift+Tab will move you to the previous cell to
the left. If you are at the first column of a row, pressing Shift+Tab will move you to
the last cell of the previous row.
3. Using the Go To command: You can use the Go To command to quickly navigate to a
specific cell in the table. Press Ctrl+G to open the Go To dialog box, enter the cell
reference, and click OK.
4. Using the Name box: The Name box displays the active cell's address and allows you
to jump to a specific cell by typing its reference.
5. Using the Scrollbar: You can use the vertical or horizontal scrollbar on the right or
bottom of the Excel window to scroll through the table.

By using these methods, you can quickly and efficiently navigate through a table in Excel.

If you need to add or remove columns or rows in a table after you create it, you have several
ways to do both.

Use the Resize Table Feature in Excel

If you want to work with both tables and columns, whether adding or deleting them, the
handiest way is with the Resize Table feature.

Select any cell within the table. Go to the Table Design tab that appears and click “Resize
Table” on the left side of the ribbon.

In the pop-up window, you can use the cell range text box to adjust the cell references. If you
prefer, you can drag through the columns and rows while the window is open. Click “OK”
when you have the table sized as you want it.

Add Columns or Rows to an Excel Table

If you simply want to add more columns or rows, there are a few ways to do it. You can use
whichever method is most convenient or comfortable for you.
Remove duplicates
One of the operations that you can perform is to remove duplicate values from your table.

1. Select the columns that contain duplicate values.

2. Go to the Home tab.
3. In the Reduce rows group, select Remove rows.
4. From the drop-down menu, select Remove duplicates.

Sorting a Data in table

Sorting data is an integral part of data analysis.You can sort data by text (A to Z or Z to A),
numbers (smallest to largest or largest to smallest), and dates and times (oldest to newest and
newest to oldest) in one or more columns. You can also sort by a custom list you create (such
as Large, Medium, and Small) or by format, including cell color, font color, or icon set.

Sort text

Select a cell in the column you want to sort.

On the Data tab, in the Sort & Filter group, do one of the following:

To quick sort in ascending order, click (Sort A to Z).

To quick sort in descending order, click (Sort Z to A).

Converting Table into Range

Click anywhere in the table and then go to Table Tools > Design on the Ribbon.

In the Tools group, click Convert to Range.

-OR-
Right-click the table, then in the shortcut menu, click Table > Convert to Range.

In Excel, formatting worksheet (or sheet) data is easier than ever. You can use
several fast and simple ways to create professional-looking worksheets that display
your data effectively. For example, you can use document themes for a uniform
look throughout all of your Excel spreadsheets, styles to apply predefined formats,
and other manual formatting features to highlight important data.

Formatting tools on the Home tab

In Excel, formatting worksheet (or sheet) data is easier than

ever. You can use several fast and simple ways to create
professional-looking worksheets that display your data
effectively. For example, you can use document themes for a
uniform look throughout all of your Excel spreadsheets, styles
to apply predefined formats, and other manual formatting
features to highlight important data.

The Home tab in Excel provides a variety of formatting tools

that allow you to customize the appearance of your data.
Here are some of the most commonly used formatting tools
on the Home tab:
 1. Font: You can change the font type, size, and color of
your text using the Font group.
2. Number Format: You can change the format of numbers,
dates, and times using the Number group.
3. Alignment: You can adjust the horizontal and vertical
alignment of text, as well as its orientation using the
Alignment group.
4. Fill: You can fill cells with color or patterns using the Fill
group.
5. Borders: You can add borders to cells or ranges of cells
using the Borders group.
6. Conditional Formatting: You can apply formatting to
cells based on their values using the Conditional
Formatting group.
7. Styles: You can apply pre-defined styles to cells or
ranges of cells using the Styles group.

Mini Toolbar

Excel does not have a mini toolbar in the same way that other Microsoft Office
applications like Word or PowerPoint do. However, when you right-click on a cell or range
of cells in Excel, a mini toolbar-like menu does appear. This menu is called the "Shortcut
Menu" or "Context Menu", and it provides quick access to some commonly used
formatting and editing options.

The options available in the Shortcut Menu depend on the context in which you
right-click. For example, if you right-click on a cell, the Shortcut Menu may provide
options for formatting the cell's number or changing its alignment. If you right-click on a
range of cells, the Shortcut Menu may provide options for merging cells, formatting as a
table, or sorting data.
While it's not exactly the same as the mini toolbar in other Office applications, the
Shortcut Menu in Excel provides a similar function by allowing you to quickly access
formatting and editing options without having to navigate through the ribbon.
● Fonts: You can change the font type, size and color of the text in Excel.
To do this, select the cell(s) you want to format and then click on the
“Font” button in the “Home” tab. From there you can choose your
desired font type, size and color.
● Text Alignment: You can align text horizontally or vertically in a cell. To
do this, select the cell(s) you want to format and then click on the
“Alignment” button in the “Home” tab. From there you can choose your
desired alignment.
● Wrapping text to fit a cell: You can wrap text so it appears on multiple
lines in a cell. To do this, select the cell(s) you want to format and then
click on the “Wrap Text” button in the “Home” tab.
● Colors and Shading: You can change the background color of a cell or
range of cells. To do this, select the cell(s) you want to format and then
click on the “Fill Color” button in the “Home” tab. From there you can
choose your desired color.
● Borders and Lines: You can add borders or lines around cells or ranges
of cells. To do this, select the cell(s) you want to format and then click
on the “Borders” button in the “Home” tab. From there you can choose
your desired border style.
● Naming Styles: You can create custom styles for cells that include
formatting such as font size, color, alignment, etc. To do this, select the
cell(s) you want to format and then click on the “Cell Styles” button in
the “Home” tab. From there you can choose your desired style or create
a new one.