Posting as :
works at
You are currently posting as works at

I keep a spreadsheet of Excel hacks lol. I use paste transpose all the time. One trick that has saved me hours: filling blank cells with values. Say you have a column with a value, several blank cells, another value, more blank cells, etc. If I needed to copy the values into the cells below, it could take a really long time to do them individually. Instead:
Highlight all the cells from the first one down to where you want to copy the last one.
Cntl-G or Alt-5 (both are Go To)
Special
Blanks
Up arrow-Cntl-enter (if the value to be copied is above the blank cells)
Voila!

If it was a number that incremented and it needs to not (i.e. 111 instead of 123), simply type Cntl-D and 123 will change to 111.

Testing corners of a range to see if my data are contiguous or if I have any blank rows/columns that might screw things up. Ctrl-A to select all contiguous data, then Ctrl-period to cycle through the corners of the data.

Related Posts

Any one in HPE? I recently got an offer (~60lpa, 13 YOE) the variable pay/RSU etc seems to be on the lower side compared to the industry. No joining bonus too. Any one who can offer insights on feasible payranges/ yearly hike trends etc?

like

Hey fishes.. anyone from analyst relations? Appreciate advice on career path, scope from seniors/vets..

Is MGMA datadive access worth the cost for salary negotiation info?

like

So I ordered a 1 backpack and 1 desktop monitor on PwC’s IT order form. And I somehow got shipped 3 backpacks and 3 desktop monitors. The shipping center said I had to take them, but should cont.

like

Get in my belly!!! 🍕 Absolutely loved the homemade pizza.. probably will never order in now on

Post Photo
likeuplifting

How is Data services (DATA) BU at LTI? Getting designation for Senior Data Engineer.

like

Hi All,

I have completed interview with vous and HR has send me indicative breakup and asking me to resign from the current company because offer letter is taking time.


Should I resign on the basic of breakup communication or wait for offer letter ?

Healthcare consulting practice. Deloitte vs Accenture vs other players. Thoughts on who has the best practice overall?

And any insight/who has the best practice in the provider space specifically?

like

I just realized I’m the dead participant.

Post Photo
likefunny

Hi I have a 2 year experience on SAP PO/BPM I want to switch to CPI also I want to learn DevOps.Which is better ?

like

What is the average pay for a Laboratory Assistant at Adventhealth.

Which is the more lucrative and flexible long term career path when comparing .NET development vs salesforce development.

By flexible I mean in terms of moving between positions, companies and technologies.

I've done both and am currently doing salesforce development but don't want to get pigeonholed.

like

Biglaw first year associates, what’s your favorite thing about your job (beside the paychecks)?

like

Ideas for a night in alone? Feeling Chinese take out to start it right

like

Freelance at copywriter with 10+ years experience looking to go full time at my current agency to ride out the recession. What kind of salary range can I expect?

like

Is it good to join in hitachi for java 6 years senior product engineer?

Any Recruitment Project Managers willing to share salary and location (even East Coast, West Coast, etc,)?
Thank you !

like

I like my job, and my team, and what I do. But damn if I’m not underpaid 😅 small company problems lol. I know I’ll have a salary review next spring (one year) but I’m working a second job cuz student loans blow ass lol. Gotta repay those degrees I don’t use anymore 🙃

Don’t mind me just venting lol. Carry on!

like

Hi Fishes.. Does Barclays offer any Diwali gifts ? Just curious to know 😄

Anyone at Pfizer who can refer me for a role in U.S? I have two years of life sciences consulting experience and a PhD.

like

Additional Posts in Excel Genius

For a linear regression analysis… the equation of a line is Y=MX+B, where M is the slope. So if I’m measuring a line across 365 days and M=.2, can I say that I have increased production by 20% over a year. Or is production increasing 20% each day?

like

Hi Group, seems like it should be basic format, but I just can not get it to work. a list of data and want to alternate shade (blue,white,blue,etc) each line but if I have multiple lines I want to group together and they all shade the same. I want to group by the same data in column A, and the number of rows that are the same could vary. So let’s say row 1& 2 same entry in column A(blue) row 3 different (white) row 4,5,6 same as each other different to other rows (blue) etc… help 🙏

like

Can dates be grouped just by Month-Year in pivot tables on a Mac without having the year as its own dimension?

Post Photo
like

What’s the keyboard shortcut to get into the “insert options” dialogue when you’ve inserted a new row with Ctrl+Shift+Plus?

like

Hey fishies, looking for some help with the below

I have 4 lots of data categories which are assigned a code in the adjacent cell
Eg:
Cell A2= 'xyz' Cell B2 = 'w1'
Cell C2= 'ghi' Cell D2 = 'x1'
Cell E2= 'plm' Cell F2 = 'y1'
Cell G2= 'mhn' Cell H2 = 'z1'

The combination of the 'W','X','Y' and 'Z' codes identity a specific trend
I am extracting data which includes multiple combinations of these codes. What is the best way to show frequency of the codes against the category it is assigned to

like

I am trying to recreate this without the numbers at the end. Any advice?

Post Photo
likehelpful

Is there anything similar to the INDIRECT function that I can use to build a formula reference to another workbook? INDIRECT creates errors when the referenced workbook is closed..

like

Help me save the day: have a massive excel file (600k rows) with duplicate records (column a, b and c are duplicate, not a duplicate if a and b are the same though). How can I filter for just these records? If it didn’t crash excel I could concat into one column, Conditional format for duplicates, and sort by color. What’s a work around?

likehelpful

Client sent over a 90 MB model this evening. Took 10 minutes to open and a fraction of that to crash. Can’t wait for tomorrow 😭

like

Does anyone know what purple tables mean in Excel?

I know about green and orange, but never saw purple before and trying to figure it out.

likefunny

What’s the symbol that you can type in a cell so you can input a text, but that text would not appear in the spreadsheet? I want to describe the formula without using a text box, which uses more space.

The equivalent of this is in SQL, you can use the apostrophe in the beginning of and end of a text. That text will not affect the function of the query, but that text can be seen if you proofread the query.

like

Anybody use a personal computer for work? How's your experience been, any horror stories/watch-outs?

like

Hi excel geniuses, how do I quickly copy info from one cell to blanks for same if. Semi below I have products and names, for the blank fields w same product how do I quickly copy for the rest of the blank cells?

Post Photo

Will need to migrate to office 365 soon. What’s the difference for excel from the 2013 version?

likehelpful

Best free data sites to tap into to build a city analysis model? Basically I want to "discover" cities with certain dimensions, like: population, elevation, climate, and proximity to other things (ski resorts, particularly)?

like

Hello. Quick question about formatting. I have an excel sheet with bunch of negative numbers, but some are percentage values and some are dollar values. Is there a way to make all of them have a parenthesis for being negative while keeping them still percentage and dollar value separately or do I have to target them separately?

like

Does anyone have any tips/tricks for knowing where to plug +1/-1 in a large table with totals across the y and x axis that contain un-rounded numbers? Every time I fix a row I throw off a column or my check figures to the true data source.

Post Photo
like

Hey, I have been tasked with teaching some of my coworkers excel from the very basics to more advanced. Does anyone have recommendations on the skills to teach them about? Thank you all in advance!

helpful

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

Would anyone be willing to teach me vlookup?

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

Scan your QR code to download
Fishbowl app on your mobile

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