Excel Tips & Tricks ITT

Help Support Talkbeer:

Joined
Apr 12, 2013
Messages
10,070
Location
Oakland, CA
You can also CTRL-click the plussy thing, and get a menu of a bunch of options for filling down. Great if you have to populate a pattern 1000s of times (for example, you want to fill a column starting with 1/1/2010 to 12/31/2025 and don't feel like dragging for 5 minutes :))
I usually just make a formula and ctrl+d whatever rows I need to do.

In terms of general playing with excel lately I've been using indirect a lot. It took me a while to get working but I like this guy:

=AVERAGE(INDIRECT(CONCATENATE("'D 190308-3'!",ADDRESS(16*($A2-1)+2,COLUMN(B$1)+4))):INDIRECT(CONCATENATE("'D 190308-3'!",ADDRESS(16*($A2)+1,COLUMN(B$1)+4))))

I have a sheet of many different sets of 16x24 data sets and on a different sheet I want to average each set of 16 rows for every column, then have the next row be the next 16 sets, etc. I think you'd normally do this with a pivot table or something but I couldn't get it to work right and it was a lot of clicking, this is nice because I can just keep copying the analysis sheet for every new dataset.

thrashD, I might be a little confused about what you're doing, but why can't you just =A1-A2? All of my datasets are text files that have a first column of the form "3/11/2019 11:38:21 PM", and when I import it into excel it naturally converts it to a date, and I can subtract them just fine. So for instance A1233 is 3/11/2019 11:16:44 PM, and A1249 is 3/11/2019 11:38:21 PM, so if you do =24*(A1249-1233) the result is 0.72, because 0.72 hours elapsed between my runs (excel time differences are in days). Unless I'm wildly misunderstanding this solves your problem. You can certainly do the more involved things, but if you have something of the form mm/dd/yy hh:mm:ss you can just subtract.
 
Joined
Jun 9, 2015
Messages
2,869
Location
FL
Basic noob refresher needed (I'm using Google Sheets if that makes a difference)

Fairly certain I need to setup an IF:THEN formula, but I noob:

I have a column with numbers in it and another column with names. I'd like to add up all of the numbers by each name.

More or less, a cell that automatically adds up the numbers for each of the 4 people.


So when that row says "Bill" in one column, I want it to take the # in another column in that row and add it all up in another cell.

Anyone?
 
Joined
Jan 15, 2014
Messages
3,098
Location
West
Basic noob refresher needed (I'm using Google Sheets if that makes a difference)

Fairly certain I need to setup an IF:THEN formula, but I noob:

I have a column with numbers in it and another column with names. I'd like to add up all of the numbers by each name.

More or less, a cell that automatically adds up the numbers for each of the 4 people.


So when that row says "Bill" in one column, I want it to take the # in another column in that row and add it all up in another cell.

Anyone?
=sumif(range, criteria, [sum_range])

Range is the column where you have "Bill"
Criteria is "Bill" (can also reference a cell containing the word you're looking for, i.e., "Bill")
Sum Range is the column where you have all the numbers
 
Joined
Jun 9, 2015
Messages
2,869
Location
FL
=sumif(range, criteria, [sum_range])

Range is the column where you have "Bill"
Criteria is "Bill" (can also reference a cell containing the word you're looking for, i.e., "Bill")
Sum Range is the column where you have all the numbers
Thank you very much!

I just improved my usefulness by about 40% today.
 
Joined
Oct 12, 2015
Messages
219
Location
SF
I usually just make a formula and ctrl+d whatever rows I need to do.
Ctrl-d works as long as you have rows already (one I use all the time). The application for the Ctrl-click of the cross is where you are on a blank sheet creating your first column of data. I use it the most when I need to create a large calendar table for some reason, for example, A2=1/1/2010 filling thru 12/31/2025
 

Josh

Premium Non-Supporting Supporter
Staff member
Contributor
Joined
Sep 28, 2013
Messages
11,062
Location
In a van by a river
I somehow hid about 300 columns of a report. Is there an easy way to unhide all these?
I've tried selecting the whole worksheet and clicking unhide, but 'unhide' is grayed out.

I really dont want to have to manually unhide each column.
 
Joined
Oct 6, 2013
Messages
1,535
Location
The land of steady habits
I somehow hid about 300 columns of a report. Is there an easy way to unhide all these?
I've tried selecting the whole worksheet and clicking unhide, but 'unhide' is grayed out.

I really dont want to have to manually unhide each column.
You should be able to highlight all the columns, but do so by left clicking on the column heading and dragging over the all of the columns, you just can't select the whole worksheet (ctr-A or the top left sheet button). Not sure why that is the case, would be much easier to select all.
 

Josh

Premium Non-Supporting Supporter
Staff member
Contributor
Joined
Sep 28, 2013
Messages
11,062
Location
In a van by a river
You should be able to highlight all the columns, but do so by left clicking on the column heading and dragging over the all of the columns, you just can't select the whole worksheet (ctr-A or the top left sheet button). Not sure why that is the case, would be much easier to select all.
I love you. Thanks!
 
Joined
Jan 30, 2014
Messages
13,888
Location
Chicagoish
I somehow hid about 300 columns of a report. Is there an easy way to unhide all these?
I've tried selecting the whole worksheet and clicking unhide, but 'unhide' is grayed out.

I really dont want to have to manually unhide each column.
Columns, yes. Tabs, no. Happy fixing.
 

Josh

Premium Non-Supporting Supporter
Staff member
Contributor
Joined
Sep 28, 2013
Messages
11,062
Location
In a van by a river
Columns, yes. Tabs, no. Happy fixing.
Tabs are easy to unhide. You just right click on a tab and hit unhide and itll bring up a list of hidden tabs.
That's far easier than un hiding columns. Well, it was until I learned the right way to do it
 
Joined
Jan 30, 2014
Messages
13,888
Location
Chicagoish
Tabs are easy to unhide. You just right click on a tab and hit unhide and itll bring up a list of hidden tabs.
That's far easier than un hiding columns. Well, it was until I learned the right way to do it
Yes but you still have to unhide them one at a time when you cam hide them in bulk.
 

Trady

Moderator
Staff member
Joined
Apr 5, 2013
Messages
6,058
Location
South Jersey
Calling Fingolfin

I have a tracker I'm using for work that I want to set up a formula conditional formatting for that is crushing my soul, because I can't get it to work.

I have a column that I manually populate dates in that I use to notify me that I need to touch that individual project, because we have a standard interval of noting/updating the site every (5) business days, or once a week. Every time I make a note, I manually update the column, pushing it a week out for my next touchpoint.

My goal is to condition this column to highlight in yellow when the date hits todays date, and red if it's past due. I'm manually doing it now, and clearing the highlight when I make a new note, but I'd rather have this done automatically so I don't miss anything.

Thoughts on the formula I can use for this? I want to highlight yellow when the DATE=TODAY, and red when DATE=LESS THAN TODAY, I would think, but I've tried multiple ways to format this.

First cell this affects is J2, if that helps write up the formula...
 

Trady

Moderator
Staff member
Joined
Apr 5, 2013
Messages
6,058
Location
South Jersey
Calling Fingolfin

I have a tracker I'm using for work that I want to set up a formula conditional formatting for that is crushing my soul, because I can't get it to work.

I have a column that I manually populate dates in that I use to notify me that I need to touch that individual project, because we have a standard interval of noting/updating the site every (5) business days, or once a week. Every time I make a note, I manually update the column, pushing it a week out for my next touchpoint.

My goal is to condition this column to highlight in yellow when the date hits todays date, and red if it's past due. I'm manually doing it now, and clearing the highlight when I make a new note, but I'd rather have this done automatically so I don't miss anything.

Thoughts on the formula I can use for this? I want to highlight yellow when the DATE=TODAY, and red when DATE=LESS THAN TODAY, I would think, but I've tried multiple ways to format this.

First cell this affects is J2, if that helps write up the formula...
As a follow up on this tracker, it's about 55 columns broken into (5) sections of data. Besides hiding the individual columns, and not breaking the data into tabs, is there a function I can drop in to quickly collapse/uncollapse columns? I find myself scrolling horizontally a lot, and the data columns are pretty wide, I'd like to have a quick push button open/close of a section.

If that just doesn't exist, well then fuck Excel.
 
Joined
Jul 16, 2013
Messages
8,417
Location
Connecticut
Thoughts on the formula I can use for this? I want to highlight yellow when the DATE=TODAY, and red when DATE=LESS THAN TODAY, I would think, but I've tried multiple ways to format this.
This?
trady.png

As a follow up on this tracker, it's about 55 columns broken into (5) sections of data. Besides hiding the individual columns, and not breaking the data into tabs, is there a function I can drop in to quickly collapse/uncollapse columns? I find myself scrolling horizontally a lot, and the data columns are pretty wide, I'd like to have a quick push button open/close of a section.

If that just doesn't exist, well then fuck Excel.
Not a function but would grouping your columns work? I don't use it and get pissed when sheets have it, but...
https://support.office.com/en-ie/article/outline-group-data-in-a-worksheet-08ce98c4-0063-4d42-8ac7-8278c49e9aff
 

Trady

Moderator
Staff member
Joined
Apr 5, 2013
Messages
6,058
Location
South Jersey

Trady

Moderator
Staff member
Joined
Apr 5, 2013
Messages
6,058
Location
South Jersey
Last edited:
Joined
Oct 12, 2015
Messages
219
Location
SF
I'm doing an analysis of inventory management performance over time. the workbook I have uses ~150,000 COUNTIFS() formulas w/2 conditions in each, evaluating across a 10,000 row table. I knew it was going to be ugly, so I turned off automatic calculations.

After I got everything in order, I started the workbook calculation, walked to a store a few blocks away to get a four pack, came back, and workbook was only about 60% computed :(. I long for the day Excel on Mac supports Data Model & Power Query - would have been so much easier.
 
2
Group Builder
Top