Learn Excel Now

Conquer the Fear of Excel

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

Sorting Data with Excel Pivot Tables

March 9, 2016 by Tyrone Pernsley

The Pivot Table is one of the most useful features in Excel. Sorting data with  Excel Pivot Tables allows you to examine your data from multiple points of view for better summary and analysis. You can sort data, count totals, averages, percentages, etc. In addition to giving you the ability to organize your data, Pivot Tables also makes your data more presentable.

Creating a PivotTable

Sorting Data with Excel Pivot Tables GIF 1

Once you have highlighted the data that you want in your Pivot Table report, go to the “Insert” tab and click on “Pivot Table.” You can then be able to choose to create the Pivot Table report on a new page or keep it on the same page as your data. The Pivot Table that is generated will be blank, so you have to pick which values you want and where you want them. You can arrange your data in rows or columns and include sums of different data series. To do this, you simply use the Pivot Table Field List on the right to drag and drop fields to the area where you want them to go. Your Pivot Table will then be generated and then you can sort and organize it.

PivotTable image1

Sorting Data

PivotTables can be used to organize and sort all kinds of data. Some examples include:

  • Alphabetically
  • Numerically
  • By dates and time

The options for selecting which data to look will be affected by the row and column labels in the original data set. For example, if you have a list of purchases and want to organize them by date, you can move the two column labels into the Pivot Table fields.

In the Pivot Table report, click on the drop-down arrow next to “Row Labels” or “Column Labels,” then select how you want your data to be sorted. If all of your values are words, you’ll be able to sort the data alphabetically from A to Z or from Z to A. For numeric values, you can sort it from smallest to largest or from largest to smallest. For times and dates, you can sort form oldest to newest or newest to oldest. You can also drag and move data to sort it in any way you want.

Sorting Data with Excel Pivot Tables GIF 2

Once you have your data organized into the proper fields, you can begin to summarize the findings for improved decision-making.

We here at Learn Excel Now hope you enjoyed this quick guide on sorting data with Pivot Tables. It is our desire to bring you the best advice and training available so you can effectively and efficiently use your Excel tools.

Liked this sorting data with Excel Pivot Tables quick tip? Have questions? Leave your comments below; we’d love to hear from you.

Getting Social with Excel: Spread the word and share the knowledge!

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

Converting Numbers to Percentage in Excel

February 10, 2016 by Tyrone Pernsley

Cell formatting in Excel allows you to represent your numbers in a variety of ways: as a date, as a percentage, as currency, and with custom formatting, even phone numbers. In today’s quick tip, we are going to show you how to quickly change a number to a percentage in Excel.

After entering a number or an array of numbers into different cells, you can format them (or format the cell before the number is entered – your preference).

To convert the values to a percent:

  • Highlight the desired cells
  • Right click them
  • Click the Format Cells option
  • Click the Number category
  • Then chose the percentage tab

This will give you the option to change how many decimals points you desire to show. After you have picked your desired decimal points, click OK and bam, it is now a percentage!

Percentage in Excel GIf 1

Tip: you can also click on the lettered column heading to convert the all cells within the column to your formatting preference. This is good for spreadsheets where you will be adding values over time, so that you don’t have to keep reformatting each time.

Note:  if you apply the percentage format to existing numbers in a spreadsheet, excel multiplies the numbers by 100 to convert them to percentages. (If a cell contains 20, it will covert to 2000.00 %).

To format empty cells, follow the same procedure for formatting cells with existing values. Tip: you can also click on the lettered column heading to convert the all cells within the column to your formatting preference. This is good for spreadsheets where you will be adding values over time, so that you don’t have to keep reformatting each time.

Percentage in Excel GIF 2

As with many functions in Excel, there is another way to this as well using the Ribbon – the top toolbar in Excel.

Percentage in Excel Image 1

To change numbers to a percentage in Excel using the Ribbon, click on the Ribbon, make sure you are on the home Ribbon tab. The go to the number tab and use the dropbox to select percentage. On this tab, you can also use the decimal arrow keys to select the number of decimal places you want.

Percentage in Excel GIF 3

We hope you found today’s quick and easy lesson on converting numbers to a percentage in Excel 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

Getting Starting with Excel Formatting

January 20, 2016 by Tyrone Pernsley

When it come to Excel formatting, there are so many options and tools that it can be a little overwhelming trying to determine what to use and how to use it. Luckily, there are some beginning steps that can help you focus on where to begin.

Special Paste

