Project 1 - Data Cleaning and Transformation using Pivot table and Charts for the Report on requirement of Team Hiring
Q.a format data using borders, font size, and appropriate alignment:
-
Borders: Use borders to separate data and make it easier to read. You can apply borders to individual cells or groups of cells. Choose a border style that is easy on the eyes, such as a thin solid line.
-
Font size: Use an appropriate font size for the data you're working with. If you're working with a large table, a smaller font size may be necessary to fit everything on the page. If you're working with a single cell or a small amount of data, a larger font size may be easier to read.
-
Alignment: Use appropriate alignment for the data in each cell. For example, numerical data should be right-aligned, while text data should be left-aligned. You can also center-align text if it looks better visually.
Overall, the key is to make sure your data is well-organized, easy to read, and visually appealing.
-
Total Sum of Sales: The total sum of all product sales is the sum of the individual sales amounts. Simply add up all the sales numbers to get the total sum.
-
Average of Sales: The average, or mean, of product sales is found by adding up all the sales numbers and then dividing by the total number of sales. This will give you an idea of the typical sales amount.
-
Standard Deviation of Sales: The standard deviation measures the amount of variation in product sales. A higher standard deviation means that the sales numbers are more spread out from the average, while a lower standard deviation means that they are more tightly clustered around the mean.
-
Variance of Sales: The variance is another measure of the amount of variation in product sales. It is calculated by squaring the standard deviation. A higher variance means more variation in sales.
-
Maximum Sales: The maximum sales amount is the highest amount of any individual product sale.
-
Minimum Sales: The minimum sales amount is the lowest amount of any individual product sale.
These summary statistics can help provide a better understanding of the sales performance of products and can be used to make informed decisions about sales strategies, pricing, and inventory management.
-
First, create a table that includes all the relevant data, including product line, status, and sales. Make sure the table has a header row with clear, descriptive labels for each column.
-
Select the entire table, including the header row.
-
Go to the "Insert" tab on the ribbon and click on "PivotTable" in the "Tables" section.
-
In the "Create PivotTable" dialog box, make sure that the range is correct and choose where you want to place the PivotTable (either a new worksheet or an existing one).
-
Click "OK". This will create a blank PivotTable.
-
On the right-hand side of the screen, you'll see a "PivotTable Fields" panel. Drag the "Product Line" field to the "Rows" area and the "Status" field to the "Columns" area.
-
Drag the "Sales" field to the "Values" area. By default, the PivotTable will show the total sales for each combination of product line and status.
-
To make the PivotTable more useful, you can apply filters, sorts, and other formatting options. For example, you could filter by a specific product line to see its sales by status or sort by sales amount to see which product lines and statuses have the highest sales.
With the pivot table, you can easily analyze and summarize sales data by product line and status, which can help identify trends, opportunities, and potential issues.
new tab and apply formatting commands in Excel:
-
Right-click on any existing tab in the Excel workbook and select "Insert" from the context menu.
-
In the "Insert" dialog box, choose the type of sheet you want to create (e.g., Worksheet, Chart, etc.) and click "OK".
-
A new tab will be created with a default name (e.g., Sheet1). Double-click on the tab to rename it to "ABC".
-
Select the cells or range of cells that you want to format.
-
Choose the desired formatting command from the ribbon or the formatting options in the Home tab.
-
Some examples of formatting commands include changing font size and style, applying borders or shading, and aligning cell contents.
-
To apply a customized formatting command, such as a specific font or color, click on the "More Colors" or "More Fonts" buttons to access additional options.
-
Once you have applied the desired formatting commands, you can save the changes and continue working on the ABC tab.
By creating a customized tab and applying formatting commands, you can organize and present your data in a clear and visually appealing way, making it easier to read and analyze.
analyze quantities ordered by each country in different quarters for the years 2019, 2020, and 2021 using a pivot table in Excel:
-
Create a table that includes all the relevant data, including the country, year, quarter, and quantity ordered. Make sure the table has a header row with clear, descriptive labels for each column.
-
Select the entire table, including the header row.
-
Go to the "Insert" tab on the ribbon and click on "PivotTable" in the "Tables" section.
-
In the "Create PivotTable" dialog box, make sure that the range is correct and choose where you want to place the PivotTable (either a new worksheet or an existing one).
-
Click "OK". This will create a blank PivotTable.
-
On the right-hand side of the screen, you'll see a "PivotTable Fields" panel. Drag the "Country" field to the "Rows" area, the "Year" field to the "Columns" area, and the "Quarter" field to the "Columns" area.
-
Drag the "Quantity Ordered" field to the "Values" area. By default, the PivotTable will show the total quantities ordered for each combination of country, year, and quarter.
-
To filter by year, click on the drop-down arrow next to "Row Labels" in the "PivotTable Fields" panel and uncheck any years that you don't want to include in the analysis.
-
To apply conditional formatting, such as color scales or data bars, select the cells you want to format and choose the desired formatting option from the ribbon or the formatting options in the Home tab.
-
To sort the PivotTable, click on any cell in the table and choose the desired sort option from the ribbon or the "Sort & Filter" options in the Home tab.
With the pivot table, you can easily analyze and summarize quantities ordered by country, year, and quarter, which can help identify trends, opportunities, and potential issues.
conditional formatting to highlight data in different colors based on the values in the "Status" and "ProductLine" fields:
-
Select the cells or range of cells that you want to apply the conditional formatting to.
-
Go to the "Home" tab on the ribbon and click on "Conditional Formatting" in the "Styles" section.
-
Choose "Highlight Cells Rules" and then "Equal To" from the drop-down menu.
-
In the "Equal To" dialog box, enter the value that you want to apply the formatting to. For example, if you want to highlight all cells where the status is "Pending", enter "Pending" in the value field.
-
Click on the "Format" button to choose the formatting style you want to apply. This will open the "Format Cells" dialog box.
-
In the "Format Cells" dialog box, choose the desired formatting options, such as font color, fill color, and border style.
-
Click "OK" to close the "Format Cells" dialog box and return to the "Equal To" dialog box.
-
Repeat the steps above to create additional formatting rules for other values in the "Status" and "ProductLine" fields. You can choose different colors and formatting styles for each rule to create a more visually appealing and informative display.
-
Click "OK" to close the "Conditional Formatting Rules Manager" dialog box and apply the formatting to the selected cells.
By using conditional formatting, you can quickly highlight data in different colors based on the values in the "Status" and "ProductLine" fields, making it easier to identify patterns, trends, and outliers in your data.
change the values of the years 2003, 2004, and 2005 to 2019, 2020, and 2021, respectively, here's how you can do it using proper date formatting options in Excel:
-
Select the cells that contain the years you want to change (e.g., 2003, 2004, 2005).
-
Right-click on the selected cells and choose "Format Cells" from the drop-down menu.
-
In the "Format Cells" dialog box, click on the "Number" tab.
-
Choose "Custom" from the Category list.
-
In the "Type" field, enter "yyyy" (without quotes) to display only the year value.
-
Click "OK" to close the dialog box and apply the formatting to the selected cells.
-
In a blank cell, enter the value of the first year you want to change (e.g., 2003) and format it using the same custom date format as above (i.e., "yyyy").
-
Select the formatted cell and copy it.
-
Select the cells that contain the years you want to change (e.g., 2003, 2004, 2005) and right-click.
-
Choose "Paste Special" from the drop-down menu.
-
In the "Paste Special" dialog box, choose "Values" in the "Paste" section and "Add" in the "Operation" section.
-
Click "OK" to close the dialog box and add the values of the formatted cell to the selected cells.
-
The years should now be changed to the new values (e.g., 2003 to 2019, 2004 to 2020, and 2005 to 2021) and formatted as a year using the custom date format "yyyy".
calculate the total sales of all products with the status of "Shipped", "Cancelled", "Disputed", "In Process", "On Hold", and "Resolved" using the SUMIF function:
-
Assume that the "Status" column is column A and the "Sales" column is column B.
-
In a new cell, enter the following formula:
=SUMIF(A:A,"Shipped",B:B)+SUMIF(A:A,"Cancelled",B:B)+SUMIF(A:A,"Disputed",B:B)+SUMIF(A:A,"In Process",B:B)+SUMIF(A:A,"On Hold",B:B)+SUMIF(A:A,"Resolved",B:B)
This formula uses the SUMIF function to add up the sales for all products with the specified statuses.
- Press Enter to calculate the total sales.
Alternatively, you can also use a PivotTable to calculate the total sales of products with different statuses:
-
Select the entire dataset including headers.
-
Go to the "Insert" tab on the ribbon and click on "PivotTable" in the "Tables" section.
-
In the "Create PivotTable" dialog box, make sure that the range is correct and choose where you want to place the PivotTable.
-
Drag the "Status" column to the "Rows" section and the "Sales" column to the "Values" section.
-
The PivotTable will display the total sales for each status.
-
To calculate the total sales for multiple statuses, simply select the relevant rows in the "Status" column before dragging it to the "Rows" section. The PivotTable will automatically sum up the sales for the selected statuses.
split the "productcode" field into two using text functions in Excel. Here's how:
-
Assume that the "productcode" field is in column A.
-
In a new column, say B, enter the following formula to extract the first three characters (i.e., the alphabetic part) of the product code:
=LEFT(A1,3)
- Press Enter and drag the formula down to apply it to all the rows.
This formula uses the LEFT function to extract the first three characters from the "productcode" field.
- In another new column, say C, enter the following formula to extract the remaining characters (i.e., the numeric part) of the product code:
=RIGHT(A1,LEN(A1)-3)
- Press Enter and drag the formula down to apply it to all the rows.
This formula uses the RIGHT function to extract the characters from the "productcode" field starting from the fourth character (i.e., after the first three alphabetic characters).
- The "productcode" field is now split into two columns, one containing the alphabetic part and the other containing the numeric part. You can rename the columns as needed.
highlight the top 10% values of the "QuantityOrdered" field using conditional formatting in Excel:
-
Select the cells containing the "QuantityOrdered" values that you want to highlight.
-
Go to the "Home" tab on the ribbon and click on "Conditional Formatting" in the "Styles" section.
-
Click on "New Rule" in the dropdown menu.
-
In the "New Formatting Rule" dialog box, choose "Use a formula to determine which cells to format".
-
In the "Format values where this formula is true" field, enter the following formula:
=CELL("row")/COUNTA($A$1:$A$100)>0.9
Note: Change "$A$1:$A$100" to the range that contains your data.
-
Click on the "Format" button to specify the formatting for the top 10% values.
-
In the "Format Cells" dialog box, go to the "Fill" tab and choose the color you want to use for highlighting the cells.
-
Click "OK" to close the "Format Cells" dialog box.
-
Click "OK" to close the "New Formatting Rule" dialog box.
-
The top 10% values of the "QuantityOrdered" field will now be highlighted with the color you selected.