Excel for Data Analytics (Day Wise Agenda)

Before Learning Excel Let’s Learn Some Basic Things About Excel Workbooks.

Parts Of Ms Excel Window :-

Excel workbook is a collection of worksheets that are stored in a file.

Here are some of the things that you may see in an Excel workbook:

Basic Parts of Microsoft Excel Window

Some parts of the Excel window (such as Ribbon and scroll bars) are standard in most other Microsoft programs. However, other features are more specific to spreadsheets, like the formula bar, worksheet tabs, and name box.

The following are the basic parts of the Microsoft Excel Window:

• Quick Access Toolbar
• File Tab
• Title Bar
• Control Buttons
• Menu Bar
• Ribbon/Toolbar
• Dialog Box Launcher
• Name Box
• Formula Bar
• Scroll Bars
• Spreadsheet Area
• Leaf Bar
• Column Bar
• Row Bar Cells
• Cells
• Status Bar
• View Buttons
• Zoom control

Worksheets:

Worksheets are the primary building blocks of an Excel workbook. Each worksheet consists of a grid of cells arranged in rows and columns. You can add, delete, rename, move, and copy worksheets within a workbook.

Workbooks:

A workbook is a file that contains one or more worksheets in Excel. Workbooks are the primary organizational structure in Excel, and they allow users to work with and manage multiple sets of data or information within a single file.

Cells:

A cell is the intersection of a row and a column on a worksheet. It is the basic unit of data storage in Excel. You can enter data, formulas, and functions into cells, and format cells to control the appearance of data.

Columns and Rows:

A column is a vertical group of cells identified by a letter (A, B, C, etc.), while a row is a horizontal group of cells identified by a number (1, 2, 3, etc.). You can insert, delete, and resize columns and rows as needed.

Formulas and Functions:

Formulas are equations that perform calculations on data in cells. Functions are built-in formulas that perform specific tasks, such as SUM, AVERAGE, COUNT, IF, and VLOOKUP. You can create complex formulas and functions to analyze and manipulate data.

Charts and Graphs:

Charts and graphs are visual representations of data. You can create a variety of charts and graphs, such as bar charts, pie charts, line charts, and scatter charts, to help you visualize and communicate data.

Conditional Formatting:

Conditional Formatting allows you to apply formatting to cells based on certain conditions. For example, you can use conditional formatting to highlight cells that contain a certain value, or to apply color scales to cells based on the values they contain.

Pivot Tables:

A Pivot Table is a powerful data analysis tool that allows you to summarize and analyze large amounts of data. You can create Pivot Tables to quickly analyze and summarize data by category, such as by date, by region, or by product.

Macros:

Macros are sets of instructions that automate repetitive tasks in Excel. You can create, record, edit, and run macros to save time and increase productivity.

These are some basic information what Excel have again it’s not the whole information. With going forward you will get to know more about Excel.

Here’s a brief explanation of the different bars in Excel:

PARTS OF EXCEL WINDOW

Title Bar:

The Title Bar is located at the very top of the Excel window and displays the name of the current workbook.

Ribbon:

The Ribbon is located just below the Title Bar and contains all of the commands and tools you need to perform various tasks in Excel. The Ribbon is divided into tabs, such as Home, Insert, and Page Layout, each containing a set of related commands.

Quick Access Toolbar:

The Quick Access Toolbar is located above the Ribbon and provides quick access to frequently used commands. You can customize the Quick Access Toolbar by adding or removing commands.

Formula Bar:

The Formula Bar is located just below the Ribbon and displays the contents of the selected cell, including formulas, text, and numbers.

Worksheet Area:

The Worksheet Area is the main area of the Excel window where you can enter and edit data in cells, create tables and charts, and perform various tasks.

Status Bar:

The Status Bar is located at the bottom of the Excel window and displays information about the current status of Excel, such as the sum or average of selected cells, the page number of the current worksheet, and the mode of Excel (such as Ready, Edit, or Calculate).

Scroll Bars:

