Videos uploaded by user “AnalyzeIT”
MS Excel ActiveX: How to change the font size & style in a combo box
Ken Atigari: Learn how to change the size and style of fonts in Excel's activex combo box.
Views: 17701 AnalyzeIT
Microsoft Excel ScreenTip on Mouse Hover using Hyperlink Function
Ken Atigari. Ever wanted to move your mouse cursor over an object such as a map in Excel and see a label pop up? The ScreenTip function embedded within Excel's Hyperlink function is the way to go. This lesson will show you how you can go about creating such mouseover pop up labels that will make your Excel applications look very cool.
Views: 14209 AnalyzeIT
MS Excel ActiveX: How to link Combobox selection as an output cell value.
Ken Atigari: In this free video, you'll learn how to access the ActiveX 'properties' window in order to make changes to the combo box that will link the value presented in the combobox to an Excel cell.
Views: 17099 AnalyzeIT
Creating dropdown list using data validation, form control and activex control
Ken Atigari shows how you can create a drop-down using data validation, Form controls and Activex Control
Views: 2694 AnalyzeIT
MS EXCEL: Using a little VBA to reset the value of a dependent dropdown list
Ken Atigari: Sometimes when you're building tools and forms in Excel, you'd like values in hierarchically dependent drop downs to be reset. For example, when you change the country in a drop drown from UK to Canada, you'd expect that city box is reset from London to a blank box. This video tutorial will show you how you can use VBA to do this in Excel.
Views: 9229 AnalyzeIT
MS Excel: Learn how to Create Maps by editing Shapes
Ken Atigari -Learn how to create Microsoft Excel maps by inserting shapes and then manipulating them using 'edit points' function in Excel. After creating your maps, you can go further to RAG rate them into heat maps using some VBA syntax which is not covered in this video tutorial.
Views: 3644 AnalyzeIT
Excel: Extending a named range (name manager) for a dropdown list
Using a Tracker application, Ken Atigari demonstrates how an Excel user can extend a named range that is being used by a data validation list.
Views: 1109 AnalyzeIT
Integrating Excel VBA with SQL Server: Lesson 1
In this 4 part series, Ken Atigari demonstrates how you can integrate Excel with SQL using VBA programming.
Views: 37673 AnalyzeIT
Dependent heirarchy dropdown lists in Excel and some VBA
Ken Atigari demonstrates how to create 3 hierarchically dependent drop down lists. He also shows how a little VBA programming can reset the lists to default values.
Views: 2805 AnalyzeIT
Excel VLookup:Hard Coding and Lookup Direction
In this video, the viewer is shown how to hard code the lookup value and the table array. Ken also demonstrates that vlookup is only able to read data to the right of the 'lookthrough" data and not those on its left. The user would need to copy data from a left column to the right of the "lookthrough" column
Views: 2592 AnalyzeIT
Exporting Data from SQL to Excel using Copy and Paste Method
Ken Atigari shows an easy "Copy and Paste" way you can export some data from SQL to Excel or a CSV file without having to use SSIS or other export /import functions.
Views: 1815 AnalyzeIT
Excel Desk Booking Tool
Ken Atigari shows how to create a dynamic desk booking tool using Excel. In creating the tool, he uses Hlookups, data validation and conditional formatting mixed with good old common sense.
Views: 9675 AnalyzeIT
Excel for Scorecards, SLA's and KPI's 2
In the second lesson on Performance Management, Ken Atigari talks about SLA's and KPI's in the workplace. The lesson is a prelude to case study examples of designing scorecards using Excel with some Analytical thinking
Views: 1715 AnalyzeIT
Excel Hlookup and Row reference function
Ken Atigari shows how the Excel Hlookup function is used to extract values that are horizontally laid out.
Views: 1908 AnalyzeIT
Using nested IFs in Excel Data Vaildation
Ken Atigari: Watch how to use If and nested if statements within the Data Validation window in Excel.
Views: 1494 AnalyzeIT
Excel Conditional Formatting 3: Highlight cell rules - Between, Equal
In this series, Ken Atigari walks you through how you can use the different built-in conditional formatting functions in Excel
Views: 738 AnalyzeIT
MS Excel ActiveX: How to create combobox dropdown list.
Ken Atigari: This video will show you how to create dropdowns using ActiveX comboboxes. You'll learn how to access the ActiveX 'properties' window in order to make changes to the combobox.
Views: 3383 AnalyzeIT
Excel Vlookup: Exact and Approximate Matches
Ken Atigari introduces vlookup as an extremely useful function in Excel. He highlights the differences between using exact match and approximate match in vlookup, pointing out that if data is not sorted in ascending order before using approximate match, the retuned values could be erroneous
Views: 1341 AnalyzeIT
VLookup Organisation Case Study Example of Mapping 80,000 Spreadsheet Rows
Using a business example, we map over 60 supplier's ID to supplier's names in a spreadsheet with circa 80,000 rows. With the Vlookup function, we demonstrate that this can be done in less than 3 minutes
Views: 186 AnalyzeIT
Some UI User Interface tricks in Excel
Ken Atigari shows you a couple of tricks to create fancy Excel user interfaces.
Views: 1263 AnalyzeIT
Excel Index Match Function Case Study 1
Ken Atigari shows how you can use Excel's Index match function in a data cleansing project
Views: 346 AnalyzeIT
Excel Sumif and Sumifs function for diversity analysis
Ken shows you how to use the Sumif and Sumifs function within the context of global resource analysis
Views: 61 AnalyzeIT
Heirarchal dropdown lists and multiple substitute functions in Excel
This is a postscript to an earlier video titled "Hierarchical dropdown lists and substitute functions in Excel". In this video, Ken Atigari shows how to nest a substitute function within another substitute function when working with data validation and name manager in Excel.
Views: 2155 AnalyzeIT
Excel In Business 1: Introduction
Ken highlights the need to have a broad view of Excel in the workplace. He then introduces the viewer to Excel end to end process starting from Excel as database, Excel as an Analysis tool and Excel as a reporting tool .
Views: 51 AnalyzeIT
MS Excel: Introduction to ActiveX  controls.....great for building forms!
Ken Atigari: This is a quick introduction to ActiveX controls in Excel. It is a great tool to get familiar with if you want to start building interactive forms in Excel
Views: 2537 AnalyzeIT
Using Concatenate and Ampersand in Excel
Ken Atigari demonstrates how to use the Excel Concatenate function and ampersand to join text strings and numerical values.
Views: 911 AnalyzeIT
Excel Conditional Formatting 1: Highlight cell rules -Greater than
In this series, Ken Atigari walks you through how you can use the different conditional formatting functions in Excel.
Views: 2042 AnalyzeIT
Excel for Scorecards, SLA's and KPI's 1
Performance Management, Balance Scorecards, SLA's and KPI's. Ken gives an insight into how Excel is used as a tool in Performance Analysis
Views: 3818 AnalyzeIT
X Factor 2014 Result Analysis Using Excel
Ken Atigari shows you how you can use excel to analyse and report on the voting results of x factor
Views: 92 AnalyzeIT
2 ways to create error bar charts in Excel
Ken Atigari shows two ways to create Excel error bar charts. In one of the methods, he use a line chart with error bars and in the other method, he uses a stock chart.
Views: 280 AnalyzeIT
System Expiration Analysis Dashboard for Calender and Fiscal Years
Ken Atigari shows you how to innovatively use line charts and drop downs to make dynamic dashboards which visually captures the interplay of Calender and Financial year system expiration dates.
Views: 51 AnalyzeIT
Excel Index Match  Function Case Study 2
Ken Atigari shows how to dynamically use Microsoft Excel index and match function together . The index function provides the array that the Excel syntax will refer to, while the Match function provides the row and column numbers that will determine the exact cells in the array that will be pulled by the index function. The case study under consideration is for a "themed review" project in order to assess the quality of data hosted in an Excel database. In addition to Excel Index and Match functions, other Excel functions used include Data validation, Vlookup, Hlookup, Concatenate, Conditional formatting.
Views: 231 AnalyzeIT
Excel Countif and Countifs function
Ken shows how to use Excel Countif and Countifs functions in global human resource analysis.
Views: 136 AnalyzeIT
Skype Social Media Analysis Case Study
Ken Atigari shows how usage and content analysis can be acheived.
Views: 75 AnalyzeIT
Using Access Database Documenter
So you want to create a data dictionary to document the attributes and validation rules of your Access database. Instead of tediously going back and forth transcribing data from Access datasheet view, Ken Atigari shows you how you can use a function in Access database called the "database documenter" to export the documentation to Excel or a CSV file.
Views: 3127 AnalyzeIT
Microsoft Excel:  This is how you show charts for hidden cells
Ken Atigari: Some times, you want to hide the data source for charts within the same worksheet. However, when you do so, the chart by default would have no values to present. This video will show you how to configure Excel chart function to be able to pull values from cells that are hidden in the worksheet.
Views: 61 AnalyzeIT
Excel In Business 2: Excel as Database
The video argues that Excel can function as either a temporary of permanent database depending on the goal of the project.
Views: 85 AnalyzeIT
Excel Conditional Formatting 2:  Highlight Cell Rules -Less than
In this series, Ken Atigari walks you through how you can use the different built-in conditional formatting functions in Excel
Views: 203 AnalyzeIT
Dynamic Excel Hyperlinks for Modeling Systems Use Cases
Ken Atigari demonstrates how to use dynamic Excel hyperlinks when modelling system prototypes. This is useful in Business Analysis practices.
Views: 135 AnalyzeIT
MS Excel: Subtotal function to add unfiltered and unhidden values
Ken Atigari: Some times you want to filter a table and see the sum of some measures in the filtered table. This video will show you how to use Excel's subtotal function "=subtotal(9,A1:A50)" to achieve this.
Views: 68 AnalyzeIT
MS Excel ActiveX: Preventing other values being entered into a combobox dropdown list
Ken Atigari: Learn how to manipulate the properties of an ActiveX combo box control in order to prevent it taking any value except for those available in the drop-down list.
Views: 159 AnalyzeIT