Excel Formula Challenge #6 – Get Student Count Here is another query which came to our Excel VBA WhatsApp Group yesterday. Have a look at the screenshot below. Left table is the data table, Right table is the Result we need by excel formulas. Teacher 1 shows 15 in output, You can count the yellow cells row to get the idea to build your logic. Download Sample Data here Formula Challange #6 (152 downloads) See
Read Csv Data by VBA Ever got a csv file with large data? It is quite painful when we got larger data in excel, Our fav excel just cant handle larger data on worksheet and perform filteration, formulation and other things you know. There was scenario where we downloaded a csv file thorough an API, That csv file is having 250K rows of data. but we just needed some certrain rows out of this data
Create pivot table from multiple sheet data This article will show you how to Create pivot table from multiple sheet data by using VBA. We will be using SQL query and connection to read data from all tabs. If you dont know how to open connection with excel by using ADODB object. Read my earlier article How to read excel data by ADODB Connection How this works? Code below is creating a query by looping
VBA Intersect Function VBA Intersect Function VBA intersect function is a very useful to get the intersection area of a Range. It is generally used to exclude header from range, or to check if a certain cell laying in a range or not. Intersect function returns the Range object. We will see couple example here to under how it works. Example 1 Intersect function need two or more range to find the intersection range. Have
Remove Special Characters by Excel formula Excel formulas are always fun, especially when newer version of excel getting lot more new formulas and Dynamic arrays. In earlier Excels, We never had a way to concat array of strings. Newer version of excel got TEXTJOIN function. TEXTJOIN function can concat all string as we pass as Array or Range. We will use this function to in our Remove Special Characters formula. Look at the two tables in
How To Create Searchable Dropdown In Excel A Dropdown to select value is good, but a searchable dropdown in excel is the best! In this article we will see two ways to create a searchable dropdown in excel. By using formulas By using VBA Create Searchable Dropdown by using Formulas Pretty easy, but only if you know a trick. We are basically going to filter the main list based on the substring we enter in
VBA Array – Everything you need to know. A variable is the place holder in the memory (RAM). When program execute, all these variables take space in memory and be used to store and read values. VBA Array is the collection of these variables. Dim strName as string Dim dblSalary as string We have two variables here, strName can be used to store name and dblSalary can be used to store salary. Now, let’s say
is VBA Dying? Answer to the this Question is a BIG NO. Here is why i think it is a NO It has been 10+ years since I am developing projects on MS office suites and don’t be surprised if I say I have alone developed 2000+ projects in my carrier and yet I am pretty sure I am not the one who did so many projects in VBA. Prior VBA, there used to be
How to enable macros in excel earliar than 2003 version Open any excel workbook file. In menu bar click Tools -> Macro – > Security. (See screenshot below) After that, you will see the window showing below. Click “Low” and hit “Ok” Close Excel and then reopen. Macros are now enabled in your workbook! How to enable macros in excel Later than 2003 version Open any excel workbook file. In Ribbon, Click on File –
Excel Formula Challenge #5 In this formula challenge, we need to find out the pattern starting row and column by using only Excel Native formulas and without any helper columns. lets get the fun started. Download Sample File FormulaChallenge5.xlsx (112 downloads) Join Our Premium Group Because learning right is important Learn More About Premium Groups
What is web scrapping? Web scrapping is the automation of web browser. Yes, it is that simple, Web scrapping is the automation of web browser, where we write some codes and navigate some pages, click buttons, download reports. We can use any programming language and some web browsers to do this automation. In this book we will using Excel VBA and Internet explorer. Further in this book, we will use the term web automation instead
Tools for Data Science What you should learn to be a Data Analyst? A Data analyst is someone who can interpret raw data into a story. A story which can tell performance of an entity. There is a new fancy word popular these days call “Data Scientist”. A Scientist is someone who do experiments and analysis and comes with a result. Data Scientist or Data Analyst needs a lot of tools or operate data. Following
VBA Mail Merge – Create Stickers Using Excel and Word Mail Merge In this tutorial, We will go through the process of generating label stickers using Excel VBA and Word Mail Merge. Creating Data File Let’s have data in excel sheet like this. Save this file as “Label Tool.xlsm” Creating A Word Template This is pretty easy. let’s follow these steps Open MS Word with a blank document Insert a table on first page and
How To Delete Opened Excel file with VBA Do you want to restrict user not to use the file after a specific date? You can easily do that in VBA. You can delete the file when they open it after a specific date. How to delete opened file? To Delete a file from hard drive, the file has to be either closed or read-only. Here is a code that can do the trick when the
How to build a curve chart in Excel Excel XY Scatter chart is not just for regression analysis, it is JUST for anything you want to visualise, I really enjoy manipulating data and see how it plots on XY and sometime it amaze me. I got an email couple days back it was showing how china’s investers are investing in companies. it looks like this This chart is something that i wanted to try building
Bullseye chart in Excel What is Bullseye chart? Bullseye chart can be used to visualise the parameter in a particular range. Microsoft excel doesn’t have this as default chart type but it provides way to create one by combining couple charts like X,Y and Radar. here is one i have build for you. How it works? You can see that circles are splited into pies, the PIEs are categories. there are three level in every
How to highlight chart points in excel on mouse hover Do you know you have mouse hover event for excel charts? Yes, There are few events associated with chart objects. you can use them to to make some interactive visualisation. In this article we will see how we can highlight a point in chart series when hovering mouse cursor on it. Preparing Data let’s take a very simple example here, we have a table with
Excel XML – How to Export XML Data From Excel XML is a worldwide supported format, You can import xml data in almost any program available. In this post i will demonstrate how you can export xml data from table, Lets start. What do you need? To export xml data from excel you will need two things 1. A Data Table 2. XML Map. I am sure i dont need to explain what is data
ADODB Connection – Read excel data without opening file Ever wondered if you can read excel without opening file? well there is a way. you can have VBA treat excel file like a database and worksheets as tables by using ADODB Connection. this is a quit simple process. you need just four things 1. A Connection String 2. A Connction Object 3. A Recordset 4. A Query How to connect excel with another excel file?
Python Tutorial #2 – Read Data in Python Using Panda Hello Again, In this article i will show you how to read data in python and manipulate it, but first of all, if you havent yet install the python tool, Click here to read how to get ready. How to write code in python language? We will use spyder to write python program, spyder is a very nice IDE for python development. You have easy
Mouse Hover Event in Excel Did you know you can add a mouse hover event in excel worksheet? well, you can, and that’s pretty cool isnt it? There is a hidden trick in excel that you can use run macro when you move your mouse on worksheet How to do it? Well, it is so easy. First of all you need to write a function procedure in VBA, it has to be a function procedure,
Python Tutorial #1 – Getting started with Python Python is of course the language of future, You can do almost anything with this great language, This open source language is have a big community and have developed countless libraries to support any kind of programming needs. I am starting a new series of python tutorial and will be posting weekly here. There is no perfect time to start anything. if you have not yet started
Class Module in VBA – Create Array Class Class module in VBA can be used to create our own objects, We can write the properties and methods in class module and later in VBA program we can create objects of this class. After the object is initialized, we can use all the properties and method written in that class. For example, Range is a class and when we create Range object, we can use all
Excel Calendar Control Excel Calendar Control An Excel Calendar Control is everyone’s needs, specially when you develop a form to have user to enter the date, you sure want to have a date picker. There are other ways too to add the date picker in VBA by adding libararies. But those are sometime buggy when those libararies are not registered on the client machine. So, Here is a calander control i have developed in VBA
Excel Formula Challenge #4 This one is quite easy in comparison to the other formula challenges here. By using excel native function you need to get the price of any selected brand. Be the part of my Excel VBA WhatsApp group to see the solution and challenges like this What is your formula solution? share it with people in the commentbox. You can download the test file from here FormulaChallenge4-1.xlsx (105 downloads) Join my VBA
How to lookup value in VBA Dictionary object in VBA is a substitute of excel vlookup function. In this post i will explain how you implement dictionary in your code. How you can add keys and values in dictionary object. What is Dictionary Object? Consider dictionary as two columns array, 1. Keys 2. Items. where you can save your data against unique key. Once you save you keys and item in dictionary, you can use
Excel Outlook Automation – How to integrate Excel and Outlook Excel Outlook automation is the way to send data driven emails. You can easily bind Outlook application with Excel application. In this tutorial i will show you how you can bind the two application together and few examples of what operations you can perform on outlook by using Excel VBA. Binding Applications – First thing you need to do is to have applications talk to
Things you need to know about MS office 2019 How it works Most of you probably are aware that MS office 2019 is coming soon this year. MS Office 2019 is a successor of MS Office 2016. Microsoft released office 2016 in late 2015 and then they released Office 365. Difference between two versions was that 2016 have a perpetual licensed, Means you just need to pay One-Time to use the Office 2016, while Office
Sumif – Multi-Values criteria in Excel IFs Excel Sumif Formula is very smart for aggregating data, it filter your data prior to perform a calculation. Like Sumif, Excel have some IFs formulas where you supply your criterias and those function can quickly do the calculation on specific dataset as per supplied criterias. SumIf, CountIf, AverageIf, and Sumifs, CountIfs, AverageIfs are the example of those function. You can provide criterias for multiple columns in these function.
How to get combination (Union) of Range? Union is a VBA function which can set multiple non-continuous ranges into a variable, For example if you want to highlight rows only with certain value in column A. you will need to use Union method. Have a look at the example here – Sub HighLightRow() Dim strInput As String Dim rngRange As Range Dim rngUnion As Range Dim rngCell As Range
XY Scatter Chart – Data Visualisation Another graph developed using XY Scatter Chart to showing relationship. this chart is showing how a website is connected with other website. Red Lines are the links going out and blue lines are links coming to the website. you can download file from here LinkChart2.xlsm (96 downloads) Join my VBA whatsApp group to know more Want to learn how to build these charts? enroll to my online training Enroll
XY Scatter Chart – Data Visualisation XY Scatter chart has always been my favourite as you can customise this chart in any way. you can do any type of visualization with this graphs if your maths/Formulas is good. I am sharing one of my chart which shows the different people having different skills. this chart is interactive so that you can mouse hover on excel cells and it will update visualization in chart. You can
VBA Tutorial – Things you need to know in VBA VBA is a must-have skills you need to learn if you want to make your life easy at work. And what is most exciting about VBA is that a lot of help available over internet to teach you this beautiful technology. in fact this is very easy to learn if you know a little bit of Excel. I am excited to share some useful knowledge,
PDF Protecter – Excel Tool Download Tool PDF documents are probably best when you want to share the information but dont want people to edit it. Many times when information is too confidntials you need to proect the protect the pdf with a password. so the user who know the password can only view the document. There are many tools available to encrypt PDF with a password. ADOBE ACROBAT READER PRO DC have inbuilt functionality
Opportunity For Trainers [avia_codeblock_placeholder uid="18"] Send us mail
Rating Chart This chart can be used in visualization of rating in your dashboards. Using Xy Scatter and Bar chart i create this chart very dynamic so that you can increase number of Rating or number of people you want to give ratings to. Download file from here RatingChart Join my VBA whatsApp group to know more Subscribe to get news from IT Chat Email Address * Name Share this entryShare on FacebookShare on TwitterShare
Thanks for choosing Excel Extension tool, Your download is ready. “VBA Training – Learn the best in VBA” — IT Chat
Download Now Excel Extension Tool V1.2Save your time Appriciate by SharingShare on FacebookShare on TwitterShare on Google+Share on PinterestShare on LinkedinShare by Mail Updates in this version. Split Data – Now you can split by rows ( e.g 500 rows each group) Added new functionality Break links – Some external links in excel are stubborn and dont want to get broken, this addins will help you get rid of them Dynamic Range – Create Dynamic
Play this easy but fantastic game only developed in excel, Shuffle Slate is a game most of us have played in our childhood time. How to Play : Hit start button, it will fill the grid with some random numbers from 1-80 and will leave the last box empty. Now you will use your arrow keys to shuffle the numbers which are around that empty box. By shuffling numbers around, you need to align all
Select Multiple Files in VBA This little program here allow your program to select multiple files and return the path for each of the selected files. Sub GetMultipleFilesPath() Dim varFiles Dim varEle With Application.FileDialog(msoFileDialogFilePicker) If .Show = -1 Then .AllowMultiSelect = True Set varFiles = .SelectedItems End If End With For Each varEle In varFiles Debug.Print varEle Next varEle End Sub Join my VBA whatsApp group to know more Share this entryShare on FacebookShare on
VBA Dictionary – How to summarize data Data summarization is an elementry step to analyse data and excel itself provides a great feature which is called pivot table. pivot table has great features which you can use to make your data tell you a story. but it takes memory to store data into pivot cache as a cube to faster operation and increase file size and thats where VBA Dictionary comes in to play. Many
Want to add more feature in excel? Microsoft already have added thousands of features In Excel but nobody use them all, because Excel is not for there for any particular use. data scientist, finance executive, scientist and many more people use it in different way. But still, while working on excel everybody feel that there should be some more easier and automatic way to do a particular job. being lazy, I have so many ideas
Organization chart automated by XY Scatter plot. this is an interactive chart to see organization levels. Show me an Image and i will turn that into interactive Chart Download File – Tree_Chart Join my VBA whatsApp group to know more
Excel VBA WhatsApp Group – Get Instant Help Excel VBA WhatsApp Group is here to help you instantly with your Excel VBA queries. You can anytime ask anything like formulas, VBA, Charts. We will help you with that. Spammers are not welcome and we will remove them instantly. Quick-Guide-First-Part.pdf (1301 downloads) Fill the userform showing below only on your mobile phone to join the whatsapp group for excel, but if are using Web.WhatsApp.com you can use
How to create Excel Mega Formula 2/2 Welcome to the tutorial of creating mega formula. Assuming if you are reading this post you might already have read the first part of it, but if not, have a look here. In my last post I tried to explain how to create array by using Row() an Indirect() function and then use that array into Mid() function to split up all characters in a string. if you need
How to create a mega formula -1/2 Excel formulas are beautiful and I am obsessed with string manipulation and lookup reference formulas, individually all formulas do very simple thing but when we learn how to combine them, we can have them do whatever we want to do like the question below. We have a WhatsApp group to discuss Excel VBA stuff, you can share your query or share any solution. Click here if you want
Automate PowerPoint by VBA Creating powerpoint presentation of dashboard is a time taking process and it VBA can easily Automate PowerPoint. As powerpoint also support VBA so the powerpoint object model can be loaded in excel vba. here we are sharing a very basic example of how we can use ppt object model in to excel vba to send charts on a slide. here is the code we are using Sub SendChartToPowerPoint() '--Application-Presentations-Slides-Shapes Dim objPP As Object Dim objPresentation
Outlook Email Automation MS Excel VBA can do great things when it is connected with other applications. such as Outlook, PowerPoint, Access etc. Here we have created a small but yet powerful utility by connected outlook with Excel through VBA so that it can send all repetitive emails on a single click of a button. You can download the utility Free of cost by following the link given below. In order to run this, you
Hope you were waiting for another formula challenge here. so we have got you one. have a look at image below. you can download the sample file Sample file Join my VBA whatsApp group to know more Download Solution file. Learn All Formulas Join Our Premium Group Because learning right is important Learn More About Premium Groups Like more challenges ?
Intersect Most of the time while working with array or range, you might have got the situation where you need to exclude headers from your range reference. there are couple ways of doing it. but the best way is to get the common range by using intersect function. Intersect function is an inbuilt VBA function, which return a range object. Have a look at the data table below. Now, we need to create a range
VBA array and dictionary together are very powerful and can help you building so complex logics. though, property of array and dictionary are quite simple. Dictionary stores unique keys, and array is collection of many variables. but any operation on array is very faster as compare to range. here we have a simplest example of array and dictionary. Download a working file from here. DictionaryArray Sub FilterAndCreateTabs() Dim varData Dim varUnique Dim varRows Dim varResult
Pivot table in MS excel is a great tool to summarize data and save lot of analysis time. sometime we need to automate even pivot table by VBA. For an example – You have a large data and you want to create so many reports sheets doing filtering and summarizing it. pivot table can make it faster and with VBA you can make it easier too. This tutorial will help you understand how pivot tables can be
File Folder Operation in VBA How to select a file in VBA Sub BrowseFile() With Application.FileDialog(msoFileDialogFilePicker) If .Show = -1 Then '-- if file dialog is opened without error If .SelectedItems.Count = 1 Then Sheet1.Range("rngFile").Value = .SelectedItems(1) End If End If End With End Sub How to select a Folder in VBA Sub BrowseFolder() With Application.FileDialog(msoFileDialogFolderPicker) If .Show = -1 Then '-- if file dialog is opened without error If .SelectedItems.Count = 1 Then Sheet1.Range("rngFolder").Value =
Regular expression is very powerful in string manipulation, it can identify a given pattern in any given string and get you the matching results. but remembering/building pattern is not an easy without practice. We have build a very small utility you can do your practice on. You can even save your patterns for future reference. Buy Free Join our Excel VBA training program. More info
Today, we have got another excel challenge where we need to get all these values from non continuous into a column. Try your formula and post it in comment box. Make it even more fun, Join our Excel VBA Online training More Detail Download sample file from here Challenge #2 Have a look at Challenge #1 Join my VBA whatsApp Group to know more
Excel Formula Challenge #1 Microsoft Excel itself is more interesting when it comes to formulas, so many things are very easy to do with VBA but are challenging with formulas. so we have got one Excel formula challenge like that. have a look. Above image is explaining what needs to get done. so we need sum of highlighted cells and let me tell you why those cells are highlighted. Rule is to include all the
Excel Conditional Formatting MS excel provide great way to identify bad or good data quickly with its great feature Conditional Formatting (CF). CF have many predefined ways to to format data but sometime problems are even more complicated and predefined procedures doesn’t work. Here you are going to see one that kind of problem and the solution to that. have a look at the picture below. So, you will see that there is one table
When you have dashboards, Reports with so many pages. There is a need to have a easy navigation in excel between all sheets. Though, Excel provides it in some way but it is not user friendly. Indexer tool will make it easier for you. What is Indexer? Indexer is small but great utility developed in Excel VBA to auto create Index page in any selected workbook. This tool can do one hour job in just few
Though, This example is basic but it give a better idea , how to connect excel with access to fire the sql query from excel to access. i personally like this example to demonstrate how you can use access effectively where multiple user can use the single excel application and use the ms access as back end as a database. So, this one is a pretty simple employee login/logout tool that use some short of
