Oct 21 2020 07:26 PM - edited Oct 21 2020 07:26 PM
I am new to this and am doing my best to learn daily. I am making a chart that lists where I spend money on bills and how much. i am attempting to put the colors on the sheet where one line is blue and the other is white and vice versa. i tried to do this with the tables tab in excel but it will change my headers if i use it. Is there another way to do this without having to remove all of y headers or having to go line for line with the colors. please help. Thanks in advance
Oct 21 2020 08:03 PM - edited Oct 21 2020 08:06 PM
There is another way, yes, and I'll tell you what it is. I'm curious why you seem put off by the notion that using the Tables tab "changes your headers." In what way does the change affect things badly?
Anyway, the slightly harder way is to use the "copy format" tool, which is the little paint brush on the Home toolbar, there at the left end. it there, at the bottom under the word "Insert"....
Manually create the blue/white alternating colors by row. Highlight two rows and use the copy format tool to copy to the next two rows. Then highlight four rows and copy to the next. Then highlight eight and copy to the next. Etc. It won't take long to have quite a few rows.
Or you could use the Tables tab.
By the way, creating this as a Table offers real advantages you might find you want to use. For just one example, it makes it a lot easier to have Excel summarize for you all that you spent on restaurants, or movies, or gas....
Oct 21 2020 08:24 PM
@mathetes hello, when i tried using tables, it told me that it would change the data in the top cells and it took me a looooong time to place all the bills into the tabs. Like I said, Im new to this so I dont know a lot about the short cuts but if i click that button and it takes away my information, i am going to scream!! Excel is NOT my friend but i am trying to become acquainted lol. I appreciate your assistance and I am going to try your tip right now
Oct 21 2020 11:41 PM
Sounds like you're at the stage where your new 'best friend' could be the 'undo' button ('ctrl+z', if you want a shortcut)! You might also consider keeping a backup copy of your workbook if you think you may perpetrate a disaster.
It is possible to shade alternate rows by using Conditional Formatting with a formula
=ISEVEN(ROW(A1))
but I do not recommend it. Use a Table and choose a style to suite your taste*. That will place you one step ahead of the basic spreadsheet user when it comes to having usable data.
* I always use Table Design on the Ribbon to select the light grey style and then I remove filters and banded rows but that would defeat your objective.
Oct 22 2020 01:34 AM
IMHO, there is no suitable style with banded rows and columns plus alternate rows. Thus options are to create own style, or =MOD(ROW(),2) on the top of the existing style. The latest is the common practice, in particular it is used in Microsoft templates.
Oct 22 2020 02:02 AM
In that case I probably misread the OP initial requirement. I read it as how to get the appearance of a table without actually using a table (I guess I needed a picture). Mind you, from my postscript it might be that I am not the best person to consult on Table and PT styles, since I tend to standardise on black ink on white paper (though I do remember when green text on a black background was all that could be achieved).
Oct 22 2020 02:06 AM
Sorry, my comment was only to your post, not to initial requirements, didn't check them in details.
Oct 22 2020 05:31 AM
@Luscious1 (That's quite a user name you've chosen)
OK. Shouldn't need to scream here, but yes, Excel could be compared to a large chain saw that can work a lot of havoc in an instant used by a novice.
Take a deep breath. There are ways to approach it--and, to put it all in perspective, Excel never itself causes the spilling of blood. Never. It can undo a lot of work, so here right off the bat are two ways at least to minimize that likelihood.
Whew!
OK, now to your worry. Creating your data set as a table--I assume you're talking of doing it through the Insert tool bar, where you just click on the "Table" icon--is NOT going to delete your data at all. It will set your rows to alternating colors and highlight the header row but lo and behold, leave the text and number entries as they were. And believe me, as you move on and learn the power of Tables, you're going to be grateful that you set it up that way.
Here is a link that you can study quickly so that you can approach the task with more confidence.
https://exceljet.net/excel-tables
There are also a number of YouTube videos--just search for "Excel Tables" to find them.
Oct 22 2020 07:35 AM
@Peter Bartholomew Thank you so much, I will give that a shot!
Oct 22 2020 07:36 AM
@Sergei Baklan Thank you for this, I LITERALLY just saw a guy do this on YouTube per someone elses advice. i appreciate your assistance
Oct 22 2020 07:38 AM
@mathetes you are full of knowledge! Thank you so much, i actually copied your response and saved it to a word document for future usage. Thank you so much for your assistance