30-hour Certificate Course on “Data Insights and Analytics using MS-Excel”
The Post Graduate Department of Commerce conducted an intensive 30-hour certificate course on “Data Insights and Analytics using MS Excel”, specifically designed for M.Com Part II students. The course was led by CA Shravan Swarup, founder of D. Shravan Swarup & Associates. 45 students attended the course. The course commenced on January 16, 2024, in classroom T-302 at 9:00 am. Mr. Swarup introduced the students to foundational Excel concepts, including cell referencing, relative referencing, and mixed referencing. He provided a dataset for students to practice functions such as PROPER, LOWER, UPPER, EOMONTH, ALT+E+S (paste special), RAND, and RANDBETWEEN.
In the subsequent sessions, Mr. Swarup covered an array of text functions, including LEN, TRIM, Flash Fill, IF, and OR. He also covered advanced functions like VLOOKUP, SUMIF, AVERAGEIF, and data validation, highlighting their critical relevance and widespread application in the industry. He also discussed the GIGO (garbage in, garbage out) principle, emphasizing the importance of data cleaning prior to analysis. He focused on financial functions such as calculating the IRR of a project, determining EMI for loans, computing returns on investments like mutual funds, and tax calculations.
Some sessions were dedicated to data tables, covering the creation of pivot tables, removal of duplicates, editing of labels, modifying ranges, the application of the IFERROR function, the COUNTIF function, the use of slicers, creation of drop-down lists via data validation, and provided a preliminary overview of charts and graphs. He also elaborated on various chart types and their optimal use for different datasets. He also demonstrated the use of wildcards in conjunction with functions like SUMIF, VLOOKUP, data consolidation across multiple worksheets and offered a brief introduction to Macros, functions such as COUNTIF, TEXT, Ctrl+E, Text to Columns, the Format menu (Ctrl+1), and REPT. The eleventh session, conducted on May 15, 2024, focused on MATCH, INDEX, NEST, XLOOKUP, HLOOKUP function and further explored the application of Macros.
The course concluded with an Online MCQ based Test conducted on 15th June 2024.