Learn 2 ways to apply conditional formatting to shapes in Excel. This technique is great for dashboards and interactive reports where you don't want to be confined to the grid layout of the cells. There is no way to actually apply conditional formatting to shapes in Excel, so both these methods are workaround solutions. Here is the link to the article and page where you can download the sample Excel file: http://www.excelcampus.com/tips/conditional-formatting-shapes/ Method #1: A simple solution is to copy and past e the cell that contains conditional formatting as a Linked Picture. The paste special linked picture command will create a shape that displays the formatting and values of the cell. As the cell's value and formatting changes, the linked picture will also update to reflect the changes. You can use this technique for a single cell or a range of cells. The picture is resizable so you can make the shape bigger for your dashboard. You cannot change the shape type directly in the Excel menus or ribbon. However, you can use VBA to change the shape type. The following line of code will change the shape type of the selected shape with VBA. Selection.ShapeRange.AutoShapeType = msoShapeRightArrow Method #2: The CF Shapes Add-in allows you to create custom shapes with conditional formatting for the shapes fill color. Click the following link to learn more about the CF Shapes Add-in: http://www.excelcampus.com/cfshapes If you are new to conditional formatting or really want improve your skills then I highly recommend the Conditional Formatting Video Course by Dave Bruns at ExcelJet.net. Here is the link to learn more about the course: http://www.excelcampus.com/exceljet-conditional-formatting-course
Step 4: Building the Excel Dashboard Display with Shapes with Name Range Associations Buy the Excel Dashboard: https://gumroad.com/l/hr-salary-dashboard You can also check my website out at http://bradedgar.com. In this video we have a look at how we can use shapes to build an Excel dashboard display using shapes and associating those shapes to name ranges. Name range associations will allow us to bring over the values from our calculations tab (pivot tables) and allow our data and dashboard to change based on the filters that are selected on the slicer by the Excel dashboard user. This Excel Human Resources Salary Dashboard is built to show you the top 3 compensated and bottom 3 compensated employees based on the selections made on the dashboard pivot table slicer. We also look at setting up Excel hyperlinks to jump between pages within our dashboard menu navigation. These hyperlinks are tied to square buttons within the menu. After we've done that we look at how to associate the shapes (rectangles in this video) to an already created name range from our calculations tab. Finally, we look at using some very simple VBA and use worksheet change events to automatically refresh the pivot table using ActiveWorkbook.Refreshall. We additionally look at the code for the fail safe macro that we created to ensure that if data is added to our EMPLOYEE table and our change event fails, that we can use the update pivot table button to run our macro (which will of course in turn update our pivot table cache). Resources and Links: http://bradedgar.com/3-intelligent-ways-use-shapes-excel/ http://chandoo.org/wp/tag/slicers/ http://datapigtechnologies.com/blog/index.php/enhancing-dashboard-interfaces-with-shape-layering-tricks/ http://datapigtechnologies.com/blog/index.php/new-dashboard-tools-excel-add-in/ http://chandoo.org/wp/excel-dashboards/ http://chandoo.org/wp/tag/named-ranges/ https://www.youtube.com/watch?v=HbwsvryGZio http://www.skilledup.com/articles/best-free-excel-templates-dashboards/ http://www.ozgrid.com/VBA/shapes.htm -~-~~-~~~-~~-~- Checkout my newest video: "How to Use the Vlookup Formula in you Excel Dashboard" https://www.youtube.com/watch?v=MCBmPnaKf_g -~-~~-~~~-~~-~-
Visit http://www.TeachExcel.com for more, including Excel Consulting, Macros, and Tutorials. Learn how to prevent buttons and shapes in Excel from changing size when columns and rows are resized. This tutorial also teaches you how to prevent buttons from moving when cells change size and how to prevent buttons from being copied and pasted by a user. This is a good little tutorial that shows you how to make sure your spreadsheets will look good in the future when you go to change things. For Excel consulting or to get the spreadsheet or macro used here visit the website http://www.TeachExcel.com There, you can also get more free Excel video tutorials, macros, tips, and a forum for Excel. Have a great day!
A short video to show how simply a drawing (a shape) can be linked and connected to a cell in the spreadsheet which then becomes an interactive graphic item with dynamic features. there is NO SOUND on this video.
In this video I explain how to use the Snap to Shape feature in Excel to resize & align all the charts, slicers, images, and shapes on your dashboards. This technique helps save time when aligning all the elements on your dashboard, and is a good alternative to the other Align buttons. Snap to Shape is a mode we can toggle on/off in Excel. It's a pretty simple feature. When we move a shape by dragging it with the mouse, it will snap into alignment with other shapes on the worksheet. This also works when we are resizing shapes with the mouse. It makes it quick and easy to align all the elements on a dashboard, flowchart, org chart, or any other project in Excel where you have a lot of shapes on the worksheet. Here is a link to the article with step-by-step instructions: https://www.excelcampus.com/charts/dashboard-snap-to-shape-alignment/ Here are links to other videos mentioned in this video: Intro to Pivot Tables & Dashboards part 1 (learn how to build the dashboard used in the video): https://youtu.be/9NUjHBNWe9M Intro to Pivot Tables & Dashboards part 3 (learn how to align shapes with the Align buttons): https://youtu.be/FyggutiBKvU
Hi All in this tutorial you will see that how to connect text or cell value with different shapes and how to customize them according to our need.
How to Use Icon Sets with Text Values in Excel For more Excel Conditional Number Formatting Tips and Tricks visit me at http://bradedgar.com In this video I run a little longer than my 60 second threshold so I've added the + to my icon as you can see in my image. That being said I tackle a few different things in this video. Custom Number Formatting Syntax Review: [positive numbers; negative numbers; zero value; text] Custom number formatting has sections for each type of number as displayed above. Let's have a look how I use this in this video. (1) I show you how you can take numbers and apply text values to them based on what the value is. For example, if the value is positive, I'll show complete, if the value is negative, I will show behind and if the value is zero, I'll show on target. You will get to go through each step that it takes to do this. Notice I built a legend showing you that a value of 1 will show as complete, a value of 0 will show as on target and a value of -1 will be displayed as a negative. (2) Once we've applied the Excel custom number formatting, we can now move forward with applying our icons sets. Icon sets in excel are a great feature for displaying how well something is performing. In this video, we use icon sets with text values and custom number formatting to display icon sets with texts. Because complete is assigned a value of 1 in our cells, on target a value of 0 and behind -1, we can use the numbers applied to the cells (even though they are being displayed as text) to apply icons. Green icon sets will be applied a value of 1, Yellow icon sets a value of 0 and negative 1 will display in red. This is an excellent way to monitor and display status' in a visual way so that's easy to tell at what stage you are in your projects. I'm sure you can think of a million other uses for it. Here are a few other links and resources for you: Excel Custom Number Formatting: How To Conditionally Format Text Fields With Icon Sets Using Number Formatting: http://bradedgar.com/excel-custom-number-formatting-conditionally-format-text-fields-icon-sets-using-number-formatting/ 5 Incredible Number Formatting Tricks That Will Impress Your Boss: http://bradedgar.com/5-incredible-number-formatting-tricks-will-impress-boss/ How to Use Icon Sets in Excel: http://www.excel-easy.com/examples/icon-sets.html Excel conditional formatting Icon Sets, Data Bars and Color Scales: https://www.ablebits.com/office-addins-blog/2014/06/05/excel-conditional-formatting-icon-sets-data-bars-color-scales/ The Ultimate Guide to Custom Number Formatting: http://jonvonderheyden.net/excel/a-comprehensive-guide-to-number-formats-in-excel/ -~-~~-~~~-~~-~- Checkout my newest video: "How to Use the Vlookup Formula in you Excel Dashboard" https://www.youtube.com/watch?v=MCBmPnaKf_g -~-~~-~~~-~~-~-
Want to learn how to name and use cell ranges in Excel? Follow this step by step tutorial and learn how to do so. Don't forget to check out our site http://howtech.tv/ for more free how-to videos! http://youtube.com/ithowtovids - our feed http://www.facebook.com/howtechtv - join us on facebook https://plus.google.com/103440382717658277879 - our group in Google+ While working with spreadsheets in Ms Excel, using a variety of shortcuts and cell references can save you a lot of time. You can name cell ranges and use that name later on instead of typing out the range over and over again. Moreover, the cell names can also be used while entering a function or formula. Follow this step by step Ms Excel tutorial to learn How to Name and use cell ranges in Excel. Step # 1 -- Entering the range name In this Ms Excel tutorial, we will start off the process by giving the cell range a name. In order to do so, highlight the cells that you want to name and go to the name box which is situated on the left side of the screen. In this case, we have highlighted the data of January and in the name box written "January". When you will click on the name drop down menu, the name of the range will appear and when you will click on the name, the data range will be selected automatically. Step # 2 -- Click on the "Create from selection" button An easier method of doing the same thing is to highlight the entire area and go to the "formulas" tab. In the "defined names" group, click on the "create from selection" button. Once this has been done, a small box will appear. Over here, select the top most option and click on the "ok" button. Step # 3 -- Viewing the range names Once you use the "create from selection" option, all the titles of the columns will be taken as the names. Now in the name box, you will see that all the names of the months be given as headings. Step # 4 -- Entering the range name in the function The names of cell ranges can be used in functions as well and instead of entering range, you can type out the name of the range. In this case, suppose we want to use the "sum" function to calculate the total for each month. While entering the sum function to calculate the total for the month of January, we will write "January" within the brackets which will automatically define the range. Step # 5 -- Pressing the F3 key When inserting formulae, you can use the shortcut of pressing the "F3" key; it will show you all the names that you have stored to be displayed in "paste name" box. Step # 6 -- Using the "Autofill" handle However, the downside of using this method is that if you try to use the "autofill" option, it will not work. Instead, it will only copy the sequence of the highlighted cells that you have selected in order to use the autofill feature.
In this video I show you how to move shapes to certain cells using VBA! Kieng Iv/SAF Business Analytics https://ca.linkedin.com/in/kiengiv https://www.facebook.com/UWaterlooBusinessAnalytics