Home » Excel Tips & Tricks

Category: Excel Tips & Tricks

How to stop your Excel charts from disappearing

Have you ever experienced your Excel charts disappearing when you hide the columns showing the data? By default, Microsoft® Excel® shows only visible data in a chart. In this tip, we will show you how to easily overcome this problem, by following these simple steps:

1. First, lets create a simple chart with your data to demonstrate.
 

 
2. If you had to hide columns A and B, your chart will disappear. To prevent this from happening, click anywhere on the chart and from the ribbon, select Chart Tools > Design > Select Data
 

 
3. In the “Select Data Source” dialogue box, select “Hidden and Empty Cells” in the bottom left hand corner.
 

 
4. In the “Hidden and Empty Cell Settings” dialog box, check the box “Show data in hidden rows and columns”.
 

 
5. Select “OK” twice.
 
Now, go ahead and hide your data. Your chart should still display with the hidden data!

The post How to stop your Excel charts from disappearing appeared first on Sage Intelligence.

Source: Excel on Steroids

How to automatically highlight specific data using a bar chart in Excel

This Microsoft® Excel® tip will come in handy when you want to create a chart that will populate your data and highlight only certain subsets of that data.

Download the workbook to practise this exercise.

In the example below, we will look at a grocery store. The chart displays the revenue generated from the produce department.

Bar-Chart-In-Excel-showing-specific-Data

If, for example, you wanted to see how Pears compare to Bananas you could easily highlight these data points by following the below steps:

1. Add additional columns for “Relevant”, “Non-Relevant” and “Criteria”.

2. Enter the criteria to be met under the “Criteria” columns, e.g. Bananas and Pears.

3. The “Relevant” column uses an IF(COUNTIF formula to determine if the item meets the criteria and if so will return the value.

4. The “Non-Relevant” column will return a value if the criteria was not met for the “Relevant” column.

Creating-Bar-Chart-In-Excel-to-Highlight-specific-Data

5. Create a chart using the “Relevant” and “Non-Relevant” columns.  The columns are displayed in different colours.

Bar-Chart-In-Excel-comparing-specific-Data

6. For the chart to display correctly, right click on a data bar and select Format Data Series.

7. From the Format Data Series dialogue box, set the Series Overlap to 100%. As each produce type has two columns being used in the chart, one with a value and one equal to 0, by overlapping the bars we avoid having an “empty bar” being displayed.

Format-Data-series-overlap

By changing your criteria, the chart is automatically refreshed, and the relevant bars are highlighted.

Bar-Chart-In-Excel-Highlighting-specific-Data

The post How to automatically highlight specific data using a bar chart in Excel appeared first on Sage Intelligence.

Source: Excel on Steroids

How to create a step chart in Excel

A step chart shows changes that occur over irregular intervals. For example, it can show stock movement changes, interest rates, etc. A step chart is the perfect alternative to a line chart as it displays the trend as well as the time taken between changes.

The difference between a Step Chart and Line Chart:

A line chart connects data points and the focus in such charts is the trend and not the exact time of change.
On the other hand, a step chart shows the exact time of change in the data as well as the trend. With a step chart, you can easily identify the exact time where there was no change or compare the extent of change at each instance. In this tip, we will show you how to establish the trend of a stock item.

Note: Download the sample workbook to practice this exercise

Steps to create the Step Chart:

1. The data needs to be reconstructed and set up as follows:

  • Copy and paste the headings into new cells.

  • From your dataset, copy the dates starting from the second date and paste in the blank “Date” column.

  • From your dataset, copy your sales values starting at the first value to the second last value, then paste this in the blank “Stock Movement” column.

  • Copy your original data table and paste it directly underneath the data you have just copied over into your new columns.

2. Using this new dataset, select > Insert > Charts > Line Chart. Your Step Chart has been created and is ready for any formatting required.

 

The post How to create a step chart in Excel appeared first on Sage Intelligence.

Source: Excel on Steroids

How to format a chart in Excel to dynamically show its maximum value

Data visualisation is fast becoming the norm for effective business intelligence. Having visuals that tell the right story at a glance go a long way in achieving this. Microsoft® Excel® provides you with a multitude of ways to enhance the visuals that you create. In this tip, I’ll show you how to format a basic bar chart to dynamically highlight the maximum value in the series.

  1. I have a data series showing total sales values for each quarter in my financial year. I’ve created a basic bar chart to display this and I’ve widened each bar in the chart for greater emphasis. You can achieve this by decreasing the bar gap width under the Format Data Series options. To get to these options, right click on a bar and select Format Data Series.

Format Data Series Options

  1. To determine the maximum value, I’m going to add another series to the chart which I’ll define in the column to the right of my total sales. This column uses an IF and MAX function to return either the total sales value if it is the maximum or else zero. Use the formula displayed below.

IF/MAX formula

 

  1. I’m now going to add the new series to the chart. You can do this by right clicking on the chart and choosing Select Data > Select Add from the Select Data Source dialogue, and then pick the right cells for your series name and series values.

  1. The new series is now placed to the right of the existing series. To get it to appear in place of the existing series you need to overlap it.  You can do this by setting the Series Overlap amount in the chart properties to 100%.

Series Overlap Chart Options

 

Take note that as your series values change, the chart updates accordingly.

Chart Series Overlap

 

If the highlight isn’t displaying, go back to the Select Data Source dialogue and make sure that your highlight series is positioned after your main values series.

Select Data Source Options

 

As mentioned, Excel provides a multitude of ways for you to enhance your visuals.  It’s up to you to explore the possibilities.

The post How to format a chart in Excel to dynamically show its maximum value appeared first on Sage Intelligence.

Source: Excel on Steroids

Our Top 5 Excel tips of 2018

These Microsoft® Excel® tips and tricks are the top performing tips of 2018, and have raked up a combined viewership of over 200 000. Without further ado, here they are, the Top 5 Excel tips and tricks of 2018.

 

How to consolidate data in Excel using Data Consolidation (66,027 views)

How to get a distinct count in a Pivot Table (55,116 views)

Easy steps to opening an Excel workbook on a specific worksheet (31,146 views)

How to extract text from a text string using the MID and FIND functions (29,609 views)

How to find the position of a value in a data range using the MATCH function (27,642 views)

Our Excel tips and tricks will help you make the most of the business reporting features in Excel. If you wish to revisit any of our previously published tips, please visit our website.

The post Our Top 5 Excel tips of 2018 appeared first on Sage Intelligence.

Source: Excel on Steroids

Learn how to identify duplicate rows in your data

In a previous tip, we explained how you can easily remove duplicate rows from your data using the Remove Duplicates function.  You can find it here.  At times though, you may just want to highlight duplicate rows without actually deleting them.  In this tip, we show you how you can do this using an Excel formula.

1. First, you need to make sure that your data is sorted. Assuming it starts in cell A1 and has headings, select cell A2 and then click Sort A to Z on the Data tab on the menu ribbon.

2. Next, enter a new column in front of column A by right clicking on the label for column A and selecting Insert.

3. Now, in cell A2, we’re going to make use of an IF and AND function to identify duplicates. Enter the following:

=IF(AND(B2=B3,C2=C3,D2=D3,E2=E3,F2=F3), “Duplicate”, “Unique”)

Note that the formula is checking to see whether the value in cell B2 is equal to the value in cell B3 and whether the value in cell C2 is equal to the value in cell C3 and whether the value in cell D2 is equal to the value in cell D3, etc.  If all combinations are equal, then it implies that the row is a duplicate and the function returns the text “Duplicate”.  If not, then “Unique” is returned.

Also keep in mind that your data will likely vary in the number of columns it has and you will need to do the cell comparison for each column that you have.  Furthermore, you may regard a duplicate as consisting of a subset of columns.  For example, in the above data, I might think of a row as being duplicated only if the Customer Name and Product Name are the same.  In that case you would only do the cell comparison for those rows.

4. Now, all you need to do is use the Excel fill handle and copy the formula down to the rows below it. You will notice that any duplicates are identified.

Through the power of Excel functions, you can easily identify duplicate rows which can make working with your data more efficient.

The post Learn how to identify duplicate rows in your data appeared first on Sage Intelligence.

Source: Excel on Steroids

How to create a linked dashboard (or landing page) for your Excel workbook

Have you ever had the pleasure (or displeasure) of working with an extra large Excel workbook with many, many different sheets? This happens frequently, especially if you are working with a large amount of data that needs to be analyzed periodically. This may be due to multiple tables, lookups, references, charts, graphs etc.

This has the tendency to have you create many different sheets—each one having its own specific function and purpose.

You may notice however, that as the data grows, the number of sheets increases—forcing you to have to scroll through the list of tabs to get to the specific sheet desired.

But what if business is so good that you now have around 10, 20 or more sheets? Having to scroll back and forth through the tabs to get to each sheet to verify and hence analyze information would be a nightmare.

This is where a linked dashboard (or a landing page) comes in handy, and is a very quick fix for this quandary.

This has the effect of taking you to the desired sheet by just one click of a button and back to the dashboard with another.

Let’s get started:

What I have here is a workbook with multiple sheets consisting of tables, charts, graphs, raw data etc. Each sheet in the workbook serves its purpose. I would like to access the information in each workbook without having to scroll through the tabs at the bottom of the workbook all the time.

Notice the tabs at the bottom of the page denoting each sheet (this can get quite bothersome as they increase in number):

So how do we accomplish this?

Step one: create a Landing page (or Dashboard or home page)

I have created a very simple, but effective, landing page called “Dashboard” and added the names of sheets I would like to link to. For best practices, it is often handy to name them after the tabs they are going to point to. Hence, I have named them after the tabs in the workbook respectively.

Step two: Create and insert links

We are going to start by inserting a link for the “Home” worksheet (not to be confused with the landing page). This is how we do this:

  • Click in the cell containing the text you want to become the link (in this case cell D4 as it contains the word “Home”).
  • From the main menu in excel, click on insert, then Link (hint: you can also hit ctrl+K) on your keyboard.
  • A list may or may not appear (this contains previously opened workbooks on your machine or online).
  • Scroll all the way down and choose “insert link”.
  • A dialogue box appears. Leave the “text to display” as “Home” (unless you want it to read something else).
  • On the left (under the “Link to” options), choose “Place in this document”.
  • Now instead of typing in a cell reference, choose the option “Or select a place in this document” and choose the “Home” worksheet.

  • After clicking OK there is a now a hyperlink inserted on the Landing page. The result will look like this:

When clicking on the “Home” link, it takes me to the sheet labeled “Home”, which you can customise as you see fit.

Do this for the rest of the sheets you would like your dashboard to link to.

Points to ponder:

  1. Now would be a good time to include a link pointing back to the landing page. This will enable you to go back to the landing page (dashboard) without having to scroll all the way from whence you came.
  2. Once this is done, to get this same link on the rest of your sheets, simply copy that link (ctrl+c) and paste on the other pages. No need to perform the same steps of inserting a link.
  3. Links can be formatted to your specifications (color, size etc.)

Hopefully this should help save you time and limit the needless scrolling when working with multiple sheets in one workbook.

The post How to create a linked dashboard (or landing page) for your Excel workbook appeared first on Sage Intelligence.

Source: Excel on Steroids

How to add a variance and running total in a Pivot Table

In our example workbook, we’re working with Date, Value In and Value Out fields. We need to create a Pivot Table that will also display the variance per month, as well as a running total of the variance for the year. In this tip, we show you how to do this.

First up; select your data and create a Pivot Table as normal.

To create the additional fields:

  1. Click inside the Pivot Table and select PivotTables Tools > Analyze > Fields, Items & Sets under the Calculations menu section.

  1. An Insert Calculated Field window will pop-up. Give the field a name, enter your variance formula and select Add.  In our example we want to know the variance between our Value In and Value Out values.

  1. This new field will display in your Pivot Table. You can now format the field as required

  1. To calculate a Running Total of the Variance field, drag the Monthly Variance field under the existing Monthly Variance field in the Value section of the PivotTable. You should now have a duplicate in your PivotTable, which can be renamed.

  1. Right click on any cell inside this column and from the menu, select Show Values As > Running Totals In”.

  

  1. Select OK if the default Base Field is correct.

  1. The column has now been converted and your report displays the Variance per month as well as a Running Total for the year

And that’s it! A quick and easy way to create a Pivot Table that will also display the variance per month, as well as a running total of the variance for the year.

The post How to add a variance and running total in a Pivot Table appeared first on Sage Intelligence.

Source: Excel on Steroids

Using Wildcards to make finding or replacing data in Excel quicker and easier

This tip is useful when you want to replace or find text using Wildcards, e.g. if you have a column where you would like to replace everything after the space character with text.

What are Wildcards?

Wildcards are characters that are used to represent one or more characters.

The following wildcard characters can be used in Excel:

  • * (asterisk) – Can take the place of one or more characters
  • ? (question mark) – Takes the place of one character
  • ~ (tilde) – Indicates that the next character shouldn’t be treated as a wildcard

The examples below show how these wildcard characters can be used.

Using the * character to replace

Consider the following data in column A which contains an Id and Name separated by a space.

If you wanted to keep only the names, you could use the Text to Columns option from the Data Tab, choose Delimited, select Space and keep the column with names.

An alternate method is:

  1. Highlight column A
  2. Select Find and Replace, this can be easily done using the shortcut Ctrl and H
  3. In the Find What box, type * (asterisk and a space)

  1. Click Replace All

The * wildcard character replaced everything up until a space was found.

Using the ? and ~ character to find/filter

Consider the following data in column A that contains names.

If you wanted to filter on Names that started with the letter L and that were 4 letters in total, you would filter/search on L??? which would result in the following rows being shown.

Notice that Linda is not part of the filtered list since it contains 5 letters.

If you wanted to filter only on rows that started with L?, you would filter/search on L~?* which would result in one row being shown since ~ specifies that the next character shouldn’t be treated as a wildcard.

Searching on L?* would show the following two rows.

This is a quick and easy method to replace or find data.

The post Using Wildcards to make finding or replacing data in Excel quicker and easier appeared first on Sage Intelligence.

Source: Excel on Steroids

Sorting data in a PivotTable with Microsoft Excel

Sorting data in alphabetical order or numerical order is helpful when you have large amounts of data in the PivotTable you created. Sorting lets you organize the data so it is easier to find the items you want to analyze.

1. Within the PivotTable, click a field in the row or column you want to sort.

2. Click the arrow on Row Labels or Column Labels, and then click the sort option you want. Note: There could be a column or row name associated.

3. To sort data in ascending or descending order, click Sort A to Z or Sort Z to A.

Text entries will be sorted in alphabetical order. Numbers will be sorted from smallest to largest (or vice versa), and dates or times will be sorted from oldest to newest (or vice versa).

Tip: You can also right-click on a field, select Sort and then select a sorting option.

Setting custom sort options

If you wish to sort certain items manually or change the sort order you can set your own sort options.

1. Within your PivotTable, click a field in the row or column you want to sort.

2. Click the arrow on Row Labels or Column Labels then click the sort option you want then click More Sort Options.

3. In the Sort dialog box, pick the type of sort you want:

  • Click Manual to rearrange items by dragging them.
  • Click Ascending (A to Z) by or Descending (A to Z) by and then choose the field you want to sort.
  • For Additional options, click More Options then pick the option you want in the More Sort Options dialog box:

  • Under AutoSort, check or uncheck the Sort automatically every time the report is updated box to allow or stop automatic sorting whenever the PivotTable data is updated.
  • Under First key sort order, pick the custom order you want to use. This option is available only when Sort automatically every time the report is updated box under AutoSort has been unchecked.
  • Under Sort By, click Grand Total or Values in selected columns to sort by these values. This option is not available when you set sorting to Manual.

Note:

  • Data that has leading spaces will affect the sort results. For optimal results, remove any leading spaces before you sort the data.
  • You cannot sort case-sensitive text entries
  • In a PivotTable, you cannot sort data by a specific format like cell or font color or by conditional formatting indicators such as icon sets.

If you do not know how to create a PivotTable we have several videos and webcasts to help you learn.  Additional PivotTable tips are available at SageIntelligence.com.  Go to Resources/Tips & Tricks then search on PivotTable.

The post Sorting data in a PivotTable with Microsoft Excel appeared first on Sage Intelligence.

Source: Excel on Steroids