Excel - How To Add Up And Down Green And Red Arrows To Excel - Episode 2007
Microsoft Excel Tutorial - Up/Down Markers.
Welcome to the MrExcel Podcast, where we bring you tips and tricks to help you master Excel. In this episode, we will be discussing up/down markers and how to use them in Excel 2007. Don't forget to click the "i" in the top right-hand corner to subscribe to our channel for more Excel tutorials and tips.
Have you ever wanted to quickly see if your data is trending up or down? Conditional Formatting icon sets in Excel 2007 can help with that, but not all of the sets have the option for up/down markers. In Excel 2010, they added the 3 Triangles set, which includes two triangles and a dash. However, the challenge is that icon sets can only look at the cell where the icon appears, so it doesn't know if the data is actually trending up or down.
But fear not, I have a trick that will help you use the 3 Triangles set to show up/down markers. First, we will create helper cells off to the side and use a simple formula to calculate the change in data. Then, we will use the SIGN function to convert the numbers into plus 1, zero, or minus 1. Next, we will apply the 3 Triangles icon set to the helper cells and use conditional formatting to show the appropriate up/down markers.
But wait, there's more! We will also show you how to get rid of the numbers in the helper cells and paste a linked picture of the markers into your original data. This way, you can easily see the up/down markers for each data point without cluttering your spreadsheet. And don't worry, we will walk you through the steps to make sure the column widths match and the gridlines are turned off for a clean and professional look.
So there you have it, a simple and effective way to use the 3 Triangles icon set to show up/down markers in Excel 2007. And if you want to learn more tips and tricks like this, be sure to check out our book, which includes 40 real tips and a whole bunch of bonus tips to help you become an Excel pro. Thank you for watching and we'll see you in the next episode of the MrExcel Podcast.
Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...
Table of Contents:
(00:00) Up Down Markers in Excel Icon Sets
(00:23) Explanation of the 3 Triangles icon set in Excel 2010
(01:00) Using a formula to determine if a number is up, down, or flat
(02:03) Applying the icon set to helper cells
(03:23) Adjusting the icon set and turning off gridlines
(04:19) Clicking Like really helps the algorithm
(05:50) Outtake: Copying and pasting linked pictures using the camera tool
#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #excelnew #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #conditionalformatting #conditionalformat #iconsets #excelupdownmarkers
This video answers these common search terms:
how to add up down arrows in excel
how to add up and down green and red arrows to your excel document
how to make excel arrows up or down
how to do up and down arrows in excel
how to put up and down arrow in excel
how to put arrow up and down icon excel
Icon Sets debuted in Excel 2007
In Excel 2010, they added a set with Up, Flat, Down
But an icon set can only look at one cell! How can you use it to compare two cells?
Copy two helper columns off to the side
Use a calculation to show the change
Type that formula without using the mouse or arrow keys to prevent =GETPIVOTDATA
Use the SIGN function to convert that to +1, 0, -1
Add the Three Triangles icon set to the helper cells
Why do they call it Three Triangles, when it is really 2 triangles and a dash?
Manage Rules, Edit the Rule. Show Icon Only.
Manage Rules, Edit the Rule. Change from percent to numbers
Use greater than 0 for Green up arrow
Use greater than or =0 for yellow dash
all of the negative numbers will get the red down arrow
Copy the column widths from the original cells to the helper cells using Paste Special Column Widths
Copy the Helper Cells
Paste Linked Picture this used to be the camera tool
Turn off the gridlines with View, Gridlines
If you don't like the yellow dash for "No Change", manage rules, edit the rule, use No Cell Icon
To force the camera tool to update, click the camera tool. Click in the Formula Bar. Press Enter
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...
Watch video Excel - How To Add Up And Down Green And Red Arrows To Excel - Episode 2007 online without registration, duration hours minute second in high quality. This video was added by user MrExcel.com 02 September 2016, don't forget to share it with your friends and acquaintances, it has been viewed on our site 5,165 once and liked it 44 people.