Related Posts
More Posts
I have no problem lying to get what I want
Additional Posts in Excel Genius
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?
What are the most typical models you all build
New to Fishbowl?
unlock all discussions on Fishbowl.
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!
Mentor
VBA mostly, although python can also be used
Mentor
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
is there a shortcut for center across selected cells? or a ribbon key
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.
Ctrl + R does the same thing but to the right!
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!
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
Also my main tip is getting a Bluetooth mouth with the side buttons that you can program to quickly flip between tabs.
Is it better than Alt Tab?
Alt,H,O,I
Adjust autofit all column widths
And ALT,O,C,A autofits only selected column widths
Download ASAP utilities. It has saved me so much time.
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.
Fill down blanks - https://thesoftwarepro.com/excel-tips-how-to-fill-blank-cells/
A bit niche but I use it all the time since I regularly get data that is off set like this
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