Chayanonboo

Tag: Microsoft Excel

  • 5 Steps of Data Analysis By Using Microsoft Excel

    5 Steps of Data Analysis By Using Microsoft Excel

    Data analyst is an essential tool that enables organizations to gain deeper insights into their data. Utilizing Microsoft Excel for efficient data processing facilitates accurate and prompt decision-making while identifying trends and uncovering new business opportunities.

    Table of content

    1. The 5 steps for analyzing the sales_data_analysis.xlsx file in Microsoft Excel 365 are as follows
      1. Upload a document using the free online version of Microsoft Office 365
      2. Perform data analysis using sorting and filter tools.
      3. Perform data mining using the IF Function
      4. Create references between tables and search for information with VLOOKUP
      5. Perform data analysis using Pivot Table
        1. Pivot table
    • Excel can help data analysis
    • Excel work with a prepared spreadsheet that contains sale

    The 5 steps for analyzing the sales_data_analysis.xlsx file in Microsoft Excel 365 are as follows

    Upload a document using the free online version of Microsoft Office 365

    One drive to upload excel file

    Click add new → File upload → then upload → sales_data_analysis_23.10.2024

    • Go to Insert → Table to create a table that uses the header in the first column to filter data. → Click OK.
    • To Create a table to filter data, see the picture below.
    Create Table in microsoft excel 365
    • Then filter the data shown in the picture below.
    can filter data by columns such as Num, Date, Month, Sales Rep, Region, Customer ID, etc.

    Set it up so that when you scroll down to view data in the rows below, the first column remains visible. This makes it much easier to reference the headings.

    Perform data analysis using sorting and filter tools.

    Which column should be prioritized for sorting data to make it more effective?

    representative header and then select sort A to Z to sort it in alphabetical order

    Sort the Sales Rep column from A to Z.
    • after click it has been rearrange by alphabetical Sales Rep
    • then to make it back to select sort in column date again
    • can sort the Region by North.
    Filter by North Region.
    • To remove the filter, click the ‘Select All‘ checkbox to display sales from the North, South, and West regions, and then click.
    • Then filter the Sales Rep column by the name David Garcia.
    Calculate in the bottom right corner.
    What you can see from data?
    Average of $7,893
    Count of 9
    Sum of $71,040
    • you can see aggregate value in the bottom right corner.

    This is how to use the sorting and filtering tools to rearrange your data.

    Perform data mining using the IF Function

    • The idea behind data mining is to take the data you already have and create new or additional data from it.
    • The IF function is frequently used.

    Samples show that when an order includes 20 chairs or more, the client receives a 5% wholesale discount.

    Discount Column 3 Method
    1. Create a discount column to the right to reflect this.
    2. In the column, use ‘Y‘ for orders with a quantity ≥ 20.
    3. In the column, use ‘N‘ for orders with a quantity ≤ 20.

    Code for column Discount

    =IF(J5>=20,"Y","N")
    Create a discount column based on the quantity in the number column.
    • Final Price column

    Code for column Final Price

    =IF(J5>20,0.95*L5,L5)
    Create a Final Price column based on discount and number column.
    • column of Discount with Y is number ≥ 20 price is discount 5% final is 95% from total column
    • column of Discount with N is number ≤ 20 price is same as the total

    Create references between tables and search for information with VLOOKUP

    Goal is to insert the company name using the client ID.

    1. Create column Company Name between Customer ID and Color
    2. Create column Company Name Representative between Company name and model

    The explanation of the variables used in VLOOKUP.

    VLOOKUP(lookup_value, table_aray, col_index_num, [range_lookup])

    Create Company name column

    =VLOOKUP(F5,'Customer Info'!$A$4:$C$12,2,FALSE)

    Create Company name representative

    =VLOOKUP(F5,'Customer Info'!$A$4:$C$12,3,FALSE)
    Customer Info sheet data

    to connect data between sales data and customer info sheet

    Type of VLOOKUP
    Create column Company name and Company Name Representative column in sale data sheet

    can adding a dollar sign in front A4 and C12 to make data not change

    Perform data analysis using Pivot Table

    Method to start

    • Typing control A and it will automatically highlight all the data in the table
    • Then insert ———> Pivot table ———> click okay
    Create Pivot table in new worksheet
    • Recommend selecting new worksheet so it placement does not affect the other data that already exists

    Pivot table

    • define categories as either filters, columns, rows, value
    TypeDefinition
    Filter2 Category
    ColumnCategory can specified as a column to fit with data
    RowCategory can specified as a row to fit with data
    ValuesMust use with number
    • 1. drag Final Price to Value of pivot table
    • 2. drag Sales Rep to columns of pivot table
    • 3. drag Month to row of pivot table
    Table prepare month with Sale Rep

    how many chairs of each model were sold in each month?

    Pivot table many chairs of each model per month.
    • Finally you have learned how to create pivot tables to summarize and look at comparisons within your data.

    Access full Microsoft excel through below link:

    sales_data_analysis_23.10.2024_chayanon_boonthanomDownload

    Coursera Project Network Certificate

    https://www.coursera.org/account/accomplishments/verify/2F13S7V2LEZF

    Summary

    Data analysts help organizations gain valuable insights from data. Using Microsoft Excel enhances decision-making by processing data efficiently and identifying trends and opportunities.