Excel Tips and Tricks

Type of Event: Workshop

Date: 17 JANUARY 2024

Mode: Online

https://accaglobal.zoom.us/meeting/register/tZ0tcuGvrD8jEtz7KyxnmCpFGwjD...

Name of the Activity Coordinator: Dr Mahima Rai

Name of the Resource Person: Ms DIVYA ARORA

Name of the Collaborating Agency: ACCA

Number of Participants: 131 total, 12  from IISU

Objective of the Workshop: To enhance skills in using excel formulas

SUMMARY:

The Excel Formulas Workshop was a resounding success in enhancing participants' proficiency in utilizing Excel for effective data analysis and reporting. The workshop covered a comprehensive agenda, introducing new formulas, providing practical examples, and engaging participants in hands-on exercises. Participant feedback was overwhelmingly positive, highlighting the practical relevance of the content and the effectiveness of the hands-on approach. Despite challenges faced, solutions were discussed, fostering a collaborative learning environment. As the workshop concluded, participants left equipped with enhanced Excel skills, eager to apply their newfound knowledge in their professional endeavors. In the discussion of Excel formulas during the workshop, participants delved into a variety of key formulas essential for data analysis and reporting. Here's a brief overview of the discussed formulas:

1.Basic Formulas:

•           Recap of fundamental formulas such as SUM, AVERAGE, COUNT, SUBTOTAL and basic arithmetic operators.

•           Emphasis on constructing formulas to perform simple calculations within cells.

2.Logical Functions:

•           Exploration of logical functions like IF

•           Practical examples showcasing how to use logical functions to make data-driven decisions.

3.Lookup and Reference Functions:

•           In-depth understanding of VLOOKUP and HLOOKUP for vertical and horizontal data retrieval.

4.Date and Time Functions:

•           Overview of date and time functions, including DATE, and TIME.

•           Practical exercises demonstrating how to manipulate and calculate dates and times effectively.

Filtering data in Excel is a crucial skill for efficiently analyzing and visualizing information. In the workshop, participants explored various techniques to filter out data, facilitating targeted data analysis. Key points covered include:

1.         Basic Filtering:

•           Introduction to basic filtering using the AutoFilter feature.

•           Step-by-step guidance on applying filters to columns to display specific data subsets.

2.         Filtering Criteria:

•           Explanation of setting criteria to filter data based on specific conditions.

•           Practical examples demonstrating the use of criteria to extract relevant information.

3.         Advanced Filtering:

•           Exploration of advanced filtering options, including custom filters and text filters.

•           Application of multiple criteria to refine data selection.

In the workshop, participants explored the powerful tools of custom formatting and conditional formatting in Excel to enhance data visualization and highlight important insights. Here's a summary of the key points covered.

1.         Custom Formatting:

•           Introduction to custom formatting to change the appearance of cells based on specified conditions.

•           Demonstration of custom number formats to represent data in a more meaningful way (e.g., currency, percentages).

2.         Conditional Formatting Basics:

•           Overview of conditional formatting as a tool to dynamically format cells based on their values.

•           Step-by-step guidance on applying basic conditional formatting rules for highlighting data.

     3.Basic Currency Formatting:

•           Introduction to basic currency formatting options available in Excel.

•           Demonstration of how to apply currency formatting to selected cells or entire columns.

    4.Conditional Formatting with Borders:

•           Integration of borders into conditional formatting rules for visual emphasis.

•           Examples of using borders to highlight specific cells or ranges based on conditions.

    5.    Bar Chart Inside Data

•           SELECTION OF DATA THEN CONDITIONAL FORMATTING THEN SELECT DATA BARS

Data visualization is the process of representing information in a graphical or visual format to make it easier to understand, interpret, and derive insights from.

1.         Charts and Graphs:

•           Exploration of various chart types, including bar charts, line charts, pie charts, scatter plots, and more.

•           Guidelines on selecting the most appropriate chart type for different data scenarios.

•           Making bar charts (f11)

Pie chart: A pie chart is a circular statistical graphic that is divided into slices to illustrate numerical proportions. Each slice represents a proportionate part of the whole, and the size of each slice is determined by the value it represents in relation to the total.

Line chart: A line chart is a type of graph that displays data points connected by straight line segments. It is commonly used to visualize trends over a continuous interval or time series data. Line chart is not a good tool if there are any breakes in your data.

Chart Purpose: Always consider the purpose of your visualization. If your goal is to show a trend over time, a column chart with time on the horizontal axis might be more appropriate. If you are comparing categories, the choice may depend on the specific characteristics of your data. Bar chart is use according to situation.

2.         Dashboards:

•           Overview of creating interactive dashboards for comprehensive data representation.

•           Demonstrations on combining multiple visualizations on a single dashboard for holistic analysis.

Power BI and Tableau are both powerful business intelligence tools used for data visualization, analysis, and reporting.

OUTCOME OF THE ACTIVITY:

The outcome of the workshop on Excel formulas can be summarized in terms of the knowledge and skills gained by participants. Here's a brief summary of potential outcomes:

1.Enhanced Proficiency in Excel Formulas:

•           Participants gained a deeper understanding of various Excel formulas, ranging from basic arithmetic to advanced functions.

2.Practical Application of Formulas:

•           Through hands-on exercises and real-world examples, participants developed the skills to apply formulas in practical scenarios, improving their ability to analyze and manipulate data.

3.Improved Data Analysis Skills:

•           The workshop equipped participants with the tools to perform more complex data analysis tasks, such as conditional calculations, lookups, and statistical functions.

       4.Efficient Data Processing:

•           Participants learned techniques to streamline data processing tasks, making them more efficient in handling large datasets and automating repetitive processes.

       5.      Practical Tips and Tricks:

•           Participants gained insights into practical tips and tricks for using Excel formulas effectively, improving overall spreadsheet management.

       6.Participant Collaboration:

•           The workshop likely facilitated collaboration among participants through discussions, problem-solving sessions, and shared experiences.

       7.Feedback and Improvement:

•           If applicable, the workshop provided an opportunity for participants to provide feedback on the content, format, and effectiveness of the training, contributing to potential improvements for future workshops.

       8.Preparation for Further Learning:

•           Participants left the workshop with a foundation for further learning in Excel and data analysis. They may be better prepared to explore more advanced topics or pursue additional training.

The overall outcome is an empowered group of individuals who are better equipped to leverage Excel formulas for data analysis, reporting, and problem-solving in their professional roles. The success of the workshop is measured not only in the content delivered but also in the participants' ability to apply their newfound knowledge in practical settings