Home
Videos uploaded by user “bradedgardotcom”
How to Use Increase Decrease Arrows in Excel
 
01:22
How to Use Increase Decrease Arrows in Excel For more tips and tricks- check out my website here: http://goo.gl/89RdjM In this video we have a look at how we can insert symbols in Excel, copy those symbols using CTRL + C and then paste the symbols in our custom number format type field. In this video we use up arrows and down arrows (increase/decrease) to show the change between data sets based on percentages outlined. Negative numbers show with a down arrow and positive numbers show with an up arrow. We also show you how to use conditional number formatting within custom number formatting using square brackets. Learn about Custom Number Formatting and How it Works: http://bradedgar.com/1-thing-need-learn-using-excel-custom-number-formatting-syntax/ All articles on custom number formatting from Chandoo: http://chandoo.org/wp/tag/custom-cell-formatting/ The Definitive Guide to Custom Number Formatting: http://jonvonderheyden.net/excel/a-comprehensive-guide-to-number-formats-in-excel/ 5 Incredible Number Formatting Tricks That Will Impress Your Boss: http://bradedgar.com/5-incredible-number-formatting-tricks-will-impress-boss/ Microsofts take on Custom Number Formatting: https://support.office.microsoft.com/en-us/article/Create-a-custom-number-format-78f2a361-936b-4c03-8772-09fab54be7f4?CorrelationId=4f733bdb-092a-4a8b-ab2a-e7e8bb3ae634&ui=en-US&rs=en-US&ad=US -~-~~-~~~-~~-~- Checkout my newest video: "How to Use the Vlookup Formula in you Excel Dashboard" https://www.youtube.com/watch?v=MCBmPnaKf_g -~-~~-~~~-~~-~-
Views: 39992 bradedgardotcom
How to Lock Shapes in Your Excel Dashboards
 
01:45
How to Lock Shapes in Your Excel Dashboards For more Excel tips and tricks visit me at http://bradedgar.com. In this video, I review how you can lock shapes in your Excel dashboards. Often times when people are creating dashboards, the shapes that are left on the dashboard are left editable by the dashboard creator leaving the user the ability to move shapes around in the dashboard which can potentially ruin the dashboard functionality. Selecting a range of cells in the dashboard and then selecting protect sheet under the review tab will ensure that the user cannot accidentally move objects around in the dashboard. Here are some additional articles and resources on the subject: 3 Intelligent Ways to Use Shapes in Excel http://www.bradedgar.com/3-intelligent-ways-use-shapes-excel/ Enhancing Dashboard Interfaces with Shape Layering Tricks http://datapigtechnologies.com/blog/index.php/enhancing-dashboard-interfaces-with-shape-layering-tricks/ How do I Lock Shapes? http://www.mrexcel.com/forum/excel-questions/491027-how-do-i-lock-shapes.html Shapes.Locked Property in Excel https://msdn.microsoft.com/en-us/library/office/ff840486.aspx Stopping the movement of Shapes in Excel http://www.excelforum.com/excel-formulas-and-functions/827095-stopping-movement-of-shapes-excel-2010-a.html protect shapes so users can modify but not delete http://stackoverflow.com/questions/17443568/protect-shapes-so-users-can-modify-but-not-delete Excel Formatting Tip 3 - Prevent Buttons and Shapes from Resizing in Excel when Cells Change Size https://youtu.be/vZ8lkoaQhfc How to Freeze Shapes in Excel http://excelexperts.com/freeze-shapes-excel Lock Shape Position on Screen Excel http://www.ozgrid.com/forum/showthread.php?t=64323 Lock a Shape So You Can Reuse It in Excel http://blog.contextures.com/archives/2009/02/17/lock-a-shape-so-you-can-reuse-it-in-excel/ Buttons Don't Stay Put http://excelribbon.tips.net/T007009_Buttons_Dont_Stay_Put.html How to Lock Down Shapes in Excel Worksheet http://www.howtoexcelatexcel.com/excel-tips-tricks/how-to-lock-down-images-on-your-excel-worksheet/ Use Shapes and Images to make Prettier Charts [Dashboard Tricks] http://chandoo.org/wp/2009/12/03/use-shapes-in-dashboards/ How To Link Excel Shapes to Worksheet Cells | BreezeTree http://www.breezetree.com/articles/link-shapes-to-cells.htm 10 ways to add creative graphics to Excel http://www.techradar.com/us/news/software/applications/10-ways-to-add-creative-graphics-to-excel-598384 Excel Prevent Buttons, Shapes and Objects from Resizing - Excel Tricks https://youtu.be/thZqkFkTPLg How to create and manipulate shapes in Excel https://youtu.be/-AQseDPljqY -~-~~-~~~-~~-~- Checkout my newest video: "How to Use the Vlookup Formula in you Excel Dashboard" https://www.youtube.com/watch?v=MCBmPnaKf_g -~-~~-~~~-~~-~-
Views: 10054 bradedgardotcom
Excel Data Entry Tricks: Insert Data into Multiple Cells at Once in Excel
 
02:20
Excel Data Entry Tricks: Insert Data into Multiple Cells at Once in Excel In this video I'll show you how to insert data into multiple cells at once using this Excel Data Entry Trick. This Excel data entry trick can be done using the following steps: 1 - Use the auto fill option to create headings like months and item numbers. 2 - You'll then select the data range / range of cells. 3 - Once the data range is selected, you'll go directly into writing your formula or data in the top left cell and then type CTRL + Enter and this will automatically fill all cells that have been selected with that formula or data. Using formulas like rand or randbetween will allow you to quickly enter data and insert data into multiple cells at once in Excel. Other Resources and Links Enter the same text into multiple Excel cells at once - Computer Hope http://www.computerhope.com/tips/tip208.htm How to enter the same data (formula) into all selected cells at a time https://www.ablebits.com/office-addins-blog/2013/09/26/insert-same-data-multiple-cells-excel/ Enter same data in multiple cells https://exceljet.net/keyboard-shortcuts/enter-same-data-in-multiple-cells ENTERING DATA AND FORMULAS INTO MULTIPLE CELLS SIMULTANEOUSLY IN EXCEL http://www.k2e.com/tech-update/tips/133-entering-data-and-formulas-into-multiple-cells-simultaneously-in-excel Use CTRL+Enter to Enter Same Data in to Multiple Cells [Quick Tip] http://chandoo.org/wp/2012/01/09/how-to-enter-same-data-into-multiple-cells/ How to enter the same data/formula in multiple cells at once in Excel? https://www.extendoffice.com/documents/excel/2635-excel-enter-data-in-multiple-cells.html Excel Tricks - Using Custom Data Entry https://youtu.be/cQZTAOg_tes How to use Excel - Data Entry - Microsoft Excel Tutorials https://youtu.be/XjiJCfuoU50 Excel Power Tips: 10 Ways to make Data Entry Faster and More Accurate https://youtu.be/6BErKQ29Jjc Excel Data Entry in Specific Cells https://youtu.be/unUqxBM68zg How to create Data Entry Form in Excel - Ms Office? https://www.youtube.com/watch?v=lV9X2K8uEYE Excel 2010 Data Entry Form https://youtu.be/RlgUc33Hrj4 Excel Tip | Make data entry form without any programming Knowledge | 2016 https://youtu.be/WpL0r8zxbGs
Views: 19406 bradedgardotcom
Calculating Invoice Aging in Excel Using Pivot Tables
 
03:03
Calculating Invoice Aging in Excel Using Pivot Tables For more Excel tips and tricks visit me at http://www.bradedgar.com. In this video I'll show you how you can calculate invoice aging in Excel using pivot tables. To start, you can begin by making your days past due for each line on your raw data set for each invoice based off of the date owing column. From here the user can calculate the days past due using the today formula minus the date owing. This will give you the exact number of days the invoice is past due. From there, you can create a pivot table off of your data set or table. Once your pivot table is created, you will want to push the days past due column into the row section of your pivot table. You will also want to add the value of the line to the value section. Next you'll group on the days past due on the row section by simply selecting one of the values, right clicking and then selecting group. You can then select to group by 30 days from day 1 all the way to the number of days past due you would typically see. This will now allow you to calculate invoice aging in Excel based off of the groupings that make the most sense to you. Other Resources and Links Aging Invoice Calculator - Excel Formula SNAFU http://www.mrexcel.com/forum/excel-questions/68210-aging-invoice-calculator-excel-formula-snafu.html Making Aging Analysis Reports Using Excel – How To http://pakaccountants.com/aging-analysis-reports-using-excel-how-to/ Excel Calculate Receivable Aging http://www.excelarticles.com/LE10ePub-250.html Re: Excel Aged Receivable formula to calculate 0-30 days, 61-60 days,. http://www.pcreview.co.uk/threads/re-excel-aged-receivable-formula-to-calculate-0-30-days-61-60-days.1766795/ Date formula to Age Invoices http://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_other/date-formula-to-age-invoices/3d99560d-dcaa-4d7f-b401-6629e85e017a Accounts Receivable Aging Template http://www.spreadsheetshoppe.com/ar-aging-template/ Build an Accounts Receivable Aging Report in Excel https://youtu.be/oXLD1CaG30E Excel Magic Trick 1128: Aging Accounts Receivable Reports On Multiple Sheets With Array Formula https://youtu.be/_bxJqfQMT54 Calculate Age in Excel using Various Formulas Including DATEDIF https://blog.udemy.com/how-to-calculate-age-in-excel/ How to Calculate Age on Excel http://www.wikihow.com/Calculate-Age-on-Excel Excel 2010 #8: Calculating Overdue Invoices https://youtu.be/59olinbk8hQ Track Accounts Receivable With Invoice Aging Report Template For Excel http://www.free-power-point-templates.com/articles/track-accounts-receivable-with-invoice-aging-report-template-for-excel/ How to Calculate Days Outstanding for Overdue invoices in Excel http://excelbyjoe.com/how-to-calculate-days-outstanding-for-overdue-invoices-in-excel/
Views: 6582 bradedgardotcom
How to Use Icon Sets with Text Values in Excel
 
02:59
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 -~-~~-~~~-~~-~-
Views: 53280 bradedgardotcom
Excel Dynamic Conditional Formatting: Create a User Controlled KPI Dashboard - Video
 
06:10
Excel Dynamic Conditional Formatting: Create a User Controlled KPI Dashboard - Video Checkout the actual blog post that goes along with this video: http://www.bradedgar.com/excel-dynamic-conditional-formatting-create-a-user-controlled-kpi-dashboard/ Excel dynamic conditional formatting allows you to do so many cool things when it comes to measuring KPIs or building a KPI dashboard. In this video, you'll learn how to build a KPI Sales Dashboard with adjustable metrics for optimum sales targets and minimum sales targets. Having this flexibility allows you to not have to update your conditional formatting rule by simply creating one new rule for each section and month of your sales targets and referring to that months optimum and minimum cell range targets. As you update your targets for each month, the icon sets on your dashboard will change color between green, yellow and red. If you are above the optimum sales target, your icon sets will display in green, if you are between the optimum and minimum sales targets, your icon sets will be displayed in yellow and if you are below, your conditional formatting icon sets will be displayed in red. Of course, these will update dynamically as you update your targets from the referring cell range. At the beginning of the video, we will look at how we can use custom number formatting as well to display nothing in the cells that we've created which will display the icon sets independently. This video will help you learn how to use Excel conditional formatting in your favor but also allow you to not have to deal with the standard manual updates that would typically be required with conditional formatting. Awesome Resources to accompany this video:\ Excel Magic Trick 787: Conditional Formatting Basic To Advanced (30 Examples) by excelisfun. https://youtu.be/LOaezdoyJDE http://www.techrepublic.com/blog/10-things/10-cool-ways-to-use-excels-conditional-formatting-feature/ http://www.bradedgar.com/excel-custom-number-formatting-conditionally-format-text-fields-icon-sets-using-number-formatting/ http://spreadsheets.about.com/od/excelformatting/qt/conditn_format.htm http://chandoo.org/wp/tag/conditional-formatting/ http://chandoo.org/wp/2008/03/13/want-to-be-an-excel-conditional-formatting-rock-star-read-this/ http://chandoo.org/wp/tag/conditional-formatting-icon-sets/ http://chandoo.org/wp/2014/04/22/modeling-tiles-in-a-room-using-excel-conditional-formatting/ http://www.excel-easy.com/examples/icon-sets.html https://exceljet.net/lessons/how-to-use-icon-sets-with-conditional-formatting http://www.techrepublic.com/blog/microsoft-office/use-excels-conditional-formatting-feature-to-display-simple-icons/ -~-~~-~~~-~~-~- Checkout my newest video: "How to Use the Vlookup Formula in you Excel Dashboard" https://www.youtube.com/watch?v=MCBmPnaKf_g -~-~~-~~~-~~-~-
Views: 16801 bradedgardotcom
How to Freeze Rows and Columns in Excel
 
01:37
How to Freeze Rows and Columns in Excel For more Excel tips and tricks visit me at http://bradedgar.com. In this video I'm going to teach you how to freeze rows and columns in Excel. Using this feature in Excel allows you to keep your header visible or other columns visible when you move outside of an active screen / pane. Freeze panes can be accessed from the view tab on the quick access ribbon. The freeze pane options are under the window section. 1 - To freeze a row or anything above a specific row, you can use highlight an entire row by click on the row number and then selecting freeze panes. This will freeze anything above the row that you've selected. Typically you'll highlight just after your header row so that your column names are displayed as you scroll through your data. 2 - You can also freeze columns so that as you scroll from left to right your information stays firm. You would want to do this if you had statistics way over outside the active pane but you still needed to see like in this example the key information such as country. 3 - Finally you can freeze rows and columns both at once by select the specific cell where the rows and columns you'd like to freeze intersect. Other Resources and Links Freeze or lock rows and columns https://support.office.com/en-us/article/Freeze-or-lock-rows-and-columns-3439cfe6-010c-4d2d-a3c9-d0e8ba62d724 Freeze row and columns in Excel (at the same time) http://superuser.com/questions/92328/freeze-row-and-columns-in-excel-at-the-same-time How to Freeze Rows and Columns in Excel 2016 http://www.laptopmag.com/articles/freeze-rows-excel-2016 Freeze the Top Row and First Column of a Spreadsheet https://youtu.be/SFEhTxn0-NQ Excel 2013 Single page view print copy Freezing Panes and View Options http://www.gcflearnfree.org/excel2013/17 How to freeze panes in Excel 2013 and 2010 to lock rows and columns https://www.ablebits.com/office-addins-blog/2014/06/24/freeze-panes-excel-lock-rows-columns/ How to Freeze Panes in Excel 2013 http://www.dummies.com/how-to/content/how-to-freeze-panes-in-excel-2013.html How do I freeze panes horizontally and vertically at same time https://exceljet.net/lessons/how-to-freeze-columns-or-rows-in-a-list https://exceljet.net/lessons/how-to-freeze-columns-or-rows-in-a-list How to Freeze and Unfreeze Rows and Columns in Excel 2013 http://www.howtogeek.com/166326/how-to-freeze-and-unfreeze-rows-and-columns-in-excel-2013/ Lock Columns and Rows in Excel with Freeze Panes http://spreadsheets.about.com/od/exceltools/ss/81027freezepane.htm Excel 2013 & 2010: Freeze or Unfreeze Panes, Columns, and Rows http://www.technipages.com/excel-freeze-or-unfreeze-panes-columns-and-rows Microsoft Excel: Taking the Pain Out of Freezing Panes http://www.accountingweb.com/technology/excel/microsoft-excel-taking-the-pain-out-of-freezing-panes Freeze or lock rows and columns in an Excel worksheet http://fiveminutelessons.com/learn-microsoft-excel/freeze-or-lock-rows-and-columns-excel-worksheet#sthash.13SSyzmF.dpbs -~-~~-~~~-~~-~- Checkout my newest video: "How to Use the Vlookup Formula in you Excel Dashboard" https://www.youtube.com/watch?v=MCBmPnaKf_g -~-~~-~~~-~~-~-
Views: 13535 bradedgardotcom
How to Apply Conditional Formatting with Excel Slicers
 
