- Information
- AI Chat
Experiments#1 Labmanual BCS358A DATA Analytics WITH Excel
Data Analytics - Trainity
Visvesvaraya Technological University
Preview text
TABLE OF CONTENTS
- PREFACE..........................................................................................................................................
- INTRODUCTION TO EXCEL.........................................................................................................
- AGGREGATE FUNCTIONS IN EXCEL.........................................................................................
- WORKING WITH DATA...............................................................................................................
- PIVOT TABLES AND PIVOT CHARTS......................................................................................
- CONDITIONAL FORMATTING-1...............................................................................................
- CONDITIONAL FORMATTING-2...............................................................................................
- WHAT-IF ANALYSIS....................................................................................................................
- CLEANING DATA.........................................................................................................................
- WORKING WITH MULTIPLE SHEETS......................................................................................
- WORKSHEET CREATION-1 (Employee Data)............................................................................
- WORKSHEET CREATION-2 (Inventory Management)...............................................................
- WORKSHEET CREATION-3 (Sales Analysis of a Store).............................................................
- GENERATION OF REPORTS AND PRESENTATION...............................................................
- VIVA QUESTIONS........................................................................................................................
- REFERENCES................................................................................................................................
- NOTES.............................................................................................................................................
PREFACE
Welcome to the "Data Analytics with Excel" Lab Manual, an invaluable resource meticulously crafted to delve into the multifaceted world of data analytics using Microsoft Excel. This manual is designed to be a comprehensive guide covering a diverse range of topics, ensuring a holistic understanding of the key aspects of data analytics. The journey begins with an introduction to Excel, the foundational tool for data analytics. Understanding the basics of cells, rows, and columns sets the stage for the subsequent exploration. The manual then delves into the power of aggregate functions in Excel, unraveling the capabilities of functions like SUM, COUNT, and AVERAGE. As we progress, you will immerse yourself in the intricacies of working with data, gaining insights into data cleaning techniques and the application of conditional formatting to enhance visualizations. The transformative potential of Pivot Tables and Pivot Charts is explored, offering a dynamic approach to data summarization and visualization. A dedicated section on What-If Analysis provides a deep dive into scenario modeling, allowing you to understand how changes in variables impact outcomes. The importance of cleaning data is underscored, addressing issues such as duplicates and missing values, ensuring that data is reliable and accurate. Working with multiple sheets is a crucial skill in Excel, and this manual provides guidance on navigation, linking, grouping, and protecting sheets. The generation of reports and effective data presentation is emphasized, showcasing Excel's capabilities in conveying insights. The latter part of the manual introduces practical worksheet creation exercises, offering hands-on experience in scenarios like Employee Data Management, Inventory Management, and Sales Analysis. To consolidate your learning, a section of Viva Questions is included to reinforce theoretical knowledge. This lab manual is designed to cater to diverse learning needs. Each topic is carefully structured to provide a seamless learning experience, ensuring that you emerge with a robust skill set in data analytics with Excel. Happy Larning!
Save the spread sheet by pressing the save button on top left corner or by shortcut Ctrl+S or by File tab > Save.
Save As the Document by File tab > Save As > Choose the location where you want to save > Enter the Name of the sheet as EXERCISE#1. Inserting Rows
Click on the row number above which you want to insert a new row. The selected row will be the one below which the new row will be inserted.
Right-click the selected row number and choose "Insert" from the context menu.
Alternatively, you can use the "Insert" option from the Excel ribbon: Select any row where you want to add an extra row > Go to the "Home" tab on the Excel ribbon > In the "Cells" group, click on the "Insert" drop-down button > Select "Insert Sheet Rows."
Shortcut: Select the row and use the "Ctrl + +"
Insert a new student data (refer the Figure. 2) Inserting Columns
Click on the column letter to the left of which you want to insert a new column. The selected column will be the one to the right of the new column
Right-click the selected column letter and choose "Insert" from the context menu.
Alternatively, you can also use the "Insert" option from the Excel ribbon: Select the column where you want to add extra column > Go to the "Home" tab on the Excel ribbon > In the "Cells" group, click on the "Insert" drop-down button > Select "Insert Sheet Columns."
Shortcut: Select the column and use "Ctrl + +"
Insert a new category of data "AGE" (refer the Figure. 3) Figuere: Insertion of row Figure: Insertion of column Drag & Fill
Add one new columns "S by Insert column option
In column "S", enter the first value in the cell as “1”. Move the mouse pointer to the small square in the bottom-right corner of the selected cell (the fill handle). The cursor will change to a small black cross when you hover over it. (refer Figurte)
Click and hold the left mouse button and drag your cursor to the desired number of cells to fill. Excel will automatically continue the series or pattern based on the initial value entered. (refer Figurte)
Excel's drag-and-fill feature also provides additional options when filling cells, they are: Copy Cells: To copy the data without altering the series. Fill Series: To continue the series based on the initial value. Fill Formatting Only: To copy the formatting without changing the content Figuere: Fill & drag option Figure: Fill & drag option
Add a new column "NATIONALITY" by Insert column option
In column "NATIONALITY ", enter the first value in the cell as “INDIA”. Move the mouse pointer to the small square in the bottom-right corner of the selected cell (the fill handle) and dragthe mouse pointer till the end of column. (refer Figurte & 7)
Shortcut: Select the column and use "Ctrl +double click(left)" Figuere: Fill & drag option Figure: Fill & drag option
EXPERIMENT NO – 2 DATE
AGGREGATE FUNCTIONS IN EXCEL
OBJECTIVES
To use Microsoft EXCEL aggregate functions: COUNT, COUNTA, MAX, MIN, LARGE, and SMALL SUM, AVERAGE COUNTIF, SUMIF, AVERAGEIF EQUIPMENT
- Computer or Laptop
- Microsoft EXCEL
PROCEDURE:
Excel offers several aggregate functions that allow you to perform calculations on sets of data,
summarizing and analyzing it effectively. These functions are useful for tasks such as finding sums,
averages, maximum or minimum values, and more
COUNT: The COUNT function in Excel is used to count the number of cells in a given range that
contain numbers or values. It's a simple yet essential function for data analysis and reporting.
Syntax: =COUNT(range)
*range: The range of cells you want to count. This can be a single cell, a range of cells, or even an entire
column or row.
Note: The COUNT function counts only numeric values. If a cell contains text or is empty, it won't be
included in the count.
Q. Count the total number of students in the dataset given using column ‘D’
- Select an empty cell where you want the result to appear.
- Enter the COUNT function and specify the range you want to count. In this case, it's the range D2:D12.
D D MM Y Y Y Y M Y
COUNTA: The COUNTA function in Excel is used to count the number of non-empty cells within a given range. It counts cells that contain any type of data, including text, numbers, logical values (TRUE or FALSE), errors, and even empty cells. Syntax: =COUNTA(range) *range: The range of cells you want to count. This can be a single cell, a range of cells, or even an entire column or row. Q. Count the total number of students in the dataset given using column ‘B’ 1. Select an empty cell where you want the result to appear. 2. Enter the COUNTA function and specify the range you want to count. In this case, it's the range B2:B12.
Note: 1. The SUM function can handle both positive and negative numbers. 2. It can be used with a wide range of references, including single cells, entire rows, entire columns, and more. For example, you can use =SUM(D1, D2, D3) or =SUM(D:D) to sum all values in column D. 3. You can also use the SUM function to perform more complex calculations, such as adding the results of other formulas. For instance, =SUM(D2:D12) + 100 will give you the sum of the values in D2 to D12, plus 100. AVERAGE: The AVERAGE function is used to calculate the arithmetic mean or average of a set of numbers. Syntax: =AVERAGE(range)
- range: This is the range of cells you want to find the average for. It can include a single cell, a range of
cells, or multiple cell references separated by commas.
Q. Find the average height of students
- Select an empty cell where you want the result to appear.
- Enter the AVERAGE function and specify the range you want to average. In this case, it's the range E2:E Note:
- The AVERAGE function can handle both positive and negative numbers.
- You can use the AVERAGE function with various types of references, including single cells, entire rows, entire columns, and more. For example, you can use =AVERAGE(E1, E2, E3) or =AVERAGE(E:E) to find the average of all values in column E.
- You can also use the AVERAGE function with more complex calculations, such as finding the average of values derived from other formulas. MAX: The MAX function in Excel is used to find the highest or maximum value in a range of numbers. Syntax: =MAX(range) *range: This is the range of cells you want to find the maximum value for. It can include a single cell, a range of cells, or multiple cell references separated by commas. Q. Find the maximum weight of students
- Select an empty cell where you want the result to appear.
- Enter the MAX function and specify the range you want to find the maximum value within. In this case, it's the range F2:F12. Note:
- The MAX function can handle both positive and negative numbers.
- You can use the MAX function with various types of references, including single cells, entire rows, entire columns, and more. For example, you can use =MAX(F1, F2, F3) or =MAX(F:F) to find the maximum value of all numbers in column F. MIN: The MIN function in Excel is used to find the lowest or minimum value in a range of numbers. It's a straightforward function that helps you quickly identify the smallest value within your data. Syntax: =MIN(range)
- range: This is the range of cells you want to find the minimum value for. It can include a single cell, a range of cells, or multiple cell references separated by commas
Note: 1. The LARGE function is often used in situations where you need to find values beyond the maximum (or minimum). For example, you might want to find the third-highest sales figure or the fourth-highest salary in a dataset. 2. You can use the LARGE function with various types of references, including single cells, entire rows, entire columns, and more. For example, you can use =LARGE(F1, F2, F3, 3) or =LARGE(F:F, 3) to find the fourth-largest value in column F. SMALL: The SMALL function in Excel is used to find the "kth" smallest value in a range of numbers. It allows you to locate values in a dataset based on their position from the smallest to the largest. Syntax: =SMALL(range, k) *range: This is the range of cells or numbers from which you want to find the "kth" smallest value. It can include a single cell, a range of cells, or multiple cell references separated by commas. **k: This is a number that specifies the position of the value you want to find, starting from the smallest. For example, if k is 1, you'll find the smallest value; if k is 3, you'll find the third-smallest value, and so on. Q. Find the third smallest weight of students 1. Select an empty cell where you want the result to appear. 2. Enter the SMALL function and specify the range (F2:F12) and the value of k, which is 3 in this case.
Note: 1. The SMALL function is often used in situations where you need to find values beyond the minimum (or maximum). For example, you might want to find the third-lowest price or the fifth- lowest sales figure in a dataset. 2. You can use the SMALL function with various types of references, including single cells, entire rows, entire columns, and more. For example, you can use =SMALL(F1, F2, F3, 3) or =SMALL(F:F, 3) to find the fifth-smallest value in column F. COUNTIF: It is used to count the number of cells in a range that meet specific criteria or conditions. Syntax: =COUNTIF(range, criteria) *range: This is the range of cells that you want to count values from. It can include a single cell, a range of cells, or multiple cell references separated by commas. **criteria: This is the condition or criteria that you want to apply to the range for counting. It can be a specific value, text, expression, or even a cell reference. Q. Find the number of students whose height is greater than 167 1. Select an empty cell where you want the result to appear. 2. Enter the COUNTIF function and specify the range (E2:E12) and the criteria, which is ">67" in this case. You need to enclose the criteria in double quotes.
Note: The SUMIF function is flexible and can be used to sum numbers based on various criteria, including specific values, text, expressions, or even cell references. AVERAGEIF: is used to calculate the average of values in a range that meet specific criteria or conditions. It allows you to find the average of a set of numbers only if they satisfy the condition you specify. Syntax: =AVERAGEIF(range, criteria, [average_range]) *range: This is the range of cells that you want to evaluate against the criteria. It can include a single cell, a range of cells, or multiple cell references separated by commas. **criteria: This is the condition that you want to apply to the range. It can be a specific value, text, expression, or even a cell reference. ***[average_range] (optional): This is the range of cells from which you want to calculate the average. If omitted, Excel will calculate the average of the values in the range that meet the criteria specified in the first argument. Q. Find the average weight of student whose age is 19 1. Select an empty cell where you want the result to appear. 2. Enter the AVERAGEIF function and specify the range (C2:C12), the criteria, which is "=19" in this case, and the average_range (F2:F12) from which you want to calculate the average
Note: The AVERAGEIF function is versatile and can be used with various criteria, including specific values, text, expressions, or even cell references. SUMIFS, AVERAGEIFS, COUNTIFS: These functions extend the capabilities of SUMIF, AVERAGEIF, and COUNTIF by allowing you to apply multiple criteria. For example, you can sum values based on two or more conditions. SUMMARY: Upon successfully finishing this exercise, you will be proficient in effectively using various Excel aggregate functions.
- Open Destination Excel workbook > ‘Data’ tab (from excel ribbon) > Get Data > From File > From Text/CSV file > Select the respective .csv file to import > Load.
- The data can be imported by other file formats using the similar procedure as like .csv file. 3 Data Entry: In Excel, data can be entered by manually by typing, copy and pasting from source file to destination file, auto filling and importing from different sources.
3 Data Manipulation: Manipulation of data is the process of manipulating or changing information to make it more organized and readable. Data manipulation refers to the process of modifying, transforming, or processing raw data to derive useful insights, generate reports, or prepare it for further analysis. Data manipulation often involves tasks such as cleaning, organizing, and summarizing data to make it more meaningful and actionable The data manipulation includes addition, deletion, merging, sorting, filtering, aggregations, cleaning, transforming, mapping etc. 3 Data Sorting: Data sorting in Excel allows you to arrange the rows of a worksheet in a specific order based on the values in one or more columns. Sorting is a useful feature for organizing data in ascending or descending order, making it easier to analyze and interpret Data sorting a single column 1. Click on a cell in the column you want to sort (this selects the entire column) or select group of cells which needs to sort in either ascending or descending order. 2. To sort the column in ascending order, click the "Sort A to Z" or "Smallest to Largest" button in the "Data" tab on the Excel ribbon. 3. To sort the column in descending order, click the "Sort Z to A" or "Largest to Smallest" button on the Excel ribbon. Q. Sort (ascending) the PUC or Diploma Marks 1. Click on a cell under the column which you want to sort (this will select the entire dataset) 2. From Excel ribbon > ‘Data’ tab > from ‘Sort & Filter’ group > select ‘Sort A to Z’ icon
Experiments#1 Labmanual BCS358A DATA Analytics WITH Excel
Course: Data Analytics - Trainity
University: Visvesvaraya Technological University
This is a preview
Access to all documents
Get Unlimited Downloads
Improve your grades
