7.

Conditional Formatting: formatting, parsing, and highlighting data in spreadsheets during

data analysis.

formatting: By default, all cells in Microsoft Excel worksheets are formatted with the General format. With
the default formatting, anything you input into a cell is displayed as typed.

Opening of Format Cells Dialog Box: 4 ways to open the Format Cells dialog: To change formatting of a
certain cell or a block of cells, select the cell(s) you wish to format, and do any of the following:

1. Press Ctrl + 1 shortcut.

2. Right click the cell (or press Shift+F10), and select Format Cells… from the pop-up menu.

3. Click the Dialog Box Launcher arrow at the bottom right corner of
the Number, Alignment or Font group to open the corresponding tab of the Format Cells dialog:

4. On the Home tab, in the Cells group, click the Format button, and then click Format Cells…

Format Cells dialog in Excel

The Format Cells dialog window has six tabs that provide different formatting options for the selected cells. To
find more about each tab, click on the corresponding link:
  Number
 Alignment
 Font
 Border
 Fill
 Protection

Font tab - change font type, color and style: Use the Font tab options to change the font type, color, size,
style, font effects and other font elements: Go to “Home” use “Font” area to format text in cell.

Border tab - create cell borders of different styles

Use the Border tab options to create a border around selected cells in a color and style of your choosing. If you
don't want to remove the existing border, select None.

Alignment tab - change alignment, position and direction

As its name suggests, this tab lets you change text alignment in a cell. Additionally, it provides a number of
other options, including:

 Align the cell contents horizontally, vertically, or centered. Also, you can center the value across
selection (a great alternative to merging cells!) or indent from any edge of the cell.

 Wrap text into multiple lines depending on the column width and the length of the cell contents.
  Shrink to fit - this option automatically reduces the apparent font size so that all data in a cell fits in the
column without wrapping. The real font size applied to a cell is not changed.
 Merge two or more cells into a single cell.
 Change the text direction to define reading order and alignment. The default setting is Context, but
you can change it to Right-to-Left or Left-to-Right.
 Change the text orientation. A positive number input in the Degrees box rotates the cell contents
from lower left to upper right, and a negative degree performs the rotation from upper left to lower
right. This option may not be available if other alignment options are selected for a given cell.

The below screenshot shows the default Alignment tab settings:

Number tab - apply a specific format to numeric values

Use this tab to apply the desired format in terms of number, date, currency, time, percentage, fraction, scientific
notation, accounting number format or text.

Excel Number format

For numbers, you can change the following options:

 How many decimal places to display.

 Show or hide the thousands separator.
 Specific format for negative numbers.

By default, the Excel Number format aligns values right in cells.

Tip. Under Sample, you can view a preview of how the number will be formatted on the sheet.
Currency formats

The Currency format lets you configure the following three options:

 The number of decimal places to display

 The currency symbol to use
 The format to apply to negative numbers

Tip. To quickly apply the default currency format with 2 decimal places, select the cell or range of cells and
press the Ctrl+Shift+$ shortcut.

Both Currency and Accounting formats are used to display monetary values. The difference is as follows:

 The Excel Currency format places the currency symbol immediately before the first digit in the cell.
 The Excel Accounting number format aligns the currency symbol on the left and the values on the
right, zeros as displayed as dashes.

Tip. Some of the most often used Accounting format options are also available on the ribbon. For more details,
please see Accounting format options on the ribbon.

Date and Time formats

Microsoft Excel provides a variety of predefined Date and Time formats for different locales:
Parsing: Suppose column A contains "Last Name, First Name". Follow these steps to split the data from
column A into a "Last Name" column and a "First Name" column. No cutting and pasting necessary.

Open the Excel spreadsheet containing the data you want to split, then:

1. Highlight the column that contains the combined data (e.g., Last Name, First Name) by clicking the
letter directly above the column.
2. Click the “Data” tab in the ribbon, then look in the "Data Tools" group and click "Text to Columns."
The "Convert Text to Columns Wizard" will appear.
3. In step 1 of the wizard, choose “Delimited” > Click [Next].
4. A delimiter is the symbol or space which separates the data you wish to split. For example, if your
column reads “Smith, John” you would select “Comma” as your delimiter. Select the delimiter within
your data.
5. Check the box next to "Treat consecutive delimiters as one."
6. Click [Next].
7. Under "Column data format," choose "General."
8. Click on the "Destination" text box.
9. Highlight the columns you wish to contain the split data by clicking the letters directly above the
columns (you can choose columns from anywhere within the spreadsheet). Or, manually click and drag
to select the cells you wish to contain the split data.
10. Click the red arrow/spreadsheet icon once more to return to the wizard.
11. Click [Finish].

If the data you wish to split does NOT contain a delimiter (dash, comma, tab etc.) to
separate the data, select "Fixed width" within the first step of the "Convert Text to
Column" Wizard. This option allows you to manually created divisions within your data
by dragging a break line.

Highlighting data in Spreadsheets:

Highlighting data using Conditional Formatting - Create List and Color the required data

steps for creating this worksheet, using conditional formatting and data validation

Create Table as shown below. It is needed to highlight(Color) the data.

Create one more ITEM column , click on cell below this ITEM and go to DATA Tab ->cell Data Validation-
>Click on Data Validation, Data Validation window shows up. Create list by selecting all items under ITEM in
Table->Select Table -> HOME Tab-> Click on Conditional Formatting-> Click on New Rule-> Opens New
Formatting Rule->In the “Select a Rule Type” pane-> select “Use a formula to determine which cells to
format”->Click on “Format values where this formula is true”->select first item under “ITEM” from Table-
>Then locked row name and column name appear in that box to unlock row it delete the second $ symbol-> type
“=” in the box and select cell below ITEM(out side of the Table)->Click on Format-> Format Cell Window pops
up-> click on Fill->Select Color ->ok->OK->item chosen from the list in ITEM will be highlighted in the Table.
Experiment 6: Cleaning Data Containing Date and Time Values: Use of DATEVALUE
function, DATEADD and DATEDIF, TIMEVALUE functions.
DATEADD function: How to use DATEADD in Excel VBA

Syntax: DateAdd(interval,number,date)

Step1: Create Range of interval table in Excel Sheet as shown in pic below.

Step 2: Go to Developer Tab in Excel under Code group click on Visual Basic.

Step 3: In Visual Basic click on insert tab chose Module, editor shows up.

Step 4: Write Subroutine as below:

Sub UseDateAdd() /Start of Subroutine

Dim CurrentDate As Date / Declare CurrentDate as Date using Dim

Dim Result As Date /Declare Result as Date

CurrentDate = Now /Assign Today’s date and time using Now to CurrentDate

Debug.Print "Current Date:" & CurrentDate /Print CurrentDate which prints today’s date

Result = DateAdd("yyyy", 2, CurrentDate) /Assign result after adding 2 years to CurrentDate

Debug.Print "Result:" & Result /prints Result i.e. added date

End Sub /End of Subroutine

Step 5: Go to Run and click on Run Sub. The result will be displayed for above code.

Step 6: Make changes in interval field in Dateadd to see different intervals for month, day, week, minute,
Second, etc.,
DATEDIF: used to find difference between two given dates.

Syntax: DATEDIF(start_date, end_date, unit)

Step 1: Create an table in sheet containing start date in C2 and end date in C3 as below. Units of difference is
mentioned in table below. Now use the function to find years completed between the dates as below:

DATEDIF(C2,C3,”y”) then press enter. It gives number of years completed between start and end date.

Like that use others to find the difference between dates in months exluding days and years, days excluding
years and months, etc.,

Experiment 8: Working with multiple sheets: work with multiple sheets within a
workbook is crucial for organizing and managing data, perform complex calculations
and create comprehensive reports.
Step 1: Create a Workbook with multiple sheets for the Sales data for each regions like East, West, South &
North and for each Quarters i.e. Q1,Q2, Q3 & Q4 as shown below:

Quarter 1 Data in Q1 Sheet Quarter 2 Data in Q2 Sheet

Quarter 3 Data in Q3 Sheet Quarter 4 Data in Q4 Sheet

Step 2: Create one more sheet for Result. Create table with rows for Regions and column for Quarters as
below. Table contains Total of Quarter1 in Q1 column, Total of Quarter 2 in Q2 column, etc.,

Step 3: To get the data from Total of sheet Q1 to the first cell under Q1 column and press = and select first
cell of Total in Q1 sheet and press enter. Data is copied from Q1 sheet to Result Sheet. Now Drag and fill
the whole column.

Step 4: To get the data from Total of sheet Q2 to first cell: Select and copy the data from Total column in
Q2 Sheet and go to Result sheet and select the first cell under Q2 and right click and select “paste link”
that pastes the data. Do the same operation to Q3 and Q4 Columns.

Step 5: In Result Sheet, Select the 1st to 4th cells under Total column and go to Home tab and select
“AutoSum” that adds the rows of East, West, South and North and keeps result under Total.

Step 6: Now Select the whole table and create reports using Charts and Graphs.

REPORT OF SALE
North

Q1 600
98 17 Q2 500
5 Q3 400
53
6 Q4 300
h
Total 200 ut
98 So
100 st
16 Ea
0
5 Q1 Q2 Q3 Q4 Total

East West South North

Experiment 9: Create worksheet with following fields: Empno, Ename, Basic

Pay(BP),Travelling Allowance(TA), Dearness Allowance(DA), House Rent Allowance(HRA),
Income Tax(IT), Provident Fund(PF), Net Pay(NP). Use appropriate formulas to calculate the
above scenario. Analyse the data using appropriate chart and report the data.

Step 1: Create a Employee table containing Columns for Empno, Ename, Basic Pay(BP),Travelling
Allowance(TA), Dearness Allowance(DA), House Rent Allowance(HRA), Income Tax(IT), Provident
Fund(PF), Net Pay(NP) as show below.

Step 2: Click on first cell under TA column, enter “=BP*5%”, will give Travelling Allowance for Employee
Raghu with Employee no E1412. Use Drag and fill of the column TA using first calculated cell.
Step 3: Click on first cell under DA column, enter “=BP*7%”, will give Dearness Allowance for Employee
Raghu with Employee no E1412. Use Drag and fill of the column DA using first calculated cell.

Step 4: Click on first cell under HRA column, enter “=BP*13%”, will give House Rent Allowance for
Employee Raghu with Employee no E1412. Use Drag and fill of the column HRA using first calculated cell.

Step 5: : Click on first cell under PF column, enter “=IF(C3>15000,0,C3*11%)”, will give Provident Fund for
Employee Raghu with Employee no E1412. Use Drag and fill of the column PF using first calculated cell.

Step 6: Click on first cell under NP column, enter “=C3+D3+E3+F3”, will give Net Pay(Net Pay = Basic Pay +
Allowances) for Employee Raghu with Employee no E1412. Use Drag and fill of the column NP using first
calculated cell.

Step 7: Click on first cell under IT column, enter =IF(J3>25000,J3*5%,0)”, will give Income Tax(Income Tax=
Net Pay*5% if Net Pay is > 25000 or else it is 0) for Employee Raghu with Employee no E1412. Use Drag and
fill of the column IT using first calculated cell.

Step 8: Select the whole table and go to insert to insert chart. Use filter to prepare different charts and report

Chart Title
40000
35000
30000
25000
20000
15000
10000
5000
0
Raghu Raja Bhagat Ajay Ramu Bharat harini kalyan nagaraj
E1412 E1413 E1414 E1415 E1416 E1417 E1418 E1419 E1420

BP TA 5% DA 7% HRA 13%
Total Allowances IT 0-5% PF 11% NP

Chart Title
12000
10000
8000
6000
4000
2000
0
E1413 Raja

BP TA 5% DA 7% HRA 13%
Total Allowances IT 0-5% PF 11% NP