05:40
How to Apply Conditional Formatting with Excel Slicers For more Excel tips and tricks visit me at http://bradedgar.com. In this video I'll show you how to apply conditional formatting with Excel slicers. Excel slicers are a handy and easy to use filtering system that Excel has built in but you can use it to actually apply conditional formatting. For the first step you need to setup your data and then create a pivot table out of your data set. Find the field that you'd like to add conditional formatting based on. Once you've done that drag it into the row section of the pivot table. Go to the design tab of the pivot table tools and then select insert slicer. Select the field that you've added to the row section of your pivot table. Cut and past the filter onto the same spreadsheet/worksheet as your data set. Click and select one of the Excel slicer selection options. Once you've done that, you can then head to conditional formatting from the home tab and create a formatting option using the formula option. In the formula bar you'll type =the cell on the data set of your first row and then = to the first cell of your pivot table. Now you're going to copy and paste the conditional formatting that you applied to the first row of your data set. You can now use the Excel slicer to change and highlight the rows based on a specific value. Other Resources and links Introduction to Slicers – What are they, how to use them, tips, advanced techniques & interactive reports using Excel Slicers http://chandoo.org/wp/2015/06/24/introduction-to-slicers/ Getting Fancy with your Excel Slicers http://datapigtechnologies.com/blog/index.php/getting-fancy-with-your-excel-slicers/ Excel Slicer Trick http://www.myonlinetraininghub.com/excel-slicer-trick Use Excel Slicer Selection in Formulas http://www.myonlinetraininghub.com/use-excel-slicer-selection-in-formulas Excel 8 tips and tricks you should know for Excel 2016 for Mac https://blogs.office.com/2015/10/29/8-tips-and-tricks-you-should-know-for-excel-2016-for-mac/ Another way to get and use slicer values in formulas http://www.powerpivotpro.com/2010/12/another-way-to-get-and-use-slicer-values-in-formulas/ Interactive Excel Dashboard Tips Using Slicers and Pivot Charts https://excel.tv/interactive-excel-dashboard-tips-using-slicers-and-pivot-charts/ Apply Conditional Formatting using Slicers http://chandoo.org/wp/2016/05/11/apply-conditional-formatting-using-slicers/ Conditional Formatting Controlled via Slicers! http://www.powerpivotpro.com/2012/09/conditional-formatting-controlled-via-slicers/ Excel Pivot Table CONDITIONAL FORMATTING: Excel 2013, 2010 & 2007 https://youtu.be/EMGy0wTQfN8 Pivot table - looses conditional formatting when slicing https://www.experts-exchange.com/questions/28344702/Pivot-table-looses-conditional-formatting-when-slicing.html http://www.excelforum.com/excel-charting-and-pivots/1061529-conditional-formatting-of-a-pivot-table-slicer.html Conditional Formatting via Slicers, Part Two http://www.powerpivot-info.com/post/1108-conditional-formatting-via-slicers--part-two Slicers in excel http://www.excel-easy.com/examples/slicers.html How to use a slicer for multiple pivot tables https://exceljet.net/tips/how-to-use-a-slicer-for-multiple-pivot-tables
Views: 4531 bradedgardotcom
How to Use Wingdings As Icon Sets In Your Excel Dashboard
 
01:24
How to Use Wingdings As Icon Sets In Your Excel Dashboard For more Excel Dashboard Tricks checkout http://bradedgar.com. In this video I'll show you how you can use the Wingdings font to create icons sets in your Excel Dashboard. In order to do that you can use the insert symbol option to put the symbol you like as a Wingding into the cell that you'd like to display the icon set. You can then apply conditional formatting to the icons using conditional formatting based on the selection made by the user with the data validation option that is available. When applying the conditional formatting, you'll use conditional formatting using the formula option. As the user selects PASS or FAIL the icon font color will change with the Wingding character that you've selected. Other Excel Dashboard Resources: Excel Data Validation: http://www.excel-easy.com/basics/data-validation.html How to use Drop down menus to make Interactive Charts and Dashboards in Excel http://www.launchexcel.com/excel-drop-down-for-dashboards/ Making a Dynamic Dashboard in Excel http://chandoo.org/wp/2010/03/30/excel-dashboard-tutorial-2/ Data Validation Examples Custom Criteria http://www.contextures.com/xlDataVal07.html Excel Data Validation List: Building a Dynamic Salesman Dashboard http://www.bradedgar.com/excel-data-validation-list-building-dynamic-salesman-dashboard-part-1/ conditional formatting icon sets http://chandoo.org/wp/tag/conditional-formatting-icon-sets/ Display Alerts in Dashboards to Grab User Attention http://chandoo.org/wp/2010/05/25/alerts-in-dashboards/ Create Your Own Excel Icon Set http://blog.contextures.com/archives/2013/09/03/create-your-own-excel-icon-set/ How to use Icon sets with conditional formatting https://exceljet.net/lessons/how-to-use-icon-sets-with-conditional-formatting Format cells Use icon sets Excel Pivot Tables http://www.lynda.com/Excel-tutorials/Formatting-cells-using-icon-sets/114891/131321-4.html -~-~~-~~~-~~-~- Checkout my newest video: "How to Use the Vlookup Formula in you Excel Dashboard" https://www.youtube.com/watch?v=MCBmPnaKf_g -~-~~-~~~-~~-~-
Views: 5770 bradedgardotcom
Excel Tables: Dynamic Data Sources for Your Excel Pivot Tables
 
05:10
Excel Tables: Dynamic Data Sources for Your Excel Pivot Tables For more Excel tips and tricks, visit me at http://bradedgar.com. In this video I'll show you how to use Excel Tables to create dynamic data sources for your Excel Pivot Tables. This is super useful because if you're using a standard reference for your data source, you'll need to update your data source as data gets added to the set. By using Excel Tables, you make your data set dynamic so that every time you add a new record to your data set, your pivot table simply needs to be refreshed in order for the data to get updated again. If you don't use a table, and you simply reference a range of cells, your data source needs to be updated before you refresh your Pivot Table. Other Resources and Links How to create a dynamic pivot table and refresh automatically in excel http://www.get-digital-help.com/2011/04/26/how-to-create-a-dynamic-pivot-table-and-refresh-automatically-in-excel-2007/ Excel Pivot Table -- Dynamic Data Source - Contextures http://www.contextures.com/xlPivot01.html How to Create a Pivot Table with Expanding Data Ranges - Excel Pivots http://excelpivots.com/excel/pivot_table_expanding_data_ranges/ Formula Friday – Create A Dynamic Pivot Table Data Source Using OFFSET and COUNTA Functions http://www.howtoexcelatexcel.com/blog/formula-friday-create-a-dynamic-pivot-table-data-source-using-offset-and-counta-functions/ Excel video: Use a table for your next pivot table | Exceljet https://exceljet.net/tips/use-a-table-for-your-next-pivot-table Creating pivot tables with expanding ranges http://www.datawright.com.au/excel_resources/expanding_pivot_tables.htm Dynamically Change A Pivot Table's Data Source Range With This VBA Macro Code http://www.thespreadsheetguru.com/the-code-vault/2014/7/9/change-a-pivot-tables-data-source-range How to make an Excel PIVOT table update automatically http://dedicatedexcel.com/how-to-make-an-excel-pivot-table-update-automatically/ How to automate your Excel models and reporting using dynamic Range? https://www.analyticsvidhya.com/blog/2014/09/automate-excel-models-reporting-dynamic-range/ Create a Dynamic Data Range with the OFFSET function http://myexcelonline.com/blog/create-dynamic-data-range-offset-function-excel/
Views: 5687 bradedgardotcom
How to Convert Dates in Excel into Year, Month, or Day Using the Text Formula
 
03:49
How to Convert Dates in Excel into Year, Month, or Day Using the Text Formula For more Excel tips and tricks be sure to visit me at http://bradedgar.com. In this video, I'll show you how to convert dates in Excel into year, month or day using the Text Formula. Using the text formula is very simply and converts the day into text but allows you to easily create and display the name of the month without having any underlying single date associated to the value. This is useful for summarizing data as you can take a date time stamp and turn into a month and then report off of that month, year or day in your pivot table. Other things I use the text formula for is showing the date as a day (i.e. Tuesday for example) and then using that to update and show my production schedule and what days each completion date falls on. Using the text formula in conjunction is the perfect tool for converting a date field to again a year, month or day in another column or field on your data set or table. Other Links and Resources Convert date to text https://exceljet.net/formula/convert-date-to-text How to convert date to text using Excel TEXT function and no-formula ways https://www.ablebits.com/office-addins-blog/2015/04/08/convert-date-text-excel/ Convert a Date to Text in Excel http://www.excelfunctions.net/convert-date-to-text.html How to convert date to number or text in Excel? https://www.extendoffice.com/documents/excel/2476-excel-convert-date-to-text-or-number.html How to convert a date into a month name in Excel 2010 http://dedicatedexcel.com/how-to-convert-a-date-into-a-month-name-in-excel-2010/ Get month name from date https://exceljet.net/formula/get-month-name-from-date How to convert date to weekday name or month name in Excel? https://www.extendoffice.com/documents/excel/821-excel-convert-date-to-weekday-month-name.html Using MONTH and EOMONTH functions in Excel - formula examples https://www.ablebits.com/office-addins-blog/2015/04/22/excel-month-eomonth-functions/ Need Formula to Convert Date/Timestamp to Month-Year http://www.mrexcel.com/forum/excel-questions/424192-need-formula-convert-date-timestamp-month-year.html Converting Text Dates to Excel Dates En Masse http://www.k2e.com/tech-update/tips/521-tips-converting-text-dates-to-excel-dates-en-mase How to convert dates from numeric values to text https://www.sageintelligence.com/tips-and-tricks/excel-tips-tricks/2013/09/convert-dates-numeric-values-text/ Excel formula: Get day name from date | Exceljet https://exceljet.net/formula/get-day-name-from-date Show dates as days of the week https://support.office.com/en-us/article/Show-dates-as-days-of-the-week-14f02f1d-8e3d-45a5-bdf8-3d962bf15327 WEEKDAY, DAY and other functions to calculate days in Excel https://www.ablebits.com/office-addins-blog/2015/04/16/excel-weekday-day-functions/ How do I get the day name into a cell in Excel? http://superuser.com/questions/41883/how-do-i-get-the-day-name-into-a-cell-in-excel How to Calculate the Day of the Week in Excel http://www.wikihow.com/Calculate-the-Day-of-the-Week-in-Excel
Views: 11784 bradedgardotcom
How to Calculate the Number of Months Between 2 Dates in Excel
 
01:06
How to Calculate the Number of Months Between 2 Dates in Excel For more tips and tricks, visit me at http://www.bradedgar.com. In this video I will review how to calculate the number of months between 2 dates in Excel. In order to do this you will use the DATEDIF formula in Excel. This easily and quickly uses a start date and end date and calculates the number of months between the two dates using the formula. You can use this to help during the calculation phase of building Excel dashboards. I often use the formula to build a unique list of months between the start and end date range of my original data set. Unfortunately, you cannot simply use the subtract formula in Excel to calculate the number of months between two days but this is why the DATEDIF Formula was created. The only thing that would make this formula better is if it was not an unlisted formula in Excel. Here is the formula layout: =DATEDIF(start date, end date, interval) The interval signifies whether you want to know the differences in days, months or years ("D", "M", "Y"). Other Excel Date Tips and Resources List of date and time functions in Excel: http://www.excel-easy.com/functions/date-time-functions.html ExcelisFun video on everything you need to know about dates: https://youtu.be/VvIvINcn79I The definitive guide to learning Excel date and time functions: http://www.exceltactics.com/definitive-guide-using-dates-times-excel/ Test dates in the future or past and 10 other awesome Excel date tips: http://chandoo.org/wp/2008/08/26/date-time-tips-ms-excel/ Learn how to format numbers and dates in Excel using the text function: https://blog.udemy.com/excel-text-function/ Excel date formula examples from AbleBits: https://www.ablebits.com/office-addins-blog/2015/06/10/excel-date-functions/ Calculate the number of months between 2 dates in Excel: https://support.microsoft.com/en-us/kb/214134 Other amazing formulas you should learn in Excel: http://www.howtogeek.com/school/microsoft-excel-formulas-and-functions/lesson4/ Get your way with the date formula in Excel from Udemy: https://blog.udemy.com/excel-date-format/ PC world with handy tips and formulas - looks at DAY and NETWORKDAY formulas (these are great for essential every day tasks in Excel). http://www.pcworld.com/article/2877236/excel-formulas-cheat-sheet-15-essential-tips-for-calculations-and-common-tasks.html -~-~~-~~~-~~-~- Checkout my newest video: "How to Use the Vlookup Formula in you Excel Dashboard" https://www.youtube.com/watch?v=MCBmPnaKf_g -~-~~-~~~-~~-~-
Views: 16518 bradedgardotcom
Excel Tables: How to Create a Dynamic Data Validation Drop Down with Excel Tables and a Name Range
 
04:56
Excel Tables: How to Create a Dynamic Data Validation Drop Down with Excel Tables and a Name Range For More Excel tips and tricks visit me at http://www.bradedgar.com. In this video I show you how to use Excel Tables to create a dynamic data validation list using Excel Tables and a name range. Unfortunately Excel data validation lists do not allow you to reference a table name but do allow you to reference a name range. With that in mind I show you how to create a name range that references an Excel Table. Because the table is dynamic, the name range becomes dynamic. Once we've done that, we create the data validation drop down list that refers to our dynamic name range. This will then allow us to add new records to the table and then also see our data validation list update with all of the new records being added to our Excel Table. Additional Resources and Links Creating a drop down list in Excel: static, dynamic, from another workbook https://www.ablebits.com/office-addins-blog/2014/09/24/excel-drop-down-list/ Create a drop-down list https://support.office.com/en-us/article/Create-a-drop-down-list-7693307a-59ef-400a-b769-c5402dce407b A Dynamic Dependent Drop Down List in Excel http://excelsemipro.com/2011/05/a-dynamic-dependent-drop-down-list-in-excel/ Create Dependent Lists using Tables http://www.contextures.com/exceldatavaldependindextables.html Dependent Lists - Tables and INDIRECT http://www.contextures.com/exceldatavaldependindextablesindirect.html Automating Data Validation Lists in Excel http://www.accountingweb.com/technology/excel/automating-data-validation-lists-in-excel Create An Excel Data Validation List Using A Table http://envisage-consulting.com/blog/create-an-excel-data-validation-list-using-a-table/ Excel Tables as Source for Data Validation Lists http://www.myonlinetraininghub.com/excel-tables-as-source-for-data-validation-lists EXCEL DROPDOWNS DONE RIGHT: DATA VALIDATION AND NAMED RANGES http://analyticsdemystified.com/excel-tips/excel-dropdowns-done-right-data-validation-and-named-ranges/ Putting table name as a reference for the purpose of cell values validation http://superuser.com/questions/235999/putting-table-name-as-a-reference-for-the-purpose-of-cell-values-validation
Views: 5115 bradedgardotcom
Step 1: Building the HR Salary Dashboard Calculations Tab Using Pivot Tables
 
