how to add colors without using the template for tables

%3CLINGO-SUB%20id%3D%22lingo-sub-1806751%22%20slang%3D%22en-US%22%3Ehow%20to%20add%20colors%20without%20using%20the%20template%20for%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1806751%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20new%20to%20this%20and%20am%20doing%20my%20best%20to%20learn%20daily.%20I%20am%20making%20a%20chart%20that%20lists%20where%20I%20spend%20money%20on%20bills%20and%20how%20much.%20i%20am%20attempting%20to%20put%20the%20colors%20on%20the%20sheet%20where%20one%20line%20is%20blue%20and%20the%20other%20is%20white%20and%20vice%20versa.%20i%20tried%20to%20do%20this%20with%20the%20tables%20tab%20in%20excel%20but%20it%20will%20change%20my%20headers%20if%20i%20use%20it.%20Is%20there%20another%20way%20to%20do%20this%20without%20having%20to%20remove%20all%20of%20y%20headers%20or%20having%20to%20go%20line%20for%20line%20with%20the%20colors.%20please%20help.%20Thanks%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1806751%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1806811%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20add%20colors%20without%20using%20the%20template%20for%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1806811%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F841555%22%20target%3D%22_blank%22%3E%40Luscious1%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20is%20another%20way%2C%20yes%2C%20and%20I'll%20tell%20you%20what%20it%20is.%20I'm%20curious%20why%20you%20seem%20put%20off%20by%20the%20notion%20that%20using%20the%20Tables%20tab%20%22changes%20your%20headers.%22%20In%20what%20way%20does%20the%20change%20affect%20things%20badly%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyway%2C%20the%20slightly%20harder%20way%20is%20to%20use%20the%20%22copy%20format%22%20tool%2C%20which%20is%20the%20little%20paint%20brush%20on%20the%20Home%20toolbar%2C%20there%20at%20the%20left%20end.%20it%20there%2C%20at%20the%20bottom%20under%20the%20word%20%22Insert%22....%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_0-1603335753482.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F228399iF136B14D815E0219%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22mathetes_0-1603335753482.png%22%20alt%3D%22mathetes_0-1603335753482.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EManually%20create%20the%20blue%2Fwhite%20alternating%20colors%20by%20row.%20Highlight%20two%20rows%20and%20use%20the%20copy%20format%20tool%20to%20copy%20to%20the%20next%20two%20rows.%20Then%20highlight%20four%20rows%20and%20copy%20to%20the%20next.%20Then%20highlight%20eight%20and%20copy%20to%20the%20next.%20Etc.%20It%20won't%20take%20long%20to%20have%20quite%20a%20few%20rows.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOr%20you%20could%20use%20the%20Tables%20tab.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1806840%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20add%20colors%20without%20using%20the%20template%20for%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1806840%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3Bhello%2C%20when%20i%20tried%20using%20tables%2C%20it%20told%20me%20that%20it%20would%20change%20the%20data%20in%20the%20top%20cells%20and%20it%20took%20me%20a%20looooong%20time%20to%20place%20all%20the%20bills%20into%20the%26nbsp%3B%20tabs.%20Like%20I%20said%2C%20Im%20new%20to%20this%20so%20I%20dont%20know%20a%20lot%20about%20the%20short%20cuts%20but%20if%20i%20click%20that%20button%20and%20it%20takes%20away%20my%20information%2C%20i%20am%20going%20to%20scream!!%20Excel%20is%20NOT%20my%20friend%20but%20i%20am%20trying%20to%20become%20acquainted%20lol.%20I%20appreciate%20your%20assistance%20and%20I%20am%20going%20to%20try%20your%20tip%20right%20now%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1807090%22%20slang%3D%22en-US%22%3ERe%3A%20how%20to%20add%20colors%20without%20using%20the%20template%20for%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1807090%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F841555%22%20target%3D%22_blank%22%3E%40Luscious1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESounds%20like%20you're%20at%20the%20stage%20where%20your%20new%20'best%20friend'%20could%20be%20the%20'undo'%20button%20('ctrl%2Bz'%2C%20if%20you%20want%20a%20shortcut)!%26nbsp%3B%20You%20might%20also%20consider%20keeping%20a%20backup%20copy%20of%20your%20workbook%20if%20you%20think%20you%20may%20perpetrate%20a%20disaster.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20is%20possible%20to%20shade%20alternate%20rows%20by%20using%20Conditional%20Formatting%20with%20a%20formula%3C%2FP%3E%3CP%3E%3DISEVEN(ROW(A1))%3C%2FP%3E%3CP%3Ebut%20I%20do%20not%20recommend%20it.%26nbsp%3B%20Use%20a%20Table%20and%20choose%20a%20style%20to%20suite%20your%20taste*.%26nbsp%3B%20That%20will%20place%20you%20one%20step%20ahead%20of%20the%20basic%20spreadsheet%20user%20when%20it%20comes%20to%20having%20usable%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%26nbsp%3BI%20always%20use%20Table%20Design%20on%20the%20Ribbon%20to%20select%20the%20light%20grey%20style%20and%20then%20I%20remove%20filters%20and%20banded%20rows%20but%20that%20would%20defeat%20your%20objective.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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

10 Replies

@Luscious1

 

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

mathetes_0-1603335753482.png

 

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

 

@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

@Luscious1 

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.

@Peter Bartholomew 

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.

@Sergei Baklan 

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

@Peter Bartholomew 

Sorry, my comment was only to your post, not to initial requirements, didn't check them in details.

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

  1. Acquaint yourself with the "Undo" button, already mentioned by @Peter Bartholomew . It's the little left-pointing arrow with a tail, up top on the green tool bar at the very top of the Excel window. Or available as a menu selection under the main Edit menu
  2. Make a copy of your file BEFORE doing anything you're worried about. Back to the main menu again, use File....Save As or Save a Copy. That way, IF a disaster should happen, you'd have a back-up copy and could just carry on.
 

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.

 

 

 

 

@Peter Bartholomew Thank you so much, I will give that a shot!

@Sergei Baklan Thank you for this, I LITERALLY just saw a guy do this on YouTube per someone elses advice. i appreciate your assistance

@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