Keyboard Shortcuts in Excel

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:

shortcuts

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.

shortcuts

 

RobandDeb

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.

Like this post? Let us know!

Calculating Sales Tax for Different Locations in Excel

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.

Tax_1

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:

Tax_Open

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

Tax_2

It’s amazing how a couple of dollars can make a difference.

Questions? Contact us at help@axbean.com

 

RobandDeb

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.

Like this post? Let us know!

How to Calculate Sales Tax in Excel

Visiting relatives

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:

Sales tax calculation

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.

rel-refa

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.

 

RobandDeb

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.

Like this post? Let us know!

Does Your Spreadsheet Bite? Here’s Ten Tips

Ten Ways to Keep Excel From Biting You in the Butt

Ten ways excel

Excel can be really helpful, but if you don’t think ahead, it can be a real pain. Check out these ten Ways to keep Excel from biting you in the butt. Pay special attention to #5. That can mess you up big time.

 

RobandDeb

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.

Like this post? Let us know!

Search a name in a long Excel list?

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:

VLookup Showing Formula list

 

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

VLookup Showing Result list

 

RobandDeb

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.

How Do You Adjust Row Height in Excel?

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.

Row-Height-before
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!

auto-row-height

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!

row-height-after

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.

RobandDeb

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.

ABC’s of Excel column tabs

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

Excel Autofit Column

Adjust Width

Adjust Column Width

 

RobandDeb

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.

Your Website Fast and Furious Tip# 2

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.

Search engines love when pages are upfront and center, it makes their job easier to pick the fastest in the bunch.  As in a previous post about getting your website out of the gate and onto search engines fast using the gzip method, there is a second tip that will keep those wheels burning rubber and make donuts holes around the competition.

Saw the movie, Honey, I shrunk the kidsIn 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, minify is one way to get your website into racing shape.  Below are before and after pictures. And don’t we love to see those?

Before MinifyHTML before. slow

After MinifyHTML_Minified

 

RobandDeb

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.

Why is Microsoft Excel so hard to learn?

Learn Excel Fill handle
The Famous Fill Handle

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.

RobandDeb

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.

Got comments? Enter them below:

404 error page ruining your website?

With a custom 404 error page, it says you care

Dreaded generic 404 error page
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>
 

Click here to see what our AXbean website’s error page looks like.

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

RobandDeb

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.