Excel wizardry

Brody

I tried to find an answer through the magical search engine, but trying to use the correct words to get the result I wanted was not working so great.

I've been using an excel sheet to track my daily hours. Its working well enough, but at the end of the week, I end up copying every cell on the page down one set, and then overwrite the top set to be blank. Is there some way I can automate adding a new set of pre-formatted rows at the top? I know a lot of people just copy one row down, but I don't think Excel saves where on the sheet you were, and scrolling down a bunch doesn't sound like my idea of fun...

Burtletoy

Can you just insert a new row/column at the top instead of moving other info down/to the side?

There's probably some macro wizardry also, but I don't know that stuff

Thundyrkatz

From what you are describing, it sounds like you have your headings, like the days of the week perhaps?, in row 1 and then you are adding hours for this week in row 2. Then each week you need to open up row 2 to receive new information. Is that what is happening? If that is what you are doing, you can always right click on the number 2 to the left in row 2 and select "insert a row". That will move everything down 1 row and give you an open row to enter this weeks hours.

Brody

Sorry, Im trying to find an easy way to describe it since it's on my work computer and hard to snip.

I've got headings for what day, then 5 rows (I need to make this part longer) of potential work codes, with a time start and time stop, then a cell that checks the difference, then 5 more rows at the bottom that sum the time values above by project number, then another row at the bottom that checks my daily/weekly hour and turns red if I'm short hours.

Elvenshae

Okay, so here's how I would do this, given that I'm having a hard time parsing exactly what you're looking for. I'd have a data sheet in Excel and a summary sheet.

The data sheet would have the columns:

Date | Work Code | Start Time | Stop Time | Duration | Week (?)

The Duration column would be Stop Time - Start Time. The Week column would be either a calculated field with your week ID based on date or user-input; depending on your final use case, you might not actually need this.

Then on my summary sheet, I'd either manually (or through the cross-tab tool) create a table with weeks and dates across the top, work codes down the left, and sum-of-Duration as the data value. You could then apply checks to each day and / or week to see where you were falling short.

Brody

Two sheets might work... I guess I'm not entirely sure how workable any of this is long term. I have to track my time daily, by work code. I'm trying to avoid being in the situation where I need to scroll forever in a sheet to get to the area where I am entering/reading data. But also I don't necessarily need to keep the data? Its all being entered into the tracking software for official use, this is just so I don't have to try and figure out what 9:45-7:30 equals, since that sort of math has always really fucked with my brain. I finally just made a snip and emailed it to myself, shown below.

mysticjuicer

If you want to keep that format, I think the easiest thing to do would be, at the end of each week, copy the rows for a week, then copy-insert it to the top of your sheet and just delete the old data from it/update the dates.

Brody

mysticjuicer wrote: »

If you want to keep that format, I think the easiest thing to do would be, at the end of each week, copy the rows for a week, then copy-insert it to the top of your sheet and just delete the old data from it/update the dates.

Yeah, I've been doing that, but its starting to involve a lot of scrolling to select all of the previous weeks, so I was hoping I could hit an "insert 17 new rows" button or something, or how difficult a macro that would do that might be to create

Elvenshae

Why would you need to select all of the previous weeks?