The Scroll Bars are located on the right side and bottom of the Worksheet Area and allow you to navigate through the worksheet when the data is too large to fit on the screen.

Control Buttons:

Control buttons are the symbols that are present in the upper-right side of the window, enabling us to change the labels, minimize, maximize, share, and close the sheet.

Menu Bar:

Under the diskette or save icon or the excel icon (this will depend on the version of the program), labels or bars which enable changing the sheet which is shown. These are the menu bar and contain a File, Insert, Page Layout, Formulas, Data, Review, View, Help, and a Search Bar with a light bulb icon. These menus are divided into subcategories which simplify the distribution of information and analysis of calculations.

These are the different bars in Excel, each serving a specific purpose to help you work efficiently and effectively with your data.

Here’s a brief explanation of some additional items in Excel:

Row Headings:

The Row Headings are the numbers on the left side of the Worksheet Area that identify each row in the worksheet. The first row is typically used for column headings.

Column Headings:

The Column Headings are the letters at the top of the Worksheet Area that identify each column in the worksheet. The first column is typically used for row headings.

Name Box:

The Name Box is located to the left of the Formula Bar and displays the name or address of the selected cell, range, or object. You can also use the Name Box to name cells, ranges, and objects.

Scroll Bars:

The Scroll Bars are located on the right and bottom of the Worksheet Area and allow you to scroll through the worksheet when the data extends beyond the visible area.

Leaf Bar:

Leaf bar is present at the bottom of the spreadsheet, which says sheet1 is shown. This sheet bar describes the spreadsheet which is currently being worked on. Using this, we can alternate a number of sheets or add a new one as per our convenience.

Navigation Buttons:

The Navigation Buttons are located on the bottom-right corner of the Worksheet Area and allow you to quickly move between worksheets in a workbook.

View Buttons:

View buttons are a set of three buttons arranged at the left of the Zoom control, close the screen’s right-bottom corner. We can see three different kinds of sheet views in Excel using this method.

Normal View: — Normal view displays the Excel page in normal view. Page Layout View: — The Page Layout view shows the precise layout of an Excel page it will be printed. Page Break View: — This displays page break preview before printing.

Sheet Tabs:

Sheet Tabs are located at the bottom of the Excel window and display the names of each worksheet in the workbook. You can click on a Sheet Tab to switch between worksheets.

Freeze Panes:

The Freeze Panes feature allows you to freeze rows or columns in the Worksheet Area so that they remain visible as you scroll through the data.

Sort and Filter:

The Sort and Filter features allow you to sort and filter data in a worksheet based on specific criteria.

Data Validation:

The Data Validation feature allows you to create rules that restrict the type of data that can be entered in a cell or range.

Conditional Formatting:

Conditional Formatting allows you to apply formatting to cells based on specific conditions or criteria.

These are some additional items in Excel that can help you work efficiently and effectively with your data.

The number of rows and columns in an Excel spreadsheet depends on the version of Excel you are using, but here is some general information:

• In Excel 2007, 2010, 2013, and 2016, there are 1,048,576 rows and 16,384 columns.
• In Excel 2019 and Microsoft 365, there are 1,048,576 rows and 32,767 columns.
• In older versions of Excel, such as Excel 97–2003, the number of rows is limited to 65,536 and the number of columns is limited to 256.

Different Data Types in Excel:

Excel recognizes 4 different data types: Text, Number, Boolean & Formula. Anything you type into Excel will fall under one of these.

• Text: Whenever you type alphabets, or a mix of alphabets and numbers into Excel (without proceeding with =), everything is recognized as text. By default, Excel aligns text to the left of the cell.
• Number: If all you type into a cell are digits, they are recognized as Number by Excel. By default, Excel aligns number to the right.
• Boolean: FALSE and TRUE are Boolean entries. You’ll hardly use them. They are used for setting up complex formulas. By default, whenever you type false or true in a cell, Excel will put it in upper case and align it to the center.
• Date/Time: This data type is used to store date and time values. Excel stores date values as serial numbers, with January 1, 1900 as the starting date (serial number 1). Time values are stored as decimal fractions of a day.
• Currency: This data type is used to store monetary values with a specified currency symbol.
• Percentage: This data type is used to store values as percentages.
• Boolean: This data type is used to store logical values, such as “TRUE” or “FALSE”. These values are used in conditional statements and calculations.
• Error: This data type is used to indicate an error in a formula or calculation.
• Array: This data type is used to store multiple values in a single cell. Array formulas are used to perform calculations on multiple cells at once.
• Formula: Once you begin a cell entry with =, Excel treats everything you type after as a formula.

Data Types In Excel

The practical importance of this is that if you receive a sales report in Excel and the numbers are aligned to left instead of right you should be concerned. There are three reasons this can happen and two of those reasons will cause some of your mathematical formulas to not work correctly. You wouldn’t want to do an incorrect analysis, so it’s best to check why the numbers are aligned to the left and not to the right as expected.

1. It could be that the author forced the numbers to align to the left. To find out if that’s the reason, check the alignment under Home menu.
2. It could be that the author forced the number to be treated as text by setting the cell format to text.
3. It could be that the author preceded the number with a single apostrophe (’) before typing the number. This is a trick savvy users use to force Excel to keep the zeros at the beginning of your phone number or bank account number. Unfortunately, it forces Excel to treat the cell entry as a text and align it to the left.

Out of these three ways of making a number show as aligned to the left, only the first one leaves the number intact. The other ways transform the number to text and will give you issues when you include them in calculations that normally would work on numbers.

Now you have an idea of how useful an understanding of the default ways Excel treat the different data types can be in your day to day use of Excel.

NAVIGATING IN A FILE

| Shortcut | Action |
| - - - - | - - - |
| Arrow Keys | Move one cell to the right, left, up or down |
| Tab | Move once cell to the right |
| Ctrl+Home | To beginning file |
| Ctrl+End | To end of typed information |
| Home | Beginning of a line |
| End | End of a line |
| Page Down | Down one screen |
| Page Up | Up one screen |
| F5 | To a specific page |
| Scroll bars | Appear at the right and on the bottom of the screen. You may click the scroll arrows, drag the scroll box or click the scroll bar to move through the documenTyping Text or Number into a Worksheet:

Typing Text or Number into a Worksheet:

Generally when you start a new spreadsheet project, the first task is to enter some headings into rows and columns. To type anything into a worksheet you need to make the cell into which you wish to enter the data active. This can be done in a number of ways but the most common is to click in it first before typing.

Typing Simple Formulas in a Worksheet:

The whole idea behind Excel is to get it to perform calculations. In order for it to do this you need to type formulas in the worksheet. Usually these formulas reference existing numbers, or even other formulas, already in the worksheet using the cell addresses of these numbers rather than the actual value in them. Formulas must be typed beginning with an equal sign (=).

To enter a formula:

1. Click the cell pointer on the desired cell and type the formula commencing with =
2. Press Enter, an arrow key or Tab to confirm the data entry and to move the cell pointer to another cell

To save a new document:

1. Click on the File Tab and select Save As
2. Locate the storage folder in the Navigation pane
3. Type a File name and click on [Save]

Filling a Series:

A series refers to a sequence of ordered entries in adjacent cells, such as the days of the week or months of the year. The fill technique can be used to create these in a worksheet for you, reducing the amount of time taken for data entry, and ensuring that the spelling is correct. Excel provides days and months as special built-in series that you can access.

Filling Series

Inserting and Deleting Worksheets:

Once you’ve decided on a structure for your workbook, you may find that there are some worksheets that can be deleted. Alternatively, you may find that you need additional blank worksheets inserted. However, remember that deletion of worksheets is permanent and can’t be undone using Undo, so always save your workbook before making these changes.

To insert a new worksheet into a workbook:

Click on the New Sheet icon to the right of the worksheet tabs To delete a worksheet from a workbook: Right click on the worksheet tab, then select Delete

Inserting Worksheet

