Learn Excel Now

Conquer the Fear of Excel

  • Home
  • Training
    • Excel
    • MS Office
    • Outlook
    • PowerPoint
    • Word
    • OneDrive
    • Google
  • All-Access
  • eLearning
    • Excel Foundations
  • Resources
  • Login

Excel Essentials: Adding Images to a Spreadsheet

September 28, 2016 by Tyrone Pernsley

Excel has many tools to help you master the look and feel of your spreadsheet. One feature it offers is the ability to add images to your spreadsheet. It can be an object from clip art, an original photo, downloaded images – as long as it’s in an image file, you can add it to Excel.

Let’s say you were putting together a sales report to send out to potential vendors. You are working with the following spreadsheet:

image-1

Now, your boss tells you he wants to add the company logo to the spreadsheet to give it a more polished and professional look. This is the process for adding the company logo:

adding-images-to-a-spreadsheet

  • Step 1: Prep your spreadsheet for the added image
  • Step 2: Go to the “Insert Ribbon”
  • Step 3: Click on the icon for ‘Pictures’
  • Step 4: Select the image you want to use in the dialogue box
  • Step 5: Click OK
  • Step 6: Using the Drag options on the image, resize to preferred size
  • Step 7: Double check to make sure it looks good.

You are now done with adding the company logo. Adding images to your spreadsheets can add value, relevance or just make them more fun.

We at Learn Excel Now hope you now feel confident adding images to Excel and formatting them to make your spreadsheets look great.

Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter

Kevin – Learn Excel Now

Excel Basics: Adding, Editing & Formatting Comments

August 31, 2016 by Tyrone Pernsley

In Excel, you can add a comment to any cell. This could be a note to yourself, a reminder, a correction, a question – anything that is useful. Using Excel comments is particularly useful for workbooks that are used to share information and to collaborate on projects. At times though, you might want to go beyond the basic comment format and give your comments more character.

First, let’s explore the basic comment. To add a comment, you will:

  • Right click on the cell you want to comment on
  • On the menu, select comment
  • Enter your comment in the comment box that opens

Once you click out of it the comment will be part of the cell

Excel Comment Gif 1

Any cell with a comment has a small, red triangle in the upper right hand corner.

Now if anyone opens this workbook they will be able to view what you wrote. This is the basic comment format:

Excel Comment Basic

Formatting Comments

As with most things in Excel, you can do more with your comment than what the default format has to offer. To open editing for your comment, you will:

  • Either create a new comment or click on Edit Comment in the menu
  • Once the comment box is open, right click on outer edge of the box
  • Select Format Comment

Once you’ve open the format comment dialogue box, you have several options:

Excel Comment Menu

From there, you can make whatever changes you see fit:

Excel Comment Gif 2

Why Change Excel Comment Formatting?

There could be several reasons to change the formatting for comments:

  • Personal preference
  • Color coding types of comments
  • Adding urgency or priority to comments
  • And many others

We here at Learn Excel Now hope you found this week’s tip on Excel comments useful.

Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter.

Kevin – Learn Excel Now

Excel Formatting Essentials: Hiding Extra Rows & Columns

August 24, 2016 by Tyrone Pernsley

Removing Lines In Excel Featured ImageToday’s Excel blog post comes directly from a Learn Excel Now customer who was having trouble formatting her spreadsheet. She wanted to hide the extra rows and columns of the spreadsheet grid after creating a chart.

Here is what she wrote to Learn Excel Now:

“I produce weekly reports for my boss. He likes them to be in a 3D Pie Chart. But I can never seem to format it correctly. He showed me the report my coworker Carol sends him. It’s just a single chart – there is no spreadsheet behind it! It looks so clean and neat, like a single image. I want to be able to do that.”

And she can. For today’s example we will take the following spreadsheet and create a chart. Then we will format that chart so that it is the single image in the worksheet.

Removing Lines in Excel Image 1

To create the chart:

  • Highlight the group of cells you want to chart
  • Go to the Insert tab on the home ribbon
  • Select the type of chart you want to use (3D Pie Chart in this case)

To make the chart the single image on the worksheet:

  • Expand the chart to cover the underlying data in the spreadsheet
  • Click on the first column visible to the right of the chart
  • Enter the keyboard shortcut Ctrl+Shift+à (right arrow key) to go to the last column
  • Open the right click menu and select Hide
  • Click on the first row visible underneath the chart
  • Enter the keyboard shortcut Ctrl+Shift+Down Arrow Key to go to the last row
  • Open the right click menu and select Hide

You have now hidden all of the rows and columns in the spreadsheet, leaving the chart as the lone visible image on the worksheet. Here is a Gif showing what to do:

Removing Lines in Excel Gif 1

Using the keyboard shortcuts to go to the last row and column can come in handy in many situations. Anytime you want to make the spreadsheet a close off image, you can hide those rows and columns.

We here at Learn Excel Now hope you feel comfortable using this convenient formatting tool.

Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter.

Kevin – Learn Excel Now

Essential Excel Skills: Protecting an Excel Worksheet

August 17, 2016 by Tyrone Pernsley

Protecting an Excel WorksheetThere are various skills everyone should learn in Excel. One of those skills is protecting an Excel worksheet. Excel allows you to add protection to a worksheet or workbook to prevent other users from making changes to your worksheet. This is particularly useful when you have an Excel workbook that multiple people use at your company.

Protecting an Excel worksheet is fairly straightforward.

Here is what you need to do:

  • Right click on the sheet tab to open the menu
  • Select Protect Worksheet
  • Set your password
  • Select the individual items you want to limit
  • Click Okay
  • Re-enter your password

Here is a quick demonstration:

Protecting an Excel Worksheet

There are several reasons to protect a worksheet. Let’s say you have a weekly sales log that has formulas and functions built into. You want your sales team to add information but don’t want them changing formulas – on accident or on purpose. So, you add protection to prevent them from being able to change anything.

Even for something as simple as not wanting someone to change your formatting, protecting an Excel worksheet can come in handy.

We here a Learn Excel Now hope you now feel comfortable protecting an Excel worksheet. This essential Excel skill will help in ensuring your data is always what you expect it to be.

Like Learn Excel Now? Follow us on social media and share our content with your networks! And don’t forget to sign up for the Newsletter

Kevin – Learn Excel Now

Removing Duplicates in Excel: Quick How-to Guide

June 22, 2016 by Tyrone Pernsley

Removing Duplicates in Excel Image 2This Excel tip from Learn Excel Now is on removing duplicates in Excel. The request is directly from a Learn Excel Now fan who asked us how to quickly remove duplicates. She told us:

“I pulled a list of all company sales from our database. It exported in Excel. The list is all of our sales, but many of our clients buy multiple products several times through the year. Yesterday, my boss asked me ‘how many unique clients have purchased in the last month?’ Those figures aren’t available because we’ve always only counted total sales. I started counting the individual companies 1-by-1, but there are so many of them. I know there has to be a way to remove the duplicates so I can see each company listed only once. Can you help me?”

So, this post was designed for one of our fans – let’s call her Rachel – and anyone else who wants to know about removing duplicates in Excel.

The following table contains multiple cases of duplicates. Similar to Rachel’s request, they are the names of companies in a sales document.

ScreenHunter_239 Jun. 22 13.50

Now, let’s get in there and remove those duplicates! Here’s how you do it:

  1. Highlight the column(s) or row(s) containing the duplicate values you want to remove
  2. Go to the ‘Data’ Tab on the Excel Ribbon
  3. Find the “Remove Duplicates” tool and click it
  4. Verify the table range on the dialogue box OR select the correct range on the dialogue box

Removing Duplicates In Excel Gif

If there are more values in the table but you want to stick to a column or row, select “Use Current Selection

Excel will automatically remove all duplicates. Now you and review and count the total unique values within the range.

We here at Learn Excel Now hope you now feel confident in removing duplicates in Excel. This useful tool is great for running quick analyses, finding unique values and cleaning up datasets for other functions or formatting.

Like Learn Excel Now? Sign up for the e-newsletter to receive weekly Excel tips and special offers just for you!

And don’t forget to follow us on social media and share our Excel tips with your networks!

Kevin – Learn Excel Now

Creating Excel Tables: Quick Tip to Get Up-to-Speed

June 15, 2016 by Tyrone Pernsley

Excel provides you with many preset formatting options that you simply need to click to activate. One of the most common and most useful is to set your data as a table. There are many advantages to organizing your data in a table. It makes it easier to sort and filter important data, find totals automatically and create charts and graphs.

Setting your data as a table is fairly easy. First, you take your basic dataset:

Tables Image

And then you apply the table setting:

Excel Tables Gif 1

As you can see from the above Gif, there are many table styles to choose from:

Excel Tables Image 2

This gives you the power to customize your Excel table to your preference.

Pro tip: you can add more customization to your formatting by using the Cell Styles tool to add colors, text formatting and other elements to make your table pop:

Excel Tables Image 3

Once you have your table formatted, you can us the dropdown menu to quickly filter information:

Tables Image 1

You also have the ability organize your dataset in a variety of ways.

We here at Learn Excel Now hope you enjoyed this week’s quick tip on Excel Tables.

Like Learn Excel Now? Follow our social media pages and share out content with your networks!

Kevin – Learn Excel Now

Hyperlinking In Excel: To the Web or to Another Worksheet

May 18, 2016 by Tyrone Pernsley

You might already know how to hyperlink a cell to external sources, namely to the web. In case you don’t, here is a quick tutorial on that:

  • Copy the URL of the website you want to hyperlink to
  • Click on the cell you want to hyperlink from
  • Right click and open the option that says “Hyperlink”
  • By default, Excel sets the dialogue box for hyperlinking to external web content
  • Paste the URL of the site you’re hyperlinking to (you can also type it if you have that kind of time)
  • Then click Okay and you are cell is hyperlinked!

Hyperlinking in Excel Gif 1

But did you also know you can hyperlink to other sources – such as within the worksheet itself?

This is convenient for large workbooks where data on one worksheet references data on another worksheet. One of my favorite things to do with large workbooks is to create a Table of Contents that identifies what each worksheet contains. Then, using the hyperlink option, anyone using the workbook can click right to each worksheet.

To do this:

  • First select a cell in one sheet that you want to create a hyperlink to another sheet
  • Right click the cell and click the hyperlink button
  • From the menu that appears, click the Place in This Document button in the Link to box
  • Select the sheet that you want to hyperlink to and enter the text you want to display in the Text to display box (or keep the text the same if you want)
  • If you want to hyperlink to a specific cell in another sheet, specify the cell in the Type the cell reference box.
  • Click OK and you will see that when you click on the data it will bring you to your desired worksheet!

Hyperlinking in Excel Gif 2

We hope you found today’s quick and easy Excel lesson beneficial. Don’t forget to follow ups on Social Media and subscribe to the blog to get convenient, quick tips like this, and other great Excel training tips so that you can take the fear out of Excel.

Like Learn Excel Now? Sign up for the newsletter!

-Kevin, Learn Excel Now

Excel Charts: Quick Tips for Getting Started

March 30, 2016 by Tyrone Pernsley

Creating Excel Charts Featured ImageExcel’s true power starts with storing and analyzing data but really begins to jump of the page with Charts. Excel charts allow users to graphically represent their data in a meaningful way. Users can easily create and customize charts. With the power of Charts, you can quickly summarize data, compare and track trends over time and see how your business is performing. You also have a tool to quickly report these numbers to colleagues and superiors.

Today, we are going to focus on creating an Excel Chart.

To create a chart, you would:

  • Select the data you want to appear on the chart
  • Go to the Insert ribbon and select the type of chart you want to use

Pro tip: there are also keyboard shortcuts to create charts:
Press F11 – a chart appears on a new workbook tab (worksheet)
Press Alt+F1 – a chart will appear on the same worksheet as the data source*

*When selecting charts from the Insert ribbon, they automatically appear on the same worksheet.

Creating Excel Charts GIF 1

Source Data for Your Chart

The quality of your chart will depend on the quality of the source data that goes into the chart. Here are a few pointers to help you create the best chart possible:

  • Select numerical cells – these will render better in the chart
  • Have a column label on the first row of each column describing the data
  • Have a row label in the first column of each row describing the data
  • It is usually best to not include totals, averages, percentages, etc. – your chart will calculate these

Creating Excel Charts Image 1

Creating Chart from a Pivot Table

Now, let’s say you wanted to take our Salary list chart and break it down even further. In this instance, we are looking at Total Salary by Department.

To create a chart from a Pivot Table, you would:

  • Create the Pivot Table from the sources data
  • Move “Department” to Axis (row labels)
  • Move “Salary” to Values
  • Select the chart you want to use

Creating Excel Charts GIF 2

And now, as you can see, we have a chart quickly showing the breakdown of salary by department.

We here at Learn Excel Now hope you enjoyed these simple tips for getting started with Excel Charts. This just begins to scratch the surface of all the things you can do with Excel Charts, but shows the necessary beginning steps. Stay tuned for more Excel tips from Learn Excel Now.

Like Learn Excel Now? Share this article and our other content with your social networks.

Kevin – Learn Excel Now

Excel Dashboards: Your Guide to Getting Started

March 2, 2016 by Tyrone Pernsley

Excel Dashboards empower you to display your data in interactive and dynamic ways. They give you a comprehensive snapshot of your data and save you the time and energy, because you don’t have to perform your own analysis. They are very customizable and allow you to help steer attention to key trends, comparisons, and exceptions. Essentially, dashboards are a great tool for putting all of your information in one place and making it easy to read. This walkthrough of Excel Dashboards will teach you all the basics to create and use your own dashboard.

Excel Dahsboards Featured Image

What to Include in Your Dashboard

The most important question to keep in mind when you’re creating your dashboard is what is important? To display the most important and relevant data, you should keep a few things in mind:

Your Key Performance Indicators – Examples include:

  • New customer acquisition
  • Demographics of potential customers
  • Turnover
  • Outstanding balances
  • Cycle time
  • Availability

Decision Points – Examples include:

  • Status of an ongoing operation
  • What’s going on in a certain area

Gap to Goal: Graphics that display how close the actual results are to goal is helpful information.

Finally, you need to consider the end-user. You need to make it user-friendly and relevant to them or your dashboard won’t be useful. You need to customize it to fit their terminology, objective, and scope of influence, so that they can understand and use it effectively.

Creating a Dashboard

Once you’ve decided on your key criteria, you will need to create the Dashboard.

To do this:

  • Go to a black sheet to create the background for the dashboard.
  • Go to the Drawing Tools section of the “Format” tab
  • Change the fill color of the cells in the worksheet or add shapes
  • Next, you can create charts, graphs, and tables to add using the data from your other spreadsheets.

After you create the visual parts of your dashboard, you’ll need to cut and paste the charts, graphs, and tables to the spreadsheet with your recently created background. From there you can arrange your visuals in a logical order and apply different effects to them.

Elements to Add to Your Dashboard

On your dashboard, there are many ways to represent your data. Here are a few examples of elements that you can add to your dashboard to represent your data.

Dashboard image 1.jpg

  • Gauges: This is good to use if you have a single value that you want to compare to a goal, because it lets you show how well you are progressing by pinpointing where your data falls as compared to where you want and don’t want it to be.
  • Bar Charts: This type of chart is good for comparing different related parts of a whole. For example, they can be used to show something like overall sales as compared to how well each product/service sells.
  • Heat Maps: Maps like this can be used to show trends. For example, you can see where a certain product is popular or sells best.
  • Charts & Graphs: Charts and graphs are a quick and easy way to show your data graphically. They let you easily identify trends in the data and make conclusions.
  • Table: This type of graphic works well for organizing and filtering a set of data that can be easily interpreted.
  • PivotTable: This type of graphic is perfect for taking a long and complicated table and making it more relevant and use-friendly by highlighting the important information.

Creating an Interactive Dashboard

Interactivity allows the viewer to interact with the dashboard, by going to the spreadsheet containing the raw data that is summarized by the chart, graph, or table or changing the data that is shown.

To create an interactive button:

  • Add a shape (for example a rectangle) to your dashboard.
  • Apply the effects and formatting that you want
  • Add text to describe where the button will take the viewer or what it will do.
  • Finally, to add the interactivity, you will need to hyper link the button to the spreadsheet you want it to be linked to.

To add the hyperlink:

  • Go to the “Insert” tab and click on “Hyperlink.”
  • From there, select Link to “Place in This Document.”
  • You can then select the sheet and cell that you want the button to be linked to.

Excel Dashboard image 2.jpg

You can also choose to link your button to a web page, another type of document, or a graphic by inserting the location you want in the “Link to” section of the Hyperlink dialog box.

We here at Learn Excel Now hope you feel confident in your ability to start using Excel Dashboards. It is our desire to bring you the best advice possible to effectively and efficiently use your desktop features so you can focus on your work.

If you are looking for more in-depth Excel Dashboard training, please join us for:

Excel Dashboards for Beginners: Present Your Data Like a Pro on Wednesday, March 23, 2016

Like Learn Excel Now? Join our newsletter and share our content on social!

Kevin – Learn Excel Now

Converting Numbers into Accounting in Excel

February 24, 2016 by Tyrone Pernsley

If you’ve ever wondered why there is an accounting format in Excel when there is already a currency format than you are not alone.

Creating excel spreadsheets that contain currency like an income statement or a balance sheet can be difficult to organize. Sometimes the numbers, decimal places and dollar signs do not line up, making the spreadsheet look confusing.

For this reason, the accounting format was created.

Currency vs Accounting screenshot

This format is very similar to the currency format. The only differences that make this format easier for accounting are:

  • It shows zero values as dashes
  • Aligns all the currency symbols and decimal places
  • Shows negative numbers in parentheses.

Here is how to navigate the accounting format in excel.

#10 gif

To convert numerical cell values to the accounting format simply highlight the desired cells and right click them. Then:

  • Click the Format Cells option
  • Click the Number category
  • Choose the accounting tab – this will give you the option to change how many decimals points or which type of currency symbols you desire to show.
  • Click OK and bam, it is now in the accounting format!

To format empty cells, highlight the desired cells and right click them. Then:

  • Click the Format Cells option
  • Click the Number category, and then chose the accounting tab.
  • Click OK and start to enter the numbers you desire.

The numbers you input will be automatically converted to the accounting. Looks neat and clean, right!?

If you would like to convert any cells back to normal numbers, simply highlight the desired cells and right click them, click the Format Cells option, click the Number category, and then chose the general tab.

We hope you found today’s quick and easy Excel lesson beneficial. Don’t forget to follow us on Social Media and subscribe to the blog to get convenient, quick tips like this, and other great Excel training tips so that you can take the fear out of Excel.

Like Learn Excel Now? Sign up for the newsletter!

-Kevin, Learn Excel Now

  • 1
  • 2
  • Next Page »
All Access Subscription
Our 1-year all-access subscription provides step-by-step guidance for mastering Microsoft Office applications, with workshops, exercises and quick reference eGuides. It's perfect for anyone that needs to brush up on certain skills or even folks who need to learn the programs - from a beginner’s level.
Excel Foundations
Take the fear out of Excel with this 20 module self-paced training course. You will cover the most essential topics to develop a solid foundation of Excel or a lifetime of mastery.

Learn Excel Now helps you conquer the fear of Excel. By providing self-paced and instructor-led training and free strategies and guides, we leave no Excel mystery unsolved.

  • Home
  • Training
  • All-Access
  • Excel Foundations
  • Resources

Connect with Us

  • Facebook
  • Linkedin

Contact

Learn Excel Now
questions@learnexcelnow.com
1-800-964-6033
660 American Ave
Suite 203
King of Prussia, PA 19406

Microsoft® Office Excel® is a registered trademark of the Microsoft Corporation in the United States and other countries. All rights reserved.

Copyright © 2021 ­Learn Excel Now

Terms of Service

Privacy Policy

Copyright © 2022 · News Pro Theme on Genesis Framework · WordPress · Log in

WELCOME BACK!

Enter your username and password below to log in

Forget Your Username or Password?

Reset Password

Lost your password? Please enter your username or email address. You will receive a link to create a new password via email.

Log In