Select the rows with Monday, Jan 10 ... down through the blank row above the Monday, Jan 3 ... row. Copy it. Select just the Monday, Jan 10 ... row. Right click on the row box (like, the one that says that you're on row 10 or whatever). Choose "Insert, shift cells down."

Update your dates and clear out the entries. Et voila.

Alternatively, have a clean dataset on a separate page, without dates, and copy and past that one each time. You can even set it up so that it automatically add 7 days to the row below you and calculates the new date, so you don't need to manually enter that stuff.

Elvenshae

Also, control-key + a direction moves all the way in a particular direction until you hit something. So if you've got a long list of entries, hitting control-down-arrow will go all the way to the bottom (or to the first gap in the list, if you missed one).

In your case, if you're at the top of the page, and need to get to the bottom of it for whatever reason, pick an empty column next to a column that has something in it (like the one to the left of the "Monday, Jan 10 ..." cell). Hit control-down-arrow, which'll take you to the bottom-most row in Excel (row 1,048,576). The move to the right one cell, and control-up-arrow to get back to your data.

Thundyrkatz

If this is purely for your personal use and you do not need to keep it for very long, maybe a few weeks worth as a backup for yourself...

Could you just copy the tab and make a duplicate tab, then maybe after you accrue 4 or 5 tabs (weeks) you could delete the tabs you don't want anymore?

localh77

I have a lot of simple-ish sheets like that that I use for various things at my job, and what ended up working for me was moving it to Google Sheets. I have a really lightweight script that runs each night, checks to see if today is the 1st (or Monday, or whatever), and if so, does the copy-insert-paste and clears out the necessary cells. I know what it's like, where it doesn't really take very long on any given week/month to do it manually, but it's just really nice to open up the sheet and not have to worry about whether it's time to do those steps or not, since it happens automatically. Just a thought.

Brody

Elvenshae wrote: »

Why would you need to select all of the previous weeks?

Select the rows with Monday, Jan 10 ... down through the blank row above the Monday, Jan 3 ... row. Copy it. Select just the Monday, Jan 10 ... row. Right click on the row box (like, the one that says that you're on row 10 or whatever). Choose "Insert, shift cells down."

Update your dates and clear out the entries. Et voila.

Alternatively, have a clean dataset on a separate page, without dates, and copy and past that one each time. You can even set it up so that it automatically add 7 days to the row below you and calculates the new date, so you don't need to manually enter that stuff.

Right now I have weeks continuing down until the week starting December 20th, and without putting more thought into it would have continued increasing forever.

I'll probably end up going with a bunch of tabs and just deleting them once they are over a couple months old.

Tofystedeth

You could also create a template excel document with your data and summary sections and then each week just create a new copy of it and archive the older ones.

Dirtmuncher

Tofystedeth wrote: »

You could also create a template excel document with your data and summary sections and then each week just create a new copy of it and archive the older ones.

Or a template tab covering 1 month and then copy that 12 times rename them to the month. The doc itself gets called worksheets 2022 or something. It's much easier to find stuff this way then scrolling through 52 excels trying to find that 1 week you put hours on code xxx.

You could even have the codes you use the most in a separate tab.

Excel is very versatile, do what works for you!

Elvenshae

Brody wrote: »

Elvenshae wrote: »

Why would you need to select all of the previous weeks?

Select the rows with Monday, Jan 10 ... down through the blank row above the Monday, Jan 3 ... row. Copy it. Select just the Monday, Jan 10 ... row. Right click on the row box (like, the one that says that you're on row 10 or whatever). Choose "Insert, shift cells down."

Update your dates and clear out the entries. Et voila.

Alternatively, have a clean dataset on a separate page, without dates, and copy and past that one each time. You can even set it up so that it automatically add 7 days to the row below you and calculates the new date, so you don't need to manually enter that stuff.

Right now I have weeks continuing down until the week starting December 20th, and without putting more thought into it would have continued increasing forever.

I'll probably end up going with a bunch of tabs and just deleting them once they are over a couple months old.

I guess I don’t now why you want to delete the data, either. Like, the storage cost is negligible for a single excel spreadsheet and you can add about 20 rows of data a week for something like a couple hundred thousand years before you hit the page limit.

Then you keep the ability to go back and check on things if you ever discover an issue.

Brody

Elvenshae wrote: »

Brody wrote: »

Elvenshae wrote: »

Why would you need to select all of the previous weeks?

Select the rows with Monday, Jan 10 ... down through the blank row above the Monday, Jan 3 ... row. Copy it. Select just the Monday, Jan 10 ... row. Right click on the row box (like, the one that says that you're on row 10 or whatever). Choose "Insert, shift cells down."

Update your dates and clear out the entries. Et voila.

Alternatively, have a clean dataset on a separate page, without dates, and copy and past that one each time. You can even set it up so that it automatically add 7 days to the row below you and calculates the new date, so you don't need to manually enter that stuff.

Right now I have weeks continuing down until the week starting December 20th, and without putting more thought into it would have continued increasing forever.

I'll probably end up going with a bunch of tabs and just deleting them once they are over a couple months old.

I guess I don’t now why you want to delete the data, either. Like, the storage cost is negligible for a single excel spreadsheet and you can add about 20 rows of data a week for something like a couple hundred thousand years before you hit the page limit.

Then you keep the ability to go back and check on things if you ever discover an issue.

Just for ease of use? I just don't want to have to scroll so much, and it's not an official tracking of hours, so I don't really need a record.

Elvenshae

Right, but why are you scrolling?

New data goes in at the top, based on what you showed there. 95% of the time, you’re dealing with the top couple dozen rows.

Brody

Elvenshae wrote: »

Right, but why are you scrolling?

New data goes in at the top, based on what you showed there. 95% of the time, you’re dealing with the top couple dozen rows.

Yeah. But when trying to copy the old stuff down I've been having to scroll more and more each time. Like I said, it's not the end of the world, I was just trying to see if there was an easy way to make it more automated.

Heffling

Highlight the top set of rows, the first 17 or 18 lines, by clicking on the row number while holding shift. Right click on a row number and this should bring up a list of commands, including "insert". Now you have a fresh set of rows at the top. Now select your one week tracking and copy to the new set of rows.

If you make sure your headers are set to the date format, you can set the new header to be the old header plus 7 so when you copy the cells, it will automatically update the dates.

Now you have a spreadsheet you can use as long as you want, just keep adding to the top.

Elvenshae

Brody wrote: »

Elvenshae wrote: »

Right, but why are you scrolling?

New data goes in at the top, based on what you showed there. 95% of the time, you’re dealing with the top couple dozen rows.

Yeah. But when trying to copy the old stuff down I've been having to scroll more and more each time. Like I said, it's not the end of the world, I was just trying to see if there was an easy way to make it more automated.

The method I have you above means you don’t have to scroll to the bottom. You just insert new rows at the top.

AlanF5

if you really want to copy-paste a large, contiguous block of cells in one go, keyboard shortcuts are your friends. Try this:

1. Select the upper left cell of your block.
2. Press the Ctrl+Shift+Down arrow keys at the same time.
3. (If you hit a blank like and have more data below to include, press Ctrl+Shift+Down again to get the next block.)
4. Press the Ctrl+Shift+Right arrow keys at the same time.
5. Press Ctrl+x keys to cut to clipboard.
6. Press Ctrl+up arrow to return to the first row.
7. Press Ctrl+left arrow to return to the first column.
8. With mouse or arrow, select where you want this block to start.
9. Press Ctrl+v keys to paste.

This is basically scrolling, but using keyboard commands to do it much faster.