BlogFor BusinessTalk to a counsellor
CoursesIIT ProgramsPlacementsLog in
BlogFor Business
Talk to a counsellor
Challenge

Project 2 - Create a report using PowerQuery, Macro VBA, List Functions and Data Validation functions for Data Reporting of Supply Chain Management companies

PPratik Ghosh25 Apr 2023

Here is a step-by-step guide on how to create a Dashboard in MS Excel:

  1. Open the downloaded file in MS Excel.
  2. Create a new sheet and name it "Dashboard".
  3. In the Dashboard sheet, insert a text box and write "Loan Applicant Dashboard" as the title.
  4. Create a table and name it "Gender Ratio of Applicants".
  5. In the Gender Ratio of Applicants table, insert the following columns: Gender and Count.
  6. In the Gender column, enter "Male" and "Female".
  7. In the Count column, use the COUNTIF function to count the number of males and females in the Loan Applicant data set. The formula for the male count should be "=COUNTIF('Loan Applicant Data'!C:C,"Male")" and the formula for the female count should be "=COUNTIF('Loan Applicant Data'!C:C,"Female")".
  8. Create a pie chart using the Gender Ratio of Applicants table.
  9. Add a chart title to the pie chart and name it "Gender Ratio of Applicants".
  10. Create a table and name it "Car Owner/ Non-Owner Ratio".
  11. In the Car Owner/ Non-Owner Ratio table, insert the following columns: Car Owner and Count.
  12. In the Car Owner column, enter "Yes" and "No".
  13. In the Count column, use the COUNTIF function to count the number of car owners and non-owners in the Loan Applicant data set. The formula for the car owner count should be "=COUNTIF('Loan Applicant Data'!E:E,"Yes")" and the formula for the non-owner count should be "=COUNTIF('Loan Applicant Data'!E:E,"No")".
  14. Create a pie chart using the Car Owner/ Non-Owner Ratio table.
  15. Add a chart title to the pie chart and name it "Car Owner/ Non-Owner Ratio".
  16. Create a table and name it "House Owner/ Non-Owner Ratio".
  17. In the House Owner/ Non-Owner Ratio table, insert the following columns: House Owner and Count.
  18. In the House Owner column, enter "Yes" and "No".
  19. In the Count column, use the COUNTIF function to count the number of house owners and non-owners in the Loan Applicant data set. The formula for the house owner count should be "=COUNTIF('Loan Applicant Data'!D:D,"Yes")" and the formula for the non-owner count should be "=COUNTIF('Loan Applicant Data'!D:D,"No")".
  20. Create a pie chart using the House Owner/ Non-Owner Ratio table.
  21. Add a chart title to the pie chart and name it "House Owner/ Non-Owner Ratio".
  22. Create a table and name it "Client Income Type".
  23. In the Client Income Type table, insert the following columns: Income Type and Count.
  24. In the Income Type column, enter "Salary", "Business", "Others".
  25. In the Count column, use the COUNTIF function to count the number of loan applicants with each income type. The formula for the salary count should be "=COUNTIF('Loan Applicant Data'!F:F,"Salary")", the formula for the business count should be "=COUNTIF('Loan Applicant Data'!F:F,"Business")" and the formula for the other count should be "=COUNTIF('Loan Applicant Data'!F:F,"Others")".
  26. Create a bar chart using the Client Income Type table.
  1. Create a new sheet and name it "Female Clients Data Summary".
  2. In the Female Clients Data Summary sheet, insert a table and name it "Female Clients Income and Loan Amount".
  3. In the Female Clients Income and Loan Amount table, insert the following columns: Client ID, Income, Loan Amount.
  4. In the Client ID column, use the FILTER function to extract the Client IDs of all female clients from the Loan Applicant data set. The formula for the Client ID column should be "=FILTER('Loan Applicant Data'!A:A, 'Loan Applicant Data'!C:C = "Female")".
  5. In the Income column, use the SUMIF function to calculate the total income of all female clients. The formula for the Income column should be "=SUMIF('Loan Applicant Data'!C:C, "Female", 'Loan Applicant Data'!G:G)".
  6. In the Loan Amount column, use the SUMIF function to calculate the total loan amount of all female clients. The formula for the Loan Amount column should be "=SUMIF('Loan Applicant Data'!C:C, "Female", 'Loan Applicant Data'!H:H)".
  7. Create a new sheet and name it "Male Clients Data Summary".
  8. In the Male Clients Data Summary sheet, insert a table and name it "Male Clients Income and Loan Amount".
  9. In the Male Clients Income and Loan Amount table, insert the following columns: Client ID, Income, Loan Amount.
  10. In the Client ID column, use the FILTER function to extract the Client IDs of all male clients from the Loan Applicant data set. The formula for the Client ID column should be "=FILTER('Loan Applicant Data'!A:A, 'Loan Applicant Data'!C:C = "Male")".
  11. In the Income column, use the SUMIF function to calculate the total income of all male clients. The formula for the Income column should be "=SUMIF('Loan Applicant Data'!C:C, "Male", 'Loan Applicant Data'!G:G)".
  12. In the Loan Amount column, use the SUMIF function to calculate the total loan amount of all male clients. The formula for the Loan Amount column should be "=SUMIF('Loan Applicant Data'!C:C, "Male", 'Loan Applicant Data'!H:H)".
  13. Create a new sheet and name it "Flag Columns Data Summary".
  14. In the Flag Columns Data Summary sheet, insert a table and name it "Flag Columns Summary".
  15. In the Flag Columns Summary table, insert the following columns: Flag Column, Value, Count.
  16. In the Flag Column column, enter the names of all flag columns (FLAG_DOCUMENT_2, FLAG_DOCUMENT_3, etc.).
  17. In the Value column, enter the possible values of the flag columns (0 or 1).
  18. In the Count column, use the DCOUNTA function to count the number of loan applicants with each flag column value. The formula for the first row should be "=DCOUNTA('Loan Applicant Data'!$1:$1048576, 'Loan Applicant Data'!$2:$2, {'FLAG_DOCUMENT_2', 0})". Repeat this formula for all possible combinations of flag column and value.
  19. You can also use other variants of the DCOUNT function (such as DCOUNT and DSUM) to summarize the data in different ways.