20:11
Building an HR Salary Dashboard Using Pivot Tables in Excel Buy the dashboard for $5: https://gumroad.com/l/hr-salary-dashboard You can also visit my website at http://bradedgar.com for more Excel goodies. There are multiple different ways that you build an Excel dashboard or an HR Salary Dashboard in Excel. The final dashboard needs to include the top 3 and bottom 3 compensation salaries and it needs to include the total employee count, the cumulative salary value, and the average salary. The pivot tables allow us to have a dynamic result data set that will change as records are added to our employee table. In this video I walk you through some of the following different Excel techniques: (1) How to setup a pivot table in Excel using tables (Employee table in this case). (2) How to setup and change the name of your pivot table. (3) How to change your pivot table from a compact form to a tabular form pivot table. (4) I offer a quick explanation of the 4 pivot table areas (filter, columns, row labels and values). I explain that we only need to make use of the row labels area and the values area in this tutorial. (5) Once dragging the correct fields into our pivot table row label section and value section, I update the pivot tables from the design tab to be in tabular format, to have repeat items, to remove subtotals and to remove grand totals off of rows and columns. (6) I then review how you can use the sorting option on the highest level of your pivot table to sort in ascending or descending order. For top 3 highest compensation or salary, we use the descending order. For bottom 3 compensation or salary, we use the ascending. We then look at how to apply the top 10 value filter and bottom 10 value filter. (7) I also dive into how easy it is to simply copy a pivot table and paste the same pivot table elsewhere and change your field settings to suite your needs. We look at formula pasting, and formatting pasting as well. (8) The concatenate formula is also used to merge the names of the top 3 and bottom 3 compensated employees based on salary. This provides us with the employees full name for the final dashboard. (9) We then setup the employee count, cumulative salary and average salary pivot tables. I show you how you can change the custom number format to also be in thousands. http://bradedgar.com/10-ways-pivot-tables-in-excel-2010-2013-can-increase-productivity/ http://fiveminutelessons.com/learn-microsoft-excel/how-create-pivot-table-excel http://pakaccountants.com/excel-sales-dashboard-slicers-how-to/ http://chandoo.org/wp/excel-pivot-tables/ http://bradedgar.com/excel-kpi-dashboard-monitor-salesmen-meet-reach-sales-targets/ http://www.skilledup.com/articles/10-helpful-excel-pivottable-tips/ http://www.businessinsider.com/excel-tricks-vlookup-index-match-pivot-tables-array-2013-5?op=1 http://www.myonlinetraininghub.com/excel-pivottable-calculated-fields -~-~~-~~~-~~-~- Checkout my newest video: "How to Use the Vlookup Formula in you Excel Dashboard" https://www.youtube.com/watch?v=MCBmPnaKf_g -~-~~-~~~-~~-~-
Views: 30466 bradedgardotcom
How to Dynamically Highlight a Record with Today's Date in Excel
 