Deleting Worksheet

Copying a Worksheet:

Just as you can copy the contents of cells and ranges within a worksheet, you can duplicate worksheets within a workbook. This technique is ideal for replicating layouts. For example, if you have a budget workbook that contains data for several departments, you can create a worksheet for the first department and then copy it to create identical worksheets for other departments.

To copy a worksheet:

1. Right-click on the worksheet to copy, then select Move or Copy
2. Click on Create a copy so it appears ticked
3. Click on [OK]

Renaming a Worksheet:

By default, Excel names worksheets as Sheet1, Sheet2, Sheet3, etc. These names are fine if you are not planning to share the workbook, but changing these to something more relevant makes it much easier to understand the purpose of a worksheet. You can also adjust the horizontal scroll bar to make room for longer, more meaningful worksheet names.

To rename a worksheet:

1. Double click on the current name on the worksheet tab
2. Type the new name and press Enter

Moving or Copying a Sheet to another Workbook:

You can copy worksheets to other workbooks as required. For example, you might need to keep records for six different divisions — rather than send each division the entire set of records, you can copy their worksheet to another workbook and send them their data only. If worksheets exist in the other workbook, you will need to determine the order in which to place the copied worksheet.

To copy a sheet to another workbook:

1. Right click on the worksheet tab, then click on Move or Copy
2. Select either (new book) or the name of another workbook in To book
3. Tick Create a copy, then click on [OK]

Typing Text or Numbers into a Worksheet:

Generally when you start a new spreadsheet project, the first task is to enter some headings into rows and columns. To type anything into a worksheet you need to make the cell into which you wish to enter the data active. This can be done in a number of ways but the most common is to click in it first before typing.

To enter text:

1. Click the cell pointer on the desired cell and type the required information
2. Press , an arrow key or to confirm the data entry and to move the cell pointer to another cell

To save a new document:

1. Click on the File Tab and select Save As
2. Locate the storage folder in the Navigationpane
3. Type a File name and click on [Save]

Typing Simple Formulas in a Worksheet:

The whole idea behind Excel is to get it to perform calculations. In order for it to do this you need to type formulas in the worksheet. Usually these formulas reference existing numbers, or even other formulas, already in the worksheet using the cell addresses of these numbers rather than the actual value in them. Formulas must be typed beginning with an equal sign (=).

To enter a formula:

1. Click the cell pointer on the desired cell and type the formula commencing with =
2. Press , an arrow key or to confirm the data entry and to move the cell pointer to another cell

Operators

• Addition
• Subtraction
• Multiplication / Division

Changing Worksheet Tab Colours:

To make it easier for you to distinguish between worksheets, Excel enables you to change the colours of worksheet tabs. This allows you, for example, to quickly distinguish between different financial years, departments or months. The active sheet appears as underlined in a gradient version of the selected colour, while inactive tabs will display a solid colour background.

To change the colour of a worksheet tab:

1. Right-click on the worksheet tab to display the shortcut menu
2. Point to Tab colour to display a palette of colour options
3. Click on the desired colour Handy to Know…

Grouping Worksheets:

Worksheet grouping enables you to make the same change at once to all selected worksheets. This feature is useful in situations where your worksheets have identical layouts or text. For example, if you want to format the heading for multiple worksheets, you simply group the worksheets, make a change to one worksheet and the other worksheets will reflect the change also.

To group worksheet tabs:

1. Click on the first worksheet tab
2. Hold down , then click on the last worksheet tab

To freeze panes in a worksheet:

1. Click in the cell below and to the right of the area you want to freeze/unfreeze
2. Click on the VIEW tab
3. Click on Freeze Panes in the Window group, then select Freeze Panes

Selecting Ranges:

A contiguous range is any group of selected cells that form either a square or a rectangle. A single cell that is selected is also considered to be a range. Ranges can be selected using the mouse, the keyboard or a combination of the two. Once selected, you can use the range for input, or apply formatting, or copy the cells as required.

To select ranges with the mouse:

1. Click in the left-most cell of the range
2. Hold down the key and click in the last cell, Or
3. Drag the mouse pointer to the bottom right corner of the range

SELECTING ROWS

If you want to make changes to an entire row, such as bolding all of the headings in a row or changing the font of all the cell entries, you must first select the row. This is done by clicking on the row header to the left of the row. Remember that any changes you make will apply to every cell in the row all the way across to column XFD, so becareful!

To select an entire row:

1. Click on the row header of the row that you want to select OR
2. Click in any cell in the row and press +

Selecting Columns:

If you want to make changes to an entire column, such as bolding all of the headings in a column or changing the font of all the cell entries, you must first select the column. This is done by clicking on the column header directly above the column. Remember that any changes you make will apply to every cell in the column all the way down to row 1,048,576!

To select an entire column:

1. Click on the column heading of the column that you want to select OR
2. Click in any cell in the column and press +

Understanding Formatting:

In Excel there are always two aspects to a number: how the number presents on the screen (known as formatting) and the underlying value of the number. Take 2% as an example — on the screen it is formatted to appear as a number with a percentage sign, whereas the real value in the cell is .02.

Number Formatting:

The Veil Placed Over Numbers All calculations in Excel are performed using numbers — this is only logical. So, when you want to perform a calculation, you type the numbers in various cells, then create formulas to reference those numbers.

Excel allows you to show these representations using number formatting. With number formatting you change the way a number looks so that it makes immediate sense to the reader of your worksheet. The underlying value of number, however, remains unchanged. For example, instead of showing sales tax in a worksheet as .1 you show it as 10%, to show 12889.95 as currency it would appears $12,889.95 or €12,889.95 (depending upon the currency you are working with), and to show 44104 as a date you show it as 30-Sep-2020 (remember, dates are actually numbers representing the number of days from January 1, 1900).

Applying General Formatting:

The Number Format command in the Number group on the HOME tab contains a drop arrow that provides a gallery of the more commonly used number formats. You can apply these formats easily and quickly to a selected cell or range of cells in the worksheet.

To apply general formatting to numbers:

1. Select the range to format
2. Click on the HOME tab, then click on the drop arrow for Number Format in the Number group
3. Click on the desired number format

Changing Fonts:

The appearance that you choose for your text is referred to as the font or typeface. Font traditionally refers to a combination of typeface, style and size in points (e.g. Arial Bold 12 pt). In Excel 2007, font just refers to the typeface or shape of the letters. Typical classic fonts include Times New Roman, Arial, Century Gothic and Copperplate.

To apply font formatting:

1. Select the text
2. Click on the drop arrow for Font
3. Point to a font to preview it
4. Click on the font to apply it

To change font size:

1. Select the cell or range that you want to change
2. Click on the drop arrow of Font Size
3. Click on the required font size

Understanding Borders:

Borders are lines that are placed around the edges of individual cells or ranges. The lines may be thin, thick, solid, dashed, black or coloured, or even double lines. The reason for using borders is that the lines can be used to group together data or indicate totals, or to draw the user’s attention to critical cells that may need special data entry. Here are some examples.

A Worksheet without and with Borders:

Borders can be used to apply a structure. Here’s the same worksheet shown without borders and then with borders applied. The use of borders helps to highlight the totals and separate them from the other data.

To apply a border to a range:

1. Select the range
2. Click on the drop arrow for Borders in the Font group on the Home tab
3. Click on the border option of your choice

Wraping and Merging Text:

Microsoft Excel will allow long cell entries to spill across to other adjacent cells to the right as long as those cells are empty. If those cells contain data the spill-over will be chopped off. If you need to place long text entries in a cell you can arrange for Microsoft Excel to wrap the text within the cell and also merge that cell with others to accommodate the longer text entry.

To wrap text:

click in the cell to merge and click on the Wrap Text command in the Alignment group on the Home tab

To merge text:

click on the drop arrow for Merge & Centre in the Alignment group and select Merge Cells

By : Chitta Ranjan Sahoo