Complete List Of Things You Can Do With Excel - Featured Image - Someka Blog

Complete List of Things You Can Do With Excel

Excel is one of the most used softwares 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..)

1 Paper Excel Spot the Difference

Good old days..

Actually this is not only specific to Microsoft’s Excel but most of the spreadsheet softwares like open office or google sheets. However we will focus on Excel today, as it offers a 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:

2 Excel Dashboard Someka

For more details, check Someka – Custom Services

We have built this calculator for an app development company executive. He was changing the parameters he want and see 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:

3 Financial Modeling with Excel Joke - Someka Blog

Financial Modeling with Excel Evolution

 

Financial modeling is also being used to test the excel skills between 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: Feasibility Study Template in Excel – Trade Business Model

Trade Business Model - Feasibility Study Template in Excel - Template Screenshot Image 6 - Someka

This is a comprehensive Feasibility Study Excel Template for trade startups with sales projections, costs, financial calculations, charts, dashboard and more.

 

2. You can create professional reports and dashboards with nice looking charts and visualizations

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.

4 Reports and Charts in Excel - Someka Blog

Reports and Charts in Excel

 

Pivot Tables

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:

5 Pivot Tables in Excel Reporting - Someka Blog

Pivot Tables are quite useful

 

Dashboards

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 “changable” 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.

Like this:

6 Excel Dashboard Logic - Someka Blog

Excel Dashboard Logic

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:

7 Excel Dashboard - Someka Blog

Excel Dashboard from Someka Custom Services

 

Or a dashboard for a livestock feasibility study:

8 Excel Feasibility Study Dashboard - Someka Blog

Livestock Feasibility Study Dashboard

 

If you are interested in Sales Dashboards, you may want to check out our: Sales Report Template – Excel Dashboard for Sales Managers

Sales Report Template - Excel Dashboard for Sales Managers - Template Screenshot Image 1 - Someka

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 seperately)

I will not dive into VBA here since it is a detailed area. But there are some basic things I guess it will be beneficial to know for someone 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:
9 Excel User Form VBA Example - Someka Blog

Source: onlinepclearning.com

 

4. You can solve optimization problems and make statistical analysis (data analysis)

It is not surprising for a 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 the recent years with the development of powerful computers and improved softwares. We are collecting and recording much much more data compared to the past. Take a look at this chart to understand what I mean:

Source: hrboss.com

Source: hrboss.com

 

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 “Big Data” world. Lets 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 for 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

 

Solver

Have you ever heard of “optimization”? When we have more than one parameters which effect 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 example 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?”

11 Farmer Fence Optimization Problem Excel Solver Solution - Someka Blog

Farmer Fence Optimization Problem Excel Solver Solution

This is a very simple example to explain what solver does. But actually you can run much more complicated data sets with solver.

 

Regression Analysis

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 among the variables. For example, you may want to know what is the relation between the number of birds flied 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 analysis to see if there is any correlation.

It seems something like this:

You put your data:

12 Excel Regression Analysis Sample Data - Someka Blog

Sample Data

 

Run the regression from Analysis Toolpak:

13 Analysis ToolPak Excel - Someka Blog

Analysis ToolPak Excel

 

And get results something like this:

14 Excel Regression Analysis Results - Someka Blog

Excel Regression Analysis Results

 

Of course there are much more sophisticated softwares to run data analysis. However, there is a joke in business intelligence communities:

  • What is the most used feature in any business intelligence solution?
  • It is “Export to Excel”

 

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 todo lists or anything. Just open the excel and write down. Did we mention about that “paper alternative” thing? Oh yes, we did.

A lead list example:

15 Lead Lists in Excel - Someka Blog

Lead Lists in Excel

 

You can also convert PDF files into Excel files in order to make it easier to work on. This can be done with automatically with some softwares. But some pdf files cannot be processed automatically (like handwritten documents, scanned invoices etc). You will need to do it manually.

16 PDF to Excel - Someka Blog

PDF to Excel

 

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:

17 Oscars List in Excel Sample - Someka Blog

Oscars List in Excel Sample

 

Everybody loves lists. And we have put together some useful lists from Fortune 500 to Nobel awards history. Check it out, it is free:

Useful Lists Collection: Fortune 500 Companies, Top Movies,Country Facts, NBA Awards and more

 

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 a RPG game. Take a look at this:

18 Arena XLSM Excel Games - Someka Blog

Source: carywalkin.ca

 

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 😉

Another example:

19 Flight Simulator Excel Games - Someka Blog

Source: excelunusual.com

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 benefit of this to create interactive tools.

One example comes to my mind is this spreadsheet, guys from San Francisco have prepared:

 

20 Excel Everest Interactive Education - Someka Blog

Source: exceleverest.com

I haven’t tried it myself but an Excel tutorial in Excel. Liked the idea!

 

Quizzes are good tools for interactive learning and you can prepare in Excel as well. A quiz master template from indzara.com:

 

21 Indzara Excel Quiz Master - Someka Blog

Source: indzara.com

 

A periodic table in excel which we have prepared recently:

22 Periodic Table in Excel - Someka Blog

Someka Periodic Table Template

 

You can learn Excel in Excel!

As said: Practice Makes Perfect!

You can test your Excel skills in Excel with: Excel Formulas Trainer:

someka excel formulas full trainer inpost

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 many more excel formulas by doing. If you like the idea “learning by doing”, then it is worth to check.

 

8. You can prepare “cheatsheets” in Excel

By cheatsheets 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 cheatsheet:

23 HTML CheatSheet in Excel - Someka Blog

Created by Jacob Seidelin

 

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 cheatsheet made in Excel:

24 HTML Cheat Sheet in Excel - Someka Blog

Created by Dave Child

This one is from Dave Child (cheatography.com) and I was also using this one I first learned HTML.

 

Last example is an Excel Cheatsheet made for Excel shortcuts:

24 Excel Shortcuts CheatSheet - Someka Blog

Source: alchemex.com

 

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 for trying and using these kind 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 in the web.

An example from vertex42.com:

Source: vertex42.com

Source: vertex42.com

 

I just found out a reporting structure mockup I have prepared in Excel once upon a time:

26 Making Mockups in Excel - Someka Blog

A bit blurry but explains the purpose

 

By the way, did you see our Automatic Organization Chart Generator?

Automatic Organization Chart Maker - Info - Someka Blog

This is an Excel Template which lets you create organization charts from Excel lists with a click of a button. Can be useful for small business owners and Human Resources departments. Find details here: Automatic Organization Chart Maker – Excel Template

 

10. You can fetch live data from 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)

 

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 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 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 of your Excel file because it has 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.

 

Conclusion

As 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:

Excel Templates and Spreadsheets – Someka


Now it is time to follow us on Social Media:

 

This Post Has 6 Comments

  1. Just wish to say your article is as amazing. The clearness in your post is
    simply spectacular and i can assume you are an expert on this subject.
    Well with your permission allow me to grab your RSS feed
    to keep up to date with forthcoming post. Thanks a million and
    please keep up the rewarding work. math solver, Toby,

  2. Great information, but how do you do all these creative tasks? Are there tutorials available that provide step-by-step instructions? I uses Excel daily and wasn’t aware it is such a robust tool.

  3. Good post! thanks for sharing this. As a finance expert I laughed at the financial modeling wit 😉 keep up the good stuff.

    1. Thank you Richard. Glad you liked it. We will continue to come up with the best stuff about excel and other related subjects. Follow us at social media 😉

Leave a Reply

Your email address will not be published. Required fields are marked *