Understanding Data Entry and Formatting in Excel
Intro
This guide focuses on practical steps for mastering data entry and formatting in Excel to ensure accuracy and consistency in any project.
StepbyStep Instructions
1. Data Entry
 Open Excel: Launch the Excel application.
 New Workbook: Click 'File' > 'New' > 'Blank Workbook'.
 Entering Text:
 Click on a cell (e.g., A1).
 Type the desired text (e.g., "Name") and press
Enter
.
 Entering Numbers:
 Click on the next cell (e.g., A2).
 Type the number (e.g., "100") and press
Enter
.
2. Formatting Cells
 Text Formatting:
 Select the cell(s) containing text.
 Use the toolbar to set font type, size, bold, italics, underline.
 Number Formatting:
 Select the cell(s) containing numbers.
 Rightclick and select 'Format Cells'.
 Choose the 'Number' tab and select the appropriate category (e.g., Number, Currency, Date).
3. Formatting Columns and Rows
 Adjusting Column Width:
 Click on the letter header of the column (e.g., A).
 Place the cursor at the edge until it becomes a doublesided arrow.
 Click and drag to adjust the width.
 Adjusting Row Height:
 Click on the number header of the row (e.g., 1).
 Place the cursor at the edge until it becomes a doublesided arrow.
 Click and drag to adjust the height.
4. Using Fill Handle
 Fill Handle for Series:
 Enter a starting value in a cell (e.g., A1: 1).
 Select the cell, then drag the fill handle (small square at the bottomright corner) down or across to fill the series.
5. Quick Formatting Techniques
 Quick Format Cells to Currency:
 Select the target cells.
 Use the shortcut
Ctrl + Shift + $
.
 Quick Format Cells to Percentage:
 Select the target cells.
 Use the shortcut
Ctrl + Shift + %
.
6. Basic Formula Usage
 Sum:
 Click on a cell where you want the sum.
 Type
=SUM(A1:A10)
assuming you want to sum values from A1 to A10.  Press
Enter
.
 Average:
 Click on a cell where you want the average.
 Type
=AVERAGE(A1:A10)
.  Press
Enter
.
7. Conditional Formatting
 Apply Conditional Formatting:
 Select the cells to format.
 Click on 'Home' > 'Conditional Formatting'.
 Choose a rule (e.g., Highlight Cells Rules > Greater Than).
 Enter the criteria and select the format.
 Click 'OK'.
Practice Exercises

Exercise 1:
 Create a new workbook.
 Enter a list of 10 items in column A.
 Format the text to be bold and size 12.
 In column B, enter corresponding prices and format as currency.
 Sum the prices in cell B12.

Exercise 2:
 Create a date range from January 1 to January 10 in column A using fill handle.
 Format the dates to 'ddmmmyy'.
 Average the values in column B and display the result in cell B12.
This concludes Part #1 of the project. Continue practicing to solidify your understanding of data entry and formatting in Excel.
Using Basic Formulas for Data Calculations in Excel
Sum Function
Calculates the total sum of a range of cells.
=SUM(A1:A10)
Average Function
Finds the average (mean) of a range of cells.
=AVERAGE(B1:B10)
Count Function
Counts the number of cells in a range that contain numbers.
=COUNT(C1:C10)
Max Function
Finds the maximum value in a range of cells.
=MAX(D1:D10)
Min Function
Finds the minimum value in a range of cells.
=MIN(E1:E10)
IF Function
Checks whether a condition is true, and returns one value if true and another if false.
=IF(F1>10, "Greater than 10", "10 or less")
VLOOKUP Function
Looks up a value in the first column of a range, and returns a value in the same row from a specified column.
=VLOOKUP(G1, A1:D10, 3, FALSE)
HLOOKUP Function
Looks up a value in the top row of a range, and returns a value in the same column from a specified row.
=HLOOKUP(H1, A1:D10, 3, FALSE)
CONCATENATE Function
Joins several text strings into one text string.
=CONCATENATE(I1, " ", J1)
LEFT Function
Returns the first character(s) from the left side of a text string.
=LEFT(K1, 3)
RIGHT Function
Returns the last character(s) from the right side of a text string.
=RIGHT(L1, 2)
MID Function
Returns a specific number of characters from a text string, starting at the specified position.
=MID(M1, 2, 4)
TODAY Function
Returns the current date.
=TODAY()
NOW Function
Returns the current date and time.
=NOW()
LEN Function
Returns the number of characters in a text string.
=LEN(N1)
TRIM Function
Removes extra spaces from a text string, leaving only single spaces between words.
=TRIM(O1)
These formulas are foundational tools for data calculations and analysis in Excel. Use them directly within your spreadsheet to perform calculations effectively.
Advanced Formulas for Enhanced Data Manipulation in Excel
1. Nested IF Statements
Perform multiple conditions checks.
=IF(A1 > 90, "A", IF(A1 > 80, "B", IF(A1 > 70, "C", "D")))
2. VLOOKUP for Data Fetching
Retrieve related data from different tables.
=VLOOKUP(B2, Table1!A:B, 2, FALSE)
3. INDEXMATCH Combination
A more flexible lookup than VLOOKUP.
=INDEX(A:A, MATCH("LookupValue", B:B, 0))
4. CONCATENATE Text Strings
Merge data from separate cells.
=CONCATENATE(A2, " ", B2, " ", "Example")
5. SUMIF/SUMIFS for Conditional Summation
Sum numbers based on criteria.
=SUMIF(A:A, ">100", B:B)
=SUMIFS(C:C, A:A, ">100", B:B, "<200")
6. COUNTIF/COUNTIFS for Conditional Counting
Count cells based on criteria.
=COUNTIF(A:A, ">=50")
=COUNTIFS(A:A, ">=50", B:B, "<=100")
7. TEXT Function
Format numbers as text.
=TEXT(A2, "0.00")
8. Array Formulas for Advanced Calculations
Perform batch operations on multiple cells.
=SUM(A2:A10 * B2:B10) // Press Ctrl+Shift+Enter
9. LEFT, MID, RIGHT for Substring Extraction
Extract parts of text strings.
=LEFT(A2, 3)
=MID(A2, 2, 5)
=RIGHT(A2, 4)
10. IFERROR for Error Handling
Catch and handle errors in formulas.
=IFERROR(VLOOKUP("Value", A:B, 2, FALSE), "Not Found")
11. Dynamic Named Ranges with OFFSET
Create ranges that expand automatically as data grows.
OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A:$A), 1)
12. Use of AND/OR in Conditional Statements
Combine multiple conditions.
=IF(AND(A2 > 50, B2 < 100), "Pass", "Fail")
=IF(OR(A2 > 50, B2 < 100), "Pass", "Fail")
These advanced formulas enhance your ability to manipulate and analyze data effectively in Excel.
4. Manipulating and Cleaning Data with Excel Tools
4.1 Remove Duplicates

