My AMS keywords are showing me that people are searching on Amazon for how to lock or unlock columns in Excel.
First, a retail site like Amazon is not the best place to do a how-to sort of search. It’s just too granular a question. You want to use Google or Bing or something for that kind of question. But putting that aside…
What those people are probably searching for is how to freeze or unfreeze panes. That’s how you keep a top row or left-hand column (or both or more than one) always visible as you scroll through your data.
So. In case someone needs that…First, you can get the ebook of Excel Tips and Tricks for free on all major retailers. It covers freeze panes at a very basic level along with a number of other little tips and tricks to make using Excel easier.
Also, I have a video up on this, too, on YouTube:
Enjoy. Freeze panes is a lifesaver if you don’t already know it.
I don’t do big releases, as you may have noticed by hanging around here for a bit.
I essentially write a book, get it ready to go, and then when all the links are live do a blog post, send out a message to the newsletter, sometimes remember to post about it on Facebook, and update my website to list the book. Oh, and usually throw an AMS ad or two at it.
(By the way, not only did I release the four books I told you about two days ago, but I also released the six books in the Easy Excel 365 Essentials series, too, yesterday. That would be Excel 365 Formatting, Conditional Formatting, Charts, Pivot Tables, The IF Functions, andLOOKUP Functions. Usually I wait to do that for a bit after the main release since these books are derived from the core series but I had a few days to wait until 2023 so got them ready then and also wanted the LOOKUP Functions book out there ASAP for anyone who knows Excel but doesn’t know XLOOKUP. Anyway. More books. Yay. Now you know. Go learn XLOOKUP if you love VLOOKUP.)
By releasing the way I do, it usually means that I don’t have a lot of work that I have to do leading up to a release, but that I then get to panic that the release was a complete failure for the next week or two after the release date.
Sometimes longer than that.
Since nobody knows the books are coming, most readers aren’t ready to drop money on the day of a release. And it takes time to get ads up and running and in front of the right audience. And for a book to get any visibility from ranking well.
Which means generally for me I don’t see what a book has the potential to be for a month or two.
And sometimes a book doesn’t get launched into the right place or in the right format so it takes even longer than that.
For example. I had that new pen name book I randomly wrote and published last year. I felt like writing something that didn’t fit under any of my existing names so I took a week or two, put it together, put it in audio, got it out there and…
Nothing. It died.
I put it in KU and ran a few AMS ads on it, which got clicks but no reads to speak of and no purchases.
In audio all I can see in real-time (ish) is what’s happening on ACX, which also was ugly. It’s a shorter title so not one that would attract subscription listeners.
I basically wrote it off as a dud. Until I checked my wide audio numbers for November which only came out yesterday.
Lo and behold, it turns out that maybe the market for that particular book is libraries. Because when I saw month-end library listens for the audiobook, that was a promising number. Not huge, but a high enough number to make me think month two could be good if it keeps going.
It took six weeks to know that, though.
Another book I released many, many years ago didn’t get any sort of traction until it was in audio two years later.
Which is all to say that unless you’re savvy as hell about who buys your books and exactly how to position them in front of that audience and also write the types of books that people need day one (so a hot series on the fiction side or a buzz-worthy non-fiction title that everyone wants to be able to talk about), don’t judge your books by their early performance. Things will sort themselves out over the long haul.
It’s also not over until you’ve published in every possible format you can think of, which probably means it’s never truly over…
Anyway. Today’s release-related thoughts. Off to update the website with some book links I guess. Or to have dinner maybe…(The admin side of launching ten books in a week is downright painful.)
You may see some symmetry in those titles compared to the original Excel Essentials series (Excel for Beginners, Intermediate Excel, 50 Useful Excel Functions, and 50 More Excel Functions) and that’s because these are the updated versions of those titles.
Now, to be clear, the original Excel Essentials series still very much has its place for any users of Excel up to Excel 2019 as well as any users who need to worry about backwards compatibility with an older version of Microsoft Excel.
So if you want to learn how to use Excel and be able to function in any version of Excel, then the original series is still your best choice.
But Microsoft has made enough improvements to Excel over the years that if that’s not a concern for you, then this new Excel 365 Essentials series is the best choice. Because it incorporates things like a faster way to apply borders, how to use pivot charts, as well as some of the newer functions that can completely replace older functions.
For example, XLOOKUP, which is available in Excel 365 can take the place of VLOOKUP, HLOOKUP, and perhaps a few others. And TEXTJOIN takes the place of CONCATENATE (at least how I used it). And IFS replaces the need to use nested IF functions.
All tremendous advances. (I may in fact have included a marriage proposal in 102 Functions for whoever developed XLOOKUP, it’s that amazing a function.)
So if you want the latest and greatest, Excel 365 Essentials is my take on that as of December 2022. If you need tried and true and won’t fail you no matter which version of Excel you use, stick to Excel Essentials. And, of course, if you have Excel 2019 then the Excel Essentials 2019 series is there for you. (All links can be found on this page.)
Now, I also mentioned a free book. I put together Excel Tips and Tricks, which is a short little book that includes my favorite shortcuts and ways to make life easier. Things like freeze panes and print titles are covered in there, for example.
In ebook it is free on all major retailers. Amazon may or may not price match on any given day, but if they aren’t it’ll be just 99 cents there. But all the other retailers who are sane enough to allow free pricing will always have it free. Or you can get it on Payhip direct from me if you want. There is also a print version available, but that is, sadly, not free.
The other day I went to check my book page for some reason and saw this:
Excel for Beginners now has 1,000 ratings on Amazon. That’s not reviews mind you, just ratings, but still a pretty nice little milestone to reach.
So in honor of that event, I’ve decided to put the ebook versions of Pivot Tables and Excel 2019 Pivot Tables to free until the end of September. (Amazon, as usual, is going to not be free just yet, but the other stores are and Amazon will catch up in a day or two.)
I also figured I’d share my little origin story on these books.
So.
Five years ago I sat down to write a book on using pivot tables in Microsoft Excel. This was back in the day when it was not at all easy to know how many copies of a book you had sold through Amazon. There were graphs you could see, by country, but no pretty summary numbers like we have today.
You could see sales for a country for that day for that format by holding your mouse over the graph bars, but to get one bottom-line number required exporting a spreadsheet and then applying a pivot table. (Or summing the data if you only had one title.)
I told people about how they could use pivot tables to do this more than once on Kboards, but usually the response was “I don’t know how to use those.”
My thought was, “You could try Googling it?”, but eventually I got tired of hearing authors say they couldn’t tell you how many sales they’d had that month when using pivot tables was so easy. Two minutes of effort and they’d have their answer.
So I figured I would write a book about exactly how to do it. With screenshots and everything. Push this button, go here, there you go. That would give me something to point people to and if they were still clueless at that point it was on them for not wanting to follow the step-by-step instructions in the book.
And it was a unique angle on using Excel that I hadn’t seen covered yet so it made sense to put it out there because no one else had.
I sat down to write the book. A quick little title. Just knock it out.
But then I realized I had a problem. I didn’t want to walk people through Excel from absolute beginner to using pivot tables just for this one task. That was a lot.
(I sort of had done a walkthrough from start to basic math with the Juggling Your Finances Basic Excel Primer book that was a companion to Budgeting for Beginners but I thought my audience for this book was going to be those people who already knew Excel some.)
So I had a dilemma. Do I go from “this is Excel” all the way to “this is how you download this specific report on Amazon and apply a pivot table to it?”
Or did I need to split the material up into separate books? That way people could join in on the learning process wherever they were in their own personal knowledge without bogging down in things they didn’t need to learn.
Luckily for me, I decided to split the material up.
Ultimately, I ended up publishing four separate titles in September 2017: Excel for Beginners (for anyone brand new to Excel), Intermediate Excel (for those who knew the basics of Excel but didn’t know things like pivot tables and conditional formatting), Excel for Writers (which covered things I’d done with Excel that were writing-related but not self-publishing-related), and then Excel for Self-Publishers. (which was the book I’d actually set out to write and which at the end of the day also included a lot of AMS-related uses of Excel as well, that are also no longer needed today thanks to advancements in reporting by Amazon).
At that point I had what may or may not have been a lucky break.
I’m not sure how much it did or didn’t contribute to sales, but I think it maybe helped a little. It certainly didn’t hurt.
Basically, one of the groups I was in had an open call for any material that might work for a NaNoWriMo bundle, and I mentioned the two books on Excel for writers and self-publishers.
The books were included in the bundle which ran in October and November 2017.
(I say that’s luck, because, yes, I did have the books ready and had done the work that put me in the path of hearing about that invitation. But the fact that someone made that open call and that they included little no-name me, was pure luck.)
Maybe a few of those folks circled back to the Excel for Beginners and/or Intermediate Excel titles and gave them a little boost.
Maybe they didn’t. I’d also started some AMS ads. Those could’ve been the reason the titles gained traction.
Whatever the cause, first month sales of Excel for Beginners were 24 copies. Second month, 47 copies. Third month, 69. Fourth, 122. And so on.
Sales eventually hit their level. They can’t double each month forever.
End result, between September 2017 and August 2018 I had a four-fold increase in sales and an eight-fold increase in profit, largely driven by those Excel titles.
And they’ve held relatively steady for me ever since. I have to work harder for those sales now than I did in 2018, but they’re still there.
All because I had a niche little area of expertise and was annoyed enough that other people didn’t know about it to write a book. And because I luckily ended up in the process writing a book that was more universally accessible than the subject that originally started me down that road.
Other than that bundle, Excel for Self-Publishers, the title that I originally set out to write, only ever sold 50 copies. And it’s now only available on Payhip because so much has changed with the data that’s now available to self-publishers that you don’t need to jump through so many hoops so I unpublished it from the major stores.
(I leave it on Payhip because it has how to calculate an average customer value and series sellthrough which are still useful. But I replaced it with Data Analysis for Self-Publishers which talks about the thoughts behind those kinds of calculations but doesn’t do the step-by-step thing that the original book did.)
Anyway.
It would have never occurred to me to write a beginner-level book on Excel otherwise.
But I did. And I’m happy I did. Because 1,000 reviews on Amazon later, that little title still chugs along and hangs out in the top 100 for its category most days.
Now, would I have the same result if I did that today?
No.
AMS has changed drastically since then, so those ads would not work near as well for who I was then if I published that same title today.
Also, because I mentioned having success with my Excel books a lot of others jumped in there, too, starting sometime in 2018. So there are far more titles competing in that space now than there were when I first started out with those books.
When I first published them the trade publishers weren’t even using AMS to advertise their books. Now they are.
And there was only maybe one or two self-publishers in the space. So a $12.95 paperback stood out as a good, affordable alternative to the $40+ versions from the trade publishers.
Now…Not so much.
So the lesson to take is not, “write a book about Excel for beginners.” The lesson is, “find a personal pain point where you can share knowledge”, “find your own angle on that pain point that no one else has covered yet”, and then “try to leverage off of that to find something more universal or broadly applicable.” It may just work.
And, please, if you have access to Excel and don’t know how to use pivot tables and you work with data that needs to be summed up, go download one of the two pivot tables books and learn it. Please. For my sanity.
It’s summer. It’s hot. I have to move. I don’t want to. Because it’s hot. So I put a few books to free to make myself feel better.
Which is your gain. Haha.
Until the end of July (unless something drastic happens and I change my mind), you can get the ebook version of either Excel for Beginners or Word for Beginners for free on all major retailers.
If you already have the print versions it’s a great time to pick up the portable, color versions. If you don’t have one or the other yet, great time to check ’em out, because, free.
Now, keep in mind, Amazon sometimes has a mind of its own so it may fall off of free there unexpectedly. So if you click to Amazon and it shows as $4.99, try one of the other stores before you give up.
I don’t normally do one of these, but it was a busy year and it’s quite possible that I published a few books but never mentioned that I’d done so.
I’m not one for big releases. I put a book out there, throw some AMS ads at it, try to remember to post to my blog and send a newsletter and maybe remember to do a FB post, and then it’s on to the next. If it does well, great. If it doesn’t, I turn the ads off and carry on.
So, 2021:
Let’s start with the video courses. New ones as well as two old ones that had been unpublished and I republished:
And now on to the books:
And some of the non-ML Humphrey stuff as well. The MH Lee title is very short, but I published it so I could publish the audio since that was a little experiment I did this year to see if I’d want to narrate the cozies myself since they’re written in first-person. (Conclusion, probably not because I’d need a better recording space than I currently have although it was interesting realizing how much more dynamic a story is in my head than on the page.):
I didn’t feel like it but I actually got a lot done this year. Keep in mind that about half of what you see above is either a collection or derived from other material so it was less writing than it looks like. But still…Not bad for a crazy up and down year where I also had to go through the process of listing my house twice, the process of selling it once, lived in a hotel for three weeks with my dog, and then had to move into a new place and go through all the fun of unpacking and setting up.
(Which I must find somewhat fun since I’ve already rearranged my office and my bedroom at least once each.)
What does 2022 have in store? I’d really love to dive in on some fiction. I’ve actually on my personal FB declared it the year I “walk into the woods” and basically try to get away from outside distractions so I can really focus in on the projects I want to do.
Of course, just yesterday I wrapped up the second draft of a non-fiction title. And it probably warrants a second title since I’d wanted to do a book about X and Y but only X fit in the first book. So…good intentions and all that.
Honestly as long as I’m moving forward and enjoying myself I call that a win. Hope you all had a good holiday and wishing you as good a new year as it can be.
After I published the Affinity Publisher video courses to Teachable, I decided I might as well add the Excel for Beginners, Intermediate Excel, and Easy ExcelEssentials video courses there as well.
These were courses that I originally published on Udemy in 2018, but I didn’t do much with them and eventually Udemy asked for tax information but in such a way that I couldn’t figure out how to give them an EIN for a sole proprietor so I just unpublished them rather than deal with it.
Me being me, once I put those courses up on Teachable I realized that I should also complete the circle and put together a video course on Excel formulas and functions to correspond to 50 Useful Excel Functions and 50 More Excel Functions, so there is now also a video course available on Teachable that covers the content of those two books, Excel Formulas and Functions.
(It’s a long one and if I never have to talk about another Excel formula or function again I will be a very happy person. Of course, I say that but then I’ll get all excited about some new formula or function and want to do so anyway.)
I’ll probably put more content up later but I’m writing a novel for NaNoWriMo this year, so those are the only ones for now. But for anyone looking to learn Excel who learns better by seeing, you do now have those courses available as an option.
Use code MLH50 to get 50% off on most of the courses. (Not on the individual Easy Excel titles like Formatting, IF Functions, etc. because those are priced cheap already, but it should work on the longer courses.)
Those who’ve been around here a while may remember that at one point I had Excel for Beginners, Intermediate Excel, and the Easy Excel Essentials content (Printing, Formatting, Pivot Tables, Charts, IF Functions, and Conditional Formatting) available as video courses through Udemy.
I pulled those courses when they introduced a nonsensical tax form that I couldn’t fill out. But I still had the videos. And when I went back and looked at them this week, they were actually good.
They use the whole “I will tell you, then I will show you” approach which is not my personal favorite, but it is theoretically the best way to present information for a large audience, so that’s why I did them that way.
Anyway. I have now added those videos to the Teachable store I set up. So if you prefer to learn visually that is now an option. Use code MLH50 on Excel for Beginners or Intermediate Excel to get those half off. The individual Easy Excel Essentials courses are also available for just $15 a pop.
I expect I will add more video courses. I’ve started prep for an Excel formulas and functions course and know I definitely want to do that one to complete that series of videos, but not sure what will come next. So if there’s some topic you’d really like to see covered, now is the time to let me know. No guarantees I’ll cover it, but if it was already on the list it may move higher.
Also, when I put together the Affinity Publisher for Fiction Layouts content, I decided to put templates that people could download up on Payhip. So if you want an Affinity Publisher file that already has the master pages and text styles created that’s where you can find them. It saves some time, for sure, but you still absolutely need to know the basics of working in Affinity Publisher for a print layout to effectively use them. They’re not for an absolute novice.
Alright then. That’s it. Hope you’re all doing well.
First, I had a few new releases recently. Between moving and unpacking (how many books can one person own??), I didn’t post about them here because they were compilations of the Excel, Word, PowerPoint, and Access titles I released earlier this year.
But here you go: Excel Essentials 2019, Word Essentials 2019, PowerPoint Essentials 2019, and Access Essentials 2019. These are perfect choices for anyone who knows they want to go as far as I can take them with learning one of the above programs. Otherwise I recommend starting with the beginner title in each series because often that’s all someone needs to learn when they’re just getting started.
First, I had to work on these books a little earlier than I wanted to because of the lovely changes that IngramSpark (“IS”) has made recently. If you’re not aware of them, then settle in for a quick rant.
IS charges about $50 for every new title that’s uploaded to them and then they charge $25 to change a cover or change the interior. But there have always been promo codes floating around. Participate in NaNoWriMo, get a code for the next six months. Go to a conference, get a code for the next six months. Join a member organization like ALLI or IBPA get a code for however long it last until they decide to change it.
I joined IBPA and had a code from them that I happily used for all of my uploads and updates. But then suddenly this year IS decided that you could only use that code 50 times in a year. Which seems like a lot. 50 times. Who would need more than 50 uses?
Well, let’s look at my year-to-date. I published 22 titles so far. The four main Excel 2019 titles, three Word 2019 titles, three Access 2019 titles, three PowerPoint 2019 titles, and the Microsoft Office for Beginners 2019 title were all in paperback and hardcover. So that’s 28 uses of a code right there. Plus the other 8 titles that in this case were just paperback. So 36 uses for new titles.
Normally I might do something like update other titles I already had out to change the Also By page to reflect my new releases. If I did that for my cozies at this point I have 9 titles in paperback, paperback large print, and hard cover large print. That right there is 27 code uses and we’re not even touching on the new title which would be another three uses. So for one new release of my cozy mystery series I’d need 30 code uses.
Well, imagine how unhappy I was when IS decided that limiting codes to 50 uses per year wasn’t enough and instead decided that you could only use a code five times in a month. More uses per year (60), but it would take me six months to get all of my cozy titles updated for a new release under that scenario and wouldn’t be able to publish or update any other titles in the interim.
What makes it even worse is that they seem to have an automated process for interior updates once a book is published. So they’re literally charging $25 for a process that doesn’t involve a person. And they’re changing their rules to try and get that money out of authors who’ve been publishing with them for years who didn’t sign up for that kind of b.s.
(Their stated reason is because they want to support legitimate publishers only and not scammers, which…well. Way to throw the baby out with the bath water.)
So anyway. This latest release of four titles involved eight books, one paperback and one hard cover of each title. So to avoid paying $50 for books that might not make that money back (I do the hard covers for libraries but there’s no guarantee they’ll want the collections), I had to start the process in July and use my five codes in July and then finish it in August to do the last three titles.
Good times. Love me some self-publishing fuckery. (And there is always self-publishing fuckery.)
What else? If you haven’t yet heard about A+ Content on Amazon, it’s worth taking a look now that they’ve opened it up to all self-published authors. I’ve submitted some content for some of my titles, but it takes about a week to get approved from what I’ve heard so I don’t have examples of my own yet, but here is a link to what an author I know has done and I think it looks really good.
Scroll down to the From the Publisher section to see what she did.
One of the advantages with adding this content is that it can push an entire carousel of Sponsored Product ads down below that section, allowing authors to own more of their product page. (Not always, but sometimes.) Also, it’s pretty if done well. I think each of the images she’s added to her page there make a reader more likely to buy the book. For example, it takes what was already a strong image from the cover and makes it much larger and more engaging.
To add A+ Content, click on Promote and Advertise for one of your books and then scroll down to the A+ Content section. Next, choose a marketplace and click on Manage A+ Content. That takes you to a separate dashboard where you can create your content.
You can add the same content across books by listing multiple ASINs. (If you have a lot of books you should really have a list of these as I discussed ages ago in Excel for Self-Publishers which is no longer widely available but still available on my Payhip store.)
Content has to be added for each country, but there’s a note that they’ll let you know which other countries would accept content in that language and let you carry it across. I won’t know how well that works until my content is approved and I can test it out, but basically if you’re adding new content, just do it for one country and wait for it to get approved before you try to do all of the countries.
Also, if you use the comparison chart option it’s not well-sized for cover images, but you can do a white background and have your cover only take up part of the allowed space and that seems to work.
What else? I’m sure there were some other writerly thoughts I’ve been having lately but I’m still in post-move malaise so don’t ask me what they were. If I remember, I’ll post again.
Today I received an interesting Excel question from a user and thought I’d share the solution I came up with here.
Here was the question: In excel, what is the formula for taking a cost times a 30% margin then rounding it up to the next $x.x5 or $x.x9 (example: $3.45 x 30% margin = $4.93, roundup to $4.95) or (example: $3.48 x 30% margin = $4.97, roundup to $4.99)
At first, I thought, there’s no easy way to do this. And there isn’t. A quick search for functions that would do this specific task resulted in nothing.
But then I remembered that I’d covered the CHOOSE function in Excel 2019 Formulas & Functions and that it could be used for this. As it turns out, it still wasn’t easy to do, but it is doable.
(And there may very well be a much simpler solution to the problem, but this seemed to work for me, so it’s what I went with.)
So we start with the (ROUND(A1/0.7,2) portion which is just giving us the number we’re working with.
Next is the RIGHT function. What that’s doing is taking the right-most digit of our number. So in the examples above that would either be the 3 of $4.93 or the 7 of $4.97.
Finally, we have the CHOOSE function which basically provides a different result depending on the value you give it. So in this case a 1 returns a value of .04. A 2 returns a value of .03, etc. Once we cross the 5 mark we have to switch things up a bit so that 6, 7, 8, and 9 will result in values that end in a 9 instead of values that end in a 5.
That value gets added to our original calculated value to create a result that ends in either a 5 or a 9.
But because I used the RIGHT function we have a problem. Any value that’s a whole number, like $9.00, isn’t going to calculate properly because RIGHT will pull in a value of 9 not 0. And any value like $7.30, $8.20, etc. will also not work properly because RIGHT will pull a 3 or a 2 or whatever the next actual digit is instead of the zero.
We can solve that, though, with IF functions. Namely,
IF(TRUNC(A1/0.7)-A1/0.7=0,0.05,
and
IF(ROUND(A1/0.7,1)-ROUND(A1/0.7,2)=0,0.05,
IF(TRUNC(A1/0.7)-A1/0.7=0,0.05 basically says that if it’s a whole number then add .05 to it. And IF(ROUND(A1/0.7,1)-ROUND(A1/0.7,2)=0,0.05 says that if the rounded value with one digit is the same as the rounded value with two digits, then also add .05.
A little messy, but it solves those two issues.
And I think all of it taken together answers solves the issue.
M.L. Humphrey 