Many of our clients use Ms Excel every day.But,we often find that unless they have received some specific training in Excel, they are only using a fraction of the functions of the software. Most people learn how to use Ms Excel by figuring it out as they go along. They are generally   comfortable doing the standard spreadsheet actions:  enter data, create basic formulas, and apply formatting. Although they may not want to be MS Excel experts, they’re always on the lookout for something that can make life  easier. If this sounds like you then take a look at these tips and see if there’s a better way.

On the other hand, if you are a computer genius, and can use Ms Excel like an expert, then why not  bookmark this article of Ms Excel tips. You can then pass them on when you are ready to pull your hair out in frustration when a newbie repeatedly asks you the how to questions.

The list is based on my own experience and techniques I cover in our Ms Excel training courses. if you use the tips below, you may pick up a few handy tricks that you may not have discovered yet. That will help you save time, reduce stress and get more done.

 

1. Access any ribbon command in a few keystrokes

Access keys provide a way to quickly use a command by pressing a few keystrokes, no matter where you are in the program. Every command in a program that uses an Office Fluent ribbon can be accessed by using an access key. You can get to most commands by using two to four keystrokes.

Press and release the ALT key.

image

The KeyTips are displayed over each feature that is available in the current view. You can then select other keystrokes such as P for Print.

 


2. Use Pictures in your Charts

Why not create customised Charts that deliver your message using images rather than  solid colours or patterns.

  • Create your chart
  • Select the element you want to change  and right-click the mouse.
  • Choose Format Data Series,
  •  Fill 
  • Select Picture or texture fill.
  • Select the image you want to use from your folder .
  • Select either stacked or  stretched
  • Click Close.

 

Charts in Excel

 

Note: You have a choice, you can add a single image to an entire data series, or you can click a single bar or column and use Format Data Point to add a different image to each column or bar.

 


3. Add your company logo to Your printed Documents

When you take the time to create a professional report it can be useful to include  your company logo. If you insert it in the header of an Excel worksheet  it will print on each page of the report.

image

 

 

  1. Go to the Insert Ribbon
  2. Insert, Header & Footer
  3. Click the Picture button
  4. Choose an image to insert.

If the image is larger than the header, it will appear around and below the worksheet.This is because the image is not automatically resized to fit when you place an image in a header or a footer.

 

To resize the image

  1. Click the View tab.
  2. Click in the header or footer section where the image is located.
  3. Click in the &[Picture] text of the image.
  4. On the Design tab under Header & Footer Tools
  5. Click Format Picture in the Header & Footer group.
  6. On the Size tab, adjust the size of the image to fit inside the header or footer section.
  7. Click OK.

 

To see the result

Go to View

Page Layout


4. Identify and Highlight Trends  Using Conditional Formatting

The new features in Conditional Formatting can be used to identify and highlight important trends and exceptions in your data. You can choose from Text changes or use the new Heat Maps, Data bars, and Icons.

 

image

 

  1. Select the required data column or row
  2. Click on Conditional Formatting
  3. Select from Data Bars or Icons
  4. Pick the icon that communicates your message
  5. You will have a live Preview of the effect on your data
  6. Click Ok

 

image

To clear the formatting

  1. Select the data
  2. Click on Conditional Formatting
  3. Clear Rules

 


 

5. Quick Filtering Using Tables

Sorting and filtering are two of the most commonly used commands. The Format as Table option allows you to speed up your sorting and filtering. You can also  select multiple filters, sort or filter by colour.

  1. Select the data to be used in your Table
  2. Click on Format as Table
  3. Select your Format Style
  4. You will have a Live Preview of the Formatting options
  5. Excel automatically adds Drop down options to the first row of your Table
  6. You can Filter from the Drop downs

 image

 

Extra Features

  1. Click on the Table
  2. The Table Tools appears above the Ribbon
  3. Click  on  Total Row
  4. A Total Row is added to your table
  5. Click in the cell containing the Total data
  6. A Drop down appears
  7. You can now select many of the functions used to  Analyse Data such as Average, Max, Min etc

 

When you have competed your analysis you may want to convert your table into a Range and remove the Drop downs.

To Convert your Table to a Range

  • Click on the Table
  • The Table Tools appears above the Ribbon
  • Click on Convert to Range  

    Hope these tips are useful for you.  If there are any of your favourite expert power tips that I’ve missed, please feel free to share them in the comments.