Radial Bar Chart in Google Sheets Example – Learn How To Make Radial Chart in Spreadsheet

The Google Sheets Radial Bar Charts look great when you want to grab the attention of users. However, the radial bar charts in Google Sheets should be designed carefully since they are hard to read than a regular chart. This is because it is difficult to measure the lengths in Google Sheets. And that is why here is an article, which tells you everything about how to design a radial bar chart in Google Sheets with simple G-Sheet tricks. Read further to find more.

Table of Contents

 

How To Create Radial Bar Charts in Google Sheets?

In order to create a Radial Chart in Spreadsheet, we need to have a dataset.

For these three series, such as items with a number of units sold, we’ll require a column of values.

Following that, we’ll need an upper limit (maximum value) for our bars. This helps us to appropriately scale the bars.

Radial Bar Chart in Google Sheets

Finally, we require a helper column that computes the difference between the maximum and actual value. So our dataset for creating a radial bar chart is shown below:

Steps to Create Radial Bar Chart in Google Sheets

Follow the steps as outlined below to create Radial Bar Charts in Google Spreadsheet.

Step 1: Creating Inner Circle

The step towards creating a Radial Bar Chart is to create an inner circle.

  1. The first row of data should be highlighted, but the maximum value column should be left out.
  2. Now click on the Chart icon in the menubar. By default, Google Sheets will come up with a chart.
    Radial Bar Chart in Google Sheets
  3. Under the Chart Editor Pane, move to the Setup section.
  4. Click on the Chart Type drop-down menu and choose “Doughnut Chart“.
  5. Under Customize, make the following settings to the Doughnut Chart you have created.
    • Color of the background: none
    • Color of chart border: none
    • 67 percent of doughnut holes size.
    • Set the color of Slice 2 to none.
    • Remove the chart title and set none to the legend.
  6. Now the inner radial bar chart will look like the following image. That’s it the inner circle has been created in Google Sheets Radial Chart.

Radial Bar Chart in Google Sheets

Step 2: Creating Middle Circle

For the inner circle, repeat the steps above, but use the next row of data, a new color, and a 77 percent doughnut hole size. You may need to work about these percentages to get everything to line up in the end.

To produce the same, drag the second doughnut chart on top of the first and align the radial bar. So that our chart will look like the following image.

Radial Bar Chart in Google Sheets

Step 3: Creating Outer Circle

To make a third doughnut chart, repeat the procedures above from the inner circle, but this time use the third row of data, a change in color, and set the doughnut hole size to 81 percent. This may need to be tweaked to match everything up.

You can make a radial bar chart in Google Sheets by dragging this third doughnut chart on top of the previous two.

Because the charts are stacked on top of one another, you will only be able to alter the top chart. To get to the chart below, you will have to move it to the side, and then move that one if you want to go to the inner chart.

Radial Bar Chart in Google Sheets

Add the Data Labels in Google Sheets

Because using the chart editor to add data labels to each chart gets messy, I decided to use formulas to add my data labels to the cells adjacent to each bar of the radial bar chart.

Click on a cell outside of the chart area to view cells under the charts, then use the arrow keys on your keyboard to go to the required cell.

Add the following formula after that:

