Making Friends with Excel: How I Stopped Worrying and Learned to Love the Cells

As part of my Microsoft Business Analysis Professional Certificate, Excel and I were forced into an arranged marriage. I come from the land of ideas, analysis, big messy theories. I see a problem and I burst sparkles in order to find solutions... And, frankly, Excel tend to be more- how to put it- *stiff*(?) in its approach:

“You forgot a comma in your formula, and now everything’s on fire.”

Naturally, it’s been a ride.

I’m writing this as a way to make myself a little more amicable with Excel - and maybe help you if, like me, you see a spreadsheet and feel your soul quietly packing a suitcase.

In my desperate attempts to avoid another "VLOOKUP horror story," I found a few quirky methods that made Excel less of a grid-shaped torture chamber and more like a slightly quirky, misunderstood friend who just happens to have trust issues.

Let me share with you some of them:

• TEXTJOIN:

  Why waste time copy-pasting when you can TEXTJOIN your columns into one, with any separator you like. It’s like Frankenstein, but for your client names and IDs.

  How to use it: =TEXTJOIN(", ", TRUE, A1:A5) will join all values in cells A1 to A5, separating them with a comma. You can swap the separator for whatever you need—like spaces or even emojis (yes, it works)

  
  

• FILTER and UNIQUE:

  These functions let you filter and grab unique values on the fly.

  How to use it:

  =FILTER(A1:A10, A1:A10>5) filters all values greater than 5 in the range A1 to A10.

  =UNIQUE(A1:A10) gives you a list of only the unique values in A1 to A10, no duplicates/

  
  

• WEBSERVICE and FILTERXML combo:

  You can pull live data- currency exchange rates, weather, or even random cat facts- directly into your spreadsheet. Sounds like something only tech giants can do, right? Nope. Excel does it too.

  How to use it:

  =WEBSERVICE("https://api.exchangerate-api.com/v4/latest/USD") pulls live data (in this case, exchange rates).

  After that, use =FILTERXML(A1, "XPathQuery") to extract specific data (e.g., get a certain currency rate).

  
  

• Sparklines:

  Forget about overwhelming PowerPoint slides. Sneak in a few sparklines in your summary sheets and suddenly you look like you’re a data wizard, and you probably just saved a ton of time.

  How to use it: Go to Insert > Sparklines, select a range, and Excel will create a mini chart in a single cell. It’s great for showing trends without creating full-blown graphs.

  
  

• Name your ranges or prepare for chaos:

  Stop with the random cell references like “$A$124:$E$687.” Trust me, it's a trap. Name your ranges, like “2025_Sales_North” or “Client_Misery_Index.”

  How to use it: Select a range (e.g., A1:A10), then go to the Name Box (top-left) and give it a name. Now, you can refer to that range in formulas like =SUM(2025_Sales_North) instead of confusing cell references.

  
  

• Power Query: The grown-up of the Excel family

  Merge, transform, and clean your data without wanting to cry into your coffee afterward.

  How to use it: Go to Data > Get & Transform Data > From Table/Range to load your data into Power Query. Then, use its powerful editor to merge, filter, and clean data effortlessly.

  
  

• Flash Fill: Excel’s mind-reading trick

  Type one example, hit Ctrl+E, and boom, Flash Fill fills in the rest.

  How to use it: Start typing the pattern you want Excel to follow (e.g., “John Smith” in column A, “John” in column B). Press Ctrl+E, and Excel will guess the rest of the entries. It’s like magic, but sometimes it’s very picky.

  
  

• Random formulas : because why not..

  RANDBETWEEN (perfect for creating fake data you’ll forget to replace).

  RANDARRAY (because sometimes one random number just isn't enough chaos). SEQUENCE (for when you’re too proud to drag cells like it’s 1997).

  How to use it:

  =RANDBETWEEN(1, 100) generates a random integer between 1 and 100.

  =RANDARRAY(3, 3) generates a 3x3 array of random numbers between 0 and 1.

  =SEQUENCE(10, 1, 1, 1) generates a vertical sequence of numbers from 1 to 10.

  
  

• Data Validation dropdowns:  Set up a dropdown list. Limit the madness. It’s like protecting your sanity with a few clicks.

  How to use it: Select a cell, then go to Data > Data Validation. Under "Allow," select "List" and type in your options (e.g., Yes, No). Now, people can only select from the list, preventing all those creative spellings.

  
  

• Custom Number Formats: Turn zeros into “—,” negative numbers into red with parentheses, or hide errors completely.

  How to use it: Select the range, then go to Home > Number > More Number Formats. You can then create custom formats, like 0; ;—, which shows zeros as dashes and negative numbers in red.

  
  

• Sheet Protection: Because trust is overrated

  Someone’s gonna break your formulas. It’s not a matter of “if” — it’s “when.”

  How to use it: Select the cells you want to lock, then go to Review > Protect Sheet. Check the boxes for what you want to allow (like selecting unlocked cells), and voila, your formulas are safe!

Excel will never be the cozy library or open wild field where my brain naturally roams.

It's a spreadsheet, a grid, a stubborn little box demanding obedience.

But - when we can see its quirks, lean into the absurdity, and arm ourselves with a few low-effort high-impact tricks - it starts feeling less like a daily punishment and more like a mildly chaotic pet.

One that occasionally bites, but mostly sits where you tell it! ;)

Have a great week ahead my dear, good-good BAs!

Irena Kolek - Business Analyst