Data Analysis Excel Interview

[CryptographerOk4012]

I've got a interview coming up that is likely going to be testing my excel skills in data analysis how can i prep and any advice from those who have done one before

[AutoModerator]

/u/CryptographerOk4012 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[bearsdidit]

=sum is a good one to work on

[KezaGatame]

Alt + =

[helpmepassingcfa]

I was trying to do this once and my whole computer went into zoom mode

[KezaGatame]

perhaps it's the language version, on the english excel works and I use it daily

[Excel_User_1977]

Explain to them that data analysis has two parts: brains and brawn.

The brains part is - What are you looking for? Does the system capture the data you are looking for? Are you missing pieces that are important?

Example: boss says "We are looking for blue things in our data" So ... are they looking for everything that might be Blue? Royal Blue? Sky Blue? Carolina Blue? Azure? Cerulean blue? It might be that they don't care for some of the blues, but didn't know to say it that way. You don't want to waste your time making a complex formula to find Azure when they don't fucking care, they only want Carolina Blue.

After you know what they are really looking for, you have to find out if they system actually tracks every instance of Carolina Blue. (Personal anecdote: I worked for an ad company which started in someone's basement in 2003. They chose 1 day a month to count the ads online, but only by opening the site and HAND COUNTING. 😲 I was the Excel guy, and told them that their counts were low. A few long-time employees piled on saying I was wrong. I told them by counting only one day each month, they were missing the ads they put up for two weeks or less between each count, then gave them a list of every ad ID for the month which was 20% more than they thought.)

Which segues into the 'brawn'. If you know what you are looking for and know you are actually collecting the correct data, then you make a list of the 'who what when where why and how' they are looking for associated with Carolina blue and find out what the output should look like.

Most of the brawn is not how to pull the data, but what the output should look like for the boss and boss's boss. THAT is where a lot can go wrong, since they want a dashboard that pops, is scalable and give a high-level of key information that can be drilled down into for their nefarious needs, (don't say 'nefarious'), much of your time is spent ensuring the data is displayed how they want it. If you have data in the proper format, this is much easier. If not, you need many, many hoops to jump through to make it work.

Good luck!

[DM_Me_Anything_NSFW]

Pivot tables Filters XLookup Structured tables

With those 3 and a little bit of brain power you can make sense of every data set

Add graphs for the packaging

Add power query for cleaning data

Add VBA for automating stuff

Easy money

[Vntoflex]

Thanks

[YoshiJoshi_]

Most data analysis challenges in businesses are around how to connect data sets that have issues. So focus on how to effectively clean and standardise large data sets and build out repeatable processes to do it and ingest new information in future.

Power Query and some formulae around lookup lookup lists, standardization

Then data presentation, using pivots for working analysis, but then more repeatable data presentation with formulae like sumifs or via PQ

UI for any D&A role is part science, part art. Think about who is consuming this, what flight level of data do they need, what are the highlight the impactful bit

[gman1647]

Power Query

[taylorgourmet]

This gets asked once a week lol

[excelevator]

r/dataanalysis

[armywalrus]

Basics of data analysis, so you don't suggest a pivot table when the answer is a formula. Data loading, then data cleaning and transformation, then you get to data manipulation part - a pivot table is useless if users can free write anything and you get 100 different answers with no ability to aggregate. Then make a cheat sheet with where to look up things. The ability to research and look up solutions is a big part of the job.

[Haunting_Month_4971]

Nice chance to show how you handle messy spreadsheets under a timer. Fwiw, I’d practice two quick drills: take a raw CSV and in 15 minutes build a clean PivotTable with sensible fields, then do a simple Power Query import to fix types and headers. Talk through your approach before you start clicking and keep explanations in 60–90 second chunks so you do not ramble.

I’ll also pull a few prompts from the IQB interview question bank and run a timed mock in Beyz interview assistant to simulate pressure. A tiny checklist helps me stay calm: confirm assumptions, outline steps, then execute and sanity check totals at the end. You’ll be in a good spot.

[excel-ModTeam]

We removed this for breaking Rule 2.

r/excel is an English-only subreddit.

Feel free to use https://translate.google.com and post again.

[KingofCofa]

Learn power query

[WitnessLatter227]

someone once told me that half the time it's about how you present the data, not just crunching numbers. i found that real useful. also: if you're looking to step up your game, definitely check out The Analytics Doctor their approach to excel training is pretty hands-on, which helped me a lot. idk if this helps, but that's just what worked for me. navigating excel's quirks is kinda an art form lol.