Select the range:
 Select the data range including the headers. Example:
A1:D100
 Select the data range including the headers. Example:

Remove duplicates:
 Go to the
Data
tab.  Click
Remove Duplicates
.  Ensure that relevant columns are checked in the popup window.
 Click
OK
.
 Go to the
4.2 Handle Missing Data
 Find and Replace:
 Select the range.
 Press
Ctrl + H
to open 'Find and Replace'.  Leave 'Find what' blank.
 Type the value (e.g.,
NA
,0
) into 'Replace with'.  Click
Replace All
.
4.3 Convert Text to Columns

Select the column:
 Click the header of the column to select.

Text to Columns:
 Go to the
Data
tab.  Click
Text to Columns
.  Choose
Delimited
.  Select delimiter (e.g., Comma).
 Click
Finish
.
 Go to the
4.4 Trim Spaces
 Trim Function:
 Create a new column next to the target.
 Use the formula
=TRIM(A1)
assuming the target cell isA1
.  Drag the formula down to apply to other cells.
 Copy the trimmed data column.
 Paste values into the original column.
4.5 Change Case of Text

Upper Case:
=UPPER(A1)
for converting to upper case.

Lower Case:
=LOWER(A1)
for converting to lower case.

Proper Case:
=PROPER(A1)
for capitalizing the first letter of each word.
4.6 Split Data into Multiple Columns
 Using Flash Fill:
 Enter the desired format in the neighboring column manually.
 Start typing the cleanedup data.
 Excel will show a suggestion; press
Enter
to accept.
4.7 Consolidate Data from Multiple Sheets
 Consolidate Tool:
 Go to
Data
tab.  Click
Consolidate
.  Select function (e.g., SUM, AVERAGE).
 Add the ranges from different sheets.
 Click
OK
.
 Go to
4.8 Filter Data
 Apply Filter:
 Select the header row.
 Go to
Data
tab.  Click
Filter
.  Use the dropdown arrows to filter data as needed.
4.9 Sort Data
 Sort:
 Select the data range.
 Go to
Data
tab.  Click
Sort
.  Choose the column for sorting and sort order (Ascending/Descending).
4.10 Convert Text to Dates/Numbers
 Convert Text to Date/Number:
 Select the column.
 Go to the
Data
tab.  Click
Text to Columns > Next > Next
.  Choose
Date
orGeneral
.  Click
Finish
.
4.11 Use Conditional Formatting
 Apply Conditional Formatting:
 Select the range.
 Go to
Home
tab.  Click
Conditional Formatting
.  Choose rules (e.g., Highlight Cells Rules > Greater than).
 Set criteria and format.
 Click
OK
.
Complete these steps using the described tools to manipulate and clean your data efficiently.
Part 5: Visualizing Data with Charts and Graphs in Excel
StepbyStep Implementation
Creating a Basic Chart

