Extracting data is one of the powerful sides of Excel. Read this post and learn how web scraping in Excel saves a lot of time!
Last Updated: Aug 19, 2019
- Tools, Calculators and Simulations
- Dashboards and Reports with Charts
- Automate Jobs with VBA macros
- Solver Add-in & Statistical Analysis
- Data Entry and Lists
- Games in Excel!
- Educational use with Interactive features
- Create Cheatsheets with Excel
- Diagrams, Mockups, Gantt Charts
- Fetch live data from web
- Excel as a Database
Excel is one of the most used software in today’s digital world. Most of the people quickly open up an Excel file when they need to write or calculate anything. It is like “paper”. (remember those graph notebooks from school times..)
Actually, this is not only specific to Microsoft’s Excel but most of the spreadsheet software like open office or google sheets. However, we will focus on Excel today, as it offers huge flexibility you will discover below.
1. You can create tools, calculators and simulations with Excel
Let’s start with the main usage areas of Excel. As we all know, spreadsheets are designed to make calculations easier. So they contain “formulas”. They allow us to make basic math like summing, multiplying, finding average as well as advanced calculations like regression analysis, conversions and so on.
When we combine these powerful math features with some tables, lists or other UI elements, we can come up with a calculator. And most of the time they will be dynamic (meaning that when you change a parameter all the rest of the calculations will adapt accordingly)
Below see an example from our past studies as Someka:
We have built this calculator for an app development company executive. He was changing the parameters he wants and sees the outcomes immediately.
This is great especially when you try to make big “models” in excel. Financial Modeling is one of the most used application areas of these big models. If we tried to do this with pen – paper (which used to be the way once upon a time) it would be horrible I guess:
Financial modeling is also being used to test the excel skills of experts. They even make a competition for it: ModelOff
We also have a tool for startups to make a feasibility study playing with their own variables: Mobile App Financial Model Template:
This is a comprehensive Feasibility Study Excel Template for app startups with downloads projections, costs, financial calculations, charts, dashboard and more.
2. You can create professional reports and dashboards with nice looking charts and visualizations
The business world is demanding. It is not enough just to make the calculations, set up your tables and write the text. You have to create pie charts, trends, line graphs and many more. Whether you are getting prepared for your pitch or make a presentation in your company, you can use Excel’s chart features.
One of the greatest features which Excel offers is Pivot tables. This is an advanced Excel tool which helps you create dynamic summary reports from raw data very easily. After you create your table you can play with parameters easily with a drag and drop interface.
It looks like this:
Complex excel models do have lots of variables, calculations and settings. And instead of managing all variables one by one on different sheets, different places it is a very good idea to put them together like a “control panel”. You can think dashboards as cockpits of planes.
Recently dashboards became very popular. There are lots of training videos about how to build and design control panels for our excel models. Actually, they are not so different from the rest of the calculations. But the main idea is: if there is something you may want to change later on, don’t write it directly in the formula but bind it to a variable.
Let’s say you are building a sales report for your manager. He asks you to make the file “changeable” so that he can see the results in US dollar or Euros according to the situation. Instead of writing an Fx rate into the calculations, you should bind this to a cell which you can play with later on.
This may seem so obvious to some of you. But this is the basic approach of all dashboards in excel files. Of course, you can improve it with more complex formulas, buttons, cool charts and even VBA but the main idea stands still.
Here is an example of a complete set of dashboard:
Or a dashboard for a livestock feasibility study:
If you are interested in Sales Dashboards, you may want to check out our: Sales Report Template – Excel Dashboard for Sales Managers
This is an interactive Sales Report Template in Excel. Features a dashboard with profitability, sales analysis and charts.
3. You can code in Excel with VBA (Visual Basic for Applications)
Most of the users who use Excel extensively are already coding. But if you ask them whether they know how to code most probably they will say no. Of course, writing formulas is a very small part of the things you can do with VBA. It is a strong programming language which lets you create small scripts (macros), user forms, user-defined functions, add-ins and even games! (which we will touch below separately)
I will not dive into VBA here since it is a detailed area. But there are some basic things that will be beneficial to know for those who use Excel often:
- You can record macros for repeating jobs: You don’t need to code from scratch. Just click on the record macro button and it will write the code for you in the background. (If you want, you can modify later on)
- It extends the borders of Excel world. If you feel like you are limited somehow in Excel, you are more like an advanced user. It is time to get a little bit into VBA.
- You can create user forms with VBA only. If you see something like this, know that it is using VBA:
VBA is quite powerful and if you work with Excel extensively you won’t regret learning a bit.
4. You can solve optimization problems and make statistical analysis (data analysis)
It is not surprising for spreadsheet software like Excel to offer advanced math techniques to make more complicated studies. (To be honest, I am not a statistics expert but with an engineering background, I will try to do my best to explain the basics. Feel free to correct me if I’m wrong)
Data analysis is a trending concept for recent years with the development of powerful computers and improved software. We are collecting and recording much much more data compared to the past. Take a look at this chart to understand what I mean:
Especially this part:
“more data has been created in the past two years than in the entire previous history of the human race”
It is a bit frightening, isn’t it? Ok, we are not going to dive into the “Big Data” world. Let’s get back to our humble excel world.
As we collect this much data, some people will want to analyze it. Otherwise, it makes no sense to spend billions of dollars on those data centers. Excel has built-in functions for basic descriptive statistics methods like Mean, Median, Mode, Standard Deviation, Variance etc. But if we want to go a bit further I will mention two Excel features (actually add-ins) at this step: Solver and Regression Analysis
Have you ever heard of “optimization”? When we have more than one parameters which affect the outcome, we can only have a “most optimized” solution rather than a “maximum solution”. This may sound weird but it is very valid in our daily lives.
One of the simplest and popular examples is: Farmer Fence Optimization Problem
“A farmer owns 500 meters of fence and wants to enclose the largest possible rectangular area. How should he use his fence?”
This is a very simple example to explain what solver does. But actually, you can run much more complicated data sets with Solver.
Since this is a bit advanced topic for this blog post, I will only touch the surface.
In most simple terms, regression analysis helps you find the correlation between the variables. For example, you may want to know what is the relation between the number of birds flown over your head and money you earned today. (sorry for the silly example. No, I am not curious about it 🙂 You will need to gather sample data and put in an analysis to see if there is any correlation.
It seems something like this:
You put your data:
Run the regression from Analysis Toolpak:
And get results something like this:
Of course, there is much more sophisticated software to run data analysis. However, there is a joke in business intelligence communities:
- What is the most used feature of any business intelligence solution?
- It is “Export to Excel”
Looks like we won’t stop using Excel anytime soon.
5. You can collect information, make data entry and create lists
Coming back from boring data analysis world, let’s mention the simplest and most handy usage area of excel: Make Lists!
It is already self-explaining so I won’t bother with the details. When you want to list down some simple data, take notes, create to-do lists or anything. Just open the excel and write down. Did we mention that “paper alternative” thing? Oh yes, we did.
A lead list example:
You can also convert PDF files into Excel files in order to make it easier to work on. This can be done automatically with some software. But some pdf files cannot be processed automatically (like handwritten documents, scanned invoices etc). You will need to do it manually.
When you want to play with the data on a web page, you can easily copy-paste it into an excel file and then you can sort, filter or do anything you want:
For example, Oscars awards since 2000:
Everybody loves lists. And we have put together some useful lists from Fortune 500 to Nobel awards history. Check it out, it is free:
6. Games in Excel!
We already mentioned this in VBA section above. But it is worth to talk a bit more.
Visual Basic allows you to code complex things like games as well. But of course, don’t expect a GTA or FIFA. Things like chess, sudoku or Monopoly is OK. But, a few people have gone far and created more complicated things, like an RPG game. Take a look at this:
This game has been created by an accountant, Cary Walkin. I know it doesn’t look great but it is in Excel! (you can play it at the office 😉
A flight simulator in Excel?? Is it the same thing we use to sum up the sales figures? Lol yeah.
You can also embed flash games into Excel (like Super Mario, Angry Birds or whatever) But I count them off as they are not built with VBA.
7. You can use Excel for educational purposes with interactive features
As we mentioned in Financial Modeling section, Excel is quite good for creating dynamic results according to the inputs. We get the benefit of this to create interactive tools.
One example comes to my mind is this spreadsheet, guys from San Francisco have prepared:
I haven’t tried it myself but an Excel tutorial in Excel. Liked the idea!
Another similar interactive Excel learning tool is hotkeyexcel.com:
Actually, this is not completely in Excel and works as separate software but I liked how they combine the Excel training with gamification features.
Quizzes are good tools for interactive learning and you can prepare in Excel as well. A quizmaster template from indzara.com:
A periodic table in excel which we have prepared recently:
You can learn Excel in Excel!
As said: Practice Makes Perfect!
You can test your Excel skills in Excel with Excel Formulas Trainer:
This is actually an Excel template prepared with VBA macros and basically works as a practice worksheet. It has 30 sections and around 100 questions. You can learn VLOOKUP, IF and much more excel formulas by doing. If you like the idea “learning by doing”, then it is worth to check.
Also, this online course from GoSkills is for everyone as well, covering beginner, intermediate and advanced lessons.
8. You can prepare “cheat sheets” in Excel
By cheat sheets, we don’t refer to the piece of paper with information written down on it that an unethical person might create if they weren’t prepared for a test. What we mean is a reference tool that provides simple, brief instructions for accomplishing a specific task. We use this term because it is highly popular recently.
For example, this is a cheat sheet:
These compacted and summarized info is very useful in many aspects. When you try to memorize things, lookup, reference etc. And can be easily created with Excel. Let’s make a Google search for a cheat sheet made in Excel:
This one is from Dave Child (cheatography.com) and I was also using this one I first learned HTML.
The last example is an Excel Cheat sheet made for Excel shortcuts:
Of course, if you are looking for stylish infographics and cheat sheets, you should check out design software.
9. You can prepare Diagrams, Mockups or Gantt Charts
I know Excel is maybe not the best tool to do these. There are great programs or websites to make mockups, diagrams, brainstorming, mind-mapping or project scheduling. But there are habits as well. Even though I am very open to try and use these kinds of brand new tools, I find myself using excel for a mockup or a mind map. (select shapes, put notes, put arrows, change colors etc. Omg it is tedious)
Gantt charts can be a bit old-school as agile project management methods are increasing in popularity, they are still being used widely. There are several Gantt chart excel templates on the web.
A Gantt chart example from vertex42.com:
I just found out a reporting structure mockup I have prepared in Excel once upon a time:
By the way, did you see our Automatic Organization Chart Generator?
This is an Excel Template which lets you create organization charts from Excel lists with a click of a button. It can be useful for small business owners and Human Resources departments. Find details here: Automatic Organization Chart Maker – Excel Template
These type of charts are directly related to Excel as most of the companies already keep their data in spreadsheets. But I also know people who even build their website mockups in Excel (with links to other sections, placement of buttons, sliders etc.).
10. You can fetch live data from the web into Excel
Sometimes you may need your excel files to be updated automatically from a live data source. For example, if you are making a stock market analysis and want the latest data of some stock prices at NYSE, you can connect your Excel file to a data feed and let it take the latest info automatically (unless you want to input them one by one!)
As this is a comprehensive topic I will leave it for another post. But here is a few things you can fetch into excel:
- Stock prices
- Match results of soccer, NBA, NFL or any sports games (from live score sites)
- Fx rates
- Real-time flight data of airports
- Any info in a shared database (whether it is your company intranet or public)
This topic is getting more and more important as most data is kept on cloud systems. We don’t download info bits to our computers as we used to do in the past. So, Microsoft is working hard to improve the web integration of Excel.
11. Use Excel as a simple database
Yes, it is not the best idea to use Excel as a database. Because it is not designed for this purpose. Queries will take a long time especially when data gets bigger. It can be unreliable sometimes and not very secure. It is all accepted. However, we are not always after a complete set of database system and it can serve us as a mini-warehouse for our little data.
For example, if you keep records of your invoice data and want to make some sales analysis, it can be a good starting point. If later, you want to see more details, want to record more breakdowns you will need to move to a “real database”. It can be Access, SQL or anything. Just keep an eye on your Excel file because it has a maximum of 1 million rows. Some of you may say “hey, it is more than enough, isn’t it?” Generally yes. But you cannot believe how data increase in size when you want to see details. I remember when I was working as an analyst in a game development company, we were holding records of 1+ billion rows of data.
As the internet gets more available for everybody people started to use collaboration platforms more than before. In this aspect, online spreadsheet applications, like Google Sheets, increase in popularity and stands as a competitor to Microsoft’s Excel. Other free alternatives like open office or libre office are also popular. But if you need the advanced functionalities of Excel there is still no substitute.
Microsoft is improving the software actively. PowerPivot, Power BI and Excel Online are all brand new features they developed recently. We will wait and see how things evolve in the following years. (investintech.com has made interviews with Excel experts about the future of Excel)
I tried to cover most of the things that can be done with Excel. If I have missed anything or if you find any error, let me know by commenting down or sending an email.
Also, don’t forget to check our Excel Templates Collection. You may find something useful for yourself: