I'm in final consideration for a new gig that I really, really, really want - last step is a sample project that includes building a somewhat involved dashboard in excel.

I regularly work in excel, and zip around using obvious features like basic math functions, sorting data etc - but in this case I know they're particularly assessing my excel chops and I want to pull out all the stops.

If you were building something in excel with specifically showing off an advanced skillset in mind - what special functions would you make sure to include?

I don't know wth a pivot table or a macro is, but I know they're things lol

Give me some suggestions please and thanks!

That demonstrates that you know how to overcome the limitations of base Excel and can use the program efficiently

it's actually pretty easy once you play around with it

Macro's are a very good tool to have in your bag....i'm sorta 'learning' it now...

Power Pivot-- after you graduate from regular pivot tables....handles larger data sets and cool for showing off

VLookup-- I never learned it...it's not hard...I just haven't taken the time....but VERY useful/helpful

I'll add more if I think of any

You essentially enter logic such that if X is, say, >= Y, make the cell green. If X==Y, cell is yellow. If X<Y, cell is red.

I've also gotten alot of run out of knowing how to name a range of values and then using countif() to find the certain values in that range.

For instance, if I've got 10 values in column A of Sheet1 that I need to find amongst 5000 values in another tab in Sheet 2 of the spreadsheet, I'll name the large range of values "MyRange".

Then in column B of Sheet1, I'll put =countif(MyRange, A1)>0 . The ">0" at the end changes the countif into a True/False statement.

So if A1 is in the range of 5000 values in "MyRange", B1 will be "True". If not, it'll be false.

Autofill that function for all 10 entries and I've quickly determined whether any entry in column A is in "MyRange".

as far as stepping into the next realm of data consumption. and they're easy once you get the hang of them

the level I am looking to get on is VBA where you are getting not only multiple worksheets in Excel, but multiple programs like Access / Outlook working together.

colleague taught me about it a few years ago after 10 years of manually figuring ways to perform same task. i use it weekly if not daily now.

are there any particular online resources you're a fan of?

I'm a seasoned googler and am confident I can find the instructions and info I need - youtube etc, but if there's something dope out there you're already aware of let me know

Thanks all!

And keep any more great ideas coming! This has been super useful so far

https://www.youtube.com/channel/UCkndrGoNpUDV-uia6a9jwVg

I work pretty extensively with Excel in my new position.

I wouldn't say I'm an expert, but I'm proficient enough to get a job that sounds like what you're going after.

What's the point of the dashboard? What information are you trying to convey?

What kind of data are you working with?

and make strategic recommendations

(which is what I already know how to do)

and then make a dashboard for that theoretical organization to use to manage (forecast, allocate, etc) their finances moving forward (which I kinda know how to do, and have done many times in simplified formats, but I never tried to make anything hugely integrated and pretty)

Would love the opportunity to inbox with you or others in this post in more detail, if you're game to advise further. All input is hugely helpful and appreciated!

Feel free to inbox me. I'm at work today and tomorrow reading up on how to design and implement a data governance framework, so if you know anything about that, sharing is caring.

I'm not sure how much help I can be, but yeah, happy to assist however I can.

Is the forecasting straight line linear regression?

The guy who taught me a majority of my excel trickery loved using slicers in dashboards:

https://www.youtube.com/watch?v=R7HF1bG70IE

Learn pivot tables in 6 minutes:
https://www.youtube.com/watch?v=qu-AK0Hv0b4

A more thorough dashboard tutorial:
https://www.youtube.com/watch?v=9NUjHBNWe9M

https://www.benlcollins.com/tag/excel/


if you go to the top level url, he mostly fux w/ google sheets now (which is what i mostly mess with), but he got some fire on excel, too, at the link i posted.

in particular, he's GREAT at explaining stuff -- one of the best tutorialists (? that a word?) on the 'net, and he always has sample files, etc.

he will have you looking like the excel goat.


peace & blessings,

x.

www.twitter.com/poetx

=========================================
I'm an advocate for working smarter, not harder. If you just
focus on working hard you end up making someone else rich and
not having much to show for it. (c) mad

I have dashboards already I could repurpose wholesale lol