What are your top Excel ninja tricks? Ones that few people know...

like
Posting as :
works at
You are currently posting as works at
Highlighted IconHIGHLIGHTED

I’ve written many of my own buttons that make my life easier, such as:
• Jumping to the end / beginning of a worksheet
• Unhiding all hidden and very hidden sheets (you can’t unhide multiple sheets at a time)
• Unprotecting password protected cells and objects
• Automating some regular tasks such as daily web scraping, pdf and email reports, etc.

Also, if you don’t know the Alt shortcuts, then you’re losing a significant amount of time!

like

VBA mostly, although python can also be used

like

There are hundreds of tricks. The ones I've found most useful regarding formulas:

- Using a Vlookup set as close match inside a Vlookup with exact match significantly decreases the time taken to perform a Vlookup (also works with index match/Xlookup)
- Using conditional sumproduct (without ifs)
- using abs min and index match to perform closes match for values

For other purposes:
- Alt + ; to select only visible cells
- Center across selection (PLS FOR THE LOVD OF GOD DO NOT MERGE CELLS)
- Reference dynamic arrays

likesmart

is there a shortcut for center across selected cells? or a ribbon key

Recent IconRecent

Copy down only on visible cells: ctrl+D. I just use it as my default way of copying down but where it comes in really handy is if you are on a filtered set of cells that you need to copy a value or formula down only on those visible filtered cells and not the full range including those cells that have been filtered out.

likesmart

Ctrl + R does the same thing but to the right!

like

Not using a mouse. If you do everything with the keyboard, with repetition and muscle memory, you can slice, splice and dice small data (<20k rows) at the speed of thought!

likeuplifting

Pivot table: mouse is most optimal. But you can construct a sumif/average if/countif table almost as fast with keyboard if you practice. Build the frame of your table, then fill in with formulas. I use both methods… One con of pivot table is that it makes your Excel file really big

like

Also my main tip is getting a Bluetooth mouth with the side buttons that you can program to quickly flip between tabs.

like

Is it better than Alt Tab?

like

Alt,H,O,I

Adjust autofit all column widths

like

And ALT,O,C,A autofits only selected column widths

Download ASAP utilities. It has saved me so much time.

like

I don't have Excel Ninja skills yet but I am working on it.
I use concatenate, proper, round up, countifs,sumifs, and Vlookup most of the time with multiple sheets and files. I would really like to learn how to do multiple if statements combined with a Vlookup. For instance if the work schedule rule is true to another work schedule and the date is on a Wednesday then the cell must be marked as a training shift day.

like

This is pretty basic, but using F5 to go back is very convenient if you have a lot of tabs.

Opening a new excel window of the same spreadsheet to be able to "alt+tab" between spreadsheet tabs

Power query. It is so powerful

Related Posts

Anyone have a gym bag they travel with? Looking for some recs! Ideally something that has enough room for two pairs of shoes (lifting and gym shoes), accessories, and supplements. Durable for travel.

Howz studying at UK different than US or Australia ?

Does it have brand value better in terms of working in UK for some years and travel back to India ?

like

Anyone used southwest chase credit card? Thoughts? Have you gotten the companion pass? How was it?

like

Travel industry sales reps check in here! What's your base? Mine is 60k which seems low.

like

Man that United Healthcare Chuck Norris spot... ouch.

like

Does anybody know what the typical salary range is and total comp for someone starting out as an internal or external wholesaler? If you are one of these, can you share about your experience in terms of whether you liked it or not, work life balance, travel, etc?

like

Does HCL provide work from home?
What was the situation pre covid? I live in Gurgaon and HCL doesn’t have an office here, would there be an expectation to travel to Noida everyday?

FINALLY saved up enough for a Leica kit - I was very excited for the CL - but the shop suggested the Q2 as a really great travel camera, and now I’m not sure what to get. Anyone here shoot Leica? I’m looking for a simple to use, compact travel camera. I love the lens -swap on the CL (one day want to own an M and SL kit) - but the Q2 has me thinking…

On a local project through the end of the year and will be 10k MQM short of renewing Delta gold. Delta Platinum Amex no longer gives you a 10k sign up MQM bonus. What to do? Settle for Silver 😱?

like

Can anyone offer advice , possible job openings for an individual that is in the last semester of earning certification after student teaching hours are acquired? This individual has been working/ cos chi big in the school system for over 12 years… experience with autistic students , middle school , students spending time in the alternative setting… pending masters degree in secondary education… coursework completed (with exception of student teaching)… also enrolled in an alt/cert. master

like

