This isn’t Excel…
If you are interested in Excel, you most likely are pursuing a career or hobby in regards to data. By now, you probably have realized you can do anything in Excel, and there are millions of resources out there to help you through it.
You now need to progress, which means leaving Excel behind and moving into Databases and code. I recommend starting with Python. While it is code, it is easier than most.
How do I learn this?
Again, millions of resources online to get you started. Python is very popular, and has a package feature which allows smart people to do the hard work, and you just reap the benefits. Packages are like formulas in Excel - you don’t need to understand the background code to use them.
This allows you do things like image recognition and machine learning. Relatively easily.
But before we get ahead of ourselves, let’s start small. I got bored of the online tutorials, as I learn by doing (particularly if it can be related to work or something I find useful). The best way I found to learn it, was get Python to work with Excel. This way, you can start to see the benefit of Python without being scared away from things like dataframes and CSV files.
This allows you to talk to Excel very easily.
(I’m assuming you have already figured out the basics of things like Pip Install and have your packages all sorted out. If not, I understand this is a tricky bit and something I haven’t even fully figured out. The best solution is to download the fantastic free editor, Pycharm. This does all the hard work for you - no more annoying pip download. Find a tutorial on how to get started. )
Install XLWINGS using PIP or Pycharm.
import xlwings as xw wb = xw.Book('forecast.xlsx') sht = wb.sheets['data'] sht.range('A1').value = 'Excell-en.com' wb.save()
Assuming you have a workbook called “forecast” in your root directory (or the directory with your script if using Pycharm) this will write in Cell A1.
Why not create a loop as well, to list data from a python list into Excel?
import xlwings as xw wb = xw.Book('forecast.xlsx') #Workbook location sht = wb.sheets['data'] #Sheet Name list = [1,2,3,4,5,6,7,8,9,10] #Table column = 'A' #Column add data to row = 1 #Starting row for i in list: #for each number in the list cell = column + str(row) #this just joins the A and 1 together. 1 is wrapped in STR, as you can't join an interger and string together sht.range(cell).value = i #in the data sheet, range A1, it will put in the first number in the list row = row + 1 #this increases the row number by 1 #code now loops back to start of for loop, and keeps going until it reaches the end of the list wb.save() #saves without prompting
What I showed you above is very basic. However just like Excel, you can google and look at the XLWINGs website to figure out how do pretty much anything.
For example you can launch macros with Python, add in formulas, formatting charts etc. Why not store data in Excel from Python, sync excel to google sheets and create a live dashboard of data?
I will write more blogs in the future about useful Python tools for Excel.