Submit Hint Search The Forums LinksStatsPollsHeadlinesRSS
14,000 hints and counting!

Create 'ledger paper' in Excel Apps
If you work in Excel, here's an easy way to create alternating colored and clear rows. With alternating patterns, wide reports are much easier to read, as your eye has a visible 'line' to follow across the page. Normally, you might create these patterns by selecting a row in your report, giving it a pattern, and then copying that pattern to every other row. This works great until you add or delete rows, then you have to do it all over again.

But if you use Excel's conditional formatting, you can create auto-updating alternating rows. Here's how it works. Start by selecting the entire rows for the data range you'd like to color -- click on the row number itself and you'll highlight everything. Now select Format -> Conditional Formatting. Conditional formats are formats which vary based on either a value or the result of a formula.

In this case, let's assume you want a format that creates a colored pattern on the even numbered rows, with no pattern on the odd-numbered rows. Click the pop-up menu at the left of the dialog and choose "Formula Is." Set the formula to =MOD(ROW(),2)=0. It should look like this when you're done. The MOD(ROW(),2) portion of the function gives the remainder of the current row number divided by two. If the row is even, there's no remainder; if it's odd, the remainder is 1. The =0 tells the conditional formatting to take action only when the remainder is zero, in other words, on the even rows.

Now click the Format... button and set the pattern you'd like to use -- I find that a 50% light green looks nice when printed on grayscale printers. Click OK and marvel at your now instantly-ruled report. And thanks to the formula in the conditional formatting box, the rules will remain correct, even as you add and delete rows.
    •    
  • Currently 3.20 / 5
  You rated: 5 / 5 (5 votes cast)
 
[34,179 views]  

Create 'ledger paper' in Excel | 28 comments | Create New Account
Click here to return to the 'Create 'ledger paper' in Excel' hint
The following comments are owned by whoever posted them. This site is not responsible for what they say.
Create 'ledger paper' in Excel
Authored by: jspivack on Sep 02, '04 11:43:44AM

You can also format every 3d, or every 4th row (sometimes easier on the eyes) by using MOD(ROW(),3) or MOD(ROW(),4).