Do folks have trusted/valuable resources for researching luxury family travel with young kids? I don’t care the cost, I want to know what is most convenient and nice for my family. Feel like generally most resources are geared to either a) families on a budget (I don’t care which hotel is best bang for buck w/mid breakfast) or b) couples / adults only travelers. Do folks just stick to true five star hotel concierges for help? Do folks just book luxury tours like A&K? How do folks research?

like

How is everyone feeling about post-pandemic travel? Have you planned anything for this spring/summer?

like

Tell the truth: are you more productive at home than the office? How much more? How about your coworkers? Are they more productive in your estimation? And lastly, your whole team - is it more productive in a remote environment?

likefunnysmart

Does anyone know where I can go to look up SPG/Delta related challenges? Loosing my SPG Platinum status next month and looking for ways to get back on

like

Clinician looking for a new role. Experience in SaaS technology, telehealth, sales, client success, implementation, and consulting. Looking for a remote position with little to no travel. Idealy something in the health tech space.

like

Living in Van. Previously lived in Toronto. Obviously rent prices are high as is cost of living. Generally go out and spend more often living in a city. Boyfriend and I on good salary but not saving much due to the above. Before we move home to our own country, we’d love to spend a year close to Vancouver where rent is cheaper, temptation is less, to save. A commute to Vancouver 1-2 times a month needs to be possible (his job). That can be a flight if it’s not crazy. Thoughts? Recommendations?

likehelpful

Is there any KPMG / AA or Delta status challenges going on right now?

like

Best hotel in Tokyo for 5 days in September?

Has anyone ever gotten the SGB treatment? Was doing some research on chronic pain management treatments and found something called Stellate Ganglion Block (SGB), a non-invasive injection that helps chronic pain, anxiety and PTSD symptoms. Helps 'reset' the flight or fight CNS.

Would love to hear about your experience and how has your pain/PTSD symptoms been since!

like

Heading to Costa Rica with about 7 college friends in mid July. Any recommendations on mode of travel within the country and cities? We are looking to bounce around to at least 4 cities over 7 days

like

More Posts

like

Any advice on how to handle a manager who micromanages you?

like

What is the salary package of IME process advisor. Experience 3 years 1.5 years in UK banking for Noida location. Corporate grade BA3

like

Book recommendations on Audible for a young male attorney struggling to get back to living a healthy lifestyle (physically, mentally, spiritually)? Open to any genre.

likehelpful

Pru has expansive outbrokerage options, but still captive. I was speaking to someone recently that said Pru is closer to independent than captive. A hybrid almost. Thoughts?

like

Ramadan Mubarak all! Is anyone living the yolo life and just staying up all night and sleeping their days away? (Yes I know adjusting back to normal during the week will suck)

Hi Fishes, do anyone with 5 rating have received bonus letter alongwith MSI letter?

What to say on compensation calls? They give u the numbers. You say ok. What else?

like

According to the DEA , majority of Drugs are trafficked through legal point of entries .DEA= fake news ?

likesmart

Hi folks, there's an opening for 'Big data engineer' in IMPETUS.
Please let me know if you need referral.
Happy to help!

Post Photo
like

Just got dinged from my dream role at McK and feeling pretty down. I got laid off soon after my wedding in September (low utilization and not a ton of work coming in for Q4) and am having a tough time

likehelpful

Hey all,

What's with the probation period with Deloitte India? What's the rate of termination, if there are any, unlike the other firms (who have a probation but 99.99% of the employees are confirmed at the end of probation).

Appreciate your inputs.

Thank you.

What’s your agency’s hook?

I’m starting a new offering from the ground floor - no existing social clients or work. I’ll be walking into meetings with clients who know me from other agencies, where our hook was “we’re fan-first marketers” or “we are the most premium creative shop in our industry”.

I’m working on my new hook, but would love to hear yours.

likefunny

Now the CDC says teachers don’t need vaccine shots to reopen schools?

like

I have no problem lying to get what I want

likefunnyuplifting

I need advice. A student at my worksite came to school with a gun. He’s a SPED kid, and administration did not inform anyone. I had to help the teacher and instructional aide deal with the incident. I told the AP that we needed to do more. I was ignored. I fear for the safety of teachers and students. What can I do, when union reps got involved and administrators did nothing.

like

Hi, I am a single-parent with a 9 year old daughter. Is anyone else dealing with same gender sleepovers with sexual exploration occurring? Exploring as in my daughter says outright “I am going to kiss you,” to her bestie before bed. Then after 15 minutes of giggling in bed I go to tell them to be quiet and see my daughter’s bestie is showing her buttcheeks to my daughter. To top it off they start trying to tell me they are laughing because they are reading “belly laugh books for kids”. Help.