Select Data Range:
 Click and drag to select the data range you want to visualize. This should include labels.

Insert Chart:
 Go to the
Insert
tab on the Excel Ribbon.  Choose the chart type you want to use (e.g., Column, Line, Pie).
 Go to the

Customize Chart:
 Title:
 Click on the chart title to edit it.
 Axes Titles:
 Click on
Chart Elements
(plus icon).  Check the
Axis Titles
box and edit titles as needed.
 Click on
 Legend:
 Click on
Chart Elements
, checkLegend
, and place it in your desired location.
 Click on
 Title:
Creating a Pivot Chart

Create a PivotTable:
 Select your data range.
 Go to the
Insert
tab, click onPivotTable
.  Choose where to place the PivotTable (new or existing worksheet).

Populate PivotTable:
 Drag and drop fields to the
Rows
,Columns
, andValues
areas as needed.
 Drag and drop fields to the

Insert PivotChart:
 Click within the PivotTable.
 Go to the
Analyze
orOptions
tab.  Click
PivotChart
and choose chart type.

Customize PivotChart:
 Follow similar customization steps as a basic chart.
Adding Trendlines

Insert a Trendline:
 Click on the chart to select it.
 Click on
Chart Elements
, checkTrendline
.  Choose the type of trendline you need (e.g., Linear, Exponential).

Customize Trendline:
 Rightclick on the trendline, select
Format Trendline
.  Set options like
Display Equation on chart
if necessary.
 Rightclick on the trendline, select
Creating a Combo Chart

Select Data:
 Highlight the data range to include in the combo chart.

Create Chart:
 Go to the
Insert
tab.  Click
Insert Combo Chart
.
 Go to the

Set Chart Type:
 Choose the
Custom Combination Chart
option.  Set the chart type for each data series (Column, Line, etc.).
 Choose the

Customize Combo Chart:
 Follow similar customization steps as a basic chart.
Formatting Charts

Chart Styles:
 Click on the chart.
 Go to
Chart Tools
in the Ribbon, selectDesign
, and choose a Chart Style.

Color and Font:
 Rightclick on chart elements (bars, lines).
 Choose
Format Data Series
and set colors and fonts as desired.
Exporting Charts

Copy Chart to Other Applications:
 Rightclick on the chart.
 Select
Copy
.  Paste it into applications like Word or PowerPoint.

Save as Image:
 Select the chart.
 Rightclick and choose
Save as Picture
.
Conclusion
This section enables you to create, customize, format, and export various charts and graphs using Excel, aiding in effective data visualization for analysis.
Part 6: Utilizing Data Analysis Tools: PivotTables and Solver
PivotTables
StepbyStep Guide to Create a PivotTable

Select Data Range
Select the range of data you want to analyze. (e.g., A1:D100)

Insert PivotTable
 Navigate to the
Insert
tab.  Choose
PivotTable
.  Select either a new worksheet or an existing one for the PivotTable location.
 Navigate to the

Configure PivotTable Fields
 Rows: Drag a column to the Rows area. (e.g., Region)
 Columns: Drag another column to the Columns area. (e.g., Product)
 Values: Drag a column to the Values area. (e.g., Sales)
 Filters: Drag a column to the Filters area if required. (e.g., Date)
Solver
StepbyStep Guide to Use Solver for Optimization

Set Up the Problem
 Setup your objective cell (target cell to be maximized, minimized, or set to a value).
 Identify the variable cells that can be changed to achieve the objective.

Open Solver
 Navigate to the
Data
tab.  Click on
Solver
.
 Navigate to the

Configure Solver Parameters
 Set Objective: Choose the objective cell.
 To: Set the objective to
Max
,Min
, orValue Of
.  By Changing Variable Cells: Select the range of cells to be adjusted.
 Subject to the Constraints: Add constraints by clicking on
Add
and specifying each constraint.

Run Solver
 Click on
Solve
.  Review the Solver Results and choose either to accept or restore the original values.
 Click on
Example Walkthrough
PivotTable Example
Assume you have sales data:
Region  Product  Date  Sales 

East  A  20230101  100 
West  B  20230102  150 
East  A  20230103  200 
West  B  20230104  250 
 Select range
A1:D5
.  Insert a PivotTable on a new worksheet.
 Configure Fields:
 Rows:
Region
 Columns:
Product
 Values:
Sales
(Sum)
 Rows:
Solver Example
Objective: Maximize profit based on variable resources.
Items  Profit per Unit  Units Available 

Item1  10  20 
Item2  15  30 

Set Objective: Maximize total profit in cell
C5
. 
Variable Cells: Units produced in range
B2:B3
. 
Constraints:
B2
<= 20B3
<= 30

Set and solve using the Solver:
 Set Objective:
C5
(max)  By Changing:
B2:B3
 Constraints:
B2 <= 20
,B3 <= 30
 Set Objective: