Skip to main content

Posts

Showing posts from May, 2017

New Article

How to SUM by matching partial text in Excel

How to use Horizontal Scrollbar for large chart viewing in Excel

In many dashboard you need to create a large Chart , due to small Chart is not good for proper visualization. If you add a Horizontal Scroll Bar from Developers tab to the Chart , then it would be an attractive Chart for viewing and you might be in spotlight for your good work in workplace. So, this article will help you how you can add the Horizontal Scroll Bar to your big Chart . Make a Bar Chart: Before you assign a Horizontal Scroll Bar into your big Chart , you first need to create a Chart . Assume that, based on the below data you have created the simple Bar Chart as shown in below: Image 1: Sample data Now create a Bar Chart (because Bar Char t and Horizontal Bar matches perfectly, that is why I'm using Bar Chart . But if you wish, you can change the chart type as Pie , Line or Column too) like below: Image 2: Bar Chart created After creating the above Bar Chart , you will definitely say, "this chart it is not good t...

How to run Excel Online from your web browser?

Excel Online is a new version of Microsoft Excel to stay in touch from anywhere around the world. Or assume that, your MS Excel currently is in "Process" mode and you need to analyze a data at the same time, Desktop Excel will not allow you to open and work at the same time. But you can use Excel Online for your new Excel worksheet to analyze your data. No extra software is needed to run the Excel Online. It will run on your web browser. Basically it is the Excel Web based App. All the saved data of Excel Online stores in Your Microsoft OneDrive. Microsoft OneDrive is an online based storage tool which allows you to store 5GB data. Before using it you need to signup for a free Outlook.com account to use the cloud space of Microsoft. How to Run Excel Online from Browser: (1) To do this, login to your Outlook.com account. (2) Then Click on Apps Icon from upper left corner and Click on Excel . Image 1: Excel online (3) This will load the Excel Online w...

How to use Excel in Microsoft Word or others Office application?

In many thesis or research you need to calculate data in Excel worksheet and present the calculated data with reference in MS Word. But many of us use the Table in MS Word for calculation like MS Excel does. Most of the time we failed because a Table can't do all the things like Excel fast. So, the question is "Is there a way to use the MS Excel spreadsheet in MS Word properly"? The answer is Yes. You can use your Excel in Word. The process is very simple, which described as below: Sample Excel Worksheet: Assume that, you have calculated a "HR Salary Sheet" in Excel like below: Image 1: HR Salary Sheet Way 1: Copy and Paste Special: (a) To present it in the MS Word page, just select the data table ( B3:I14 ) cell and press Ctrl+C to copy them. (b) Now open your MS Word and Click the Paste Special option. Image 2: MS Word Paste Special option (c) While Paste Special dialog box appears, Select Paste radio button and then Microsoft Ex...

How to use SUMIFS function with greater than and less than condition?

We use SUMIFS function many times in our daily professional life. Did you try anything about sum a date range based on Date, where date in formula you need to use as reference cell? I'm sure you did but can't remember. Okey, here is a cool trick about how to use it as mentioned as above. How to use the formula: First of all, follow the below sample data table: Image 1: Sample data table Think that, you need to SUM the Numbers in between 4-May to 15-June. And this date can be changed anytime. So, it is wise to use a reference cell like D3, to just change the date and the result will automatically shown. Now time to format your report: Image 2: Designed the format for calculation SUMIFS Enter the Start_Date as 4-May-17 and End_Date as 15-Jun-17. In F3 cell enter the below formula: =SUMIFS($B$2:$B$21,$A$2:$A$21,">="&$D3,$A$2:$A$21,"<="&$E3) The & symbol used before cell reference. The result will show like below: ...

How to get the unique product names based on a criteria?

It is a common problem in many company to get all the unique items list based on a single criteria. For example: if I talk about an "Automatic Invoice Generator (AIG)" where I want to enter a Invoice Number only, and all things related to this number will show in an Invoice Copy or Challan Copy. It might be a dream of many employees to create it. But today I'm gonna show you how you can easily create this Automatic Invoice Generator (AIG) based on a Criteria. Raw Data and Report Format: First thing is first. Before doing this I've done a sketch to clear the concept: Image 1: Sketch to clear the concept Now the base is required. Yes, "Sales Raw Data". This is shown as below: Image 2: Sales Raw Data for AIG (Automatic Invoice Generator) Design the Invoice Template as you like. I've designed in my way:  Image 3: Invoice Design Formula used: In H12 cell you need to enter an Invoice Number. Based on this Invoice Number C15:C2...

How to display an image in worksheet based on a List or based on IF condition?

Excel can show image on worksheet based on a specific IF condition. So, how to do it? Simple follow the below steps: Step by Step: Step 1: Insert images in your Excel Worksheet. Here I've inserted 5 different types of balls, Football, Cricket, Pool, Basketball, Tennis ball. Note that, All balls are placed into different cell. These are placed in Picture sheet. Image 1: 5 balls placed in 5 different cells and covered photo's wide and height Step 2: In the report sheet, design the report as you wish. I've designed in my way like below: Image 2: Kids asking to Donald Duck, which ball need to throw now Step 3: Make a drop down list "Games" in E6 cell in Report sheet from Data Validation. which is as below: Football Cricket Pool Basketball Tennis You can do an IF function here in E6 in Report sheet, which will meet a certain condition and returned Football, Cricket, Pool, Basketball or Tennis. Step 4: Now the tricky part is...

Frequency function in Excel

Frequency() function is used to find the occurred number of a given data group from a single data set. It is an Array formula like Transpose(). You can easily understand Frequency function if you do know Statistics, where Frequency counts based on a group or class from a raw data. I'll try to explain in details about this function here. Function Structure: The structure of this Frequency function is: =FREQUENCY(data_array,bins_array) There are 2 main arguments in this function. These are: 1) data_array: This is the raw data range. You need to select an un-grouped raw data range here for count. 2) bins_array: This is the range of your own created Class or Group by which the function will count the data_array. Example 1: Assume that, A2:A16 is the record of Rainfall in Dhaka City. B2:B11 is the Class based on which you wish to group the A2:A16 data. D2:E12 is the reporting area where you need to use Frequency function. D2:D12 is your reporting group. If you imagine...