Home of QuikBots for Microsoft Office Products

When time is short, shortcuts are the way to go

Sometimes, for whatever reason, you need to perform a task in Excel without using the mouse. Maybe you’re headed to a meeting and you just stood up and forgot to insert a chart. That’s when Keyboard Shortcuts come in handy.

Most any task available on the ribbon (the pictures across the top of the screen) can be accessed by a keyboard shortcut.

In this example, you want to add a chart to our spreadsheet before you run off to that meeting. First select the data you want to show in the chart:

Press the ALT key to show the shortcuts, then press the keys that corresponds to the command you want. In this case, the combination will be ALT-N-C or ALT-iNsert-Chart. Click on the type of chart you want to enter, and move the chart to its proper place.

Debbie Wallis and her husband Robert, are owners of AXbean, a guiding light to all things Microsoft Access and Excel. They reside in the central valley area of Sacramento, California between agriculture and government central. When not working, you can see them running about outside, exploring shops, chowing down at all kinds of eateries and whenever possible, being more curious than a cat. Find out more at AXbean.com.

Calculating With Relatives and Absolutes

In my previous post, I explained what relative references are and what they are good for. There are times, however, when you need to make a number of calculations based on a single number.

To continue with the theme of sales tax, Let’s say you want to buy a hundred dollars worth of stuff and want to find out where you can spend the least on sales tax. Your spreadsheet might look something like this:

The amount you want to spend ($100) is at the top of the sheet, and the tax rates for various counties are listed.

Enter the formula (Amount times tax rate), adding dollar signs before the letter and number of the cell where the amount is entered. The second entry is the tax rate in the first row.

Then the formula is copied into the cells below.

Now the formulas look like this:

See how in every cell the formula retains the $C$1? That’s because the dollar signs were added.

Debbie Wallis and her husband Robert, are owners of AXbean, a guiding light to all things Microsoft Access and Excel. They reside in the central valley area of Sacramento, California between agriculture and government central. When not working, you can see them running about outside, exploring shops, chowing down at all kinds of eateries and whenever possible, being more curious than a cat. Find out more at AXbean.com.

When The Relatives Come A Calling…

When you’re working in Excel, the most common way to relate a formula to a cell is through Relative References.

Relative References are used most of the time, and usually you don’t even know they are there. Let’s say you have a list of prices, and you want to calculate the sales tax for each price:

In this case your formula is calculating the value in cell B2 (2.49) times the tax rate, which you have entered as 0.0775. The result is shown in the Sales Tax column. This formula is essentially saying, “multiply whatever is in the column to the left times 0.775.”

So now you want to calculate the sales tax for all of your prices. The easiest way is to select the top formula, click and hold on the fill handle (the dot on the lower right of the cell), and drag down until you get to the last price.

See how Excel magically calculates based on the Relative Reference? In other words, you are telling Excel to calculate whatever is in the column to the left times 0.0775.

The best part is you can copy the formula as many times as you need to. It’s really that easy.

In the next post, we’ll talk about the many ways relative references can help you, as well as the difference between a Relative and an Absolute Reference.

Debbie Wallis and her husband Robert, are owners of AXbean, a guiding light to all things Microsoft Access and Excel. They reside in the central valley area of Sacramento, California between agriculture and government central. When not working, you can see them running about outside, exploring shops, chowing down at all kinds of eateries and whenever possible, being more curious than a cat. Find out more at AXbean.com.

Debbie Wallis and her husband Robert, are owners of AXbean, a guiding light to all things Microsoft Access and Excel. They reside in the central valley area of Sacramento, California between agriculture and government central. When not working, you can see them running about outside, exploring shops, chowing down at all kinds of eateries and whenever possible, being more curious than a cat. Find out more at AXbean.com.

VLOOKUP, Don’t Be So Down

There are times in the world of data when a list in Excel is so long that you would be scrolling forever just to find a single entry. Think about an inventory, or an extensive list of clients. VLOOKUP gives you the ability to type a name that’s in one column, and Excel will return the information you’re searching for in the corresponding column. The magical formula is :

=VLOOKUP(Where you’re going to enter the information, The range of cells you’re searching, Which column holds the information you want to know.)

OK, it’s probably easier to show than tell. Let’s say you have a staff list, and we’ll say there are 500 entries. Obviously finding one employee in 500+ to determine their birth date would involve a lot of scrolling and could be a huge waste of time. So…

VLOOKUP to the rescue!

Now, entering the formula can be a little tricky, so let’s take one step at a time. The format, once again, is:=VLOOKUP(Where you’re going to enter the information (A1),
The range of cells you’re searching (A6 through D12),
Which column holds the information you want to know (4).)

