IT202- Excel 3
EXCEL 3
1. Excel Error Codes
2. Formula Auditing
3. Text to Column
4. Remove Dublicates
5. Data validation
6. Naming Cells
7. Using Named Ranges in formulas and/or Functions
8. Scenario
1. Error codes:
Explain Excel Error Codes:
Error Code Description
##### The column isn’t wide enough to display the value.
#VALUE! The formula has the wrong type of argument (such as text where a TRUE or
FALSE value is required).
#NAME? The formula contains text that Excel doesn’t recognize (such as anunknown
named range).
#REF! The formula refers to a cell that doesn’t exist (which can happen whenever cells
are deleted).
#DIV/0! The formula attempts to divide by zero.
Copy “IT202_Excel_3_Files” folder to your desktop….
Open ConveyerBid.xlsx from IT202_Excel_3 folder
2. Formula Auditing:
Click cell D8
On the Formulas tab, in the Formula Auditing group, click Trace Precedents button.
1
IT202- Excel 3
Explain blue arrow on the screen.
Click Remove Arrows button.
Click cell C5
Click Trace Dependents button.
Explain blue arrow on the screen.
Click Remove Arrows button.
Understanding error in D21
Click cell D21
Click Trace Precedents button.
Double click cell D21
Correct the formula as “=C12/D20” enter
Arrows will disappear.
Click again D21 and click Trace Precedents button again.
2
IT202- Excel 3
Open Credit.xlsx from IT202_Excel_3 folder
3. Text to Column
We want to arrange customers name and last name in the different cells.
Insert a new column between B and C columns. (select C column and right click select
insert)
Type “Customer Name” in the cell B3 and “Last Name” in the cell C3
Select B4:B39
From Data tab, in Data Tools group click Text to Columns button.
Step 1 Delimited
Step 2 Clear Tab, Select Space
Step3 No change Finish.
Ta ta ta taaa, names and lastnames in separe columns now….!
3
IT202- Excel 3
4. Remove Dublicates
Some customers recorded to the list two or more times. We have to remove them.
Be careful ! Two different people might have Same Name and Same Last name
even both of them. So we have to remove same account numbered records.
Click any cell in the list.
Click Remove Duplicates button from Data Tools group at the Data Tab.
Click “Unselect All” button, and select only AccNumber checkbox. OK
Ta ta ta taaa, Duplicate records (3 records) had been removed!
4
IT202- Excel 3
5. Data Validation:
We want to define credit limit for each customers. But we are authorized until
25000 $.
Select the cell range D4:D37
On the Data tab, in the Data Tools group, click Data Validation.
In Settings tab:
Select “Whole Number” from Allow box
Select “less than or equal to” oprion from data box.
Type 25000 in Maximum box.
In Input Message tab:
Type “Enter Limit” in Title box.
Type “Please enter the customer's credit limit, omitting the dollar sign.” In
Input message box
5
IT202- Excel 3
In Error Alert tab:
Select Warning from style box.
Type “Opps!” In the title box
Type “The value you entered is not valid!” in the Error message box.
There are 3 type of error alert style.
1. Stop: You cannot enter
invalid data!
2. Warning: You can enter
invalid data by accepting
error alert message box.
3. Information: Same as
Warning. But only show
error alert message box.
Click OK.
Trying to validation…
Click A37 cell, enter 12345
Click B37 cell, enter Serçin
Click C37 cell, enter Basut
Click D37 cell, enter 26000
What happened! I need $26.000, what should I do???
Click Data validation button and select Circle Invalid Data
Look at the red circles!!! Wawww!
Click Data validation button and select Clear Validation Circles
6
IT202- Excel 3
Open NameRange.xlsx from IT202_Excel_3 folder
6. Naming Cells
If necessary, click the Tools sheet tab.
Select C4: C18 range.
Click Define Name button on the Define Names group from Formulas tab.
Type “Tools_Price” in the name box. Scope means, valid areas of named
range within the workbook.
Click Ok.
Click the Supplies sheet tab.
Select C4: C29 range.
Click Name Box and type “Supplies_Price” Enter
Click the Furniture sheet tab.
Select C3:C18 range.
Click Create From Selection button on the Defined Names group.
Leave it as “Top Row” selected. Click Ok.
7
IT202- Excel 3
Click Name Manager button on the same group.
Click on the “Price “and click edit button.
Change its name as “Furniture_Price”
Click Close.
Click the Passiflora sheet tab.
Select C4:C6 range.
Click Name Manager button.
Click New button.
Type “Passiflora_Price” as the name
Click Ok
Click Close to close Name manager dialog box.
Try to select names from name box.
8
IT202- Excel 3
7. Using Named Ranges in formulas and/or Functions
Switch to Tools sheet.
Click D3 and type “Tax (18%)”
Click D4 and type “=Tools_Price*0,18” press enter
Click D4 and double click autofill button.
Click Summary Worksheet and prepare following table:
Click B3 and type
“=AVERAGE(Tools_Price)” ENTER
Tip: You can also create this string by pressing AutoSum button in Function Library group
on the Formulas tab.
Click B4 and type
“=AVERAGE(Supplies_Price)” ENTER
Click B5 and type
“=AVERAGE(Furniture_Price)” ENTER
Click B6 and type
“=AVERAGE(Passiflora_Price)” ENTER
Tip: You can add names in formula or function by pressing Use in Formula button in the
Named Ranges group.
CLOSE NameRange.xls no need to save
9
IT202- Excel 3
Open BalanceSheet.xlsx from IT202_Excel_3 folder
8. Scenarios:
Select C3:C6 and name this range as “Expenses”
(Select C3:C6, click on namebox and type Expenses)
Name C10 cell as “Sales”
(Select B10:C10 range and click create from selection button on the Defined Names group, from
Formulas Tab. Select left Column and click OK.)
Name C11 cell as “Rent”
(Select B11:C11 range and click create from selection button on the Defined Names group, from
Formulas Tab. Select left Column and click OK.)
Name C7 cell as “EXPENSES_TOTAL”
(Select B7:C7 range and click create from selection button on the Defined Names group, from
Formulas Tab. Select left Column and click OK.)
Name C12 cell as “INCOMES_TOTAL”
(Select B12:C12 range and click create from selection button on the Defined Names group, from
Formulas Tab. Select left Column and click OK.)
Name C14 cell as “BALANCE”
(Select B14:C14 range and click create from selection button on the Defined Names group, from
Formulas Tab. Select left Column and click OK.)
Click C7 And type or create this formula, and pres enter.
=SUM(Expenses)
Click C12 And type or create this formula, and pres enter.
=Sales+Rent
Click C12 And type or create this formula, and pres enter.
=Sales+Rent
Click C14 And type or create this formula, and pres enter.
=INCOMES_TOTAL-EXPENSES_TOTAL
10
IT202- Excel 3
So We have different scenarios;
1. Scenario (worst scenario):
Sales is $50.000
2. Scenario (Moderate scenario):
Sales is $60.000
3. Scenario (Best scenario):
Sales is $70.000
Click C10, Click What If Analysis button on Data Tools Group From Data Tab. And
select Scenario Manger.
Click Add button on Scenario Manager dialog box.
11
IT202- Excel 3
Enter Worst as the scenario name.
Changing Cells C10
OK.
Enter 50000
Click Add button.
Enter Moderate as the scenario name.
Changing Cells C10
OK.
Enter 60000
Click Add button.
Enter Best as the scenario name.
Changing Cells C10
OK.
Enter 70000
Click OK button.
12
IT202- Excel 3
Double click on Best, Worst or Moderate items on this box. And follow the changes
on the worksheet.
Click Summary button.
Select C14 as Result Cells.
Click OK.
Ta ta taaa Scenario Summary Sheet was created…
Select D column and delete it from Scenario Summary Sheet.
13