Excel Tips and Tricks

1. How to Create a Table

With our spreadsheet (worksheet) opened in Excel, we first need to create some mock-up data. For this example, we could use the following:

Fname	Lname	Order Number	Order Item	Order Cost	Additional Fees	Order Quantity
Jeffrey	Thomas	10001	LRG Shirt	12	2	1
Austin	Walter	10002	SM Shirt	10	2	2
Beth	Spencer	10003	XL Shirt	13	2	2
Lily	Peters	10004	M Shirt	11	2	3
Gregory	Hart	10005	LRG Shirt	12	2	1

Note: We will use this data throughout the examples to demonstrate tips like calculating the order total with basic formulas and identifying orders eligible for free shipping using conditional formatting.

Steps to Create a Table:

1. Highlight all the data, including the column headers and rows.
2. Navigate to the Ribbon, locate the Insert tab, and select Table.
3. A dialog box will appear, showing the selected range (e.g., $A$1:$G$6).
   • Ensure the "My table has headers" checkbox is selected.
4. Click OK. The data will be converted into a table with a default style.

Your table will look like this:

Fname	Lname	Order Number	Order Item	Order Cost	Additional Fees	Order Quantity
Jeffrey	Thomas	10001	LRG Shirt	12	2	1
Austin	Walter	10002	SM Shirt	10	2	2
Beth	Spencer	10003	XL Shirt	13	2	2
Lily	Peters	10004	M Shirt	11	2	3
Gregory	Hart	10005	LRG Shirt	12	2	1

Why Convert Data to a Table?

Using a table format makes managing data more efficient. Benefits include:

• Easy addition of rows and columns.
• Automatic formatting and rule application.
• Improved sorting and filtering capabilities.
• Enhanced usability when creating pivot tables or combining data across worksheets.

---

2. How to Add a Column

Adding Columns:

1. Right-click on any column header within the table.
2. From the menu, select Insert and then Table Columns to the Left. This will insert a new column to the left of the selected column.

For example, if a new column is inserted, the table might look like this:

Fname	Lname	Order Number	New Column	Order Item	Order Cost	Additional Fees	Order Quantity
Jeffrey	Thomas	10001		LRG Shirt	12	2	1
Austin	Walter	10002		SM Shirt	10	2	2
Beth	Spencer	10003		XL Shirt	13	2	2
Lily	Peters	10004		M Shirt	11	2	3
Gregory	Hart	10005		LRG Shirt	12	2	1

Renaming Columns:

• Double-click the header of the new column (e.g., "New Column").
• Type the desired name (e.g., "Order Date") and press Enter.

Adding Columns at the End:

• Click the cell to the right of the last column in the table.
• Type the desired column header (e.g., "Order Total") and press Tab. This will automatically extend the table to include the new column.

---

3. How to Add a Row

Adding Rows:

1. Right-click on any row within the table.
2. Select Insert and choose Table Row Below or Table Row Above.

Example:

Fname	Lname	Order Number	Order Item	Order Cost	Additional Fees	Order Quantity
Jeffrey	Thomas	10001	LRG Shirt	12	2	1
Austin	Walter	10002	SM Shirt	10	2	2
Beth	Spencer	10003	XL Shirt	13	2	2
Lily	Peters	10004	M Shirt	11	2	3
Gregory	Hart	10005	LRG Shirt	12	2	1
Deb	Wrenley	10006	SM Shirt	10	2	3

Automatically Adding Rows:

• Start typing in the row below the last row in the table. The table will automatically expand to include the new row.

---

4. How to Adjust Column Width and Row Height

1. Hover over the border between two columns or rows until the cursor changes to a double-sided arrow.
   • To resize manually: Click and drag the border to increase or decrease the size.
   • To auto-fit content: Double-click the border to adjust the size to fit the content.
2. For specific dimensions:
   • Right-click the column header or row number.
   • Select Column Width or Row Height from the menu.
   • Enter the desired value and click OK.

---

5. How to Move Columns and Rows

Moving Columns:

1. Right-click the column header and select Cut.
2. Right-click the header of the target column and select Insert Cut Cells. The column will move to the new location.

Example:

Fname	Lname	Order Date	Order Number	Order Item	Order Cost	Additional Fees	Order Quantity
Jeffrey	Thomas	2023-01-15	10001	LRG Shirt	12	2	1

Moving Rows:

1. Right-click the row number and select Cut.
2. Right-click the target row and select Insert Cut Cells. The row will move to the new location.

Alternatively, drag and drop rows or columns using the mouse.

---

6. How to Delete or Hide Rows and Columns

To Delete:

1. Right-click the row number or column header you want to delete.
2. Select Delete from the context menu.

To Hide:

1. Right-click the row number or column header you want to hide.
2. Select Hide from the context menu.

To Unhide:

1. Highlight the adjacent rows or columns.
2. Right-click and select Unhide.

---

7. How to Rename the Worksheet

1. Double-click the worksheet tab at the bottom of the Excel window.
2. Type the new name for the worksheet.
3. Press Enter to save the new name.

---

8. How to Use Autofill Series

1. Enter a starting value in a cell. For example, insert a new column and name it "Customer Number." In the first empty cell of this column, type a starting value such as "110-1."

Example:

Fname	Lname	Customer Number	Order Date	Order Number	Order Item	Order Cost	Additional Fees	Order Quantity	Order Total
Jeffrey	Thomas	110-1	5/22/2024	10001	LRG Shirt	12	2	1
Austin	Walter		5/22/2024	10002	SM Shirt	10	2	2
Beth	Spencer		5/24/2024	10003	XL Shirt	13	2	2
Lily	Peters		5/25/2024	10004	M Shirt	11	2	3
Gregory	Hart		5/25/2024	10005	LRG Shirt	12	2	1

2. Hover the cursor over the lower-right corner of the starting cell until the cursor turns into a bold plus sign. Drag the fill handle across adjacent cells to autofill the series.
3. Excel will automatically continue the series (e.g., numbers, dates, or days of the week).
4. Use the Auto Fill Options dropdown to customize the series if needed.

Updated Example:

Fname	Lname	Customer Number	Order Date	Order Number	Order Item	Order Cost	Additional Fees	Order Quantity	Order Total
Jeffrey	Thomas	110-1	5/22/2024	10001	LRG Shirt	12	2	1
Austin	Walter	110-2	5/22/2024	10002	SM Shirt	10	2	2
Beth	Spencer	110-3	5/24/2024	10003	XL Shirt	13	2	2
Lily	Peters	110-4	5/25/2024	10004	M Shirt	11	2	3
Gregory	Hart	110-5	5/25/2024	10005	LRG Shirt	12	2	1

---

9. How to Undo or Redo Actions

• Undo: Press Ctrl + Z or click the Undo arrow in the top-left toolbar.
• Redo: Press Ctrl + Y or click the Redo arrow next to Undo.

---

10. How to Protect Your Workbook

1. Navigate to the File tab and select Info.
2. Click Protect Workbook and choose an option (e.g., Encrypt with Password, Always Open Read-Only).
3. Follow the prompts to set a password or apply restrictions.
4. Save the workbook to ensure the protection settings are applied.

---

Additional Tips

1. How to Create Basic Formulas Using Mathematical Operators

Use the table below to perform the following example:

Fname	Lname	Customer Number	Order Date	Order Number	Order Item	Order Cost	Additional Fees	Order Quantity	Order Total
Jeffrey	Thomas	110-1	5/22/2024	10001	LRG Shirt	12	2	1
Austin	Walter	110-2	5/22/2024	10002	SM Shirt	10	2	2
Beth	Spencer	110-3	5/24/2024	10003	XL Shirt	13	2	2

1. In the Order Total column, select the first empty cell.
2. Start your formula by typing =. Then:
   • Click the Order Cost cell (e.g., G2).
   • Type * (to multiply).
   • Click the Order Quantity cell (e.g., I2).
   • Type + (to add).
   • Click the Additional Fees cell (e.g., H2).
3. Press Enter to calculate the total.

Example Formula:

=([@Order Cost]*[@Order Quantity])+[@Additional Fees]

Result:

Fname	Lname	Customer Number	Order Date	Order Number	Order Item	Order Cost	Additional Fees	Order Quantity	Order Total
Jeffrey	Thomas	110-1	5/22/2024	10001	LRG Shirt	12	2	1	14
Austin	Walter	110-2	5/22/2024	10002	SM Shirt	10	2	2	22
Beth	Spencer	110-3	5/24/2024	10003	XL Shirt	13	2	2	28

---

2. How to Create a Basic Conditional Formatting Rule

1. Select the Order Total column.
2. Go to the Home tab and select Conditional Formatting > Highlight Cell Rules > Greater Than.
3. In the dialog box, type 20 and choose a formatting style (e.g., Green Fill with Dark Green Text).
4. Click OK.

Result:

Fname	Lname	Customer Number	Order Date	Order Number	Order Item	Order Cost	Additional Fees	Order Quantity	Order Total
Jeffrey	Thomas	110-1	5/22/2024	10001	LRG Shirt	12	2	1	14
Austin	Walter	110-2	5/22/2024	10002	SM Shirt	10	2	2	22
Beth	Spencer	110-3	5/24/2024	10003	XL Shirt	13	2	2	28

This highlights cells in the Order Total column that are greater than 20.