Plotting monthly salary based on gender and age in Excel

Consider having the following raw data (on the left). How do you plot the average monthly salary of your employees based on gender, age, and function (plot on the right)? See the steps below.



1. You need to categorize your raw data into the following table:. To do that: 
    a. Sort your data according to age from smallest to largest, 
    b. Use pivot-table for each range of age (eg, 20-30, 30-40, etc.),  
    c. Copy and paste the averages into the table.   

2. Select the previous table as a whole and click "Insert" from the menu. You may choose the bar chart to get the following plot:


NB. To add a secondary axis to this plot see Add-second-y-axis-to-right-of.html

Comments

Post a Comment

Popular posts from this blog

Covid19 data analysis in Python

Image
Statistics on Covid19 cases in a few cities in Saudia Arabia Data source : King Abdullah Petroleum Studies and Research Center (KAPSARC) ,  Retrieved on 7-Sep-2020.   Objective : Get a glimpse of the Covid19 virus spread in KSA and visualize the curves of the total cases, active cases, recoveries, and mortalities. The steps followed in making this report are: 1. Data collection and cleaning  2. Data exploration and filtering by City with Pandas' DataFrame 3. Visualizing the data with Matplotlib  In the following, let us focus on a few big cities in KSA, e.g., Riyadh, Jeddah, Mekka, Medinah, and Dammam. Before diving into the different results let us see the event data on the first registered case, curfew, and lockdown.  Riyadh Let's start with summary statistics on the daily cases, recoveries, and mortalities.   The cumulative number of cases in Riyad are given in the following plot: Jeddah The summary statistics of Jeddah daily cases are as follo...
Read more

Showing your company revenues and return-on-investment percentage in single plot

Image
 How do you add a second y-axis to the right of your figure in Excel?  Consider the following example of Incomes in thousands and gain as a percentage and you want to show it as follows.   If you plot them on the same y-axis, "Gain" will not be recognizable. The solution is to plot 'Gain' on a secondary y-axis. The steps are as follow: 1. Select all your data  2. Click 'Insert' from the menu and select your chart type (eg, bar, line, scatter) 3. Right-click on the line you want to show it in the secondary y-axis (Gain in our example)  4. Choose 'Format Data Series' from the open drop menu. The 'Format Data Series' menu will open on the right of your Excel sheet see below.  5. Choose from under "Series Options" plot series on the "Secondary Axis".  6. If you want to show "Incomes" as bar chart then right-click on the blue line and select ''Change Series Type". A new window will open as follows. Click th...
Read more