How To Split A Cell Diagonally In Excel

Someone reached out to me today because they’d been reading one of my Excel guides and were wondering how you could split a cell diagonally in Excel and still be able to enter information into each section.

Short answer is, you really can’t. At least, not that I’m aware of.

A quick internet search turned up a couple approaches that basically involve creating the appearance of a split cell, but the problem with that approach is that if you want to do anything with the values you’ve split, you really can’t.

For two different ways to do that approach,  see here and here.

It bugged me that you couldn’t do anything with the values with these approaches, so I came up with another way to do it. (Which may already be out there, but it wasn’t the first five or six of the search results I found and I told the person who emailed me I’d put it up here with screenshots for them.)

This approach involves using four cells to basically create the appearance of one split cell. Here are the steps.

  1. Select four cells, two in one row and the two directly below them. Fill those cells with white and put a border around them.Excel Create Horizontal 1
  2. Pick two opposite cells. So in this case A and D or B and C and choose Format Cells, Border and then add a diagonal border to create a continuous line across the selected cells. Here I’ve chosen A and D so I want line slanted left to right.Excel Create Horizontal 2
  3. Enter your values into the other pair of cells. So in this case that would be B and C. Middle Align and Center the values in those cells and then change the height of the columns and width of the rows involved to get the appearance you want.Excel Create Horizontal 3
  4. The issue you now have is that for that diagonal box there are two rows and two columns around it. If you wanted to create just one row to the right of the box or just one column below the box, you’d need to use merge cells. Here I’ve used Merge & Center on cells H6 and H7 and on cells F8 and G8. I then used the Format Painter to merge and center the remaining cells. (You have to do it one row or column at a time or you’ll end up with one giant merged cell, which you don’t want.)Excel Create Horizontal4

And there you have it. A way to create a diagonal across a “cell” and still be able to manipulate the values in that cell if you need to.

(For more Excel resources, click here: https://mlhumphrey.com/microsoft-excel-resources/)

Halfway Through Nano

So it’s November 15th. Which means we’re halfway through Nano. I have never actually done Nano myself. It’s not something that would work for me. (Although I have written 20,000 words or so so far this month so may actually hit the Nano goal. But when you self-publish, most months are Nano-style months. Or at least you wish they were.)

And I suspect at this point that there are some folks out there that have maybe decided that Nano isn’t for them either. If you’re one of those people, that’s OKAY. One of the joys and frustrations of being a writer is that there’s no clear path that we all need to follow. It’s like a million streams rolling down a hill, each one taking it’s own unique approach. So Nano didn’t do it for you? That’s fine. Just keep writing when you can and at your own pace.

I’m taking a great class right now called Write Better Faster (https://www.margielawson.com/lawson-writers-academy-courses) that delves into how different personality types approach writing and how they encounter different issues with their writing because of it. Today’s lecture reminded me why one of my best writer-friends routinely does all her writing in a bar and why I have to do my writing in a dedicated home office. And why I would probably be miserable trying to write in a bar and she’d be miserable writing at home.

We’re all different. So if one approach isn’t working for you, don’t beat yourself up or think that means you can’t do this. It just means you need to take a different approach– one that works for you. Along those lines, Patricia C. Wrede had a great post up today: Pavement Conditions. As someone who cusses out the California drivers every year the first snow falls in Colorado and who grew up in the mountains, I found her analogy here very apt.

You have to know where you are and what will work under those conditions. And realize that sometimes what worked before isn’t going to work now. The key is to just keep trying and moving forward.

(And if you find that you’re sort of kind of done with Nano at this point but still committed to writing, might I suggest you take a look at the NaNoWriMo StoryBundle. There may just be a book in there that speaks to you…)

Let’s Talk Pricing

So over on FB a fellow author was essentially calling out trade publishers for how they price ebooks. And they’re not the only person who has ever done that. It happens on a fairly frequent basis that someone questions why trade publishers price ebooks so high.

Usually, the argument that’s made is that it doesn’t cost all that much to put out an ebook. There’s no paper or ink or printing process that needs to happen. So the marginal cost of an ebook is negligible.

But what those arguments all fail to account for is that people are willing to pay that much for those books. Lots of people. Right now The Midnight Line by Lee Child is $14.99 in ebook. It’s ranked number 2 in the Kindle US store. That means somewhere around six or seven thousand people were willing to pay that for that book today. And it’s a book that’ll be in the top of the charts for a while so that many people are going to be paying that much for that book each day for weeks.

What benefit is there to the publisher to drop that price? It won’t improve the book’s rank on Amazon. It’s already #2. Where else can it go?

Well, the argument goes, they’ll get more readers if they drop the price. Okay. True.

But they won’t make more money. And ultimately they may capture all of those readers. The problem with a lot of the “price lower” arguments are that they fail to account for long-term pricing strategies like price-pulsing

Let’s walk through some numbers to show you what I’m talking about.

First, we need a set of assumptions. For our fictitious book let’s assume that there are 12,250 people willing to buy this book. 5000 of those people will only buy the book if it’s available at 99 cents. 2500 will buy it for $2.99 or less. 1000 will buy it for $3.99 or less. Another 1000 will buy it for $4.99 or less. 750 each will buy it at $5.99 or $6.99 or less. 1,250 will buy it for $7.99 or less. 750 will pay $8.99 or less. 250 will pay $9.99 or less.

(I did this in Excel. It’s the chart on the left below. That third column is the cumulative number of customers who’d be willing to pay that price. So everyone would pay 99 cents, but only 250 would pay $9.99.)

Pricing Scenario

Let’s start with the ideal world scenario where we somehow manage to sell our book to every buyer at the maximum price they’re willing to pay. We capture the 99 centers at their price, but also get the $9.99 buyers at their price.

In that scenario, we sell 12,250 copies of the book and we gross $42,127.50. But you have to account for the Amazon cut, so we net $27,756.75.

That’s the ideal scenario. It doesn’t happen, because we have to list our book for sale at one price and even if we change prices over time (as we’ll discuss in a minute) there’s no way to ensure that the customers who are willing to spend $9.99 only see our book when it’s at that price. So in reality we’ll end up with a customer who would’ve paid $9.99 paying $4.99 or even 99 cents depending on when they see the book.

Now, a lot of times the argument is made that you should maximum your sales by pricing low. So 99 cents. That captures the most possible customers. You get all 12,250 customers at that price, no doubts about it. So what do you earn with that approach? You gross $12,127.50, but you net $4,244.63. Same number of sales. But because you priced for the lowest-paying customer, you earn $23,500 less than the ideal scenario.

Of course, as we mentioned, the ideal scenario isn’t likely anyway. So let’s compare the 99 cent approach to another alternative, pricing at $4.99. That captures anyone willing to pay $4.99 to $9.99 but loses anyone who would only pay less than $4.99.  Instead of 12,250 sales you only get 3,750. But those 3,750 gross you $18,712.50 and net you $13,098.75. So you lose 70% of your potential customers but you make three times as much.

Now, what about the final option? Price-pulsing. You list at $4.99, so you’re giving away some potential income there, and then, after you’ve captured those buyers, you drop the price to 99 cents to capture the bargain hunters. Under this approach you sell all 12,250 copies. You do worse than the ideal scenario (because your highest price paid is $4.99) but better than the other two scenarios (because you’re capturing some of the higher-paying market by initially pricing at $4.99 as well as the lower-paying market by dropping the price to 99 cents). In this approach, you gross $27,127.50 and you net $16,044.

So, really, price pulsing is the best approach if you’re willing to be patient about when you capture your buyers.

And for trade publishers with established authors who aren’t struggling for visibility, pricing really high initially and then slowly lowering prices over time is the most profit-maximizing decision. That’s the approach that’s most likely to allow them to stay in business long-term and pay all those salaries and overhead costs and find new undiscovered authors who aren’t established and cant command those prices.

Does that mean self-publishers should price that high? No. There are other factors at play when you’re not Lee Child. Key among those Amazon’s algos that reward early success and seem to have a support level that means that ranking high early means better long-term performance. But it does mean that if you’ve chosen to always price at 99 cents that you’re leaving a lot of money on the table.

 

AMS and Also a Vellum Shoutout

First, let’s talk Vellum real quick. I switched all my files over to Vellum this summer. Me being me I just sort of stumbled my way through how to use it and had to learn a lot on my own through trial and error that wasn’t covered in any of the FAQs. (I was doing a lot of non-fiction formatting.)

But turns out there’s now a pretty good guide to the basics of Vellum available. (You know where? Can you guess? You got it. In the NaNo StoryBundle. After that’s gone if you stumble across this post and want it, look for The Author’s Guide to Vellum by Chuck Heintzelman.)

The guides includes a few of the workarounds I had figured out, like how to have my Also By listing before my title page, and using Vellum Styles before you import from Word. So if you’re new to Vellum or shaky on using it, check it out. It’s a good resource.

(On a side note: After I did it, I honestly wasn’t entirely sure it was worth it for me to have moved all my files over to Vellum. It took a lot of time, I didn’t see any drastic change in sales, and it added an extra step every time I wanted to make a change to a file. But moving to Vellum did make all of my non-fiction titles eligible for Overdrive, which has brought me money, and it also made it incredibly easy for me to participate in the StoryBundle. So for those reasons alone it ended up being worth it. But on a list of things to do, buy Vellum and make all your files pretty probably isn’t where I’d recommend you start.)

Now. On to AMS.

I am not happy with Amazon at the moment.

The other day, I noticed something a little odd when I went to look up one of my romance titles. The first entry I saw in my search results was a Sponsored Product ad for the book followed by the normal, organic search result for the same book.

I almost clicked on the ad and I know better.

So here’s Amazon, taking anyone who comes looking for my book in particular, and charging me money for it by having them click on my ad instead of returning an organic search result first. How tacky is that?

Here’s another example that’s even worse. I have a book under the name Cassie Leigh that is called Puppy Parenting in an Apartment. It’s not a big seller, but I can run AMS on it and generate a few sales here or there. This is what I see when I go to Amazon and search for it:

AMS sponsored ad

The ONLY result is my ad. My actual book with that actual title isn’t shown at all. And, because someone asked, I don’t have the book’s title in my keywords for that ad. So Amazon knows damned well what they’re doing and could actually display my book in their search results, but they’re not.

They’re trying to suck every last penny they can out of their authors instead.

Does this mean abandon AMS?

No. No more than any of the crap they pull means stop selling on Amazon. They’re too damned big and dominate the market too much for it to be feasible for most people to not sell their books on Amazon. (Which is why they can pull things like this…)

Fact is, AMS have become too much of a driver of traffic on Amazon US to ignore without likely taking a hit to your income.

What this tells me is that Amazon is slowly tightening the noose and that authors are going to have to spend even more money to get every single sale on their platform.

I’m grateful to AMS. They let me move from low three figures a month to low four figures a month. But I’m not putting all my eggs in that basket and neither should you.  I have some list-based promos I’m running this month as well as a Kobo promo and I’m playing with FB and Google CPC ads, too.

AMS should just be one part of getting attention for your books. It can’t be everything. Amazon is too prone to pulling the rug out from under authors to rely on them that heavily.

Time to NaNo

And I have to say that Patricia C. Wrede’s post for the day, Looking for Perfection, is a must-read for any writer really, but especially anyone doing NaNo who isn’t quite sure of the ground under their feet.

Remember, with writing, there are no wasted words or bad directions, there’s just learning what works and what doesn’t and constantly improving one little step at a time.

(And, since it is the start of NaNo and you just knew I had to do it, a little reminder that the NaNoWriMo StoryBundle is still available and full of lots of wonderful writerly advice, some that will work for you and some that might not, but all of it worth considering.)

10,000 Copies Sold

Sometime this last month I crossed the 10,000 copies sold mark. Half of that came in the last six months.

I should be thrilled. I should be dancing on the tables, overjoyed that maybe I’m finally starting to get some traction on this whole writing thing and to figure out how to sell what I write.

But I’m not.

While there is a part of me that’s excited about where I am, I know this might not continue on an upward trajectory. I have a color-coded list of income by month and I can look at that list and say, “Hey, there’s December 2014 when I published my first romance novel and a successful romance short and thought I was on my way. My first $500+ month.” And then I can look at July 2015 and see that I only made $56 that month. And that that was followed by August 2015 where I had a $600+ month. And then December 2015 which was again under $100.

So I know better by now. Some folks do great right from the start and just keeping going, but I’d say that for most writers it’s more peaks and valleys.  Good months and then not so good months.

What makes me even more uneasy is where those sales have come from this last six months. In July, almost half of my revenue for the month was from a single romance novel. In October, that novel is next to nothing for revenue, but I had a successful fantasy promo and non-fiction picked up the slack.

On one hand, yay, diversification. When one track slips, another can catch the slack. On the other hand, where do you put your efforts when from month-to-month there’s no good way to predict which titles will do best?

I like to tell my friends entering the corporate world to just get that first job, put your head down, and do the best damn job you can. Even if it isn’t the ideal position for you, you need to focus on where you are to get forward momentum and move up. You want to tell a good story when you’re ready for that next job, you need to commit to the job you have now.

That should be true of writing, too. Pick one path, focus on it, and follow it until you succeed. And I think it is for many. But what path do you choose when you could easily choose any of three paths?

(Me being me, I just wrote a new story on path four that I should just drop already. Someone please smack me upside the head.)

The other killer about that number is that it’s not enough. I know how amazed I would’ve been in year one of self-publishing to sell 10,000 copies. That’s thousands of people I don’t know who paid cash money for something I wrote.

Think about that for a minute.

Let that sink in.

Thousands of people have paid money for something I wrote. How many people can say that?

But then realize that selling 10,000 copies isn’t enough to make this sustainable. For one year, let alone four of them.

I often ask myself why I stick with the self-publishing. There are so many ways I could make money that would be far, far easier in terms of hours spent and money earned. And I think part of the answer is that it’s so damned hard for me. I have some weird, twisted need to fight for what I get or I don’t consider it worth keeping. If I’m not challenged, it doesn’t work for me.

(Also there’s theI get to work alone and from home aspect of it…)

Anyway, yay for me. Pauses to celebrate this milestone. Now time to get back to it.

20K here we come. And sometime this decade, please.

There’s Usually a Reason Regulations Exist

In my old day job I was responsible for enforcing a set of regulations that a lot of people found annoying and stupid. And, because I was on-site enforcing them, I got to hear about it. Often. And later when one of my areas of expertise was a new, but important area of regulation, I heard the complaints from both those enforcing the regulations as well as those who had to comply with them.

“Do we really have to be so hard on this firm just because they failed to properly identify who owns that account?” and “So what if we didn’t report that one little pattern of suspicious activity? Do you really have to fine us for it?”

Moment to moment, I could sympathize with their frustrations. When you have eight million things going on and deadlines and responsibilities, getting tripped up by something that seems minor or having to take a case for something that seems minor is frustrating.

What a waste, right? Everyone has better things to do, don’t they?

One of the rules that we enforced that people found most eye-roll-inducing was the requirement to file an FBAR. For those of you not in the know, an FBAR is a form that the U.S. government requires U.S. persons to file that “is used to report a financial interest in or signature authority over a foreign financial account.”

Basically, if you are a U.S. person and you have control over at least $10,000 worth of funds overseas, the U.S. government wants to know about it. And, if you fail to tell them about it and they can show you did so willfully you can owe up to $100,000 or 50% of the value of the account.

One little form. Screw it up and you lose half the value of the account. Seems absurd right?

Ah, but there’s a reason that form filing requirement exists. And a reason the penalty can be so high.

That’s because charging someone with failing to file that form is a hook a prosecutor can use to catch those who engage in activity they don’t want their government to know about.

Sometimes (often) it’s good old-fashioned tax evasion. Rich people love to hide their funds overseas where Uncle Sam can’t tax it. (Just Google UBS and tax evasion to get a small idea of the size of the issue.)

And sometimes it’s someone representing the interests of a foreign government against U.S. interests who wants to hide what they’re doing.

Either way, that little form is very handy when these things happen.

Proving tax evasion and money laundering and criminal intent can take a ton of effort and documentation and chasing money trails all over the world, and possibly years of effort to build the case. But proving that someone had over $10,000 stashed overseas and they failed to file that little form? That’s pretty damned easy to prove. (As we just saw.)

So all hail the mighty FBAR.

And the next time you’re tempted to complain about some useless regulation, remember: there’s usually a reason regulations exist.

 

I’m Not Good At This Self-Employment Thing

The last time I held a full-time office job was right about this time in 2009. I think my last day was September 30th. I worked on-site on a project out of town until 10:30 that night helping my team finish up a report, went back to my hotel, and flew home the next day.

I then took off the next three months, something I had never done before since I worked through college, both during the school year and summers and all breaks. I’d take a two-week vacation most years after college, but the rest of the time it was work, work, work. Often sixty hour weeks.

I spent part of that time off traveling around New Zealand. I cannot tell you how amazing those six weeks were. Such a beautiful country, such amazing people, such fun things to do. I wanted more of that and less of the stress and deadlines of full-time work.

So January 1st, 2010 I got back to work as a self-employed consultant who worked from home (which in those early years turned out to be New Zealand a lot of the time). I’ve stayed working as either a self-employed consultant or writer ever since.

Now, you might look at that and think, “Well, you seem to be doing something right since you’ve been self-employed for seven years now. Don’t most businesses fail after five years?”

But I’m not really. It turns out I was so good at being full-time employed that it allowed me to coast into self-employment.

In two ways.

First, almost all of my consulting business has come from people I knew when I was full-time calling me up and offering me work. That’s what happened in December 2009 and it’s continued to happen since. Only one project I’ve worked on in the last seven years came from someone I didn’t know before I started consulting.

Worse, I like to work from home and I know that if I go out to companies and say, “Hey, you have any work for me?” and they say, “Yes. And it’s on-site in this random city” that I’m kind of stuck taking work that involves travel I don’t want. So I have sat back and waited for people to reach out to me and say, “Any chance you’re free?” so I can then say, “As long as I can work from home (and as long as the rate’s good).”

That’s not a way to run a sustainable business. A successful entrepreneur should be out there hustling for new clients or for more business from their existing clients. Instead I do things like decide that the work a client was giving me wasn’t challenging enough and wasn’t using my expertise (even though it was highly lucrative) and move on at the end of the project instead of hanging around for more opportunities.

That’s a horrible way to run a business.

And the only reason I can do that is because of the other thing that being good at full-time employment gave me: savings. I can walk away from a good-paying project because I know I can cover my rent this month one way or the other out of savings.

But you can only do that kind of thing for so long. And the longer you do it for the worse it all gets. The savings go down, the people who remember you become fewer and more far between. Your skills atrophy or your knowledge becomes stale.

It’s amazing how long you can coast without realizing that you’re failing.

I envision it sometimes as my early career was one of those ramps you see at the X games and I’m some motorcycle that has gone flying off the end. The initially trajectory was even higher, but at some point now that I’m off the ramp, I’m going to start coming down.

I think the year that happens is 2018.

Unless I finally get my head out of my ass and get disciplined about being self-employed and start to treat it like a real bona fide business that requires deadlines and focus (for the writing) or actually pursuing work (for the consulting).

(Before you get out the violins, I am in the five-figure range with the writing this year, so it’s not like I’m selling three copies. It’s just that it’s not enough for how I want to live.)

Anyway. As part of that effort to break myself out of this funk, today I read one of the books in the NaNo bundle: Time Management by Kristine Kathryn Rusch. Reading it brought home yet again that I don’t treat my writing as a real business. I’m still treating it as a hobby, which I can’t keep doing unless I want to go get one of those day jobs I’m so good at but hate.

So there you have it. Even though I’ve managed to do this for seven years now, I’m not actually good at this self-employment thing. But I’m going to get better at it, damn it.

I swear. (And I’ll get right on that after I waste the rest of this morning on the internet…)

On Collaboration

I’ll admit it, I don’t always play well with others. But when I was working in a corporate environment, I pretty much had to. A lot of the work I did involved coordinating my efforts with others and then writing up reports on what we’d found. Sometimes those reports were a hundred pages long and involved five or six people reading through and making sure that the terminology was correct and that we all agreed on the presentation of the facts.

So when I started writing I was glad to play in my own little sandbox. But there have been times I’ve been tempted to collaborate with a fellow writer. I have one writer friend who has brilliant off-the-wall ideas that I could never match. But I do better than they do with continuity and character development. So at one point I thought it might be good for us to combine those two strengths to write something together.

(We never did, but it was something I thought about.)

One of my hesitations though–other than the fact that I tend to prefer my own solutions to problems–was the legal aspect of it. You can’t just say, “Hey, let’s write a novel together.” You have to think about who owns the copyright, how much you’ll each earn from it, what happens if one of you doesn’t want to continue, etc.

There was just too much that could go wrong and that I couldn’t foresee for me to be comfortable entering into a collaboration like that.

And how do you work together? Who writes the first draft? Who makes edits?

There were just too many moving parts to it for me to be comfortable doing it.

But it turns out that one of the books in the NaNo Bundle (and only in the Nano Bundle at this point) is Writing as a Team Sport by Kevin J. Anderson.

(You might’ve heard of him. He’s the guy who’s co-written all those Dune books.  And many more besides. Been on the bestseller lists multiple times. Sold millions of copies of his books. Just an average Joe, really.)

In that book, not only does he talk about different possible approaches to collaborating and outline some of the pros and cons of doing so, he also includes at the end a sample legal agreement that you can use.

That agreement alone is worth the price of the bundle. For example, it would’ve never occurred to me to include an indemnification clause in a collaboration contract even though it makes total sense to do so now that I think about it. Or to mention plagiarism, for that matter.

The book didn’t convince me to rush out and start collaborating with other authors, but I do feel much more confident in my ability to do so successfully and in a way that protects both me and any potential co-author.

So if you’ve collaborated in the past or you’re thinking about it now, buy the bundle, read the book. It’s a tremendous resource that you should definitely check out and that will probably save you a lot of heartache or drama down the road.

(And, not covered by this book, but shared by another author recently: Be careful if you decide to collaborate with someone you’re dating but not in a long-term relationship with. Keven and his wife and DWS and KKR have both successfully collaborated and stayed married for decades, but imagine collaborating on a book with someone you then break up with.)

Breathe

For some reason the level of “oh my god, the sky is falling” seems to have ratcheted up in the last few days.

The latest one I’m seeing is the “OMG, CreateSpace” drama because they decided to close their eStore. Now, I realize this does impact some people who used the store for discounts and perhaps sales at conventions, but I never used the eStore. I honestly never understood why it existed in the first place. Not like you could search it or really use it to shop.

So a business discontinued one aspect of its product offerings because it was probably minimally used and required too many resources to maintain.

And now there’s a five page long thread on one of the writing forums of people saying they have to switch to Ingram Spark. Um, why? If you were fine putting your books out on Amazon using CreateSpace before this, that hasn’t changed. That’s not what they’re closing.

Now, if enough people abandon CreateSpace altogether that may just lead them to shut the whole thing down in favor of KDP Print. But we’re not there.

So, breathe.  Remember the whole serenity prayer? Focus on what you can control and stop freaking out about things that aren’t happening.

(This from someone who runs contingency plans in their head non-stop. If X, I’ll do Y, If A, I’ll do B, If C, I’ll do D. Oh, look, E happened.)