03:50
How to Dynamically Highlight a Record with Today's Date in Excel For more Excel tips and tricks visit me at http://bradedgar.com. In this video I'll show you how you can dynamically highlight a record based on today's date in Excel by using conditional formatting. To do this, you'll need to first select the first row of your data set. From here you'll use the conditional formatting and then select use formula to determine which cells to format. From here you'll then select the date in the row that you've highlighted after typing in the equals symbol and then make sure that the reference is relative on the column but no t on the row (i.e. absolute reference). Once you type the equals symbol again, you'll then type in the today formula TODAY(). This is going to mean that all cells that you've highlighted in the row (meaning all columns) will have formatting applied if the date field = today's date. Once you hit enter, you'll copy the entire row that you've applied the conditional formatting to and then apply it to all of the other rows in the data set. Voila! When the date changes so will the record that is being highlighted based on the current day (today's date). Other Links and Resources How to Highlight a Row in Excel Using Conditional Formatting http://www.howtogeek.com/howto/45670/how-to-highlight-a-row-in-excel-using-conditional-formatting/ How to highlight rows with conditional formatting https://exceljet.net/tips/how-to-highlight-rows-with-conditional-formatting Highlight entire rows https://exceljet.net/formula/highlight-entire-rows Highlighting the Rows of Selected Cells http://excel.tips.net/T002457_Highlighting_the_Rows_of_Selected_Cells.html Excel 2007: Highlight Active Row https://youtu.be/bGjqDGF7xaM How to change the row color based on a cell's value in Excel https://www.ablebits.com/office-addins-blog/2013/10/29/excel-change-row-background-color/ Apply shading to alternate rows in a worksheet https://support.office.com/en-us/article/Apply-shading-to-alternate-rows-in-a-worksheet-a443b0f5-2025-42f6-9099-5de09c05e880 Microsoft Excel: Highlight the current row and column https://thomas.vanhoutte.be/miniblog/microsoft-excel-highlight-the-current-row-and-column/ How to auto-highlight row and column of active cell in Excel? https://www.extendoffice.com/documents/excel/1494-excel-highlight-active-row-and-column.html Hightlight active row/column in Excel without using VBA? http://stackoverflow.com/questions/22349424/hightlight-active-row-column-in-excel-without-using-vba http://www.tushar-mehta.com/publish_train/xl_vba_cases/0121%20highlight%20row%20and%20col%20of%20selected%20cell.shtml Learn How to Highlight Rows in Excel Based on Duplicates http://www.accountingweb.com/technology/excel/learn-how-to-highlight-rows-in-excel-based-on-duplicates How to use Conditional Formatting to Highlight Data https://www.timeatlas.com/excel-conditional-formatting/ Format Entire Row Based on One Cell http://www.contextures.com/xlCondFormat02.html TODAY function https://support.office.com/en-us/article/TODAY-function-5eb3078d-a82c-4736-8930-2f51a028fdd9
Views: 3495 bradedgardotcom
Create a Dynamic Filter in Excel Using Conditional Formatting
 
07:03
Create a Dynamic Filter in Excel Using Conditional Formatting Buy the Cheat Sheet and Workbook: https://gumroad.com/l/ytfiltercheatsheet For more Excel tips and tricks be sure to visit me at http://www.bradedgar.com. In this video I'll show you exactly how you can setup a data validation list in Excel. To do this you will go to your data validation list under the data tab in the quick access ribbon, select data validation and then refer to a range that lists your part numbers. Once added, you will then create and add the filter options by using CTRL + SHIFT + L. The next step will be to apply Excel conditional formatting to the field/column you want to filter by and you will make sure that the conditional formatting refers to the data validation list selection cell so that items that are equal to the value selected are formatted in a white color. We made sure it was the same color so that it was not noticeable which items were highlighted in our data set for the analysis. Once this is done, you will filter by the field that you are dealing with (in our case the part number) by the new color applied using the conditional formatting. Once you've done this, whatever part number you select will be filtered. As soon as you change the part number in your drop down, you'll notice that the filter is not update. You need to reapply your filter. To do this, we will write a macro as follows and create a module: Sub ApplyFilter() ActiveSheet.AutoFilter.ApplyFilter End Sub We will the use the event change under the spreadsheet that the drop down and data set exist on and call the ApplyFilter macro. This will make our filter reapply every time an update is made to the drop down list value for the data validation. All of our items will then filter based on the selection. Other resources for this article: https://youtu.be/pCAXRLYr59E http://trumpexcel.com/2015/01/dynamic-excel-filter/ http://chandoo.org/wp/2009/02/12/make-a-dynamic-chart-using-data-filters/ https://youtu.be/0xOHevuILxI http://www.contextures.com/xlautofilter01.html https://blog.udemy.com/excel-vba-autofilter/ http://www.excelguru.ca/forums/showthread.php?2052-Excel-2010-Dynamic-Autofilter-on-Change http://www.techrepublic.com/blog/microsoft-office/how-to-sum-values-in-an-excel-filtered-list/ http://www.bradedgar.com/premium-excel-slicer-tutorial-filter-sort-topbottom-5/ http://www.bradedgar.com/excel-tables-using-excel-tables-summarize-filter-autofill-formulas-like-boss/ http://www.excel-easy.com/examples/advanced-filter.html -~-~~-~~~-~~-~- Checkout my newest video: "How to Use the Vlookup Formula in you Excel Dashboard" https://www.youtube.com/watch?v=MCBmPnaKf_g -~-~~-~~~-~~-~-
Views: 12935 bradedgardotcom
How to Use the Excel Camera Tool
 
04:40
How to Use the Excel Camera Tool For more tips and tricks, visit me at You can checkout the blog post here: http://www.bradedgar.com/5-little-known-excel-tips-make-you-look-smarter-than-co-workers/. In this video, I'll show you how you can use the Excel camera tool to build and create dynamic visual displays of anything that you build within a worksheet / spreadsheet. In the video, I'll go over how to setup the camera tool in the quick access toolbar and then how you can easily use it. The camera tool allows you select area of a spreadsheet (i.e. a range of cells) and then display any information, graphs, charts, displays dynamically of that area. If you were to overlap an object for example you would see the object all of a sudden be displayed in that area. The nice thing about the Excel camera tool is you can change data, information or the display that you've taken a picture of, and you'll automatically update that picture. The picture is also easily resizable so you can move it around or add many different pictures in one worksheet. Other Excel Camera Tool Resources and Links Excel Camera Tool: How to Use the Camera Tool To Build Excel Dashboard Worksheets http://www.bradedgar.com/excel-camera-tool-use-camera-tool-build-excel-dashboard-worksheets/ Camera Tool Function In Excel 2010 http://www.addictivetips.com/microsoft-office/camera-tool-function-in-excel-2010/ What is the Excel Camera Tool and how to Use It http://chandoo.org/wp/2008/12/02/excel-camera-tool-help/ The Excel Camera Tool Don't Leave Home Without It http://blog.accountants.intuit.com/from-the-experts/excels-camera-tool-dont-leave-home-without-it/ Excel Tip: Using the Excel camera feature http://www.accountingweb.com/technology/excel/excel-tip-using-the-excel-camera-feature Enhance Your Excel Dashboards with the Excel Camera Tool and PIcture Effects http://datapigtechnologies.com/blog/index.php/enhance-excel-2010-dashboards-with-camera-tool-and-picture-effects/ Using the Excel Camera Tool to create a Vertical Line Chart in Excel http://www.exceldashboardtemplates.com/using-the-camera-tool-to-create-a-vertical-line-chart-in-excel/ -~-~~-~~~-~~-~- Checkout my newest video: "How to Use the Vlookup Formula in you Excel Dashboard" https://www.youtube.com/watch?v=MCBmPnaKf_g -~-~~-~~~-~~-~-
Views: 7252 bradedgardotcom
How to Create a Product Review Data Chart in Excel
 
03:39
How to Create a Product Review Data Chart in Excel For more Excel tips and tricks visit me at http://www.bradedgar.com. This is a technique you can use on other charts but in this video I'll show you how to create a product review data chart in Excel using the repeat / rept formula. This rept formula (repeat formula) allows you to consecutively display a character by the number of times provided to the formula. In this video I use the Wingding font to display a star and use the repeat formula with Excel data validation to force the user to put in the number of stars that they feel is valid for the review of the product / product review. More Resources and Links on the Repeat Formula and Wingdings Fonts How to Use Wingdings As Icon Sets In Your Excel Dashboard https://youtu.be/mOY9Wxg2fGI Wingding Character Mapping from Spreadsheet Guru http://www.thespreadsheetguru.com/blog/wingdings-webdings-font-icon-cheat-sheet-printable Wingdings and Other Symbolic Fonts in Slicers! http://www.powerpivotpro.com/2012/07/wingdings-and-other-symbolic-fonts-in-slicers/ Wingdings 2 character set and equivalent Unicode characters www.alanwood.net/demos/wingdings-2.htm Use WingDings to display special check box controls in an Access report http://www.techrepublic.com/blog/microsoft-office/use-wingdings-to-display-special-check-box-controls-in-an-access-report/ How to insert symbols and special characters in Excel https://exceljet.net/lessons/how-to-insert-symbols-and-special-characters-in-excel Conditional Formatting Icons in Excel 2003 http://blog.contextures.com/archives/2009/04/14/conditional-formatting-icons-in-excel-2003/ The Ultimate Guide to Bullet Points in Excel http://www.launchexcel.com/bullet-in-excel/ Excel REPT Function https://exceljet.net/excel-functions/excel-rept-function The Excel REPT Function http://www.excelfunctions.net/Excel-Rept-Function.html http://chandoo.org/excel-formulas/rept.shtml 30 Excel Functions in 30 Days: 15 – REPT http://blog.contextures.com/archives/2011/01/16/30-excel-functions-in-30-days-15-rept/ Excel REPT Function Tutorial http://spreadsheetsuperstar.com/excel-rept-function-tutorial/ Use Excel's REPT function to visually display data comparisons http://www.techrepublic.com/blog/microsoft-office/-use-excels-rept-function-to-visually-display-data-comparisons/ -~-~~-~~~-~~-~- Checkout my newest video: "How to Use the Vlookup Formula in you Excel Dashboard" https://www.youtube.com/watch?v=MCBmPnaKf_g -~-~~-~~~-~~-~-
Views: 6329 bradedgardotcom
How You Can Use the Excel Name Box
 
02:51
How You Can Use the Excel Name Box For more tips and tricks visit me at You can checkout the blog post here: http://www.bradedgar.com/5-little-known-excel-tips-make-you-look-smarter-than-co-workers/. In this video I review 6 different ways you can quickly use the Excel name range box to highlight cells and ranges, create ranges and then of course use those ranges in your formulas. This is such an easy and great way to navigate around your spreadsheet especially if you are looking for certain ranges to reverse engineer a dashboard that is using formulas. Here are the 6 tips that I bring up in the video: Although I’ve only technically written here that there is one step to this tip, obviously there are many but I’m going to tell you 6 simple points how you can use the name box: In the Excel name box, type in 3:3 (the row numbers) to highlight row 3. You can highlight any given row by simply separating your row numbers by a colon. In the Excel name box, type in C:C (the column numbers) to highlight column C. You can highlight any given column by simply separating your column letters by a colon. If you want to highlight multiple columns and rows at once, you can do that by selecting the range of rows then placing a comma and then the range of columns (e.g. in the name box type 3:4,C:D). If you want to select multiple cells at a time, you can type in individual cell or range of cells and separate other ranges by commas (e.g. C5:D8,J8:J15). Creating a name range is also very simple. Select a range of cells that you like to name, and then type the name in the Excel name box. You can then use the newly created name range in your formulas. In the video, I have a range of cells called Sales2015. I can then perform the SUM of that name range to find total sales for the year. Finally, you can find and select an already created name range (or cell for that matter) by selecting a name range from the Excel name box drop down. You can also just simply type in the name range and it will automatically highlight the cells associated to the name range. Other Excel Resources on Name Ranges and the Excel Name Box: Twenty-Five Ways to Use Excel's Name Box http://www.accountingweb.com/technology/excel/twenty-five-ways-to-use-excels-name-box Excel Tip: 10 Quick Range Name Tricks http://www.accountingweb.com/technology/accounting-software/excel-tip-10-quick-range-name-tricks The Fastest Way to Created Defined Names in Excel - Name Box http://www.k2e.com/tech-update/tips/418-tip-fastest-way-to-create-defined-names-in-excel The Best Use of the Excel Name Box http://www.thewindowsclub.com/use-of-name-box-in-excel Some Great Naming Techniques You can Use in Excel http://spreadsheetpage.com/index.php/tip/naming_techniques/ How do I create a named cell in Microsoft Excel? http://www.computerhope.com/issues/ch000704.htm Excel Shortcut: Create Quick Name Ranges From Large Tables of Data Using Ctrl+Shift+F3 http://www.bradedgar.com/excel-shortcut-create-quick-name-ranges-large-tables-data-using-ctrlshiftf3/ Be More Valuable in the Office: 5 Ways to Increase Your Efficiency in Excel http://www.bradedgar.com/be-more-valuable-in-the-office-5-ways-to-increase-your-efficiency-in-excel/ Named Ranges in Excel http://www.contextures.com/xlNames01.html Using Named Ranges in Excel Formulas http://www.excel-easy.com/examples/names-in-formulas.html How to Create a Dynamic Defined Name Range in Excel https://support.microsoft.com/en-us/kb/830287 -~-~~-~~~-~~-~- Checkout my newest video: "How to Use the Vlookup Formula in you Excel Dashboard" https://www.youtube.com/watch?v=MCBmPnaKf_g -~-~~-~~~-~~-~-
Views: 5085 bradedgardotcom
How to Create Live Forecasting with Excel GETPIVOTDATA
 
07:25
How to Create Live Forecasting with Excel GETPIVOTDATA This is a guest video post from John Michaloudis at http://www.myexcelonline.com. To checkout the accompanying blog post checkout the blog post 3 Reasons Why You Need to Start Using Excel GETPIVOTDATA. http://www.bradedgar.com/3-reasons-why-you-need-to-start-using-excel-getpivotdata/ In this video John will show you how to create live forecasting with Excel GETPIVOTDATA. He shows you how the Excel PivotTable generates a not so pretty report and dashboard. He then reviews how to create a live forecasting report below the PivotTable. You'll also use the eomdate formula to determine whether or not the data that will be displayed is actual data or planned data. If the current date is before the current end of month then we will show the information as actual data. We then use the IF formula to determine if each of the dates are a planned month or an actual month. This information will the be used with the Excel GETPIVOTDATA function and will match against the information and fields outlined in the Excel PivotTable. He then looks at using conditional formatting on the cells to display whether or not it's planned or actual data in the cell. Other Resources http://www.contextures.com/xlPivot06.html https://support.office.com/en-ca/article/GETPIVOTDATA-function-026db7b1-dd6f-4057-9396-2c2798d159db?CorrelationId=d2c7003f-5eb1-4cb4-9997-2d144d5e10f0&ui=en-US&rs=en-CA&ad=CA http://www.databison.com/getpivotdata-formula-excel-how-to-use-getpivotdata-function-with-pivot-table/ http://www.bluepecantraining.com/portfolio/useful-ways-to-use-the-getpivotdata-function/ https://wiki.openoffice.org/wiki/Documentation/OOo3_User_Guides/Calc_Guide/GETPIVOTDATA http://chandoo.org/wp/tag/getpivotdata/ http://www.notjustnumbers.co.uk/2009/04/excel-tip-using-getpivotdata-part-1.html https://youtu.be/f3a1xf7-fPk -~-~~-~~~-~~-~- Checkout my newest video: "How to Use the Vlookup Formula in you Excel Dashboard" https://www.youtube.com/watch?v=MCBmPnaKf_g -~-~~-~~~-~~-~-
Views: 3873 bradedgardotcom
How to Use Excel Pivot Tables and Grouping to Summarize Your Monthly Sales Data
 
03:27
How to Use Excel Pivot Tables and Grouping to Summarize Your Monthly Sales Data For more Excel tips and tricks be sure to visit me at http://bradedgar.com. In this video I'll show you how to use Excel Pivot Tables and grouping to summarize your monthly sales or usage data. It's incredible how quickly you can summarize any set of large transactional data using pivot tables. I'll firstly show you how to quickly select your data set and then insert a new pivot table on a new worksheet. From there I'll show you how you can easily drag and drop values to the row section, column section and value section to generate a quick analysis of the total sum of all sales/usage. Once this is complete, I'll show you how you can cleanup the look and feel of your pivot table by creating a tabular pivot table, removing some subtotals and then also always setting the values to repeat on the rows. After this is done, you'll be able to then take the transaction dates in the column area of the pivot table and group them into years and months. This is a great way to show exactly what your monthly sales is by month for a given product, product group or even for all products. Other Links and Resources for this Pivot Table video: Excel Pivot Table -- Grouping Data http://www.contextures.com/xlPivot07.html Group or ungroup data in a PivotTable report https://support.office.com/en-us/article/Group-or-ungroup-data-in-a-PivotTable-report-c9d1ddd0-6580-47d1-82bc-c84a5a340725 Group Pivot Table Items http://www.excel-easy.com/examples/group-pivot-table-items.html How to group by week in pivot table? https://www.extendoffice.com/documents/excel/1874-excel-pivot-table-group-by-week.html Grouping by Date in an Excel Pivot Table https://fiveminutelessons.com/learn-microsoft-excel/grouping-date-excel-pivot-table How to group a pivot table by age range https://exceljet.net/blog/how-to-group-a-pivot-table-by-age-range Pivot Table Tutorial Part 3 - Group A Pivot Table in Excel http://www.excelfunctions.net/group-pivot-table.html Pro tip: Group an Excel PivotTable by dates http://www.techrepublic.com/article/pro-tip-group-an-excel-pivottable-by-dates/ Grouping Dates in Pivot Tables http://chandoo.org/wp/2009/11/17/group-dates-in-pivot-tables/ Guide to Excel Pivot Tables http://chandoo.org/wp/excel-pivot-tables/ How to Create a Pivot Table in Excel: A Step-by-Step Tutorial (With Video) http://blog.hubspot.com/marketing/how-to-create-pivot-table-tutorial-ht Quick start: Create a PivotTable report https://support.office.com/en-us/article/Quick-start-Create-a-PivotTable-report-0c220f35-e478-49aa-beaf-24018b7f0f64 Create a PivotTable to analyze worksheet data https://support.office.com/en-us/article/Create-a-PivotTable-to-analyze-worksheet-data-A9A84538-BFE9-40A9-A8E9-F99134456576 Working with Pivot Tables in Microsoft Excel http://www.howtogeek.com/howto/13336/working-with-pivottables-in-excel/ How to Create a Dashboard Using Pivot Tables and Charts in Excel (Part 3) https://youtu.be/FyggutiBKvU Create a quick and effective dashboard using Excel's PivotChart and Slicer objects http://www.techrepublic.com/article/create-a-quick-and-effective-dashboard-using-excels-pivotchart-and-slicer-objects/ Make Dynamic Dashboards using Pivot Tables & Slicers [Video & Download] http://chandoo.org/wp/2010/12/08/dynamic-dashboard-video-tutorial/ PowerPivot Example: Building a Dashboard with PowerPivot http://www.pivotdashboard.com/Pages/PowerPivotExample.aspx How to Create a Standard Slicer for Excel Dashboards http://www.dummies.com/how-to/content/how-to-create-a-standard-slicer-for-excel-dashboar.html 10 WAYS EXCEL PIVOT TABLES CAN INCREASE YOUR PRODUCTIVITY http://www.bradedgar.com/10-ways-pivot-tables-in-excel-2010-2013-can-increase-productivity/ How Do Pivot Tables Work? https://www.excelcampus.com/pivot-tables/pivot-tables-work/
Views: 1900 bradedgardotcom
How to Use the Vlookup Formula in your Excel Dashboard
 
06:15
How to Use the Vlookup Formula in your Excel Dashboard For more Excel tips and tricks visit me at http://bradedgar.com. In this video I'll show you how you can use the Vlookup Formula in your Excel Dashboard. You'll learn how to use a data validation drop down list and then use the Vlookup formula on a calculations sheet to then find the associated value that was selected on the drop down. You'll learn how to use the lookup value, review the table array and then use the proper column index to find the associated value off of a table / data set. In this case I also then use Excel shapes with name ranges to display the values on the Excel dashboard. When you then change the value on the drop down list, the Vlookup formula will then change the values being displayed on the dashboard. Other Resources and Links Excel VLOOKUP Function https://exceljet.net/excel-functions/excel-vlookup-function How to use Excel's VLOOKUP function http://www.gcflearnfree.org/excel-tips/how-to-use-excels-vlookup-function/1/ The Last Guide to VLOOKUP You’ll Ever Need http://spreadsheeto.com/vlookup/ How to Use VLOOKUP in Excel http://www.howtogeek.com/howto/13780/using-vlookup-in-excel/ How to Use the VLOOKUP in Excel https://www.youtube.com/watch?v=-WAEzokHSJM How to do VLOOKUP in Excel https://www.timeatlas.com/vlookup-tutorial/ The Excel VLOOKUP Function http://www.excelfunctions.net/ExcelVlookup.html VLOOKUP Function Examples And Videos http://www.contextures.com/xlFunctions02.html Excel Vlookup Tutorial Part 3 - Vlookup Example With Exact Match http://www.excelfunctions.net/Vlookup-Example-Exact-Match.html EXCEL DASHBOARDS AND REPORTS: THE VLOOKUP FUNCTION http://www.dummies.com/software/microsoft-office/excel/excel-dashboards-and-reports-the-vlookup-function/ Create Interactive Dashboard using VLOOKUP https://www.youtube.com/watch?v=KFKlmst4r7g Vlookup With Dropdown List To Create MIS Dashboard https://www.youtube.com/watch?v=rv-HEdamwKA How to use INDEX-MATCH to replace VLOOKUP in Dashboards/Xcelsius http://scn.sap.com/docs/DOC-48706 Strategize Financial Modelling – Dynamic VLOOKUP example http://strategizefm.com/dynamic_vlookup/ Dashboard with Vlookup Formula http://forum.chandoo.org/threads/dashboard-with-vlookup.30442/ All Articles on Vlookup http://chandoo.org/wp/tag/vlookup-week/ Comprehensive Guide to Vlookup and other Lookup formulas http://chandoo.org/wp/2012/03/30/comprehensive-guide-excel-vlookup/ VLOOKUP Formula Cheat-sheet – FREE Download http://chandoo.org/wp/2010/11/19/download-vlookup-cheatsheet/ How to Create a Dashboard in Excel https://www.smartsheet.com/how-create-dashboard-excel Creating Interactive Dashboards in Excel https://www.lynda.com/Access-tutorials/Creating-Interactive-Dashboards-Excel-2013/374773-2.html Excel Dashboard Templates: Make Excel Work for You! https://blog.udemy.com/excel-dashboard-templates/ Defining Excel Dashboards and Reports http://www.dummies.com/how-to/content/defining-excel-dashboards-and-reports.html Excel Charts, Dashboards, & Visualization Tools http://www.exceluser.com/excel_dashboards/ How to Get Started with Excel Dashboard Reporting http://www.exceluser.com/excel_dashboards/startdash.htm
Views: 2041 bradedgardotcom
Step 3: Creating Excel Slicers to Control and Filter Multiple Pivot Tables at Once
 
17:21
Step 3: Creating Excel Slicers to Control and Filter Multiple Pivot Tables at Once Buy this Dashboard: https://gumroad.com/l/hr-salary-dashboard. Checkout my website at http://bradedgar.com. In this video, I'll show you how you can use Excel slicers to control and filter multiple pivot tables at once. You will learn how to setup a slicer from one pivot table and then apply and add report connections to all pivot tables on your calculations tab. This will allow you to filter all of the pivot tables that you've created using the same Excel slicer. Once you create the Excel slicers, you can change the slicer style and look and feel. I'll show you how you can build your own customer slicer style by duplicating one of the pre-existing slicer styles. Once you create it, you can move forward with setting up the pivot table slicer settings to ensure that no blank records will show in the slicer section should records be deleted from your tables and pivot tables. Finally the last section of the video will look at setting up the slicers on the final copy of the dashboard and including shapes as the background. A couple of useful techniques during the video: CTRL + PAGEUP/PAGEDOWN to switch between tabs. Useful Resources and Links: http://bradedgar.com/premium-excel-slicer-tutorial-filter-sort-topbottom-5/ http://pakaccountants.com/excel-sales-dashboard-slicers-how-to/ http://chandoo.org/wp/tag/slicers/ http://chandoo.org/wp/2014/08/13/network-relationship-chart/ http://chandoo.org/wp/2014/04/17/cp005-form-controls/ http://www.skilledup.com/articles/how-to-add-a-slicer-to-a-pivot-table/ http://www.addictivetips.com/microsoft-office/excel-2010-insert-slicer-in-pivot-tables-charts/ http://datapigtechnologies.com/blog/index.php/getting-fancy-with-your-excel-slicers/ -~-~~-~~~-~~-~- Checkout my newest video: "How to Use the Vlookup Formula in you Excel Dashboard" https://www.youtube.com/watch?v=MCBmPnaKf_g -~-~~-~~~-~~-~-
Views: 27167 bradedgardotcom
How to Display Numbers in Thousands in Excel
 
01:01
How to Display Numbers in Thousands in Excel For more tips and tricks- check out my website here: http://goo.gl/89RdjM In this video, I describe how you can use Excel custom number formatting to change positive numbers into thousands. I also review and show you that you can change numbers into millions for the selected cells. Custom number formats syntax works as follows: positive numbers; negative numbers; zero; text $ - The dollar sign in the number format syntax is a value that is displayed as is. This means that if you place a dollar sign at the start of your number format it will be displayed as so. # - This is a placeholder for numbers in you format. It will not display insignificant zeros. For example, if the number format is ##.##, 10.9 will be displayed as 10.9. 0 - Zeros are almost the same when it comes to the syntax as the # placeholder. The zero however will display insignificant zeros meaning 10.9 will be displayed 10.90 if you were to have the syntax #0.00 This makes for a great Microsoft Excel Tutorial that shows you how to perform number conversions in Excel. This is all part of this weeks Excel custom number formatting tricks and part of the Excel in 60 seconds custom number formatting week playlist. Learn about Custom Number Formatting and How it Works: http://bradedgar.com/1-thing-need-learn-using-excel-custom-number-formatting-syntax/ All articles on custom number formatting from Chandoo: http://chandoo.org/wp/tag/custom-cell-formatting/ The Definitive Guide to Custom Number Formatting: http://jonvonderheyden.net/excel/a-comprehensive-guide-to-number-formats-in-excel/ 5 Incredible Number Formatting Tricks That Will Impress Your Boss: http://bradedgar.com/5-incredible-number-formatting-tricks-will-impress-boss/ Microsofts take on Custom Number Formatting: https://support.office.microsoft.com/en-us/article/Create-a-custom-number-format-78f2a361-936b-4c03-8772-09fab54be7f4?CorrelationId=4f733bdb-092a-4a8b-ab2a-e7e8bb3ae634&ui=en-US&rs=en-US&ad=US -~-~~-~~~-~~-~- Checkout my newest video: "How to Use the Vlookup Formula in you Excel Dashboard" https://www.youtube.com/watch?v=MCBmPnaKf_g -~-~~-~~~-~~-~-
Views: 3975 bradedgardotcom
Excel Aged Receivables Dashboard: Summarizing Your Aged Receivable Data
 
05:18
Excel Aged Receivables Dashboard: Summarizing Your Aged Receivable Data To purchase/buy this product you can go to either of the following links: http://goo.gl/NxHBCR - link to my website - scroll to the bottom of the article to purchase. https://gum.co/epCl - Direct link to product page. This video shows you how you can use the Excel Aged Receivables Dashboard to select a customer and summarize your aged receivables data to make quick and easy business decisions for any of your customers. This Excel dashboard allows you to easily access your customer contact information (including customer contact information, email address, and phone number as well as customer notes) and also to review the number of past due invoices, total past due invoice value, oldest invoice number, oldest customer PO#. On the right hand side of the dashboard there is a past due invoice distribution showing the percentage of past due invoices within each aging category (1-30 days, 31-60 days, 61-90 days, and greater than 91 days). Using the aged receivables information you can easily determine how your customer is performing and it also will show you the aging group with the greatest past due value of invoices. The Excel Aged Receivables Dashboard also handles customers who do not have contact information setup yet as well as customers who do not have any invoices past due. In the header of the dashboard, you'll notice that there are hyperlinks to each of the tabs where you can add customer data, invoice data as well as a tab that shows you how to use the dashboard. I've included the calculations tab in the Excel file also which is where the calculations are performed to allow the dashboard to run. Each tab on the dashboard will have buttons in the header to easily and quickly navigate around the file. Other Cool Dashboard Resources Learning How to Use the VLOOKUP Statement with Data Validation: http://bradedgar.com/chandoo-orgs-vlookup-book-short-review/ 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/ Excel Sparklines: Merge Sparkline Cells For Quick and Easy Charts http://bradedgar.com/excel-sparklines-merge-sparkline-cells-quick-easy-charts/ Creating a Salesman KPI Dashboard - Excel KPI Dashboard: How to Monitor Your Sales to Reach Your Targets http://bradedgar.com/excel-kpi-dashboard-monitor-salesmen-meet-reach-sales-targets/ Excel Dashboards - Chandoo.org http://chandoo.org/wp/excel-dashboards/ How to make a good looking Excel Dashboard - Part 1: Introduction http://youtu.be/RIPcer_C4gM www.excelcharts.com/blog/excel-dashboards/ -~-~~-~~~-~~-~- Checkout my newest video: "How to Use the Vlookup Formula in you Excel Dashboard" https://www.youtube.com/watch?v=MCBmPnaKf_g -~-~~-~~~-~~-~-
Views: 8222 bradedgardotcom
How to Create Excel Custom Fill Sequences
 
02:42
How to Create Excel Custom Fill Sequences For more tips and tricks checkout You can checkout the blog post here: http://www.bradedgar.com/5-little-known-excel-tips-make-you-look-smarter-than-co-workers/. In this video I'll show you how you can create Excel custom fill sequences using the edit custom lists in under file - Excel Options - General - Edit Custom Lists. This allows you to create a either manually create a fill list or select and import a list that you would like to create a fill list form using the cell selection and import method. Once you've imported a list or manually create a list, you can then go ahead and type in one of the names that you've added to your custom list and then use the Excel fill feature to drag down and have the new custom list automatically generate for you in your cells. Other Cool Excel Fill Handle / Custom List Resources 5 Tips for Using the Excel Fill Handle Efficiently http://www.techrepublic.com/blog/five-apps/five-tips-for-working-efficiently-with-excels-fill-handle/ How to Use Autfill in Excel https://www.ablebits.com/office-addins-blog/2014/05/30/howto-use-autofill-excel/ Excel Fill Handle Tips http://myexcelonline.com/blog/excels-fill-handle-tips/ Excel Fill Down Command http://spreadsheets.about.com/od/tipsandfaqs/qt/81130filldown.htm Excel Fill Down From Cell Above https://exceljet.net/keyboard-shortcuts/fill-down-from-cell-above Quickly Copy Excel Formula Down http://blog.contextures.com/archives/2011/08/31/quickly-copy-excel-formula-down/ http://www.keynotesupport.com/excel-basics/excel-auto-fill-handle-numbers-dates.shtml Excel Fill Handle Explained http://www.ozgrid.com/Excel/excel-fill-handle.htm -~-~~-~~~-~~-~- Checkout my newest video: "How to Use the Vlookup Formula in you Excel Dashboard" https://www.youtube.com/watch?v=MCBmPnaKf_g -~-~~-~~~-~~-~-
Views: 3142 bradedgardotcom
How to Create Sample/Dummy Data Sets in Excel
 
11:38
How to Create Sample/Dummy Data Sets in Excel For more Excel tips and tricks visit me at http://bradedgar.com. Also, if you want to learn more about data models checkout the website mentioned in the video - http://www.databaseanswers.org/data_models/. In this video I'll show you how you can create sample/dummy data sets in Excel. The first thing you want to do is find a data model that suits what you'd like to use the data set for. I've used a product table in this case. You can then use the Excel fill handle to create a column of continuous product Ids. You can then do the same with PN and description as I've shown in the video. When creating a product category, I then used the index formula with the randbetween function to generate random results based off of 3 different product categories that were staged on 3 different cells in the spreadsheet. Next I was able to make use of the rand formula to then multiply it by 100 to come up with random unit pricing / costs for each product. Finally, I used the index formula with the randbetween formula again to manage and create the lot sizes or reorder quantities. Other Links and Resources Excel Sample Data http://www.bradedgar.com/excel-sample-data/ Excel Sample Data http://www.contextures.com/xlSampleData01.html How to Create a Random Sample in Excel http://www.wikihow.com/Create-a-Random-Sample-in-Excel How to Create a Random Sample in Excel (in 3 minutes!) https://youtu.be/q8fU001P2lI Dummy Data – How to use the Random Functions http://chandoo.org/wp/2011/05/04/dummy-data-random-functions/ Real Statistics Using Excel http://www.real-statistics.com/ http://www.real-statistics.com/sampling-distributions/sampling/ Generate Sample Data http://dailydoseofexcel.com/archives/2010/06/17/generate-sample-data/ Microsoft Excel 2007 Getting a Random Sample http://www.uwec.edu/help/excel07/randomdata.htm Excel Sampling: How to find a Sample in Excel http://www.statisticshowto.com/use-excel-sampling-find-sample-size/ How to generate a sample set with normal distribution in Excel http://www.databison.com/how-to-generate-normal-distribution-sample-set-in-excel/ How to Create a Normally Distributed Set of Random Numbers in Excel http://www.mbaexcel.com/excel/how-to-create-a-normally-distributed-set-of-random-numbers-in-excel/
Views: 4487 bradedgardotcom
29 Excel Skills And Tips You Need to Learn - A Tutorial For Beginners
 
29:40
Excel Beginner Tutorial: 29 Excel Skills and Tips For Excel Beginners For more tips and tricks visit me at http://bradedgar.com. Download The Workbook Here: http://goo.gl/nO28CE or check my site out here: http://goo.gl/89RdjM. These are 29 Excel tips and skills I wish I would have known when I first started using Excel. These tips should save you a ton of time especially if you use Excel everyday in your work. I've listed the tricks and tips below. 1 ESC to launch spreadsheet (Excel 2013) (ESC) 2 Enter text on a different line in one cell (ALT + ENTER) 3 Fixing the width of rows/columns and making them all the same size (if you want) or wrapping text (DOUBLE CLICK BETWEEN ROWS/COL) 4 Entering Data into multiple sheets in a workbook at Once (CTRL + SELECT TABS) 5 Shortcut to Navigate between worksheets (CTRL + PAGEUP/PAGEDOWN) 6 Navigate left/right within cells without using your mouse during data entry (SHIFT TAB/TAB) 7 Navigate up/down within cells without using your mouse during data entry (SHIFT ENTER/ENTER) 8 Copying Data above or to the right at the stroke of a couple of keys (CTRL + D / CTRL + R) 9 Using Excel Auto Fill To Finish your data entry Job - Bottom Right Drag-Drop (DRAG BOTTOM CORNER OR DOUBLE CLICK TO FILL DOWN TO WHEREVER RANGE COLUMN ENDS) 10 Copy Paste Excel Functions (using the different paste options available to us) (ALT, H, V (THEN OPTION)) 11 Dragging content from one cell to another (DRAG) 12 Inserting a row or column and deleting a row or column (CTRL + (+) / CTRL + (--)) 13 Creating a table in Excel (CTRL + T) 14 Quick way to apply common formulas to a range of cells and using name ranges (ALT, H, U, S(SUM)) 15 Show all of the formulas in a spreadsheet (CTRL + ` (KEY BESIDE 1)) 16 How to get to the end/start/right/left of your data range in an instant (CTRL + RIGHT/DOWN/ (SHIFT TO SELECT)) 17 Select all of your data in a spreadsheet - Fast (CTRL + A) 18 Create a new name range in your spreadsheet (CTRL + SHIFT + F3) 19 Link something to another page or document - Hyperlink (CTRL + K) 20 Freeze Panes - How to freeze the area to display your header and data that you need (ALT, W, F) 21 Group and Ungroup data ("ALT + SHIFT + LEFT (UNGROUP) ) (ALT + SHIFT + RIGHT (GROUP)") 22 Text to Columns (ALT, A , E) 23 Remove duplicate records in a data set (ALT, A, M) 24 Format Painter (ALT, H, F, P) 25 Hide tabs of your workbook or cells 26 Removing the gridlines from the spreadsheet (CTRL + ALT + G) 27 Quickest way to open a new workbook (CTRL + N) 28 Navigate between multiple workbooks (CTRL + TAB) 29 Saving the workbook & Print the workbook (CTRL + S (CTRL + F12) - CTRL + P) Excel 2010 Tutorial For Beginners #1 - Overview (Microsoft Excel) https://youtu.be/eI_7oc-E3h0 Microsoft Excel Full Tutorial - Ms Excel Full Tutorial in English https://youtu.be/9ZIb_BcSlm0 MS Excel 2013 Tutorial FOR BEGINNERS PART 1 - How to use Excel - formulas, charts, tables https://youtu.be/QLDGZQARL6Q Microsoft Excel Tutorial for Beginners #1 - Overview https://youtu.be/8L1OVkw2ZQ8 Tutorial - Excel 2010 - 10 Things you must know https://youtu.be/-SnBlC_1tSk Basic Excel Skills - Chandoo http://chandoo.org/wp/excel-basics/ Microsoft Excel – Online Tutorial for Beginners http://chandoo.org/wp/excel-tutorial/ How to Use Excel - Excel Tutorials for Beginners http://spreadsheets.about.com/od/excel101/a/Excel_beg_guide.htm Excel Basics http://www.excel-easy.com/basics.html Learn Excel Without Spending a Penny: 40 Free Excel Tutorials & Reference Sites http://www.skilledup.com/articles/free-excel-tutorials/ Excel Made Easy - A Beginner's Guide http://www.keynotesupport.com/excel-basics/excel-for-beginners-guide.shtml -~-~~-~~~-~~-~- Checkout my newest video: "How to Use the Vlookup Formula in you Excel Dashboard" https://www.youtube.com/watch?v=MCBmPnaKf_g -~-~~-~~~-~~-~-
Views: 30108 bradedgardotcom
How to Apply Excel Conditional Formatting with Wingding Characters to Help Visualize Your Data
 
05:21
How to Apply Excel Conditional Formatting with Wingding Characters to Help Visualize Your Data For more Excel tips and tricks visit me at http://bradedgar.com. In this video I'll show you how you can apply Excel conditional formatting with wingding characters to help you visualize your data sets. You're going to need to use the IF Formula as well as insert symbol in the quick access ribbon. The insert symbol is going to allow you to select a set of images or wingding characters that you'd like to use to help you visualize your data and then later apply excel conditional formatting to. You'll use the IF formula to determine what value / wingding character will be displayed based on the value of another cell. For example in this video I used number of years and tenure to show employees who had at least 5 years with the company. Once your IF Formula is applied, you can then go to conditional formatting and use a formula to determine which cells to format. Once your conditional formatting has been applied you can now see that you're able to visualize which employees have been there for 5 years or more versus who hasn't at a glance. Other Resources and Links Conditional Formatting Icons in Excel 2003 http://blog.contextures.com/archives/2009/04/14/conditional-formatting-icons-in-excel-2003/ Wingdings & Webdings Font Icon Character Map (Printable Cheat Sheet) http://www.thespreadsheetguru.com/blog/wingdings-webdings-font-icon-cheat-sheet-printable How to Use Wingdings As Icon Sets In Your Excel Dashboard https://youtu.be/mOY9Wxg2fGI Conditional Format Wingdings http://www.mrexcel.com/forum/excel-questions/452188-conditional-format-wingdings.html Conditional Formatting and Smileys! http://www.excelforum.com/excel-general/777633-conditional-formatting-and-smileys.html How to conditional formatting smiley face chart or font in Excel cells? https://www.extendoffice.com/documents/excel/2417-excel-smiley-face.html Conditional Formatting of Picture in Excel http://www.tek-tips.com/viewthread.cfm?qid=1608981 Excel Tip - Adding a conditional Thumbs Up or Thumbs Down http://www.notjustnumbers.co.uk/2014/05/excel-tip-adding-conditional-thumbs-up.html EXCEL BI TIP #20: WINGDINGS–AN EXCEL SERVICES SUPPORTED INDICATOR ALTERNATIVE https://dataonwheels.wordpress.com/2015/02/10/excel-bi-tip-20-wingdingsan-excel-services-supported-indicator-alternative/ Excel Display Up/Down Arrows http://www.excelarticles.com/LE10ePub-184.html These icons are so pretty, can I get them in green? [conditional formatting trick] http://chandoo.org/wp/2016/03/22/green-down-arrow-cf-trick/ Formula to turn YES into a checkmark and NO into an "X" https://www.reddit.com/r/excel/comments/2ywy9h/formula_to_turn_yes_into_a_checkmark_and_no_into/ Mimic 2007 Conditional Formatting Icons http://www.andypope.info/tips/tip011.htm The Ultimate Guide to Bullet Points in excel http://www.launchexcel.com/bullet-in-excel/ Need Somethingmore than conditional formatting http://www.pcreview.co.uk/threads/need-something-more-than-conditional-formatting.2945674/ In Cell Charting with Worksheet Formulas http://peltiertech.com/Excel/Charts/InCellFormulaCharts.html
Views: 2681 bradedgardotcom
Excel Data Entry Tricks: Enter Data into Multiple Worksheets Simultaneously
 
02:51
Excel Data Entry Tricks: Enter Data into Multiple Worksheets Simultaneously For more excel tips and tricks visit me at http://bradedgar.com. In this video and Excel Data Entry Trick I'm going to show you how you can enter data into multiple worksheets simultaneously. In order to do this you need to prepare your worksheets. Step 1: Add as many worksheets as you need/want to add / enter data to simultaneously. Step 2: Use CTRL + right click to select all of the worksheets that youd like to add data to. Step 3: Once selected, you can go ahead and start entering formulas, data and even some formatting to the spreadsheet. This will all copy over to multiple worksheets at the same time. In this video, I'll show you how to create some random data sets on multiple sheets at once using the randbetween formula. Again this is super easy select multiple worksheets at once using CTRL + right click and then input your data and formulas onto the worksheet that is selected and voila. Other Resources and Links on Multiple Worksheet Entries How to enter data in multiple worksheets at the same time in Excel - 1 https://youtu.be/1U2wyF5JgDY Enter data into multiple sheets at the same time https://youtu.be/tTYNrxeE1fk How to transfer data from a user form to multiple worksheets in a workbook in Excel using VBA https://youtu.be/-PH1xDvSijA Transfer Data from Multiple Workbooks into Master Workbook Automatically https://youtu.be/wgCqFcylPQY Enter data in multiple worksheets at the same time https://support.office.com/en-us/article/Enter-data-in-multiple-worksheets-at-the-same-time-6223d385-4c75-4dff-9881-67d17972a6c9 How to enter same data in multiple worksheets at once in Excel? https://www.extendoffice.com/documents/excel/2636-excel-enter-data-in-multiple-worksheets-at-the-same-time.html Hack #2. Enter Data into Multiple Worksheets Simultaneously https://www.safaribooksonline.com/library/view/excel-hacks/059600625X/ch01s03.html How to Enter Data Into Multiple Worksheets at the Same Time in Excel 2010 http://www.solveyourtech.com/how-to-enter-data-into-multiple-worksheets-at-the-same-time-in-excel-2010/ Excel Tip: Enter Data into Multiple Worksheets at the Same Time in Excel http://www.microassist.com/software-tips/excel-tip-enter-data-into-multiple-worksheets-at-the-same-time-in-excel/ Enter Excel data in multiple worksheets simultaneously http://www.techrepublic.com/article/enter-excel-data-in-multiple-worksheets-simultaneously/ Editing the Same Cell in Multiple Sheets http://excelribbon.tips.net/T010653_Editing_the_Same_Cell_in_Multiple_Sheets.html Excel Data Entry Tips http://www.contextures.com/xlDataEntry01.html Working with Data in Excel Part 1: 10 Excel Data Entry Tips Everyone Should Know http://www.launchexcel.com/excel-data-entry-power-tips/
Views: 11558 bradedgardotcom
Use Excel Custom Number Formatting for Data Validation
 
01:24
Use Excel Custom Number Formatting for Data Validation For more Excel tips and tricks visit me at http://bradedgar.com. In this video, we take a look at how we can use Excel custom number formatting as a data validation tool for our data set and area/range of cells. Step #1: Select your entire range in which you want to apply Excel data validation to. Step #2: Type CTRL +1 once you've highlighted your data set and then this will bring up the format cells screen. Head down to the custom category. Step # 3: The standard syntax applies to your number format here. [positive numbers; negative numbers; zero; text]. The syntax before the first semi-colon will be the syntax that gets applied to +ve numbers, the syntax prior to the 2nd semi-colon will be applied to negative numbers, the third ";" will be zero and then the syntax after the last semi-colon will be applied if a text value is placed into the cell. STEP # 4: This is where you can get creative. Because you can use custom number formatting to tell Excel what to display if text is placed in any of the cells that the custom number formatting is applied to, you can put in quotations any statement that you would like. This means that if text is applied to any of the cells, no matter what is placed in the cell will be displayed as Please enter a number. 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 -~-~~-~~~-~~-~-
Views: 4806 bradedgardotcom
Step 4: Building the Excel Dashboard Display with Shapes with Name Range Associations
 
16:18
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 -~-~~-~~~-~~-~-
Views: 82197 bradedgardotcom
Excel Tables: How to Use Structured References with the SUMIF Formula in an Excel Dashboard
 
07:09
Excel Tables: How to Use Structured References with the SUMIF Formula in an Excel Dashboard For more Excel tips and tricks visit me at http://www.bradedgar.com. In this video on Excel Tables I'll show you how to use structured references for your formulas. In particular the SUMIF formula. This can be used in your Excel dashboards in conjunction with your data validation drop down lists. The video discusses some of the best and most useful reasons for using structured references / Excel tables. They are great for ensuring that your data sets remain dynamic and it's also easy to understand what referencing you need to use in your formulas. The structured reference allows you to use the table name and then literally just add a square bracket to include your field name or column name on the table in Excel. I'll show you then how you can reference this structured reference formula into a shape on your dashboard and then have it display beautifully. Other Links and Resources Using structured references with Excel tables https://support.office.com/en-us/article/Using-structured-references-with-Excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e Absolute Structured References in Excel Table Formulas https://www.excelcampus.com/tips/absolute-formula-references-excel-structured-table/ Structured References http://www.excel-easy.com/examples/structured-references.html Structured Referencing to Identify Parts of Excel Tables http://peltiertech.com/structured-referencing-excel-tables/ Why you should use structured references in tables http://www.excelandaccess.com/why-you-should-use-structured-references-in-tables/ Introduction to Structural References http://chandoo.org/wp/2013/06/26/introduction-to-structural-references/ Formulas and Structured Data in Excel Tables http://excelsemipro.com/2011/04/formulas-and-structured-data-in-excel-tables/ Excel Tips – Tables and Structured References https://excel.tv/excel-tips-tables-and-structured-references/ Excel Table Absolute Structured References http://www.myonlinetraininghub.com/excel-table-absolute-structured-references How to Create a Dashboard in Excel https://www.smartsheet.com/how-create-dashboard-excel Excel Dashboards – Tutorials, Templates, & Examples http://chandoo.org/wp/excel-dashboards/ 10 Secrets for Creating Awesome Excel Tables http://www.pcworld.com/article/259633/10-secrets-for-creating-awesome-excel-tables.html Creating an Excel Table http://www.contextures.com/xlExcelTable01.html Excel Tables — Overlooked, Yet Awesome http://analyticsdemystified.com/excel-tips/excel-tables-overlooked-yet-awesome/
Views: 3286 bradedgardotcom
Excel Data Entry Tricks: Create Custom Lists in Excel For Repetitive Data Entry
 
02:32
Excel Data Entry Tricks: Create Custom Lists in Excel For Repetitive Data Entry For more Excel tips and tricks visit me at http://www.bradedgar.com. Custom Lists in Excel In this video, I'll show you how to setup custom lists in Excel to help you use the auto fill handle to improve and quicken your data entry in Excel. This is a super useful tool that was built into Excel and helps you use the fill handle to quickly fill data sets that you use repetitivly. In my example I show you how you can create a list that you use all of the time and then head over to the file tab in Excel. From there you can then go to options and then advanced. From here you'll scroll down until you hit Edit Custom Lists. From here you can either import or you can create a custom list right the edit custom list screen. once you've created your custom list you can then wrie anyword in a cell that is contained in your custom list and the use the fill handle to generate the custom fill sequence. Other Useful Resources and Links Custom Lists in Excel http://www.excel-easy.com/examples/custom-lists.html https://support.office.com/en-us/article/Create-or-delete-a-custom-list-for-sorting-and-filling-data-d1cf624f-2d2b-44fa-814b-ba213ec2fd61 Sort data using a custom list https://support.office.com/en-us/article/Sort-data-using-a-custom-list-def8ff2b-681a-4fc3-9bd2-a06455c379e1 How to create and fill new custom lists quickly in Excel? https://www.extendoffice.com/documents/excel/865-excel-create-custom-list.html How to Create and Use Your Own Lists in Excel https://www.ablebits.com/office-addins-blog/2011/11/02/create-excel-custom-lists/ How to Create Custom Auto Fill Lists in Excel http://www.dummies.com/software/microsoft-office/excel/how-to-create-custom-autofill-lists-in-excel-2013/ 10 steps to creating a custom list for sorting in Excel http://www.techrepublic.com/blog/10-things/10-steps-to-creating-a-custom-list-for-sorting-in-excel/ HOW TO USE CUSTOM LISTS WITH EXCEL 2010’S AUTOFILL FEATURE http://www.dummies.com/software/microsoft-office/excel/how-to-use-custom-lists-with-excel-2010s-autofill-feature/ http://www.addintools.com/documents/excel/where-is-custom-list.html Where is Custom List in Microsoft Excel 2010 and 2013? Importing Custom Lists in Excel http://excelribbon.tips.net/T006243_Importing_Custom_Lists.html EXCEL: USING CUSTOM LISTS WITH THE FILL HANDLE http://sfmagazine.com/post-entry/july-2016-excel-using-custom-lists-with-the-fill-handle/ How to Create a custom list in Excel http://myexcelonline.com/blog/how-to-create-a-custom-list-in-excel/
Views: 1331 bradedgardotcom
Excel Data Entry Tricks: 6 Excel Data Entry Shortcuts I use Everyday
 
02:45
Excel Data Entry Tricks: 6 Excel Data Entry Shortcuts I use Everyday For more Excel tips and tricks visit me at http://bradedgar.com. In this short video I'll show you how you can use these 6 Excel data entry shortcuts to speed up your data entry process so you don't have to use your mouse. 1 - Enter Key shortcut - automatically moves you one cell down. 2 - Shift Enter shortcut - moves you one cell up. 3 - Tab shortcut - moves you one cell to the right. 4 - Shift tab shortcut - moves you one cell to the left. 5 - CTRL + D - copies the cell contents / formula directly above your current cell. 6 - CTRL + R - copies the cell contents / formula directly to the right of your current cell. These tricks will help you navigate your worksheet during data entry a little more quickly and efficiently than if you were using your mouse or even a standard copy and paste option. Additional Resources and Links Make Data Entry Easier in Microsoft Excel: 10 Tricks http://www.pcworld.com/article/252947/make_data_entry_easier_in_microsoft_excel_10_tricks.html Working with Data in Excel Part 1: 10 Excel Data Entry Tips Everyone Should Know http://www.launchexcel.com/excel-data-entry-power-tips/ 7 Tricks To Make You A Spreadsheet Expert http://www.lifehack.org/articles/technology/7-tricks-make-you-spreadsheet-expert.html The 5 Data Entry Hacks You Need to be Doing https://www.freelancer.ca/community/articles/the-5-data-entry-hacks-you-need-to-be-doing EXCEL DATA ENTRY SHORTCUT KEYS http://www.dummies.com/software/microsoft-office/excel/excel-data-entry-shortcut-keys/ Data Entry Shortcuts in Excel 2013 https://youtu.be/Y26ZAWE48wE 24 Excel Keyboard Shortcuts to Help You Work Faster, Better https://blog.udemy.com/excel-keyboard-shortcuts/ 10+ keyboard shortcuts for speeding your work with Excel data http://www.techrepublic.com/blog/microsoft-office/10-plus-keyboard-shortcuts-for-speeding-your-work-with-excel-data-142287/ Excel Data Form Makes Input Easier https://www.timeatlas.com/excel-data-form/ Microsoft Excel: 5 Data Entry Tips http://thesoftwarepro.com/microsoft-excel-data-entry-tips/ Data Entry Shortcuts and Tips http://help.ebird.org/customer/portal/articles/1006786-data-entry-shortcuts-and-tips Excel Data Entry Tips and Tricks http://www.projectwoman.com/articles/34ExcelDataEntryTricks.htm 13 Keyboard Shortcuts for Data Entry in Excel http://www.excelsupersite.com/13-keyboard-shortcuts-for-data-entry/
Views: 3623 bradedgardotcom
How to Use Transparent Charts and Shape Layering in Excel
 
06:42
How to Use Transparent Charts and Shape Layering in Excel For more Excel Tips and Tricks please visit me at You can checkout the blog post here: http://www.bradedgar.com/5-little-known-excel-tips-make-you-look-smarter-than-co-workers/. In this video, I review how to use transparent charts and shape layering in Excel to create a cool finished product Excel dashboard type display. To make your pie chart, you use and add a specific percentage in one cell and then in another, you subtract that value (i.e. .70) by 1. You can then insert a pie chart onto your work sheet by highlighting those two sets of data. Note I do hide the 1-value total by using custom number formatting. You can do this by putting 3 consecutive semi-colons in your custom number format box from format cell options. Once your chart is created, you will want to make your background fill (no fill) and then by remove the outline / line on the chart. You can then remove the title and the legend series by simply clicking delete. The next step is adding the shape layering. You can then add a shape layer by going to the insert tab. You'll want to push the image back and then place the chart over the shape. You can then select both shapes by using shift left click and then grouping the two together. Finally, you can add another layer / shape by adding the percentage value shown by referencing the shape to the name range you've created (in this case the average). Once that is all done, you can also group those shapes. More Excel shape layering and transparent chart resources 3 Intelligent Ways to Use Shapes in Excel http://www.bradedgar.com/3-intelligent-ways-use-shapes-excel/ Enhancing Dashboard Interfaces with Shape Layering Tricks http://datapigtechnologies.com/blog/index.php/enhancing-dashboard-interfaces-with-shape-layering-tricks/ Group or ungroup shapes in Excel https://support.office.com/en-us/article/Group-or-ungroup-shapes-pictures-or-other-objects-in-Excel-2007-2175ca3d-ae49-4059-ad0f-30ff76f5b4b0 https://youtu.be/sVLjgiajsi4 Step 4: Building the Excel Dashboard Display with Shapes with Name Range Associations http://www.bradedgar.com/3-intelligent-ways-use-shapes-excel/data-pig-technologies-excel-shape-layering/ how do I create a transparent chart in Excel? http://www.pcreview.co.uk/threads/how-do-i-create-a-transparent-chart-in-excel.3975465/ Compare two sets of Excel chart data using transparent columns http://www.techrepublic.com/blog/microsoft-office/compare-two-sets-of-excel-chart-data-using-transparent-columns/ Setting a Transparent Color for an Image http://excelribbon.tips.net/T008729_Setting_a_Transparent_Color_for_an_Image.html How to Make Your Excel Curve Solid or Transparent http://www.wikihow.com/Make-Your-Excel-Curve-Solid-or-Transparent -~-~~-~~~-~~-~- Checkout my newest video: "How to Use the Vlookup Formula in you Excel Dashboard" https://www.youtube.com/watch?v=MCBmPnaKf_g -~-~~-~~~-~~-~-
Views: 5207 bradedgardotcom
Using the Excel SUMIFS Formula with Data Validation Drop Down Lists
 
12:14
Using the Excel SUMIFS Formula with Data Validation Drop Down Lists For more tips and tricks visit me at http://bradedgar.com. In this video I'll review how you can use the SUMIFS formula and function with the help of data validation drop down lists to generate and find information from a data set. This comes in handy will building dashboards in Excel. I looking at how to use the SUMIFS formula with multiple conditions or using the sumif based on two criteria. In this video I show you how you can create and use the data validation drop down lists so that when the user selects multiple conditions or uses multiple criteria we can perform the sum formula to get the results based on the selection. I use the month/year selection for the date selection and then I use the item number as a secondary lookup item. For more on using the sum formula with multiple criteria see additional links in the resources below. Other Links and Resources Sum values based on multiple conditions https://support.office.com/en-us/article/Sum-values-based-on-multiple-conditions-e610ae0f-4d27-480c-9119-eb644f1e847e How to use Excel SUMIFS and SUMIF with multiple criteria - formula examples https://www.ablebits.com/office-addins-blog/2014/11/12/excel-sumifs-sumif-multiple-criteria/ Use SUMIFS() to sum by multiple conditions in Excel http://www.techrepublic.com/blog/windows-and-office/use-sumifs-to-sum-by-multiple-conditions-in-excel-246576/ SUMIFS with multiple criteria and OR logic https://exceljet.net/formula/sumifs-with-multiple-criteria-and-or-logic Use SUMIFS to sum cells that match multiple criteria in Excel https://fiveminutelessons.com/learn-microsoft-excel/use-sumifs-sum-cells-match-multiple-criteria-excel Excel's SUMIF with multiple criteria: SUMIFS https://www.deskbright.com/excel/excel-sumif-multiple-criteria/ Using SUMIFS with multiple AND OR conditions http://stackoverflow.com/questions/20238816/using-sumifs-with-multiple-and-or-conditions Excel Sum If With Multiple Criteria http://www.ozgrid.com/Excel/sum-if.htm SUMIF Using Multiple Criteria in Microsoft Excel http://www.officearticles.com/excel/sumif_using_multiple_criteria_in_microsoft_excel.htm Summing And Counting Using Multiple Criteria http://spreadsheetpage.com/index.php/tip/summing_and_counting_using_multiple_criteria/ Dealing with Multiple Criteria https://www.sumproduct.com/thought/multiple-criteria Use Conditional Sum Wizard To Enter CSE Formulas http://www.mrexcel.com/tip083.shtml EXCEL: SUMIFS – DEMYSTIFIED https://mauriceausum.com/tag/sumifs-multiple-criteria-in-same-column/ How to sumif with multiple criteria in one column? https://www.extendoffice.com/documents/excel/2469-excel-sumif-multiple-criteria-same-column.html
Views: 5804 bradedgardotcom
How to Use Pictures in Your Excel Bar Charts
 
01:26
How to Use Pictures in Your Excel Bar Charts For more Excel Tips and Tricks you can check out my website at You can checkout the blog post here: http://www.bradedgar.com/5-little-known-excel-tips-make-you-look-smarter-than-co-workers/. In this video, I'll review with you how you can change a solid fill bar chart to use a picture/image/icon and display that icon as your fill on your chart. You can do this by simply selecting your data series on your bar chart, right clicking, and then selecting format data series. From there, you can then go to the fill option under the format data series and then select from solid fill to picture or texture fill. You will then select, the file option and find the image / picture you would like to use in your bar chart. Once you've find it, select it. In the case of this video, I use a credit card icon displaying for sales. After I've selected the image, it will try and stretch the image. This is not what we want to do. We want to select stack from the options available. This will then display our picture very small as the fill of the bar chart in a stacked format. Other Resources and Links Learning Excel - Replace charts with pictures https://youtu.be/IipyT8NI8DU Excel Video 125 Filling Column Charts With Pictures https://youtu.be/wqbb-Tyj050 Use a Picture in a Chart in Excel https://support.office.com/en-us/article/Use-a-picture-in-a-chart-c53cf530-160b-4a3e-9b38-efe6cb858d10 Creating Picture Charts http://www.officetooltips.com/excel/tips/creating_picture_charts.html Creating Pictographs in Excel http://pjnicholson.com/excel_pictographs/pictographs.html How to Create a Column Chart with A background image in Excel http://chandoo.org/wp/2013/05/06/column-chart-with-background-image/ Add Pictures to Chart Axis in Excel http://www.get-digital-help.com/2014/09/23/add-pictures-to-a-chart-axis/ How to Make A Bar Chart / Graph in Excel http://www.wikihow.com/Make-a-Bar-Graph-in-Excel How to Add Totals to Stacked Charts for Readability http://www.exceltactics.com/how-to-add-totals-to-stacked-charts-for-readability/ 6 Excel Charts, One Excel Chart Guide: How to Choose the Best Chart For You http://www.bradedgar.com/6-excel-charts-1-excel-chart-guide-choose-best-chart/ 4 Easy Steps to Using the Excel Option button for Dynamic Dashboards http://www.bradedgar.com/effortlessly-use-excel-option-button-keep-finance-operation-executives-happy-time/ How to Build an Excel Bar Chart/ Excel Graph http://blog.hubspot.com/marketing/how-to-build-excel-graph -~-~~-~~~-~~-~- Checkout my newest video: "How to Use the Vlookup Formula in you Excel Dashboard" https://www.youtube.com/watch?v=MCBmPnaKf_g -~-~~-~~~-~~-~-
Views: 3439 bradedgardotcom
How to Create a Custom Report with Excel GETPIVOTDATA
 
05:25
How to Create a Custom Report with GETPIVOTDATA This is a guest video post from John Michaloudis at http://www.myexcelonline.com. Checkout the guest blog post - 3 Reasons Why You Need to Start Using Excel GETPIVOTDATA: http://www.bradedgar.com/3-reasons-why-you-need-to-start-using-excel-getpivotdata/ In this video, John reviews how to actually build a custom sales report in Excel using the GETPIVOTDATA formula by year, quarter and region. John uses an Excel PivotTable in conjunction with the GETPIVOTDATA formula to build a custom report. He'll go over how to apply the GETPIVOTDATA formula and how to reference a structured table area that you've created. In particular, he references his sales data and then looks at each of the regions, sales year, and sales quarter. The GETPIVOTDATA formula refers to each of the column headings as well as the region rows to automatically build the Excel custom report. John then uses the form control combo box in the custom report to build and see the different between quarters, as well as to see the different between totals. The index formula refers to the drop down value cell link which creates a number based on the selection made by the user. John then associates each quarter to a number to follow the structure of his chart area. Other Resources http://www.contextures.com/excelgetpivotdatareports.html https://youtu.be/PEorffGvVYs https://youtu.be/DL5KndtG3kE - Learn Excel Pivot Tables http://www.accountingweb.co.uk/article/tutorial-create-flexible-pl-excel-20102013/540300 http://www.nhdayton.com/blog/artmid/58211/articleid/8655/using-excel%E2%80%99s-getpivotdata-formula-to-build-a-report-outside-of-a-pivot-table-video -~-~~-~~~-~~-~- Checkout my newest video: "How to Use the Vlookup Formula in you Excel Dashboard" https://www.youtube.com/watch?v=MCBmPnaKf_g -~-~~-~~~-~~-~-
Views: 1857 bradedgardotcom
How to Build Data validation into your User Forms in Excel Using Conditional Formatting
 
09:11
How to Build Data validation into your User Forms in Excel Using Conditional Formatting For more Excel tips and tricks visit me at http://bradedgar.com. In this video I'll show you how to build data validation into your user forms in Excel using conditional formatting and the IF Formula. This is a great way to show users that they need to update and add data into specific cells in the form. You will also use the len formula (length formula) with conditional formatting to determine whether or not a cell contains anything. If the cell doesn't contain anything, the cell will have a red border. If the LEN formula is greater than a character length of 0 then the border of the cell will be green. The len formula gets used with conditional formatting. You will then use the IF Formula with the LEN formula to show an X or a check mark beside the form field. If LEN is greater than 0 then you'd show a check mark and if it's = 0 (no characters) then you can use conditional formatting to change the color of the wingding character that was inserted into the cell beside the cell. Other Resources and Links Make Awesome Data Entry Forms by using Conditional Formatting + Data Validation How to Build Data validation into your User Forms in Excel Using Conditional Formatting and the IF F Use Excel's conditional formatting features to highlight input cells http://www.techrepublic.com/blog/microsoft-office/use-excels-conditional-formatting-features-to-highlight-input-cells/ Conditional Formatting and Dropdown Menus http://www.elearnexcel.com/2013/10/01/conditional-formatting-and-drop-down-menus/ Excel formatting and features that are not transferred to other file format https://support.office.com/en-us/article/Excel-formatting-and-features-that-are-not-transferred-to-other-file-formats-8fdd91a3-792e-4aef-a5bb-46f603d0e585 Create Dependent Drop-down Lists with Conditional Data Validation http://www.excel-university.com/create-depdendent-drop-downs-conditional-data-validation/ Conditional Format that Checks for Data Type http://excelribbon.tips.net/T007073_Conditional_Format_that_Checks_for_Data_Type.html Conditional Formatting http://excel.tips.net/C0186_Conditional_Formatting.html Turn Conditional Formatting and Data Validation On and Off with a Checkbox http://www.brainbell.com/tutorials/ms-office/excel/Turn_Conditional_Formatting_And_Data_Validation_On_And_Off_With_A_Checkbox.htm 11 Incredible Excel Conditional Formatting Tricks http://www.exceltip.com/tips/11-incredible-excel-conditional-formatting-tricks.html 10 cool ways to use Excel's conditional formatting feature http://www.techrepublic.com/blog/10-things/10-cool-ways-to-use-excels-conditional-formatting-feature/ Conditional formatting tricks: Sum values in Excel by cell color http://www.techrepublic.com/blog/windows-and-office/conditional-formatting-tricks-sum-values-in-excel-by-cell-color/
Views: 4194 bradedgardotcom
Excel Human Resources Salary Dashboard Overview
 
05:30
Excel Human Resources Salary Dashboard Overview Buy the workbook to follow along for $3 USD - https://gum.co/hr-salary-data To checkout other dashboards and products visit http://bradedgar.com. This Excel Dashboard is built specifically to review human resources (HR) salary data. This particular video will be an overview of the excel human resources salary dashboard. I walk you through the current setup, how you can buy the dashboard and the workbook to follow along and build the dashboard as we go along. We first review the dashboard menu and then go to setup dashboard data. From the calculations tab you'll see that the entire dashboard will work off of multiple pivot tables. You can then review and see the top three highest compensation and bottom 3 compensation. We've added an update pivot table cache if our dashboard data automatic update macro in case it fails. We have a section that also shows the total number of employees, the cumulative salaries based on the excel slicer criteria selected, and then finally we show you exactly what the average salary is for the slicer selection as well. The slicer selection will allow you filter based on country and/or on department name. If we go to setup employee data, we can manually add a record and our pivot tables will update automatically based on the macro that we've created. You can actually add and dump a data set as long as your column headings and field names match exactly the table setup for employees. Again our dashboard and pivot tables will update whenever you add new records to your table. It's a great interactive human resources salary dashboard. Resources and additional links: Dynamic Excel Data Validation Lists: How to Remove Duplicate Records: https://youtu.be/rXH7hQ15RtA How to Automate Number Formatting Using Option Buttons in Excel https://youtu.be/m7c4A7diGVY 10 Ways Excel Pivot Tables Can Increase Your Productivity http://bradedgar.com/10-ways-pivot-tables-in-excel-2010-2013-can-increase-productivity/ HR dashboard from Excel Tips: http://www.exceltip.com/human-resources/human-resource-dashboard-by-pangam_syahoo-com.html -~-~~-~~~-~~-~- Checkout my newest video: "How to Use the Vlookup Formula in you Excel Dashboard" https://www.youtube.com/watch?v=MCBmPnaKf_g -~-~~-~~~-~~-~-
Views: 8152 bradedgardotcom
Excel Calculate Option: How to Recalculate Your Excel Formulas Manually Or Automatically
 
02:19
Excel Calculate Option: How to Recalculate Your Excel Formulas Manually Or Automatically For more Excel Tips and Trick visit me http://bradedgar.com. In this video, I'll review and show you how to use the Excel Calculate option. This is available literally for users that might have massive sets of data where there are many calculations in the workbook. Excel can calculate manually or automatically. Typically your workbooks will be setup to automatically calculate but you can set it like I said for larger workbooks to manually calculate so the spreadsheet doesn't get bogged down each time you update or add more raw data to your spreadsheet. Other Resources and Links: Excel Formulas Not Calculating http://blog.contextures.com/archives/2012/02/16/excel-formulas-not-calculating/ Excel 2010 Performance: Improving Calculation Performance https://msdn.microsoft.com/en-us/library/office/ff700515(v=office.14).aspx Controlling Excel Calculation http://www.decisionmodels.com/calcsecretse.htm Change formula recalculation, iteration, or precision https://support.office.com/en-us/article/Change-formula-recalculation-iteration-or-precision-73fc7dac-91cf-4d36-86e8-67124f6bcce4 Understanding Manual Calculation http://excelribbon.tips.net/T009999_Understanding_Manual_Calculation.html How Excel determines the current mode of calculation https://support.microsoft.com/en-us/help/214395/how-excel-determines-the-current-mode-of-calculation Black-Scholes Excel Formulas and How to Create a Simple Option Pricing Spreadsheet http://www.macroption.com/black-scholes-excel/ How to Change the Automatic Calculation and Multi-Threading Features in Excel 2013 http://www.howtogeek.com/162219/how-to-change-the-automatic-calculation-and-multi-threading-features-in-excel-2013/ Is Your Excel on Automatic or Manual? http://blog.contextures.com/archives/2010/07/19/is-your-excel-on-automatic-or-manual/
Views: 811 bradedgardotcom
4 Google Search Techniques to Find the Best Excel Content on the Web
 
02:52
4 Google Search Techniques to Find the Best Excel Content on the Web Checkout the blog post - 8 Search Techniques You Can Use Right Now to Find the Best Excel Content On The Internet - http://www.bradedgar.com/8-search-techniques-can-use-right-now-find-best-excel-content-internet/ The amount of Excel content on the web is staggering. Being able to properly navigate the web and other engines is the only way that you can really find the best Excel content out there. In this video, I review all of the 4 Google search techniques that I use which includes being able to find similar content to Excel content that you know was great, finding the best Excel websites out there using the related option, how you can use quotations to find exact phrases, and then also how you find a specific phrase within a specific website. As you learn each of these Excel search techniques, you'll be able to find the best content out there. I suggest you checkout my blog post to get and find some other amazing search techniques from some different engines. Other Links and Resources 10 tips for smarter, more efficient Internet searching http://www.techrepublic.com/blog/10-things/10-tips-for-smarter-more-efficient-internet-searching/ How to Find Anything Online With Advanced Search Techniques http://computers.tutsplus.com/tutorials/how-to-find-anything-online-with-advanced-search-techniques--cms-21154 A Guide To Better Google Search Techniques http://www.hongkiat.com/blog/better-google-searching/ How to search on Google https://support.google.com/websearch/answer/134479?hl=en Advanced Google Search Tips: 10 Techniques to Get The Most out of Searching on Google http://www.add3.com/insights/advanced-google-search-tips/ How to Search on Google: 31 Advanced Google Search Tips http://blog.hubspot.com/blog/tabid/6307/bid/1264/12-Quick-Tips-To-Search-Google-Like-An-Expert.aspx 10 Quick Time-Saving Excel Shortcuts & Mouse Tricks for Marketers https://searchenginewatch.com/sew/how-to/2292786/10-quick-timesaving-excel-shortcuts-mouse-tricks-for-marketers 3 Crazy Excel Formulas That Do Amazing Things http://www.makeuseof.com/tag/3-crazy-excel-formulas-that-do-amazing-things/ 133 Best Excel Resources: Tutorials, Guides, Add-ins, Templates, & Courses http://www.skilledup.com/articles/excel-resources-tutorials-templates-addins-courses 50+ Best Free Excel Templates & Dashboards To Help You Get Ahead at the Office http://www.skilledup.com/articles/best-free-excel-templates-dashboards Beyond If and Sum, 15 really useful excel formulas for everyone http://chandoo.org/wp/2008/08/13/15-microsoft-excel-formulas/ How to Use Excel: 14 Simple Excel Shortcuts, Tips & Tricks http://blog.hubspot.com/marketing/how-to-use-excel-tips -~-~~-~~~-~~-~- Checkout my newest video: "How to Use the Vlookup Formula in you Excel Dashboard" https://www.youtube.com/watch?v=MCBmPnaKf_g -~-~~-~~~-~~-~-
Views: 1192 bradedgardotcom
The Excel Sales Trend Dashboard
 
03:53
The Excel Sales Trend Dashboard Buy the dashboard - https://gumroad.com/l/sales-trend-dashboard. In this video I'm going to quickly go over how the Excel Sales Trend Dashboard is setup and how and what information the dashboard will provide you with. This dashboard allows you as a user to upload your raw data to the sales tab and this will automatically then update your dashboard to show you your sales trend with the highest and lowest sold values and this being shown with data callouts. On the right hand side of the chart, I also included the month with the sales value for the highest and lowest months of sales. On the detailed section of the dashboard, the user can select a month/year combination and then display the total sales for that month with the corresponding highest customer sales and lowest customer sales. As you add data to your data set, the numbers and months available will automatically update. Tutorials and Resources: Create a Mini Pie Chart in Excel to Display a % of a Total https://youtu.be/FgZ60yzKvOU How to Use Excel Data Validation in Your Dashboards https://youtu.be/R8n444UBF9w Excel Dashboard Tutorial: Add Data Callouts for the Highest and Lowest Values in a Chart https://youtu.be/dQgWxSnPT5k Step 4: Building the Excel Dashboard Display with Shapes with Name Range Associations https://youtu.be/sVLjgiajsi4 Step by Step Instructions for Easy EXCEL Dashboards https://youtu.be/dI_axvl21J0 Introduction to Pivot Tables, Charts, and Dashboards in E https://youtu.be/9NUjHBNWe9M Chandoo - Excel Dashboards Template http://chandoo.org/wp/excel-dashboards/ How to Make a Dashboard http://chandoo.org/wp/excel-dashboards/#how-to-make-dashboard How to Create a KPI Dashboard in Excel http://chandoo.org/wp/2008/08/20/create-kpi-dashboards-excel-1/ -~-~~-~~~-~~-~- Checkout my newest video: "How to Use the Vlookup Formula in you Excel Dashboard" https://www.youtube.com/watch?v=MCBmPnaKf_g -~-~~-~~~-~~-~-
Views: 3108 bradedgardotcom
Excel Pivot Tables: Stop Manually Updating Your Source Data By Using Excel Tables
 
06:36
Excel Pivot Tables: Stop Manually Updating Your Source Data By Using Excel Tables This video shows you how to create a Pivot Table from an Excel Table (Does not show you how to create an Excel table - you can create one by selecting a cell within your data range and then typing Ctrl+T). It reviews how to setup your pivot table, add information to your source data and then simply refresh the information within your pivot table without having to update your source data. -~-~~-~~~-~~-~- Checkout my newest video: "How to Use the Vlookup Formula in you Excel Dashboard" https://www.youtube.com/watch?v=MCBmPnaKf_g -~-~~-~~~-~~-~-
Views: 1372 bradedgardotcom
Excel Absolute Reference: Using an Absolute Reference to Calculate Tax Charges in Your Data Set
 
01:43
Excel Absolute Reference: Using an Absolute Reference to Calculate Tax Charges in Your Data Set For more Excel tips and tricks visit me at http://bradedgar.com. In this short Excel Absolute Reference video, I'll show you the meaning behind what an absolute reference means in Excel and how you can use it. The dollar signs that are placed ahead of the column and row reference make the reference fully absolute. If there is only a dollar sign in from of one of the references, this is considered a mixed reference. In this video I show you what the relative reference looks like and what happens if we try and drag this formula down in our data set to calculate a tax charge. Basically, the reference becomes null because our tax rate reference is only in one specific cell. This is where the absolute reference comes in handy. I set my formula to reference always (absolute) the same cell so the calculation remains the same as I drag the formula down my data set and use the autofill handle in Excel. Other Resources and Links Excel 2013 Formulas 02: Absolute Cell References https://youtu.be/m2rqYHH8R14 Excel Basics #8: Cell References Relative & Absolute https://youtu.be/NmVMjQzseLA How to Use Excel 2010 absolute reference https://youtu.be/zC5gVnQQRAQ Excel 2016: Relative and Absolute Cell References https://youtu.be/iDg9s7BJ2m4 Working with Formulas and Functions in Excel 2013 http://www.quepublishing.com/articles/article.aspx?p=2031329&seqNum=7 Absolute references http://www.gcflearnfree.org/excel2013/relative-and-absolute-cell-references/2/ Excel 2016: Relative and Absolute Cell References http://www.gcflearnfree.org/excel2016/relative-and-absolute-cell-references/1/ Cell References in Excel http://www.excel-easy.com/functions/cell-references.html Switch between relative, absolute, and mixed references https://support.office.com/en-us/article/Switch-between-relative-absolute-and-mixed-references-dfec08cd-ae65-4f56-839e-5f0d8d0baca9 Switch between relative and absolute references https://support.office.com/en-us/article/Switch-between-relative-and-absolute-references-981F5871-7864-42CC-B3F0-41FFA10CC6FC How do I change an Excel relative cell to an absolute cell? http://www.computerhope.com/issues/ch000774.htm Relative vs. Absolute Cell References in Spreadsheets http://web.pdx.edu/~stipakb/CellRefs.htm Toggle absolute and relative references https://exceljet.net/keyboard-shortcuts/toggle-absolute-and-relative-references Lesson 3: Relative and Absolute Cell Reference, and Formatting http://www.howtogeek.com/school/microsoft-excel-formulas-and-functions/lesson3/ Why use dollar sign ($) in Excel formulas - absolute and relative cell references https://www.ablebits.com/office-addins-blog/2015/11/25/relative-absolute-reference-excel/ Absolute reference https://exceljet.net/glossary/absolute-reference
Views: 575 bradedgardotcom
How to Create Bar Charts Using Excel Conditional Formatting
 
01:37
How to Create Bar Charts Using Excel Conditional Formatting For more Excel Dashboard tricks visit me at http://bradedgar.com. In this video tutorial I'll show you how you can use Excel conditional formatting to create in cell bar charts that change with your data sets. In this particular case I use and create an Excel bar chart set that shows the number of days past due. On the top column I add a number from 1 to 110 and then I use custom number formatting (using only semi-colons) to display nothing in those header cells. I then create and use conditional formatting using the formula type to create a formula that checks to see if the value in our data set is greater than the header value. If it is, I then conditionally format the cell to display a color for that number. This automatically changes as your data set changes. As past due increases, so do the cells that are conditionally formatted. The key to doing this is using absolute and relative referencing, custom number formatting, conditional formatting and just some straight up awesome visual display techniques to get this thing to shine. Other Resources and Links: Relative vs. Absolute References in Formulas [spreadcheats] http://chandoo.org/wp/2008/11/04/relative-absolute-references-in-formulas/ Excel 2013 Tutorial - Absolute vs Relative Cell Referencing https://youtu.be/N7oq_MTboB8 Relative and Absolute Cell Reference, and Formatting http://www.howtogeek.com/school/microsoft-excel-formulas-and-functions/lesson3/ Use Excel's conditional formatting feature to display simple icons http://www.techrepublic.com/blog/microsoft-office/use-excels-conditional-formatting-feature-to-display-simple-icons/ 10 cool ways to use Excel's conditional formatting feature http://www.techrepublic.com/blog/10-things/10-cool-ways-to-use-excels-conditional-formatting-feature/ Excel Conditional Formatting - Data Bars http://www.contextures.com/excel-conditional-formatting-data-bars.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/ Conditional Formatting in Excel 2007 and Excel 2010 spreadsheets using Formulas and Icon Sets http://hubpages.com/technology/Conditional-Formatting-in-Excel-2007-and-Excel-2010-spreadsheets-using-Formulas-and-Icon-Sets Cool things you can do with conditional formatting https://exceljet.net/blog/cool-things-you-can-do-with-conditional-formatting Microsoft Excel Conditional Formatting http://chandoo.org/wp/tag/conditional-formatting/ 5 Incredible Number Formatting Tricks That Will Impress Your Boss http://www.bradedgar.com/5-incredible-number-formatting-tricks-will-impress-boss/ Excel Hide Zeros & Other Custom Number Formatting Tricks http://www.excelarticles.com/LE10ePub-303.html A Technique to Quickly Develop Custom Number Formats http://chandoo.org/wp/2011/11/02/a-technique-to-quickly-develop-custom-number-formats/ 10 Simple Tips To Make Your Excel Charts Sexier http://searchengineland.com/10-tips-to-make-your-excel-charts-sexier-135407 5 simple rules for making awesome column charts http://chandoo.org/wp/2013/08/29/rules-for-making-awesome-column-charts/ 10 cool new charting features in Excel 2013 http://www.techrepublic.com/blog/10-things/10-cool-new-charting-features-in-excel-2013/ Variance on Clustered Column or Bar Chart http://www.excelcampus.com/charts/variance-clustered-column-bar-chart/ How to create a bar or column chart in Excel http://fiveminutelessons.com/learn-microsoft-excel/how-create-bar-or-column-chart-excel -~-~~-~~~-~~-~- Checkout my newest video: "How to Use the Vlookup Formula in you Excel Dashboard" https://www.youtube.com/watch?v=MCBmPnaKf_g -~-~~-~~~-~~-~-
Views: 3892 bradedgardotcom
How to Use Excel Data Validation in Your Dashboards
 
08:38
How to Use Excel Data Validation in Your Dashboards For more Excel Dashboard tips and tricks visit me at http://bradedgar.com. I do have to give a big shout out to Jordan Goldmeier from http://optionexplicitvba.com/. I picked up this technique in one of his books and you can see him go over it in detail here (https://youtu.be/yoPVlOwGFKA). In this video I'll review how you can use Excel data validation in your Excel dashboards. In this case I've used them to indicate whether or not less than or more than 12 months of data have been included in my raw data set. I do this by using the IF formula and the count formula. I count the number of months that are listed in my raw data set. If this value is greater than 12, I show yes in a cell. If the value is less than 12 in terms of the number of months of sales data, I put no in that cell. I then use that field with another IF formula (using the correct characters for the wingdings 2 font) to display either a check mark or an X. If the value of the cell is No, a check mark is displayed. If the value of the cell is yes, I display an X. After this is completed, you'll then use conditional formatting based on the wingding character used and it's corresponding standard font character. If the value is an X, I'll display the font in red and if the value is a check mark, I'll display the value in green. Once this is completed I add a new name range to each of these cells. These will be used on the Excel dashboard. I then will refer each cell on the dashboard to the 2 cells that I created in the previous 2 steps using the name ranges that were created. Once these are set, I'll apply the same conditional formatting as was used on the calculations tab of my workbook. This information will allow us to know whether or not your chart and raw data set includes more than or less than 12 months of data. Other Resources and Links: Data Validation with Wingdings - Excel Tips https://youtu.be/yoPVlOwGFKA Use Excel Custom Number Formatting for Data Validation https://youtu.be/GnWV4EjegGE Data Validation: http://www.excel-easy.com/basics/data-validation.html Creating a KPI Dashboard in Excel http://chandoo.org/wp/2008/08/20/create-kpi-dashboards-excel-1/ 10 Formulas that Helped me Keep My Job https://blog.udemy.com/excel-formulas/ How to use the IF function in Excel to calculate values based on different criteria http://fiveminutelessons.com/learn-microsoft-excel/how-use-if-function-excel-calculate-values-based-different-criteria#sthash.7xvK5OdZ.dpbs How to Write an If Statement in Excel http://www.excelcampus.com/functions/how-to-write-if-function/ The Ultimate Guide to Bullet Points in Excel http://www.launchexcel.com/bullet-in-excel/ Introduction to Excel Data Validation http://www.contextures.com/xlDataVal01.html Learn Advanced Data Validation Techniques from Chandoo http://chandoo.org/wp/2008/11/25/advanced-data-validation-techniques-in-excel-spreadcheats/ Use GETPIVOTDATA to integrate pivot tables with dashboards http://chandoo.org/wp/tag/data-validation/ http://chandoo.org/wp/2015/08/26/getpivotdata-in-dashboards/ Quickly Remove Data Validation Rules http://chandoo.org/wp/2011/03/04/quickly-remove-data-validation-rules-using-paste-special/using Paste Special -~-~~-~~~-~~-~- Checkout my newest video: "How to Use the Vlookup Formula in you Excel Dashboard" https://www.youtube.com/watch?v=MCBmPnaKf_g -~-~~-~~~-~~-~-
Views: 6176 bradedgardotcom
How to Create a Conditional Custom Number Format in Excel
 
01:17
How to Create a Conditional Custom Number Format in Excel For more tips and tricks- check out my website here: http://goo.gl/89RdjM In this video, I show you how you can change the way your data appears based on the value in the cell. I'll use conditional custom number formatting in Excel to show you how to display values that are greater than a certain number in a specific color, values that are between numbers in a specific color and also values that are less than the last value in a specific color. Using the conditional number formatting method applied in this video is a great way to visually identify poor performance for sales revenue or any other metric as well as also identify where you are performing well in your business. Excel conditional custom number formatting is used in a similar way to the regular formatting but has a few minor differences: (1) The semicolon (;) acts as a way to split out your conditions for your syntax as opposed to splitting out your number formatting between positive numbers, negative numbers, zero and text. (2) Square brackets ([ ]) are used to outline the conditions you would like applied to each section of your statement. You can apply color conditional formatting for the text and you can apply formatting using operators and numbers. This simply means that if a number is greater than 10000, you can have your numbers display in a certain way that will appeal to the user. Checkout the links below for extra and additional aids for learning the topic of conditional custom number formatting in Excel. Learn about Custom Number Formatting and How it Works: http://bradedgar.com/1-thing-need-learn-using-excel-custom-number-formatting-syntax/ All articles on custom number formatting from Chandoo: http://chandoo.org/wp/tag/custom-cell-formatting/ The Definitive Guide to Custom Number Formatting: http://jonvonderheyden.net/excel/a-comprehensive-guide-to-number-formats-in-excel/ 5 Incredible Number Formatting Tricks That Will Impress Your Boss: http://bradedgar.com/5-incredible-number-formatting-tricks-will-impress-boss/ Microsofts take on Custom Number Formatting: https://support.office.microsoft.com/en-us/article/Create-a-custom-number-format-78f2a361-936b-4c03-8772-09fab54be7f4?CorrelationId=4f733bdb-092a-4a8b-ab2a-e7e8bb3ae634&ui=en-US&rs=en-US&ad=US -~-~~-~~~-~~-~- Checkout my newest video: "How to Use the Vlookup Formula in you Excel Dashboard" https://www.youtube.com/watch?v=MCBmPnaKf_g -~-~~-~~~-~~-~-
Views: 2144 bradedgardotcom
Excel How To: How to Copy Or Move a Sheet in Excel
 
02:58
Excel How To: How to Copy Or Move a Sheet in Excel For more Excel tips and tricks visit me at http://bradedgar.com In this video I'll show you how to copy or move a sheet in Excel. I'll do this by using two different options. One is being able to access the organize sheets option / section from the home table under the format drop down. From this you can actually copy and move a sheet once the window appears. You can copy to the same sheet into a new sheet or an existing sheet or you can copy to a new workbook to a new worksheet or an existing worksheet. The other option is you can actually do this same process by using the right click option on the worksheet tab and then selecting move or copy sheet. So you do have a couple of options for accessing this functionality. The big thing is being able to successfully move and copy a sheet very easily without having to use copy or past functionality and maintaining all of your formatting. Other Links and Resources Excel How To: How to Copy Or Move a Sheet in Excel http://www.techrepublic.com/blog/microsoft-office/copy-an-excel-sheet-from-one-workbook-to-another/ Move or copy worksheets or worksheet data https://support.office.com/en-us/article/Move-or-copy-worksheets-or-worksheet-data-47207967-bbb2-4e95-9b5c-3c174aa69328 How to Copy or Move a Worksheet into Another Workbook in Excel http://www.howtogeek.com/220677/how-to-copy-or-move-a-worksheet-into-another-workbook-in-excel/ How to Copy Excel 2010 sheet to another workbook https://youtu.be/KlJxkOpu_KI Excel - How to transfer worksheet to another Excel file? http://ccm.net/faq/5113-excel-how-to-transfer-worksheet-to-another-excel-file HOW TO MOVE A WORKSHEET TO ANOTHER EXCEL 2013 WORKBOOK http://www.dummies.com/software/microsoft-office/excel/how-to-move-a-worksheet-to-another-excel-2013-workbook/ Moving a Worksheet to Another Workbook http://excel.tips.net/T002686_Moving_a_Worksheet_to_Another_Workbook.html A Quick Way to Copy an Excel Worksheet Print http://easykey.uk/excel/a-quick-way-to-copy-an-excel-worksheet
Views: 659 bradedgardotcom