Wait a minute, you say. 4? Where did that come from?

Well, Microsoft in their infinite wisdom decided to stomp on their own rules this time and, instead of using letters for columns, you have to use a column number, based on the range of cells you entered. In this case the birth date is in the fourth column, so you enter 4. So we have =VLOOKUP(A2,A6:D12,4).

Let’s say you want to determine the birth date of a certain employee. The example below shows how using VLOOKUP, you can enter the last name of the employee in column 1 and their birth date will show.

The VLOOKUP formula is entered in B2:

When you enter “Burke” in A2, VLOOKUP shows what is in column D on that line (his birth date).

Debbie Wallis and her husband Robert, are owners of AXbean, a guiding light to all things Microsoft Access and Excel. They reside in the central valley area of Sacramento, California between agriculture and government central. When not working, you can see them running about outside, exploring shops, chowing down at all kinds of eateries and whenever possible, being more curious than a cat. Find out more at AXbean.com.

In my last post, I talked about those letters at the top of the Excel screen and the wonders of Adjusting Column Width. Today we’re going to focus on the numbers at the far left, the Row Headers, and Adjusting Row Height.

Just like the columns are lettered, every row in Excel has a number. And a blank spreadsheet starts out with over a million. I doubt in the history of Excel, that anyone has had to add more rows.

But I Can’t See My Numbers!

Normally, Excel will automatically adjust the row height to the font size in the row. But, in some cases (like the person who worked on the spreadsheet before you did some row tweaking of their own), The Mighty E says “Uh-uh, not playing that game.” (Warning: Excel can have attitude.) So now you’re looking at partial letters and numbers.

Not Pretty

Fixing Row Height Automatically

No worries, just move your mouse to the far left Row Headers, and place the arrow on the line below the row you want to expand. Now doubleclick on that line and watch the magic happen. Voila!

Fixing Row Height Manually

There will be times when you want some white space in a row, just to make things look nice. In that case, the best way to adjust the height is manually.

Just like before, move your mouse to the far left Row Headers, and place the arrow on the line below the row you want to expand. Click and hold the mouse button, and drag down the bottom of the row until it looks just right. Then let go. You’re done!

Remember, once you manually adjust a row, the auto height won’t work. But you don’t care because you know how to fix it now.

Debbie Wallis and her husband Robert, are owners of AXbean, a guiding light to all things Microsoft Access and Excel. They reside in the central valley area of Sacramento, California between agriculture and government central. When not working, you can see them running about outside, exploring shops, chowing down at all kinds of eateries and whenever possible, being more curious than a cat. Find out more at AXbean.com.

What is AutoFit and how can it make a column in a spreadsheet pop?

Does Excel’s alphabet column tabs do anything special? This post is for anyone getting started in Microsoft Excel and don’t want to touch anything for fear of making an irreversible mistake. Don’t worry, there is nothing in Excel that can’t be undone.

Robots pay no attention to this next part. In fact there is a undo button just for us humans. It’s the little backward arrow at the very top left of the screen. So click away and be fearless and know that the little backward arrow will make any wrongs disappear.

So back to the column tabs with all the ABCs . They aren’t just letters in a box showing where all the typing goes underneath. They can do a couple of easy tricks that will give a more professional and easy to read spreadsheet. Instead of one that looks uneven, zigzagged and give unnecessary headaches.

One is the Autofit. It’s the ability to automatically widen a column. For instance, typing a list of U.S. Presidents where one of the names is longer the column. To have the column fit to the longer name, go to the right boundary line of the A column box and do a double click of the mouse. It will automatically adjust the width.

The second thing which doesn’t have a cool name, is what happens when making a list is there are too many white spaces. To narrow the width of the column head the mouse to the right boundary line of the A column box. It should turn from a white plus icon to a black plus icon that has a left and right arrow over a black vertical line. Holding down the left button, the line can be dragged to the left. Or even to the right if you want more white spaces. Here are a couple of gifs that will help visually show what I’m talking about.

Autofit Columns

Adjust Width

Debbie Wallis and her husband Robert, are owners of AXbean, a guiding light to all things Microsoft Access and Excel. They reside in the central valley area of Sacramento, California between agriculture and government central. When not working, you can see them running about outside, exploring shops, chowing down at all kinds of eateries and whenever possible, being more curious than a cat. Find out more at AXbean.com.

An easy way to get your website into the fast lane

There are many reasons why a website might be slow to load. That can have an effect on where it will land on a search engine’s listing. Which is why we are always on the lookout to making our own as well as our clients’ webpages fast and furious.