I hope this helps you to create the required data summaries in separate worksheets using new and list functions, and DCOUNT functions in MS Excel. Let me know if you have any further questions.

The steps to create a Macro for Data Formatting, VBA Code to convert selected data into pdf, and to check spelling mistakes in the workbook using VBA Code:

  1. Macro for Data Formatting: a. Open the workbook and go to the sheet you want to format. b. Click the "Developer" tab in the ribbon. c. Click "Record Macro" from the "Code" group. d. In the "Record Macro" dialog box, name the macro, choose where to store it, and add a description if needed. Click "OK". e. Perform the formatting actions that you want to apply to the data, such as Conditional Formatting and Freezing Panes. f. Click "Stop Recording" from the "Code" group when you are done. g. To apply the formatting to the data of the next quarter, go to the sheet you want to format and click "Macros" from the "Code" group. Select the macro you just recorded and click "Run".

  2. VBA Code to convert selected data into pdf: a. Press "Alt" + "F11" to open the Visual Basic Editor. b. Click "Insert" from the menu and select "Module" to add a new module. c. Copy and paste the following code into the module:

Sub ExportDataToPDF() Dim ws As Worksheet Dim rng As Range Dim path As String Dim FileName As String 'Set worksheet and range to export Set ws = ActiveSheet Set rng = Selection 'Get file path and name path = Application.ActiveWorkbook.path & "\" FileName = InputBox("Enter the PDF file name", "Save PDF", "Applicant Data.pdf") 'Export selection as PDF rng.ExportAsFixedFormat Type:=xlTypePDF, Filename:=path & FileName, Quality:=xlQualityStandard End Sub

d. Save the module and go back to the Excel sheet. e. Select the data you want to convert to PDF. f. Press "Alt" + "F8" to open the Macros dialog box. g. Select the "ExportDataToPDF" macro and click "Run". h. Enter the PDF file name when prompted and click "Save".

  1. VBA Code to check spelling mistakes in the workbook: a. Press "Alt" + "F11" to open the Visual Basic Editor. b. Click "Insert" from the menu and select "Module" to add a new module. c. Copy and paste the following code into the module:
Sub CheckSpellingMistakes() Dim ws As Worksheet 'Loop through all worksheets in the workbook For Each ws In ThisWorkbook.Worksheets 'Activate worksheet and check spelling ws.Activate Cells.CheckSpelling Next ws 'Show message when done MsgBox "Spelling check complete." End Sub

d. Save the module and go back to the Excel sheet. e. Press "Alt" + "F8" to open the Macros dialog box. f. Select the "CheckSpellingMistakes" macro and click "Run". g. The macro will loop through all worksheets in the workbook and check the spelling of all cells. When it is done, a message box will appear.

https://drive.google.com/drive/folders/1QZjSx7CNQOr_SXt1nbEvtiV5zQQ1umeD?usp=share_link

Your portfolio, next

Ship work like this
in your own course.

Talk to a career counsellor

Free 20-minute call. We'll map the right course to where you want to work.

By submitting you agree to our Terms and Privacy Policy.