[ Reply to This | # ]
Thanks!
Authored by: Viridian on Sep 02, '04 11:49:39AM

Great hint Rob. I've been looking for a more convenient way to do exactly this.



[ Reply to This | # ]
Thanks!
Authored by: bjmorgan on Sep 02, '04 12:13:05PM

This is truly one of those things that, if you use Excel on a day-to-day basis and know the formulas "intimately," it would seem obvious. However, for hacks like myself who need this type of hint to save them an hour of work trying to reformat those dang cells... THANKS!



[ Reply to This | # ]
Create 'ledger paper' in Excel
Authored by: arg on Sep 02, '04 12:24:17PM

OH MY GOD! Thank you so much. I've been wanting to do this for so long. In fact, just the other day I was commenting to a colleague on how I wish there were an obvious way to do this, but I could never find it. Lo and behold! Two days later, here it is on Mac OSX Hints. This is why I love this site and read it every day.

Thanks again!



[ Reply to This | # ]
Macro
Authored by: sinjin on Sep 02, '04 12:27:22PM
Here is a macro so you can instantly do this to any sheet. Just paste it into a new macro module (alt-F11; Insert->Module), preferably in your "Personal Macro Workbook" for all the time access.

Sub Ledger()
    Cells.Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=MOD(ROW(),3)=0"
    Selection.FormatConditions(1).Interior.ColorIndex = 19
'    ActiveWindow.DisplayGridlines = False
End Sub
Note the single quote is a comment tag. Remove it if you prefer to suppress gridlines. You could also record your own macro (that is what I did for the code above) with your own customizations.

[ Reply to This | # ]
Macro
Authored by: deggy on Sep 03, '04 04:53:56AM

I have been able to run the macro on the sheet I have open, but how do I get the macro to live in all new documents I create?



[ Reply to This | # ]
Macro
Authored by: sinjin on Sep 03, '04 02:12:54PM

From Excel Help:
If you want a macro to be available whenever you use Excel, store the macro in the Personal Macro Workbook in the Microsoft Office X->Office->Startup->Excel folder.

Note that this file is usually open, but hidden, every time you start Excel. It should be listed in the drop down menu for places to save a macro, and you should be able to "unhide" it from the Window menu.



[ Reply to This | # ]
Someone knows a similar hint for Filemaker?
Authored by: magir on Sep 02, '04 02:45:04PM

Nice think but as I don't do Excel and most of my "tables" are in Filemaker I was searching for something similar for ages. I tried several things and it's not that complicated to create colored background using formulars but somehow I never got the "alternating" thing work. :-(



[ Reply to This | # ]
Someone knows a similar hint for Filemaker?
Authored by: magir on Sep 16, '04 04:28:49AM
Got a tip by mail - works like a charm :-)
Create 2 fields
xColor - Global - Container
Color - Calculation -
Case(Mod(Status(CurrentRecordNumber), 2) = 0, xColor)

set the xColor field to the color of your choice (in browse mode)

set the graphic format for the calculation field to enlarge without preserving the original proportions.

make sure the fields are tranparent and put the Color field under them so that it shows thru.


[ Reply to This | # ]
Create 'ledger paper' in Excel
Authored by: da5idonimac on Sep 02, '04 03:28:52PM

it didn't work for me (using office 2004), until i changed the formula to this: =MOD(ROW();2)=0
which means changing the comma into a semicolon....



[ Reply to This | # ]
Create 'ledger paper' in Excel
Authored by: legacyb4 on Sep 02, '04 10:52:15PM

Using Office 2004, I get an error using the semicolon:

=MOD(ROW();2)=0

Odd...



[ Reply to This | # ]
Create 'ledger paper' in Excel
Authored by: bboros on Sep 02, '04 11:28:36PM

I think it depends on the your localisation settings. When your system is set to French you have to use semi-colons, whereas in English you use commas.

Bal√°zs



[ Reply to This | # ]
Create 'ledger paper' in Excel
Authored by: joe btfsplk on Sep 03, '04 11:59:39AM

On my Excel, French version, I need to use LIGNE instead of ROW.



[ Reply to This | # ]
Create 'ledger paper' in Excel
Authored by: amplitur on Sep 12, '04 05:26:45PM

German Office users need to use the following formula:

=REST(ZEILE();2)=0



[ Reply to This | # ]
Create 'ledger paper' in Excel
Authored by: morespace54 on Jun 07, '06 11:43:07AM

Yes, it seems that you localisation DOES count.

In french version of Excel, you must use semi-colon (;) and LIGNE (instead of ROW)

So if your OS system localisation is "French" (France, Canada, etc), you most likely use semi-colon (;) instead of colon (:) even if you have an english version of Excel (in wich case you would use "ROW" insted of "LIGNE" for the French version)...

Well, you got the idea...



[ Reply to This | # ]
Create 'ledger paper' in Excel
Authored by: wgscott on Sep 02, '04 04:17:02PM
I wrote a very general "edit" zsh function that allows you to select your favorite gui editor and all sorts of things like that. Feel free to use it.

[ Reply to This | # ]
Create 'ledger paper' in Excel
Authored by: aaronld on Sep 02, '04 11:21:14PM

I usually just select the area that I want every other line shaded. Then use excel's Format -> AutoFormat -> List 1 2 or 3. You can change things like the formating and boarders in the options.



[ Reply to This | # ]
Create 'ledger paper' in Excel
Authored by: andrew_zinn on Sep 03, '04 12:53:01PM

Yes, but then things get screwed up when you delete or insert lines.



[ Reply to This | # ]
Create 'ledger paper' in Excel
Authored by: lamon on Sep 03, '04 05:03:57AM

Nice hint, but I would suggest it has nothing to do with OSX. At the very least, I wouldn't look here fo that kind of things. Rather in a Excel-related site.

Sorry about the rant...



[ Reply to This | # ]
Create 'ledger paper' in Excel
Authored by: robg on Sep 03, '04 08:34:48AM
Thanks for the opinion, but I think I have to disagree ... unless you want me to remove all the existing hints that cover iMovie, Keynote, CarbonCopyCloner, OmniGraffle, GraphicConverter, PowerPoint, Entourage, Address Book, Mail, GarageBand, iCal, iChat, Palm Desktop, iPhoto, Snapz Pro, BBEdit, Terminal, Photoshop, etc.

The site has had an "Applications" category since day one, and it's one of the most popular here -- nearly one-third of the hints on the site fall into the Apps category. Technically, *none* of those are OS X related hints, and they should all go if I were holding hard and fast to the site name. And don't forget about the Web Browsers category; that's basically another Apps bucket with about 5% of the total posts on the site.

As a registered user, though, you can take care of this issue yourself -- just use the Display Preferences and disable the Apps category (and any others you don't want to see), and you won't see any more hints in the category.

The site name is potentially a bit confusing; it might be clearer if it were something like "Mac Hints," but the intent has *always* been to create a site that contained the very best tips and tricks for using OS X. And what fun is it to use an operating system if you never run any applications on top of it? Clearly we'll never try to become "exceltips.com" or "photoshopwizards.com," but I will run hints that seem to be helpful to readers of the site ... judging by the comments, I think this one fits that category.

regards;
-rob.

[ Reply to This | # ]
Solid Gold. Most valuable in everyday work
Authored by: Norm Nager on Sep 03, '04 11:31:03PM

One of the first things I do every weekday morning is visit MacOSXHints to look for ideas and tips that will help me more effectively understand and more affectively use OS 10.3.5 <b>and</b> and my applications, chief of which is Office 2004, including its Excel component.

Look to other sources for info on Excel? I subscribe to the Microsoft Excel Newsgroup among other newsgroups and include in my daily browsing such forum components as MacWorld's.com's Business software forum and MacFixIt.com's Microsoft Office forum. <b>But</b> it was this Thursday in MacOSXHints that I found this pure, solid gold hint.

This Excel hint is clear, concise and interpretive. As a communications researcher, author and professor, I salute Rob on the value, substance and presentation of it.

I copied the hint verbatim together with some ideas I picked up from the comments into my Entourage Notes and already applied it to several spreadsheets, thank you very much.

Respectfully, Norm Nager



[ Reply to This | # ]
Create 'ledger paper' in Excel
Authored by: osxpounder on Sep 07, '04 03:30:18PM

I like things just the way you are running them now, robg. Don't go changin'. Keep up the good work. I, too, check macosxhints first thing every weekday, to start my day, learn some new things, and, very often, improve my experience with OSX.

---
--
osxpounder



[ Reply to This | # ]
Create 'ledger paper' in Excel
Authored by: morespace54 on Jun 07, '06 12:01:42PM

Totally agree... App Hints as well as System Hints are making OSXHints so much more than just a mac Website...

BTW, Could there be a way to "save" some hints I could take a quick look at my prefered (or most usefull) hints later on?

thks - keep it going Rob!



[ Reply to This | # ]
Create 'ledger paper' in Excel - by Columns?
Authored by: molatept on Sep 06, '04 05:42:27PM

Is there a way to make COLUMNS alternately shaded? I tried substituting the word ROW with COLUMN, but it did nothing. Can it be done?



[ Reply to This | # ]
By Columns, works for me
Authored by: sinjin on Sep 07, '04 11:35:41PM

I'm able to sub COLUMN for ROW and get the intended result. A typo maybe?



[ Reply to This | # ]
By Columns, works for me
Authored by: molatept on Sep 09, '04 12:20:43PM

I tried it again - it seems to work - maybe it was a type - perhaps I put COLUMNS instead of COLUMN???

Thanks



[ Reply to This | # ]
Create 'ledger paper' in Excel
Authored by: Nick Sloan on Sep 14, '04 04:22:59AM

In case it should be relevant to anyone who prefers to steer clear of Microsoft, it is possible to achieve the same effect in the RagTime spreadsheet module by giving the active layer a transparent fill and placing it over a striped under-layer.

I would love to know whether any other X-compatible spreadsheets can be reliably striped.



[ Reply to This | # ]
Any luck with multiple conditions?
Authored by: scorpion on Jun 07, '06 06:53:08AM

I love this hint. But does anybody know how I can create the ledger paper and then also bold rows according to a condition? It seems Excel will only do the latest condition and not apply multiple formatting.

Sorry if this doesn't make sense -- first cup of coffee and all.

Thanks.



[ Reply to This | # ]