Excel Tips & Tricks ITT

[stupac2]

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.

 

[Beer4B]

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?

 

[jtmartino]

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

 

[Beer4B]

=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.

 

[jtmartino]

Thank you very much!

I just improved my usefulness by about 40% today.

Check out sumifs if you have multiple criteria across multiple ranges. Countifs is good too, and both avoid array sum product formulas which can be confusing sometimes.

 

[beerherder]

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]

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.

 

[pjs234]

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]

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!

 

[pjs234]

I love you. Thanks!

The worst is when you hide column A. It's a PITA to try to highlight that column and not select the entire worksheet...

 

[trickytunadicky]

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]

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

 

[trickytunadicky]

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.

 

[trickytunadicky]

The worst is when you hide column A. It's a PITA to try to highlight that column and not select the entire worksheet...

Select column B, drag left, double click.

 

[Trady]

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]

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.

 

[lambandtunaphish]

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?

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]

This?
View attachment 7330


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

No, because you are only allowed (1) grouping per tab, and I'd like (5) individual groups. I did look into it, and when I tried it, it threw everything off.

 

[Trady]

This?
View attachment 7330


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

I actually think I figured out the formatting. I had to change the "Cell Value" to "Dates Occurring", and could then set the hierarchy of the formula conditions, so it works now. Thanks!

 

[beerherder]

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.