It is almost weekend. I am sure most of you have plans (if you are USA, wish you happy 4th of July). As for me, I am going on a 80KM (50 mile) bicycle trip to a nearby lake to watch birds on Saturday morning. On Sunday, we (kids & I) are planning to make a scrapbook from our Australian vacation experiences.
So let me keep this nice & simple.
What is the coolest thing you made with Excel?
Go ahead and share your answers in the comments area.
PS: In case you are not able to watch fireworks on 4th of July, here is 4thofjulyfireworks.xlsm for you.
PPS: If you want to showcase your coolest Excel thing on Chandoo.org blog, please indicate so in the comments.
59 Responses to “What is the coolest thing you made with Excel? [weekend poll]”
I once created a conditional dropdown dependent on another dropdown and modified the two picklists to make them dynamic.
So when I beta'd it, I had two dropdowns. One that had the first list as “Produce Type” and the options were “Fruit” or “Vegetables”. Based on this selection in the first list, the second list gave options for either fruit (apples, pears, oranges) or vegetables (potatoes, carrots, onions). I did this by defining a named range using OFFSET MATCHes.
So that was the dependent data validation dropdown part. I then wanted to make the lists dynamic so that if I added new fruit or veg to the lists, the dropdown would pick up those items without me having to change the list range in data validation. This was done by inserting a COUNTA into the OFFSET MATCHes so that it would pick up everything to the last populated line i.e. the most recently added fruit or veg item.
The resulting named range looked like this:
=OFFSET(DynDepDataVals!$D$3,0,MATCH(DynDepDataVals!$A19,DynDepDataVals!$E$2:$F$2,0),COUNTA(OFFSET(DynDepDataVals!$D:$D,0,MATCH(DynDepDataVals!$A19,DynDepDataVals!$E$2:$F$2,2)))-1,1)
The Produce named range for either "Fruit" or "Veg" was simply =DynDepDataVals!$D$3:$D$4
With Produce Type selection in column A, the Item selection in B, the Produce List picklist in D, the Fruit picklist in E and Veg picklist in F.
I can email the spreadsheet if anyone wants it but the formula above is the essence of it.
Ben.
I'd really like to see the spreadsheet,
If you could upload it somewhere that'd be great.
-BH
Hi BH,
I can't upload files from work but feel free to email me at bendimech@hotmail.com and I can send it to you.
Ben.
great idea. Can you send to me?
I once made a football/soccer simulation for the top 4 english football leagues. The user could rate each teams strength on the scale of 0-100 or just use the strength made by a formula. Then simulate each round. Also there was a league cup involved.
If you are interested, here you can download it. The article is in Icelandic but the file is all in english: http://excel.is/2013/08/enski-fotboltinn-hermun-fyrir-timabilid-2013-2014/
I create a fully oriented project programming Excel Snake game.
Blog post: http://ambienteoffice.com.br/blog/jogo-da-serpente-em-excel/
Direct download: http://ambienteoffice.com.br/wp-content/uploads/jogo-da-serpente-em-excel/jogo-da-serpente-em-excel.xlsm
*object oriented programming
That is very cool!
Oh, that is soooo cool. Thanks for sharing - this made me smile (while playing)
Very cool! Gave me a lovely dose of nostalgia
I organise a recreational 5-a-side football game every Friday. My "Friday Footy organising spreadsheet" has become the stuff of legend! Here's a summary of the things it does for me:
- Keeps a list of players (currently 26) who are part of the group
- Records the availability responses of each player on each given week
- Maintains a priority list which determines which 10 players will play, promoting the (available) players who missed out to the top for the following week's game
- Records the teams and outcome of each game, producing various statistics for each player (wins, draws, losses, goals etc.)
- Maintains a record of which players have played together/against each other, and how many times. Uses this to suggest team combinations which will ensure variety
- Records player votes for "Man of the Match", assigning a weekly winner
- Automatically exports html files of player lists and priority lists, for weekly distribution, and statistics for use at the end-of-year awards dinner
I'm pretty proud of it, and it's always a work-in progress! Maybe one day I'll package it up and add instructions so others can use it.
Do you have a copy of that workbook that you can share?
Love football and spreadsheet. So combined both and have made a Fifa post. Thanks to Chandoo, it got posted on chandoo.org
chandoo.org/wp/2014/08/05/fifa-2014-worldcup-dashboard/
🙂
I worked out the formula to calculate the net monthly salary for a UK-based worker, just by entering the gross annual salary into a target cell. Useful for helping me to forecast my finances for the next few years, especially after buying our first house and combining this with a mortgage amortization table...
That sound's great! would you mind share it with us? I will love to know more about forecasting
I created an Excel addin for charting, reporting, standardization,... called Zebra BI 🙂
https://zebra.bi/
I am trying current version now. Looks very cool, can't wrap my head around how did you do this :).
And why Zebra?
I just created an Excel template for calculating the required width for a Superyacht marina berths. I created it from an algerbraic formula I found in a report. It also contains a section for converting meters to feet and vice versa.
I created a client implementation document management system for a financial services firm that serves commercial clients. Implementations can get very complex. Depending on the particular services that a client may select, the combination of agreements, addenda, pricing schedules, and workflow forms required will vary. Also the business rules, workflow, routing, approval, use and archiving of each of the forms and documents can vary. So, I created an electronic guide that allows the sales person to provides answers to some simple questions (pick list and check box) to select products and services for implementation. The guide shows them graphically and in list format which agreements are needed, pre-populates the common data elements (e.g. name, address, etc) across all relevant documents, and serves as a workflow management tool to make it easier to ensure the process maintains accuracy, organization and timely completion. It even includes thumbnail versions of the documents that can be enlarged for viewing as a user reference. Electronic documents are dynamically hidden or displayed based on need and the products selected. At the outset of the project, I researched online without success, hoping to find an existing document management system based on MS Office. All I found were pricy, complex document management systems that had to be installed on a server at an enterprise level. So, I ended up building my own solution.
K Temple, this solution is based on Excel only? how do you manage the documents from Excel? I would like to know more about your Project, it seems awesome!
http://datascopic.net/be-calc/
My break-even calculator that's embedded in a web page. My friend was planning to open her chiropractic clinic and wanted a way to alter variables like:
- Amount of initial investment
- Lowest fee per client visit
- Highest fee per client visit
- Number of visits per month
The details are that make this cool. If a user inputs $50/visit as the lowest, there is validation preventing the highest amount from being lower than $50.
Also, conditional formatting changes so that red cells mean she hasn't broken even, and blue cells show where she's recovered her investment.
I was able to create a couple interactive dashboards that management now uses (for the first time) on a regular basis! Thanks to Chandoo and others he recommended)
Chandoo,
self-promotion isn’t my cup of tea, but with today’s post you asked for it…
Here are the four most interesting Excel posts on my blog this year (so far):
Word Clouds in Excel
http://www.clearlyandsimply.com/clearly_and_simply/2015/02/word-clouds-with-microsoft-excel.html
Highlighting on Excel Dashboards
http://www.clearlyandsimply.com/clearly_and_simply/2015/03/highlighting-across-excel-dashboards.html
Selecting and highlighting areas on an Excel map
http://www.clearlyandsimply.com/clearly_and_simply/2015/05/selecting-and-highlighting-areas-on-excel-charts.html
Cartograms in Excel
http://www.clearlyandsimply.com/clearly_and_simply/2015/06/cartograms-in-microsoft-excel.html
Fantastic work!
Wow, this brings back memories - great question. One day I just had this dumb idea that I could simulate Pascal's Marble run in Excel. The idea is you drop a ball from the top and it can fall left or right. As it progresses through the puzzle the balls fall left or right finally collecting in buckets at the bottom based on probability. My puzzle uses Excel Random function to decide which way to fall.
Anyway, here is the workbook. I am not overly proud of the VBA but it works 🙂
https://dl.dropboxusercontent.com/u/30711565/falling%20balls.xls
I have a second more recent submission. I created a map of the world using conditional formatting. You can read about that one at my blog post on the topic here http://www.powerpivotpro.com/2015/05/power-query-super-charges-the-internet/
I've done many odd things with XL, and since I'm a bit odd myself, thay all seem cool to me. One that is particularly odd for this forum is an XL app that I'm pretty sure 99% of readers have no idea is even possible with XL. Would you believe XL can list what is installed on your PC, or change things on remote PCs, or remove user IDs from networks?
This app is something network admins would love or those curious to see what is possible. https://sites.google.com/site/beyondexcel/project-updates/exposingsystemsecretswithvbaandwmiapi
About halfway through that post is a link to WMI Query.xlsm where you can download the app and play with it.
Hi
I created a forecasting tool to analyze stock run outs based on forecast. While doing this i can also monitor register sales, store SOH and my warehouse SOH for last 3 years to determine if i am likely to have overstocks or customers not ordering enough. It has helped me by reducing months cover from 4 to 1.59 per month meaning stock is turning over quickly
Can you share with me
https://www.dropbox.com/s/jkegdx4y9dx5i74/Emision%20de%20Letras.xlsm?dl=0
most of my work is confidential but i can share this letter of credit ready to print
lately i have been doing customized printable random bingo cards for casinos you run the macro and in 2 minutes you get 200 printable bingo cards.
excel is awesome!!!
See https://dhexcel1.wordpress.com/2015/03/31/bingo/. It's free.
I created a pop-up calendar based entirely out of shapes (no user forms) This enabled me to position it directly under the selected cell, regardless of where it is on the sheets. The VBA included a hidden calendar that would re-create the main one should a user accidentally delete it. The calendar could be used on any workbook as another macro was assigned to recreate all the assigned calendar macros. It worked really well
My 3D Dancing pendulums for sure:
http://chandoo.org/wp/2011/07/06/3d-dancing-pendulums/
Yes they were very impressive .... until i saw them i had no idea things like this could be done using excel
Longest formula in the world LOL ...... Well has to be close if not longest .... Formula length of 7,867 of 8,192 characthers allowed by excel
Saw the ... "How to reverse string in Excel" on the forum and had to give it a try .... as it is ment to be not possible to make a dynamic formula to do this .... I managed to make a dynamic formula that works up to a string of 35 characthers long .... not practical as using vba would be the option to go with .... but i enjoyed figuring out how it could be done and how big a string it would work on ....
can be seen here in the very last post on this thread ...
dont know would it be worth showcasing though ... would take up most of a page to fit the formula on LOL LOL
http://chandoo.org/forum/threads/how-to-reverse-a-string-in-excel.23674/#post-143351
I tried to make a guitar tabbing software 🙂
http://www.bookkempt.com/2015/06/guitar-tablature-in-excel.html
I Created many games and simulations till now.
Battleships
Blackjack
Cricket
But my best creation is a Cricket game/simulator using macros and few formulas.
I was very pleased when I created the geographic heat map in Excel. The technique itself is not sophisticated or super smart, but it works. 🙂
http://indzara.com/2013/04/geographic-heat-map-india-excel-template/
Thanks.
20 years ago I created Science Pak 1.0. It included a key molecular weight UDF, along with many other advanced scientific calculators. It was named at the time by PC Magazine as one of the top 1000 downloadable files on the Internet.
Along with Peter Bartholomew, I created an Excel version of the viral game 2048:
http://www.spreadsheet1.com/2048-game-version-for-excel.html
Published an app to lock VBA projects securely without external dependencies:
http://www.spreadsheet1.com/unviewable-vba-project-app-for-excel.html
Manage the Ribbon Commander framework, which offers ribbon customization from VBA and a suite of FREE bundled addins:
http://www.spreadsheet1.com/ribbon-commander.html
I made a gearbox/car info calculator.
I was trying to explain gearboxes to someone and realised I knew enough to understand it, but not enough to explain it, so built this toy..
https://dl.dropboxusercontent.com/u/8021077/Slinkys_Vehicle_Data_Calculator.xlsx
The coolest thing that I have created thus far has been the employee leave tracker.
It automatically calculates the dates of the US holidays that my company gets off, along with providing a summary of the days taken and left that you can sort by department or person. In addition there is an overview of the entire year for any selected person, that color codes the days based on what type of leave has been taken. Everything is controlled from the leave tracker tab, and the Days for each person gets updated at the beginning of the year, or when a person starts. It calculates the next workdays which is what highlights the calendar, and then allows for entering of the days, to account for half days taken, which populates on the summary tab and calculates all of the days taken and remaining.
http://1drv.ms/1HHABDN
Grade calculator for College Speech Class
I created a calculator which my college students can download from the class website in order to calculate their class grade at any point in the semester. When a student enters his or her grade points on my worksheet, my Excel “calculator” automatically adds up the total points possible at that point in the semester and divides by the student’s personal point total to determine their percentage grade. (Pretty straight forward.) However, because I drop the lowest quiz score, the calculator also excludes the lowest quiz score to date.
This saves me a lot of time answering, "What's my current grade?" and allows students to calculate how many points they need on future assignments to reach their grade goal.
Excellent calculator slinky.
That reminded me of a calculator I created when I bored and stroked the 4.0L engine of my 2000 Jeep Cherokee.
I first gathered all the data on the various components in separate sheets and then started working on the calculator page. It took dozens of hours to get to where I could finally make very good estimates of what I could expect of the engine with a myriad of choices for the different components. Only two calculations were "guestimates", resulting horsepower and torque as I found no formulas to accurately determine them.
I bored and stroked the engine to 4.6L and it performs beautifully. If only I had chosen a torquer cam rather than the stronger one (I couldn't resist), I would get better gas mileage.
The calculator enabled me to select and buy components knowing what parts would work together and it performed beautifully.
I started roasting my own coffee in 2009 and decided to track the temperature and air speed of the roast profile with an Arduino with two sensors and a data logger.
I set up the Arduino, coded the data logger app and then worked on the Excel part of it.It was before I learned of data tables, so I used dynamic named ranges, somewhat easier for some tasks, but limited for others. I created a User Form with two drop downs allowing me to select the country of origin and then the particular farm or bean.
Hunting around the web for a way to automatically import the data into Excel proved to be a surprising challenge, but I got it to work.
I would insert the SD card in the computer, launch Excel, and then my program, and it would automatically copy the data to three ranges, Time, Temp, and Air_Vel, then chart the two lines for temperature and air speed versus time.
With this, I could easily see that as the roast progressed and the beans became lighter, turning down the fan would increase the temperature as I suspected.
I never figured out how to plot two single points for "1st Crack" and "2nd Crack" versus time. They only showed up at the first point in time.
This one was coded in vba versus the "Dynamic Compression Calculator" I mention in my post on Boring and Stroking my Jeep Cherokee.
I made a social media dashboard combining a couple of things:
1. Dynamic charts with check boxes to show YTD fan growth on different social channels
http://chandoo.org/wp/2010/08/31/dynamic-chart-with-check-boxes/
2. Data validation to let users pick a month for which they'd like to see metrics
3. V/HLookups + Charts to show YTD engagements and Month-on-month growth
4. Custom number formatting with colors and +/- signs for positive and negative month-on-month changed
One of my favorites is my "Camera Tool Selfie" code mentioned in a Chandoo post a few months back. Another is my MenuRighter addin, which allows extreme customization of Excel's right-click menus. Both of those are easily found with an internet search.
I've also been writing a lot of tools that let me quickly manipulate the SQL in data-connected tables. Here's one example: http://yoursumbuddy.com/importing-sql-files-into-data-connections/
here is a french Bingo style .
https://dl.dropboxusercontent.com/u/26284703/GD_Bingo3.xlsm
here is a step by step Sudoku (french UI)
https://dl.dropboxusercontent.com/u/26284703/SAO.xlsx
here is a Rubik simulator :
https://dl.dropboxusercontent.com/u/26284703/RAO.xlsm
Nb : all this not for Windows 64 🙁
appréciate prévious ???
virtual drummer to come ... 😉
I believe the two coolest things I have done are a tie. One was a call center coordination tool to ensure the proper amount of people based on 30 years of call history. It scheduled lunches and breaks for employees. It even allowed for friends so that they were scheduled together. A win for everyone. My favorite though was using predictive weather modeling in the modeling of shoe sales. It utilized weather reports based on 1,2,and 3 weeks out to forecast sales by department. It was creating a 9-10% increase in sales, prior to my departure from that company. I wish I could have finalized that tool. Oh well. Great shares everyone. 🙂
While not in the same ballpark as some of the above, my favourite outputs have been:
A Duckworth/Lewis (cricket) calculator
An automated employee expense form (including a fairly complex VAT/fuel formula that satisfies HMRC rules)
A football competition file
Can you please share the Duckworth/Lewis calculator and the football competition file if possible? They sound very interesting to me. Thank you.
I signed up for a developer account at http://forecast.io and programmed a Power Query JSON connection to the Forecast.io API.
Then I programmed Power Query to pull my company's store locations (latitude/longitude) from our company database, and I fed those store's latitude/longitude into the weather forecast API.
I used the resulting weather forecasts for each location and matched it up with snow removal contract data, which contained cost estimates based on snowfall. This gave me a 7-day outlook for upcoming snow removal service charges, and helped identify locations that would be hardest hit (cost-wise).
All of this was done exclusively within Excel without any VBA coding at all. Excel and Power Query is the greatest thing ever.
I made a ball bouncing in a box animated in 3D with a scatter chart!
https://scribblepouit.wordpress.com/2015/01/05/3d-bouncing-ball-in-excel/
I'm really proud for a little program where, avoiding any VBA code, but using recursive analysis of string, I extract from a given characters set all the italian words with 3 to 8 letters... it was a nice game on Facebook, I used it to win against my cousin... just a few hours, than I confessed my trick.
The one I'm posting is the short version but with the same concept it works on the whole dictionary for any word, and of course it works with any language putting the specific dictionary.
https://www.dropbox.com/s/yidqd0336l31lai/lista%20parole.xlsx?dl=0
The discovery of using wildcards in referencing to other worksheets.
http://wmfexcel.com/2015/07/11/sumc3-is-it-a-valid-formula-no-it-is-magical-indeed/
I was super excited to create this deal register for treasury department. Previously, the dept was using manual register filled with hand in a hard paper (so old fashioned!)...
I am not a programmer so i have avoided VBA, but I did use the basic formulas to create this register which needed the deal to be entered in worksheet and the limits used, position and nostro balances are automatically filled up... 🙂
My personal finance spreadsheet is wicked - it forecasts up to 6 years in the future and includes every monthly, annual expense and income automatically. It also has a checkbook sheet, mortgage sheet, utility tracking sheet, college savings calc for the kids. I've probably spent 1,000's of hours making it in zone out time during work over the last decade. It changed a lot for the first few years and I've tweaked it less and less over the last couple years - I think I've finally arrived with a package that I'm content with to manage my finances.
For work, I made a spreadsheet that calculates the trim / list / draft of a barge and runs a dynamic stability test for different loading scenarios. It took me 6 weeks working 50 hours a week to create and many hours since then tweaking it. It even had a 3d animated graph showing the barge as it would be once we loaded heavy cargo onto it. I also used the same base spreadsheet to create a roll on / roll off calculation showing heavy cargo rolling onto a barge or off a barge. My company does this often, with 100,000 lbs to 1,000,000 lbs cargo often. It totally made my career and showcased me to my superiors. I owe excel a lot for my current career path.
Built an estimate-builder which summarized all of the items into a simple, one-page invoice.
Employed some funky look-up & auto-fill functions, then some VBA to auto-caps the codes for each line item.
....if any of that made any sense, and anyone wants to see it, let me know. 🙂
Tom