=E6&”: “&TEXT(F6,”#,0”)

In Google Sheets, the TEXT function is used to merge text and numbers.

Alongside each bar, the name of the series and its value are displayed.

To finish, remove the gridlines from your Sheet to give the chart a more professional appearance.

Subtotal Function in Google Sheets – How to Subtotal in Google Sheets?

The Google Sheets Subtotal function will enable users to subtotal the vertical range of cells using a specified aggregation function. A total of 11+ functions are available in Google Spreadsheet, with the help of which we can perform the subtotal operation. On this page, let us understand how to perform a subtotal function using Google Sheets Tips and Tricks. Read further to find out more.

Subtotal Function Syntax in Google Sheets

The syntax of Google Sheets’ Subtotal function is as follows:

Subtotal function syntax=(function_code, range1, [range2,…])
  • Function_Code: The function code here specifies the function to be used in subtotal aggregation.
  • Range 1: This specifies Range 1 for which the subtotal needs to be calculated.
  • Range 2: Additional ranges which we can specify to calculate the subtotals.
Function Code (includes hidden values) Code (ignores hidden values) Function Code Meaning
AVERAGE 1 101 The AVERAGE function returns the numerical average value in a dataset, ignoring the text.
COUNT 2 102 Returns the number of numeric values in a dataset.
COUNTA 3 103 Returns the number of values in a dataset.
MAX 4 104 Returns the maximum value in a numeric dataset.
MIN 5 105 Returns the minimum value in a numeric dataset.
PRODUCT 6 106 Returns the result of multiplying a series of numbers together.
STDEV 7 107 The STDEV function calculates the standard deviation based on a sample.
STDEVP 8 108 Calculates the standard deviation based on an entire population.
SUM 9 109 Returns the sum of a series of numbers and/or cells.
VAR 10 110 Calculates the variance based on a sample.
VARP 11 111 Calculates the variance based on an entire population.

How to Use the Subtotal Function in Google Sheets?

Let us understand more about the Subtotal function in Google Spreadsheet using an example. Consider the following dataset where we have student grade reports:

Student ID Student Name Major Class Year Midterm Grade Final Grade
N1304 David Computer Science 2012 78 81
N1008 Jason Math 2011 87 80
N1866 Mary Computer Science 2012 79 80
N1774 Rob Computer Science 2012 90 85
N1365 Jason Math 2011 90 96

Now let us perform a few subtotal functions such as AVG, Max, Min, STDEV, SUM, and VAR with the students’ grade reports. The steps to get this done in Google Sheets are as follows:

  • 1st Step: Open the Google Spreadsheet on your device.
  • 2nd Step: Now copy-paste the student grade report for which you want to perform the subtotal function.

SUBTOTAL function in Google Sheets

  • 3rd Step: Next, move to the cell where you want to perform the subtotal function. Here I am creating a dataset for the calculation of subtotals, as shown in the image below.

SUBTOTAL function in Google Sheets

  • 4th Step: Now in the “Result” column, enter the formula as =SUBTOTAL (B10, $F$2: $F$6).
  • 5th Step: Press the “Return” key and you will see the results as follows.
  • 6th Step: Now drag the formula-applied cells to other cell ranges and you will see the subtotals being calculated as shown below.

SUBTOTAL function in Google Sheets

How to Create a Subtotal Function for Hidden or Filtered Data?

Let’s say we have a huge data collection that has been classified and filtered by the department. To review the data in our reports, we have total cells. The purpose is to have the total cells adjusted as an outcome of the filter. So, if we’re looking at data about electronics, the total number of cells must reflect that.

Also, if a row is hidden, we want the data cells to reflect that. The first thing to keep in mind is that the SUM function does not adapt when the data is filtered or hidden. The converse is true with the SUBTOTAL function.

To do this, we select the green filter drop-down at the top of the Department column. Select the department where you would like to show up. You will now see that the data has changed, and many rows are filtered.

You will also notice that the SUM cells in the table below are unchanged. However, the filtered cells that use the subtotal function are adjusted only to show unfiltered cells.

SUBTOTAL function in Google Sheets

REPT Function In Google Sheets – How To Repeat Rows in Google Sheets?

Google Sheets REPT function will return the text specified times repeatedly. For example, if you want to repeat the term Sheets Tips, thrice, then we can simply use the REPT function to get this done. On this page, let us understand how to use the REPT function in Google Spreadsheet using the Google Sheet Tips provided on this page. Read further to find more.

Table of Contents

Sheets Tips

REPT Function Syntax in Google Sheets

To use the repeat function in Google Spreadsheet, we will have to use the syntax. The syntax of the REPT function is as follows:

=REPT(text_to_repeat, number_of_repetitions)

  • text_to_repeat: The letter or string to be repeated is text to repeat.
  • number_of_repetitions: Indicates how many times text to repeat should appear in the returned response.

The number of repetitions cannot exceed the 32,000-character maximum of a cell. REPT will return a #VALUE! error if the number of repetitions is larger than 32,000 characters.

How to Use REPT Function in Google Sheets?

Let us say, we want the term Sheets Tips to be repeated in Google Sheets thrice in a cell. We can get this done in Google Sheets.

Follow the steps as outlined below to use the REPT function in Google Spreadsheet.

  • 1st Step: Open the Google Spreadsheet on your device.
  • 2nd Step: Now on the homepage, move to the cell where you want the term to get repeated.
  • 3rd Step: Enter the formula =REPT(“Sheets Tips “, 3).
  • 4th Step: Press the “Return” key and you will see the results as shown below.

REPT Function in Google Sheets

If you observe, I have provided extra space after the term “Sheets Tips”. If I remove the space and apply the formula, then the REPT function will print the term without providing any spaces.

Using REPT Function to Repeat Numbers in Google Sheets

Google Sheets not only repeats the characters but also helps to repeat the numbers. To repeat the numbers in Google Spreadsheet, follow the steps outlined below:

  • 1st Step: Launch the Google Spreadsheet on your system.
  • 2nd Step: In the Sheet, head to the cell range where you want to apply the REPT function to a number.
  • 3rd Step: Now enter the formula =REPT(“4”, 8). Here I am using the number 4 to repeat 8 times using the REPT function.
  • 4th Step: Press the “Enter” button and you will see the results.

REPT Function in Google Sheets

Using REPT Function to Repeat Images in Google Sheets

Google Sheets also allows users to repeat images using the REPT function. The steps to get this done in Google Spreadsheet are as follows:

  • 1st Step: Open the Google Spreadsheet.
  • 2nd Step: Copy the IMAGE URL which you would like to repeat. Here I am using the Google Logo. So my formula will be “=ArrayFormula(IMAGE(SPLIT(REPT(“https://www.google.com/favicon.ico”&”♕”,5),”♕”)))”.
  • 3rd Step: Press the “Return” key and you will see the results.

Note: The Queen symbol “♕” is added at the end of the repeated value to serve as a unique value for the SPLIT function when it divides the string.

REPT Function in Google Sheets

Using REPT Function to Repeat Charts In Google Sheets

We can also easily generate the bar charts within the cell in Google Spreadsheet using the REPT function. The steps to generate the bar chart in Google Sheets using the REPT function are as follow:

  • 1st Step: Open the Google Spreadsheet on your device.
  • 2nd Step: Now on the homepage, in column A, enter the number of times a bar chart needs to be repeated.
  • 3rd Step: In column B, enter the REPT function formula (=REPT(CHAR(10074),A1)) as shown in the image below.
  • 4th Step: Press the “Return” key and you will see the results as shown below.

REPT Function in Google Sheets

Few Other Examples of REPT Function

A few other examples using the REPT function in Google Sheets are shown below.

REPT Function in Google Sheets

How To Add a Total Row in Query Function Table in Google Sheets?

This tutorial will talk everything about how to add total rows to the tables generated using the query function. Instead of using the array formulas, on this page, we will use the pair of curly braces with the help of which we will add the total row in a query function.

Well, most of you will be thinking that we can simply write the word Total and use the SUM(range) formula to draw the total results. One must note that this is the manual step. In order to create the dynamic total row in Google Sheets, we can simply use the {….} notation in an array formula. Let us understand how to do this in Spreadsheet using the Google Sheets Tips and Tricks. Read further to find more.

Google Sheets Query Total

We will use the same example which we have used in the Onion method for complex formulas.

In that example, what we would want to do is merge all of the organization rows into a single row with a total count for that organization. We are “grouping” our data into the categories in column A, then adding up all of the values in column B that belong to each group.

To do so, we combine the number of positions data for each agency using the QUERY function and a group by clause. The formula which we used for achieving this is as follows:

=QUERY($A$11:$B$61,”select A, sum(B) group by A order by sum(B) desc label sum(B) ‘Total Positions'”,1)

By applying this formula in Google Sheets we would have got the output as shown in the image below.

query-total-row

Now let us discuss how to add dynamic total in the Google Sheets Query function.

How Do You Add a Total Row in a Query Function?

So, we are doing the same thing as the simple example before, which is to create two different tables. One is a summary table, such as the one seen above, and the other is a total row, which we merge using an array formula.

The steps to get this done in Google Sheets are as follows:

  • 1st Step: Move to the cell where you want to add a total row.
  • 2nd Step: Now modify the previously used formula with the use of the Total function. So our formula here is
={
QUERY(
$A$11:$B$61,
“select A, sum(B) group by A order by sum(B) desc label sum(B) ‘Total Positions'”,
1
)
;
{
“TOTAL”,
SUM($B$11:$B$61)
}
}
  • 3rd Step: Press the “Return” key and now you will see the results as shown below.

query-total-row

Last Step to Make the Total Row in Query Function Dynamic

This is the final step where we want to generate the query function into a dynamic. As the table expands or contracts as we add or delete data, the total row goes up or down without any manual changes.

There are two things we must achieve:

  1. Change the range to include all of columns A and B, and
  2. Change the QUERY function to eliminate the blank row that appears after step 1.

Remove the references from the range so that all columns A and B are included.

query-total-row

Then add a WHERE clause to the QUERY function to get away from all the blank rows in column A. So our formula here is

={
QUERY(
$A$11:$B,
“select A, sum(B) where A is not null and A like ‘DEPT%’ group by A order by sum(B) desc label sum(B) ‘Total Positions'”,
1
)
;
{
“TOTAL”,
SUM(QUERY($A$11:$B,”select B where A like ‘DEPT%'”,1))
}
}

Enter the above formula and press the return key and you will see the results.

query-total-row

Onion Method for Complex Formulas in Google Sheets (Chain Rule)

A technique where we apply chain rule multiple times is known as Onion Method. This technique is called as Onion method since we perform one action per one step which is more or less like peeling the Onion. This Onion method comes in handy when we build complex formulas in Google Sheets. If you are unaware of how to adapt the Onion method to build complex formulas in Google Sheets, then this will tell you everything about it. Using the Google Sheets Tips and Tricks provided on this page, we can easily build complex problems in a spreadsheet.

Sheets Tips

How To Build Onion Method for Complex Formulas?

To build an onion method for complex formulas, we will have to follow the one action per step approach. This means we will have to build our formulas in a series of steps.

Confused about how to do this? Well, don’t worry. We have outlined how to build formulas using the Onion Framework here.

Onion Framework consists of three important elements and they are outlined below:

  • Firstly outline the step-by-step formula that you will be using in your Google Sheets and put each formula in each of the cells.
  • Now label the formulas with the step number in the adjacent cell. For example, if you want to use the IF function formula in the third step, you can simply name it Step 3.
  • Then enable the different background colors against each formula cell to make it more identical.

This allows you to observe the formula move in a step-by-step manner, which is extremely useful when creating or trying to grasp complex formulas in Google Sheets.

Example: Build Complex Formulas in Google Sheets using Onion Method

Let us consider the following example where we have the organization’s name and list of available positions. If you see the dataset, it is unorganized.

complex-formulas-onion-method

We can organize this dataset with the help of complex formulas using the Onion framework and summarise the job openings. The steps to get this done are outlined below.

Step 1:

Firstly let us use the Query function to summarise the data. Since we have two columns – A, B, we will select these two columns using the Query function. The formula to select these two columns using the Query function is as follows:

=QUERY(A1:B,”select A, B”)

Although this has no effect on the data, it’s usually a good idea to run a simple query first to confirm you’re using the proper dataset as the input to your QUERY function.

complex-formulas-onion-method

Step 2:

Now we are supposed to summarise the job position in Google Sheets using the GROUP BY clause. We are using the GROUP BY clause inside the QUERY function. The formula to get this done is Google Sheets is as follows:

=QUERY(A1:B,”select A, sum(B) group by A”)

complex-formulas-onion-method

Step 3:

Now the next step is to filter out the blank rows. To do this, we will have to use the WHERE clause. This WHERE clause will be used inside the query function and GROUP BY clause as shown below:

=QUERY(A1:B,”select A, sum(B) where A is not null group by A”)

complex-formulas-onion-method

Step 4:

Now the next step is to use the ORDER BY clause. The ORDER BY clause is used here to sort the selected dataset in descending order. So our formula in the 4th step will be as follows:

=QUERY(A1:B,”select A, sum(B) where A is not null group by A order by sum(B) desc”)

complex-formulas-onion-method

Step 5:

In this step, we will have to fix the header of the total column using the LABEL clause. The formula to be used in this step is as follows:

=QUERY(A1:B,”select A, sum(B) where A is not null group by A order by sum(B) desc label sum(B) ‘Total Positions'”)

Press the Return key after using this formula in Google Sheets and you will see the results being generated as shown in the image below.

complex-formulas-onion-method

Rather than utilizing a pivot table, we used the QUERY function to generate one. The key to making this work was to build it in steps, with the formula changing slightly with each step.

Step 6:

In this step, we are using the array literals. For this, we need to add a placeholder line in the total row and the formula for the same is as follows:

={QUERY(A1:B,”select A, sum(B) where A is not null group by A order by sum(B) desc label sum(B) ‘Total Positions'”);{“TOTAL”,”TBC”}}

complex-formulas-onion-method

Step 7:

To get the right total, we need to convert this placeholder into an actual formula. We leave the range reference open-ended, much as the data provided to the query function, to guarantee that it remains dynamic and will automatically incorporate fresh data. So our formula in this step will be as follows:

={QUERY(A1:B,”select A, sum(B) where A is not null group by A order by sum(B) desc label sum(B) ‘Total Positions'”);{“TOTAL”,SUM(B1:B)}}

complex-formulas-onion-method

Now that you will have an idea of how to build complex formulas in Google Sheets using the Onion Method Framework. If you are attempting to figure out complicated formulae in Google Sheets that someone else has provided with you, you can still use the Onion Method.

Simply peel back the layers until the deepest function is disclosed. Copy the formula into a new cell and work your way up to the whole formula again, starting from the inside.

Pi Function in Google Sheets: Discover & Generate π Function in Google Sheets

Google Sheets allows users to even perform the most complex mathematical calculations. We can also use this software to perform Circle related calculations, such as finding circumference, diameter, area of a circle, and many others. However, to perform Circle related calculation, we will have to use the Pi (π) functions. Pi (π) is one of the most important mathematical operations which is widely used in Circle related calculations. On this page, we will understand how to use the Pi function to find the Circle related calculations using Google Sheets Tips. Read further to find more.

Sheets Tips

Pi Function for Circle Calculations – Syntax

To enable the Pi function in Google Sheets, we will have to use the syntax. The syntax to use the Pi function in Google Sheet is as follows:

=PI()

It is to be noted that there are no arguments in between the brackets. If you use any arguments in the bracket, then the Pi function will return an error.

Using Pi Functions in Mathematical Calculations

In order to perform mathematical calculations, you will have to know the circumference of the circle. If you know the circumference of the circle, then you can easily calculate the diameter and other calculations of the circle.

Calculating Circle Circumference in Google Sheets using Pi Function

To calculate the circumference of the circle in Google Sheets, you will have to know the diameter of the circle. Let us assume that you want to find the circumference of the circle, whose diameter value is 20 cm. The steps to find the circumference is as follows:

  • 1st Step: Open the Google Sheets where you want to calculate the circumference of the circle.
  • 2nd Step: Move to the cell where you want to draw the results.
  • 3rd Step: Now simply enter the formula = 20 * PI().
  • 4th Step: Press the Return Key and you will see the results as shown below. The value returned is the circumference of the circle.

pi-function

Calculating Circle Diameter in Google Sheets using Pi Function

To evaluate the diameter of the circle, we will have to know the circumference value. Suppose if your circumference value of the circle is 20cm, then you simply follow the steps outlined below to know the diameter of the circle.

  • 1st Step: Launch the Google Spreadsheet on your device.
  • 2nd Step: Now move to the cell where you want to find the diameter value of a circle.
  • 3rd Step: Now use the formula =20*PI().
  • 4th Step: Press the “Enter” button and you will see the results as shown in the below image.

pi-function

Calculating Area of Circle in Google Sheets using Pi Function

Google Sheets also allows the user to calculate the area of the circle using the Pi function. To calculate the area of the circle using the Pi function, we will have to first know the area of the circle formula. The area of the circle formula is π * r².

Thus we have to know the radius value. To find the radius of the circle, we will have to divide the diameter by 2. In the previous sections, we have learned how to find the diameter of the circle. Use those formulas to find the diameter of the circle.

Now let us assume that the diameter value of the circle is 20cm. The steps to find the area of a circle in Google Sheets using this data is as follows:

  • 1st Step: Open the Google Sheet on your device.
  • 2nd Step: Now move to the cell where you want to find the area of the circle.
  • 3rd Step: Enter the formula = PI() * ( 20 / 2 )^2.
  • 4th Step: Press the Return key and you will find the results as shown below.

pi-function

How to Make a PI Symbol in Google Sheets?

We can easily generate the PI symbol in a Google Spreadsheet using the CHAR function. Follow the steps as listed below to generate the PI symbol in Google Sheets:

  • 1st Step: Open Google Sheets on your device where you want to generate the PI symbol.
  • 2nd Step: Move to the cell where you want to generate the PI symbol.
  • 3rd Step: Enter the character function formula as =CHAR (960).
  • 4th Step: Press the “Enter” key and you will see the results as shown below. Here you will see the PI symbol being generated in Google Sheets.

pi-function

Fun Fact: Google Sheets also lets users generate different pies. Want to know how to do this. Well, follow the steps outlined below:

  • 1st Step: Move to the cell where you want to generate the pie in Google Sheets.
  • 2nd Step: Simply enter the formula =CHAR(129383).
  • 3rd Step: Press the return key and you will see the results.

Find the sum and maximum value of the two column in excel file using Pandas

Here, let us look at how to read data from Excel, run some mathematical operations on it, and save it in a new column in DataFrame.

Let us take an example of demo.xlsx excel spreadsheet as shown below:

Find the sum and maximum value of the two column in excel file using Pandas

Here we add the sum of two columns ‘Age’ and ‘Id’ and get maximum value from it.

Program to Find the Sum and Maximum value of the Two Column in Excel File using Pandas in Python

Step1: Import module and read an Excel File

Approach:

  • Import pandas module as pd using the import keyword.
  • Read the excel file using the read_excel() function and store it as a DataFrame.
  • The Exit of the Program.

Below is the implementation:

# Import pandas module as pd using the import keyword
import pandas as pd
 
# Read the excel file using the read_excel() function and 
# store it as a DataFrame
data_frme = pd.read_excel('demo.xlsx')
data_frme

Output:

Program to Find the Sum and Maximum value of the Two Column in Excel File using Pandas in Python

Step2: Creating Two new columns for storing sum and maximum values

# Creating Two new columns which stores the sum and maximum values
data_frme['sum'] = None
data_frme['Max_values'] = None
data_frme

Output:

Creating Two new columns for storing sum and maximum values

Step3: Create an index to allow access to the needed columns.

Approach:

  • Set the index for required columns in the above dataframe and get their column index using the columns.get_loc() method.
  • Print the index values of the required columns.
  • The Exit of the Program.
# Set the index for required columns in a dataframe and get their column index
# using the columns.get_loc() method
Age_index = data_frme.columns.get_loc('Age')
Id_index = data_frme.columns.get_loc('Id')
sum_index = data_frme.columns.get_loc('sum')
Max_values_index = data_frme.columns.get_loc('Max_values')
# Print the index values of the required columns
print(Age_index, Id_index, sum_index, Max_values_index)

Output:

5 7 8 9

Step4: Selecting each row and perform sum, maximum values operations for both the columns.

for row in range(0, len(data_frme)):
    data_frme.iat[row, sum_index] = data_frme.iat[row,
                                      Age_index] + data_frme.iat[row, Id_index]
     
    if data_frme.iat[row, Age_index] > data_frme.iat[row, Id_index]:
        data_frme.iat[row, Max_values_index] = data_frme.iat[row, Age_index]
    else:
        data_frme.iat[row, Max_values_index] = data_frme.iat[row, Id_index]
data_frme

Output:

Create an index to allow access to the needed columns

Change value in Excel using Python

Here, we will use Python to update a value in an Excel spreadsheet.

1)Opening an Excel File:

openpyxl is a Python package that allows you to read and write Excel xlsx/xlsm/xltx/xltm files. It originated from the lack of an existing library that allowed Python to read and write the Office Open XML format natively. The openpyxl library is required for the following task. You can install the openpyxl module in Python by running the following command.

pip install openpyxl

Openpyxl:

The Openpyxl module in Python is used to work with Excel files without requiring third-party Microsoft application software. We can control Excel without having to open the application by using this module. It is used to perform excel tasks such as reading data from an excel file or writing data to an excel file, drawing charts, accessing an excel sheet, renaming the sheet, modifying (adding and deleting) the sheet, formatting, styling the sheet, and any other task. Openpyxl is a very efficient tool for handling these tasks for you.

Data scientists frequently use Openpyxl to perform various operations such as data copying, data mining, and data analysis.

Functions:

  • load_workbook(): This function reads the Excel spreadsheet
  • workbook.active: directs attention to the active sheet in the excel spreadsheet
  • workbook.save(): This function saves the workbook.

Program to Change value in Excel in Python

Example1

Let us take an example of demo.xlsx excel spreadsheet as shown below:

Change value in Excel using Python import load workbook

Approach:

  • Import load_workbook from openpyxl module using the import keyword.
  • Pass the filename as an argument to the load_workbook() function to load the excel file.
  • Apply active function for the above to open the workbook.
  • Modify the cell name that you want to alter.
  • Pass the filename as an argument to the save() function to save the file.
  • The Exit of the Program.

Below is the implementation:

# Import load_workbook from openpyxl module using the import keyword
from openpyxl import load_workbook
 
# Pass the filename as an argument to the load_workbook() function
# to load the excel file
workbook = load_workbook(filename="demo.xlsx")
 
# Apply active function for the above to open the workbook
sheet = workbook.active
 
# Modify the cell name that you want to alter.
sheet["A1"] = "Full Name"
 
# Pass the filename as an argument to the save() function to save the file
workbook.save(filename="demo.xlsx")

Output:

Change value in Excel using Python

Example2: Using xlwt, xlrd, xlutils

This package contains a number of tools for working with Excel files. Because these utilities may require either or both of the xlrd and xlwt packages, they are collected together here, separate from either package.

Installation of xlwt/xlrd/xlutils modules in Python:

pip install xlwt
pip install xlrd
pip install xlutils

Functions:

  • open_workbook(): It is a function that is used to read an Excel spreadsheet.
  • copy(): duplicates the contents of a worksheet.
  • get_sheet(): refers or points to a specific sheet in Excel workbook
  • write(): writes data to a file.
  • save(): This function saves the file.

Approach:

  • Import xlwt module using the import keyword.
  • Import xlrd module using the import keyword.
  • Import copy of xlutils.copy module using the import keyword.
  • Pass the filename as an argument to the open_workbook() function to load the excel file and store it in a variable.
  • Make a dulpicate copy of excel workbook using the copy() function and store it in another variable.
  • Open the sheet that you want to work with of the excel file by passing the number as an argument to the get_sheet() function.
  • Store it in another variable
  • Pass the row number, column number, and some random data as arguments to the write() function.
  • Save the workbook using the save() function.
  • The Exit of the Program.

Below is the implementation:

# Import xlwt module using the import keyword
import xlwt
# Import xlrd module using the import keyword
import xlrd
# Import copy of xlutils.copy module using the import keyword
from xlutils.copy import copy
 
# Pass the filename as an argument to the open_workbook() function
# to load the excel file and store it in a variable
read_book = xlrd.open_workbook('demo.xlsx')
 
# Make a dulpicate copy of excel workbook using the copy() function and store it in another variable
work_book = copy(read_book)
 
# Open the sheet that you want to work with of the excel file by passing 
# the number as an argument to the get_sheet() function
# Store it in another variable
work_sheet = work_book.get_sheet(0)
 
# Pass the row number, column number and some random data as arguments to the
# write() function 
work_sheet.write(0,1,'S.No')
 
# Save the workbook using the save() function
work_book.save('demo.xlsx')

Output:

Change value in Excel using Python using xlwt

How to Import an Excel File into Python using Pandas?

The dataset may not always be available in CSV format. As a result, Pandas includes functions for converting datasets in different formats to Data frames. The format of an excel file is ‘.xlsx’.

Before we begin, we must first install a few libraries as shown below:

pip install pandas
pip install xlrd

We must use the pandas.read_excel() function to import an Excel file into Python using Pandas.

Syntax:

pandas.read_excel(io, sheet_name=0, header=0, names=None,….)

Return Value: It returns a Dataframe or a dictionary of Dataframes.

Let us take an example of demo.xlsx excel spreadsheet as shown below:

How to Import an Excel File into Python using Pandas

How to Import an Excel File into Python using Pandas?

Example1: Reading an Excel file

Approach:

  • Import pandas module as pd using the import keyword.
  • Read the excel file using the read_excel() function and store it as a DataFrame.
  • The Exit of the Program.

Below is the implementation:

# Import pandas module as pd using the import keyword
import pandas as pd
 
# Read the excel file using the read_excel() function and 
# store it as a DataFrame
data_frme = pd.read_excel('demo.xlsx')
data_frme

Output:

Reading an Excel file

Example2: Selecting a specific column

Approach:

  • Import pandas module as pd using the import keyword.
  • Select a specific column of the excel file using the index_col by passing the column index as an argument to it.
  • The Exit of the Program.
# Import pandas module as pd using the import keyword
import pandas as pd
 
# Select a specific column of the excel file using the index_col by passing the 
# column index as an argument to it
data_frme= pd.read_excel("sample.xlsx",
                   index_col = 0)    
data_frme

Output:

Selecting a specific column

Example3: 

If we don’t want the initial heading of the columns, you can change it to indexes by using the “header” argument.

# Import pandas module as pd using the import keyword
import pandas as pd
 
# Read the excel file using the read_excel() function by passing the 
# file name and header= None as the arguments to it.
# (It modifies the header with indexes)
# store it as a DataFrame
data_frme= pd.read_excel('demo.xlsx',
                   header = None)  
data_frme

Output:

Read the excel file using the read excel

Example4: Changing the datatype of columns

If we wish to modify or change the data type of a certain column, use the “dtype” argument.

# Import pandas module as pd using the import keyword
import pandas as pd
 
# Change the datatype of coulums(Gender, Age) using the dtype parameter 
# and store it in a variable
data_frme=  pd.read_excel('demo.xlsx', 
                   dtype = {"Gender": str,
                            "Age":float})
data_frme

Output:

Changing the datatype of columns

Example5: 

If we have unknown values, you can handle them with the “na_values” argument. It will convert the previously specified unknown values to “NaN.”

# Import pandas module as pd using the import keyword
import pandas as pd
 
# Convert the unknown values to NaN using the na_values argument
data_frme= df = pd.read_excel('demo.xlsx', 
                   na_values =['Mara', 
                               'Philip'])
data_frme

Output:

Changing the datatype of columns nv values

Here we converted unknown/unwanted data (Mara,Philip) to NaN values.

How to Import Excel File and Find a Specific Column using Pandas?

In this post, we’ll look at how to import an excel file into a data frame and find a particular column.

Let us take an example of demo.xlsx excel spreadsheet which is shown below:

How to Import Excel File and Find a Specific Column using Pandas?

Approach:

  • Import pandas module as pd using the import keyword.
  • Read the excel file using the read_excel() function and store it as a DataFrame(creating a dataframe).
  • Check the particular column and print it using the head() function. The head() function gives only the first 5 rows of a file.
  • The Exit of the Program.

Step1: Read an Excel File and get the first 5 rows of it.

Below is the implementation:

# Import pandas module as pd using the import keyword
import pandas as pd
# Read the excel file using the read_excel() function and 
# store it as a DataFrame
data_frme = pd.read_excel('demo.xlsx')  
# Get the first 5 rows of the excel file using the head() function
data_frme.head()

Output:

Step2: Check the particular column and display the first 5 values in that column using the head() function.

Below is the implementation:

# Import pandas module as pd using the import keyword
import pandas as pd
# Read the excel file using the read_excel() function and 
# store it as a DataFrame
data_frme= df = pd.read_excel('demo.xlsx')  
# Check the particular column and display the first 5 values in that column using the 
# head() function
# Here check if the Gender is female and display the first 5 values in that column
data_frme[data_frme["Gender"] == 'Female'].head()

Output:

Step3: Do the same for other columns

1)Here check if the Age is greater than 30 and display the first 5 values in that column.

# Import pandas module as pd using the import keyword
import pandas as pd
# Read the excel file using the read_excel() function and 
# store it as a DataFrame
data_frme= df = pd.read_excel('demo.xlsx')  
# Check the particular column and display the first 5 values in that column using the 
# head() function
# Here check if the Age is greater than 30 and display the first 5 values in that column
data_frme[data_frme["Age"]>30].head()

Output:

2)Here check if the Country is ‘United States and display the first 5 values in that column

# Import pandas module as pd using the import keyword
import pandas as pd
# Read the excel file using the read_excel() function and 
# store it as a DataFrame
data_frme= df = pd.read_excel('demo.xlsx')  
# Check the particular column and display the first 5 values in that column using the 
# head() function
# Here check if the Country is 'United States and display the first 5 values in that column
data_frme[data_frme["Country"]=='United States'].head()

Output:

Python Program to Convert any Dates in Spreadsheets

In this tutorial, we’ll look at how to use Python to convert any Dates in Spreadsheets.

Let us take an example of demo.xlsx excel spreadsheet that is shown below:

Python Program to Convert any Dates in Spreadsheets

This file contains a single column called ‘Dates,’ which stores random dates from 2020 in various formats.

to_datetime() Method: To conduct date and time operations, we must first convert the column to a datetime data type.

dt.strftime() Method:

Then, using a value of “%Y-%m-%d” we utilise the dt and strftime methods to tell Python how to format the date. The example we used here is “% Y-% m-% “, where % Y is the whole year, % m is the month with two digits, and % d is the day with two digits.

Program to Convert any Dates in Spreadsheets in Python

Example1

Approach:

  • Import pandas module as pd using the import keyword.
  • Read the file and indicate which column contains the Dates using the read_excel() function and store it in a variable.
  • (here the file has only 1 column, hence we didn’t specify Dates column).
  • Convert the Dates column in the above file to YYYY-MM-DD format using the to_datetime,dt.strftime functions.
  • And store the formated dates output into another Excel file using the to_excel() function.
  • The Exit of the Program.

Below is the implementation:

# Import pandas module as pd using the import keyword 
import pandas as pd
  
# Read the file and indicate which column contains the Dates using the read_excel() function
# and store it in a variable
# (here the file has only 1 column, hence we didn't specify Dates column)
demo_file = pd.read_excel("demo.xlsx")
  
# Convert the Dates column in the above file to YYYY-MM-DD format using the to_datetime,dt.strftime functions
# And store the formated dates output into another Excel file using the to_excel() function.
demo_file["Dates"] = pd.to_datetime(
    demo_file["Dates"]).dt.strftime("%Y-%m-%d")
demo_file.to_excel("demo_format.xlsx")

Output:

Python Program to Convert any Dates in Spreadsheets 1

Different date formats:
Example: Monday, 16 May, 2020, 5:00 PM

“%A, %B %d” -> “Monday, May18”
“%d-%b-%y” -> “16-May-20”
“%d/%m/%Y” -> “16/05/2020”
“%b %d, %Y” -> “May 16, 2020”

      Directive            Description              Example
            %a Weekday as locale’s abbreviated name Sun, Mon,….,Sat(en_US);

So, Mo,……, Sa(de_DE)

            %A Weekday as locale full name Sunday, Monday, Tuesday,….., Saturday
            %w Weekday as a decimal number, where 0 represents Sunday and 6 represents Saturday 0, 1, 2, 3, 4, 5, 6
            %d The month’s day as a zero-padded decimal number 01, 02, 03, …………,31

Example2:

Now let us change the date format as shown below for the same dataset

Format:

 "%d %b, %Y"  --> "16 May, 2020"
# Import pandas module as pd using the import keyword 
import pandas as pd
  
# Read the file and indicate which column contains the Dates using the read_excel() function
# and store it in a variable
# (here the file has only 1 column, hence we didn't specify Dates column)
demo_file = pd.read_excel("demo.xlsx")
  
# Convert the Dates column in the above file to "D MMMM, YYYY" format using the to_datetime,dt.strftime functions
# And store the formated dates output into another Excel file using the to_excel() function.
demo_file["Dates"] = pd.to_datetime(
    demo_file["Dates"]).dt.strftime("%#d %B, %Y")
demo_file.to_excel("demo_format.xlsx")

Output:

Python Program to Convert any Dates in Spreadsheets 2

Example3

Now let us change the date format as shown below for the same dataset:

Format:

"%B %d, %Y" --> "May 16, 2020"
# Import pandas module as pd using the import keyword 
import pandas as pd
  
# Read the file and indicate which column contains the Dates using the read_excel() function
# and store it in a variable
# (here the file has only 1 column, hence we didn't specify Dates column)
demo_file = pd.read_excel("demo.xlsx")
  
# Convert the Dates column in the above file to "MMMM D, YYY" format using the to_datetime,dt.strftime functions
# And store the formated dates output into another Excel file using the to_excel() function.
demo_file["Dates"] = pd.to_datetime(
    demo_file["Dates"]).dt.strftime("%B %d, %Y")
demo_file.to_excel("demo1_format.xlsx")

Output:

Python Program to Convert any Dates in Spreadsheets 3