Saw the movie, Honey, I shrunk the kids? In this tip, it’s Honey, I shrunk the CSS and HTML code. You see, one of the many causes that can make a webpage to run out of steam is the coding, which is the back end of a website. Because of the many codes that gets written into creating each and every page, clutter gets collected, usually because of unnecessary white spaces , unused lines and written comments. So it becomes too big to be fast on its toes. Then it’s time to shrink down all the coding where there are no hiccups or delays. This is where the term minify comes in.

Using a minify generator such as minifycode.com, it will compress any CSS or HTML code and also remove all excess garbage into a faster leaner file which in turn makes any website’s uploads lightning fast. The only two caveats are that it’s a good idea to backup your old code into a separate file. Since there will be no spaces or divisions, it might be hard to read if there are future code changes. The other reason as I mentioned above, the code comments will be deleted. And you probably want to save those. Other than that, minifyis one way to get your website into racing shape. Below are before and after pictures. And don’t we love to see those?

Before Minify

After Minify

Debbie Wallis and her husband Robert, are owners of AXbean, a guiding light to all things Microsoft Access and Excel. They reside in the central valley area of Sacramento, California between agriculture and government central. When not working, you can see them running about outside, exploring shops, chowing down at all kinds of eateries and whenever possible, being more curious than a cat. Find out more at AXbean.com.

Learning basic Excel can dramatically boost your confidence

The feeling of bewilderment is very common when opening an Excel spreadsheet for the first time. There’s a lot of stuff on the screen and it all does different things or one feature can do many things.

Most of the time, people’s first experience with a spreadsheet is usually going in blind without any formal training. I was one of them. The one thing noticeable when opening Excel was the mouse pointer. The arrow is replaced by a plus sign. At first I assumed it had a special purpose on its own. But it doesn’t do anything.

The worst thing I did when starting out is to begin clicking with abandonment. Suddenly those rows of numbers would move someplace else or disappear and it would be like “What just happened?” followed by “How do I get it back?”

The best thing to do is to focus on one feature at a time. The one I would start with is the rectangle box or the cell. This is the space where numbers, words, or names are typed in. There are two basic features that the cell can do. If you put the mouse over a border line, the mouse’s plus sign turns into arrow crosses or if you want to get technical, the move pointer. Holding down the mouse button will allow the cell to be moved to any part of the spreadsheet up, down or sideways. The other feature integrated into that rectangle box is a tiny green box on the bottom right of the cell. That has a name of it’s own, the fill handle. When the mouse button is held down on that little thing it acts like a copy machine and makes duplicates of anything you have in that cell when you drag it up, down, or sideways.

And if you are in a daring mood, with the other hand, hold down the control key and boom, it does something totally different, putting in sequential numbers instead.

There are a lot of features that Excel has to offer, but starting with the very basic instead of trying to learn by trial and error can make a project a little less crazy making.

Debbie Wallis and her husband Robert, are owners of AXbean, a guiding light to all things Microsoft Access and Excel. They reside in the central valley area of Sacramento, California between agriculture and government central. When not working, you can see them running about outside, exploring shops, chowing down at all kinds of eateries and whenever possible, being more curious than a cat. Find out more at AXbean.com.

With a custom 404 error page, it says you care

The dreaded generic 404 error

Humans and websites don’t always get along, especially when it comes to the dreaded 404 error pages. Whether its a broken link, meaning a page that was supposed to load, doesn’t exist, so then a 404 error page pops up instead to say, “Hey you there, that page you wanted to see is out of commission. I really can’t help except to offer you some generic sage advice.” On the business side, allowing a user to come across this one size fits all page can be perceived as such that their business is not that important, especially if you let them hang out there in a error page wasteland without leading back to your website. Creating a custom 404 error page will show that you understand their predicament and want to help them back to familiar territory, your website.

Here are instructions to create a custom 404 error webpage of your very own.

Put this code in the html where you want text to show up. The part in red tells the browser to go back a page when you click on it:

You can type any message you want between the <h3> and the </h3>. Just don’t mess with the text in red.

<h3>Error<br> You have typed in a page that does not exist. Click <a href=”#” onClick=”history.go(-1);return true;”>here </a>to go back to a real page.</h3>

And if you want a more elaborate error page, contact us. We promise no errors.

Debbie Wallis and her husband Robert, are owners of AXbean, a guiding light to all things Microsoft Access and Excel. They reside in the central valley area of Sacramento, California between agriculture and government central. When not working, you can see them running about outside, exploring shops, chowing down at all kinds of eateries and whenever possible, being more curious than a cat. Find out more at AXbean.com.