Hello everyone.. i have been rolled out of a project recently. I need to submit my client laptop via courier. Please help me with the policy of reimbursement related to this. Thanks

really interested in this guy and he’s into me as well. however he got out of a serious relationship back in february and has her all over his insta. I think it’s weird he hasn’t deleted the pics cont

Do interns in FDD usually work overtime in the summer?

likefunny

Additional Posts in Excel Genius

Tips on adjusting to Mac? I’ve figured lots of keyboard shortcuts but it’s just not the same 😪

like

How can I extract just the year into a new cell from a “yyyymmdd” cell (labeled PD here) that isn’t recognized as a date? I basically need to sort this data by year. Thanks in advance!

Post Photo
like

Can you share the most useful formulae and built in automation features you have used with the latest version of excel?

like

Hey Geniuses! I apologize in advance if this question has been asked before, I'm new here and just trying to learn as much as i can. Does anyone know a great source to learn Macros for beginners. I would like to create one for work. I appreciate it

like

How do you all prepare for excel/financial modeling tests? Every firm is different, so I imagine there are multiple effective ways to get ready.

likehelpful

If you follow him on LinkedIn you probably already saw this! Lol 😆 but I totally resonate with the post... 🤣

Post Photo
funnylike

Can anyone help me with this pls? I created a graph using data above (not shown) but for some reason “technology” here appears three times. I want sectors to be grouped together. Is there any way to fix this?

Post Photo
like

What are the most annoying things for you in old-school excel users?
I’ll start:
1) vlookup 2) concatenate 3) inability to work with file versions, therefor constantly locking file from changes

like

Has anyone encountered this error before? We can’t access a workpaper with 70% of the work done 😩😩

Post Photo
like

I have a field in excel that represents the GSID, that I want to concatenate to create a link. Example field GSID is 123-west and the link needs to be www.website/123_west.html for some reason using concatenate with left & right formulas to switch - to _ results in the field throwing an error. Anyone knows of a way to make this work?

like

Hi! I have knowledge of pivot tables, vlook ups. But I'm rusty on if statements and would like more knowledge on excel formulas. I'm looking for excel classes to take online. Any suggestions?

like

Last cell question. Some of my simple models are massive in file size because every tab has a last cell of ~XFD65. But I’m not using anything past ~U100. Continued.

like

At my old firms when I would be using excel and modeling using the font schemes (blue, green, black for hardcode, link, and formula) I could use the “control shift ;” to shift through the schemes. My new firm doesn’t do this. Does anyone know why the shortcut would be different and how I can add it back?

like

[shortcut genius] how can I use a keyboard shortcut to simulate the double-click on the cross in right low corner that extend your formula only to the row you need (ctrl+d will go too far)

likehelpful

What are the most typical models you all build

likehelpful

I’m trying to go back to the basics. How do you guys structure thoughts around identifying the correlation between 2 variables? A basic example: the number of steps I take in a day and the hours of sleep I got that night.

like

Just created my first macro! Super proud of myself! #humblebrag

likeuplifting

I am having difficulties getting my (Return on ad spend) column displaying correctly. The source data is shown in currency but on the pivot table I am only seeing count and percentage options. Any help would be greatly appreciated.

Post Photo
like

Any recommend courses (coursera, edx, etc.) people would recommend for user with intermediate experience with excel?

like

New to Fishbowl?

Download the Fishbowl app to
unlock all discussions on Fishbowl.
That was just a preview…
Sign Up to see all discussions
  • Discover what it’s like to work at companies from real professionals
  • Get candid advice from people in your field in a safe space
  • Chat and network with other professionals in your field
Sign up in seconds to unlock all discussions on Fishbowl.

Already a user?
Login here

Share

Embed this post

Copy and paste embed code on your site

Preview

Download the
Fishbowl app

See what’s happening in your industry
from the palm of your hand.

A phone with Fishbowl app

Send download link to your phone

OR

Scan your QR code to download
Fishbowl app on your mobile

By continuing you agree to Terms of Use and Privacy Policy.

Messaging rates may apply

Download app

Sign up for free to view this conversation on Fishbowl

By continuing you agree to Terms of Use and Privacy Policy

Already have an account? Log in

Sign up for free to continue using Fishbowl

By continuing you agree to Terms of Use(New) and Privacy Policy(New)
Messaging rates may apply

Already have an account? Log in

For account settings, visit Fishbowl on Desktop Browser or

General

Legal