Basic Excel – A Detailed Overview

Table Of Contents

1.Introduction for Excel

2.Tabs in the Ribbon of Excel

3.Data type conversion in Excel

4.Conditional Formatting

5.Format as Table

6.Mathematics in Excel

6.1 Calculations for Structured data

6.2 IF

6.3 AND and OR

7.Conclusion

1.INTRODUCTION

Microsoft Excel is a versatile and widely used application for analysis because it offers broad applicability as well as ease of use. It lets an individual organize, manipulate, and visualize large datasets in an efficient manner. Easy usage offers the facility of summarizing raw data quickly to then produce insightful reporting with features like pivot tables, data filtering, conditional formatting, and built-in functions.

2.TABS IN THE RIBBONS OF EXCEL:

• Home : Basic editing and formatting tools.

  • Clipboard (Copy, Paste, Cut)

  • Font settings (Bold, Italic, Font Color, Borders)

  • Alignment (Text alignment, Merge cells)

  • Number formatting (Currency, Percentage, Date)

  • Editing tools (Find & Replace, Sort & Filter, AutoSum)

• Insert : Add objects to your worksheet.

  • Tables (Pivot Tables, Regular Tables)

  • Charts (Bar, Line, Pie charts)

  • Illustrations (Pictures, Shapes, Icons)

  • Sparklines (Small graphs inside cells)

  • Text (Headers, Footers, WordArt)

• Page Layout : Control how the sheet looks when printed.

  • Themes and Colors

  • Margins, Orientation (Portrait/Landscape)

  • Print Area, Page Breaks

  • Background and Sheet Alignment

• Formulas : Work with Excel’s calculation capabilities.

  • Function Library (Financial, Logical, Text functions)

  • Defined Names (Name Manager, Create from Selection)

  • Formula Auditing (Trace Precedents, Errors)

  • Calculation Options (Manual/Automatic Calculation)

• Data : Manage and analyze data.

  • Get & Transform (Import from external sources)

  • Sort and Filter tools

  • Data Validation

  • Consolidate, Remove Duplicates

  • What-If Analysis (Goal Seek, Solver)

• Review : Proofing and collaboration tools.

  • Spelling and Grammar

  • Comments and Notes

  • Protect Sheet/Workbook

  • Track Changes (Collaborative work)

• View : Customize the worksheet view.

  • Workbook Views (Normal, Page Layout, Page Break Preview)

  • Freeze Panes, Zoom controls

  • Gridlines, Headings (Show/Hide)

  • Macros (Record or run macros)

3.DATA TYPE CONVERSION:

Select the cell/row/column that you want to convert the data type → Home → Select the data type to which you want to convert in the below dropdown:

4.CONDITIONAL FORMATTING:

Select the cell/row/column that you want to convert the data type → Home → Conditional Formatting → Select the data type to which you want to convert in the below dropdown:

5.FORMAT AS TABLE

The "Format as Table" option in Excel allows you to quickly convert a range of data into a structured table with consistent formatting and additional built-in features.The "Format as Table" option in Excel allows you to quickly convert a range of data into a structured table with consistent formatting and additional built-in features.

Home → Format as table → Choose a table style from the dropdown (various color themes).→"My table has headers"(in popup).

Benefits:

1.Automatic Formatting

2.Filters

3.Dynamic Range

4.Quick Analysis

6.MATHEMATICS IN EXCEL : To do calculations in excel we use “=” before to the operation

Addition => example: “=10+20” →30

Subtraction => example: “=10-5” →15

Multiplication => example: “=10*5” →50

Division => example: “=10/2” →5

Mod Function => example: “mod(27,5)” →2

Count => example: “=count(10,20,30,40)” → 4

Length => example: “=len(“datasceince”)” →11

6.1 Calculations for Structured Data:

Table:

1.Find total salary → “=sum(D2:D11)” → $12,054

2.Total no of Employees(Count) → “=count(D2:D11)” → 10

3.Average of salaries → “=Average(Total, count)”

4.Max Salary → “=max(D2:D11)” → $1899.00

5.Min Salary → “=min(D2:D11)” → $850.00

6.No of characters in names of each Employee → “=len(a1)” →5

7.Remaining Amount = Employee salary-Employee TDS (D2-E2) → Drag down from the bottom corner of that cell after you get the result so that the formula applied for all rows and we’ll get the result.

Example: D2-E2 will become D3-E3 by dragging down ………….the same apply for all rows.

6.2 IF Condition

The IF function in Excel is used for conditional logic. It allows you to return a value based on whether a condition is true or false. It's one of the most widely used functions for decision-making in Excel formulas.

Syntax: = IF(logical_test, value_if_true, value_if_false)

Example: \=IF(D2>1000, "Good Salary", "Low Salary")

\=IF(D2>1500, "Excellent Salary", IF(D2>1000, "Good Salary", "Low Salary"))

6.3 Truth tables of AND and OR

---

0 → false ; 1 → True

Using AND and OR for conditioning

=if(and(D2>1000,E2>100),”Good position”,”Bad position”)

Note : Use OR also in the same way.

7.CONCLUSION:

Mastery of the basic functions of Excel unfolds the powers of organizing, analyzing, and visualizing data with great efficiency. From formulas to conditional formatting and structured tables, these tools unlock apparently complicated, intricate themes of work, helping raw data give rise to meaningful insights.

Wishing you success in your continuous learning journey.🚀