It’s not exactly the same but close enough ? Excel File Download it here: 5 letter word game v4.41Download Challenge The question was bouncing around in my head: Can I recreate this word game in Excel? Turns out that it was easier to build this than solve the puzzles! I guess I’m not good with words. How Does It Work? It’s a mix of formulas, vba, and conditional formatting. Unhide sheets words and letters to see more. Sheet words The 5 letter words are in column B. vba takes the randomly selected cell E1 word and pastes it as a value in cell E3 so it won’t keep changing. I created two are ..read more

I heard about 3D Objects from MrExcel. I’ve had fun combing a 3D model with VBA (it moves inside your spreadsheet!) 3D Objects They have few practical uses but are fun to play with. In MrExcel’s words: Unless you are an engineer, I am not sure how 3D Models has a business use. But it is a cool new feature. MrExcel.com I agree completely…not practical but definitely fun to play with. Test: insert 3D Model (older versions don’t have this feature). Follow these steps: on ribbon select Insert select Illustrations select 3D Models select Stock 3D models (select an ‘animated’ one!) select green In ..read more

Earlier this week I saw an interesting rounding question on the famous Mr Excel Forum. And there was a twist! Excel File Download the Excel file and follow along. Mr Excel Forum – rounding question (LOG INT)Download Original Question Link Link to the original post on Mr Excel’s amazing AND free help forum. Description I created this Excel file to explain: Original values in cells A2:A5 (I added more below). Solution in column B. Solution Cell B2 formula: =ROUNDUP(A2,-INT(LOG10(A2))) A brilliant formula from one of the forum members. It rounds like this: $12,036,219.22 to $20,000,000.00 $43 ..read more

I played around with two different S curve concepts using dynamic arrays. What is an S curve? Related to Excel I found two concepts. In the end, both create a line that resembles an S when plotted against time. Project management: tracks allocated costs over time. Estimate ahead of time or track as it happens. Often ends up looking like an S curve. Formula generated: uses inputs & formulas to directly create an S curve. Dynamic Arrays Each Excel file is based on a source, video & xlsx, that were created without dynamic arrays: project management: video from Insights Training (how to ..read more

https://lichess.org/ has a Zen mode. Can I create a Zen mode in Excel to celebrate Spreadsheet Day?   Zen Concept Distractions are bad in 3 minute blitz chess games. I use Zen mode to focus on the game. Excel has many great features but sometimes it helps to hide them and focus on the data (or text). Can I create a Zen mode in Excel?   Excel File Download my Excel file (it has vba code… aka macros).   Features to Disable There are so many but I focused on these: formula bar gridlines headings (rows & columns) autocorrect F1 help ribbon status bar   Manual or Automated ..read more

I explored ideas to make DSUM function’s criteria argument as flexible as a gymnast. Is it a spreadsheet miracle? Maybe. (Spreadsheet Miracle: see bottom of post for info on artist Kim Murton)   Excel Files I used two files: (1) using original data here and (2) cleaned up source data here.   Why DSUM? It’s a database function that sums values based on sets of conditions. Sum a column based on these conditions or these conditions or these conditions, etc It’s easy to modify the conditions. What else can do that? SUMIFS and Pivots are great but not as flexible as DSUM if you need to mo ..read more

You may never need this…but I did recently and it’s just fun knowing that Excel can solve this! Download my Excel file here (demo data from data.world).   Requirement Drop down list must contain column headers for columns that have currency datatype. If datatypes change the drop list should also change Last week I would have said that it’s not possible….but it is possible! I used it as part of a DSUM solution (choose currency column to sum).     The Data I used this sample data from data.world. 20 total columns and 399 rows.               &n ..read more

I had some questions and improvement ideas for my formula pixel face. I learned a lot playing around with this.   Recap My curiosity got the best of me in 2019. Challenge: Can I make a pixel face move without VBA? (post) Can I display a message when the face intersects a cell with a number? Previously, I used VBA to make the face move.   Download my Excel file here.   Now what? I couldn’t get several questions out of my head: how did I use formulas to move the face? can I incorporate some dynamic arrays? can I make formulas less volatile & more efficient?   1. How d ..read more

Daniel Choi solved it using Power Query! Let’s review his solutions. Download Daniel’s Power Query solutions.   Recap It’s the same messy data & requirement found in my last post.   Daniel’s PQ Solutions He has two Power Query solutions. #1 requires writing some M code. #2 only uses the UI. I prefer #1! Why? M code is shorter easier to read the M in #1 Great way to learn about List !   PQ Solution #1 Steps Source, Change Type, Replace Value are easy steps. Text_Split Step Text_Split is a great practical use of creating a List!             I c ..read more

I need to calculate hours given data like this in each cell: “9-10, 10-11, 10-11, 12-13, 12-15+, 14-17+“ Download my Excel file with solution.   Messy Data Data exported to Excel looks like this:                 Task I need to calculate total hours in each cell. Cell A2 = 4 hours, cell A3 = 6 hours, etc. There were 10 columns and 20000 rows (my sample is smaller). Solution needs to be easy to audit.   Solution Initially it seemed daunting. I could use text-to-columns to split out each start/stop value and then a ton of formulas. Urgency of the ..read more