Excel Formatting Topi Image

In the following article, we will start with some of the more basic tools and features you can use and then get into an Excel formatting macro as a suggestion on something you can do once you’re comfortable with the basic stuff.

Freezing Column Headings

When you’re working with a long excel spreadsheet, you might want to see what your column headings are so that you don’t have to keep scrolling up or make a mistake.

To make this easy, freeze the column headings. This will always keep your first row with the headings at the top of the columns regardless of how far down you scroll.

To set this, click on “View” on the tool bar, and then select “Freeze Panes.” Click on the “Freeze Top Row” option.

Excel Formatting- image 2

Quick Tips For Adjusting Columns and Rows: Your Handy Excel Formatting Cheat Sheet

  • To quickly insert a column or row, select the row or column by clicking on a corresponding cell, then click “Ctrl” and “+”
  • To quickly delete a column or row, select the row or column by clicking on a corresponding cell, then click “Ctrl” and “-”
  • Autofit makes the columns big enough to fit their contents. To do this, select the column(s) that you want to change, then click on the “Format” icon on the tool bar and select “AutoFit Column Width.”
  • Another way to adjust the column size is to fit it to the text you enter. To do this, first enter your text, then double click the separating line between that column and the column to the right of it. The column will enlarge to fit the text.

Key Board Shortcuts to Help you Save Time in Excel Formatting

  • “Ctrl” + “b” à Bold
  • “Ctrl” + “i” à Italic
  • “Ctrl” +” u” à Underline
  • “Alt” + “=” à Auto Sum

Formula Tricks for Quick Excel Formatting 

To copy a formula down the length of a column, click on the cell with the formula. Then, put your cursor in the bottom right corner of the cell until you get the thick, black plus sign, you can either drag the formula down or double click and the formula will be copied down the length of the column.

Excel Formatting Formula Drag

However, keep in mind that this will adjust the formula in each row to correspond to the data, in the same row as the formula. So, if you have a value that will stay the same in each formula you must add “$” before the letter and in between the letter and the number of the cell you want to keep the same (for example, $A$1)

Excel Formatting Macros

Sometimes, using a Macro can help you save time in Excel formatting. To create formatting macros, first select the cells that you want to format. Then go to the “View” tab on the tool bar. Click on “Macros,” then select “Record Macros.” You will be prompted to name and provide a description of the macro.This could be useful in a spreadsheet where you are using multiple macros. Record the keystrokes to format the cells that you have selected. When you’re finished go back to “Macros” and select “Stop Recording.”

To use the macros you recorded, go to “Macros” and select “View Macros.” You will get a list of all the macros you recorded. Select the one you want to run and then click “Run.”

Excel Formatting-image 3

We here at Learn Excel Now hope you feel more comfortable using formatting.

Getting social with Excel: please feel free to share this article with anyone you think would benefit from it.

Kevin – Learn Excel Now

How to Rename a Worksheet

January 12, 2016 by Tyrone Pernsley

Today’s excel tip is very simple but can make a difference when staying organized in excel. If you have more than one worksheet in your workbook, it can get very confusing on which sheet is which when they are all labeled almost the same (sheet 1, sheet 2, sheet 3, etc).

Special Paste

If you are like me and get anxious when you are looking at the non-named titles of your worksheets than you are in the right place.

To fix this issue, simply click on one of the existing worksheets tabs at the bottom of your screen, right-click on the tab, and choose rename in the drop-down menu. Finally, type the desired name of your worksheet in the tab. This is one of the more simple tips that we have for you but if you are still having difficulties, check out the Gif below!

#24 gif

We hope you found todays 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

Auto Expand Multiple Columns in Excel

December 16, 2015 by Tyrone Pernsley

One of the most common fears of Excel is getting unruly data files and not knowing how to quickly and easily format it to look nice. Here we will go over when and how to auto expand multiple columns in Excel.
[Read more…]

Excel Conditional Formatting: Quick Tips to Get Started

December 9, 2015 by Tyrone Pernsley

Using Excel in the workplace, or for personal needs, is made much simpler when you know who to use all of the tools and features effectively. [Read more…]

Changing the Color of Text in a Cell

November 25, 2015 by Tyrone Pernsley

One of the best ways to keep an Excel spreadsheet organized is with color coordination. A personal favorite here at Learn Excel Now is white text on a color background. [Read more…]

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-484-259-7664 or 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 © 2023 ­Learn Excel Now

Terms of Service

Privacy Policy

Copyright © 2023 · 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