Skip to main content

Posts

Showing posts from July, 2018

New Article

How to SUM by matching partial text in Excel

Split a TEXT into Column after a specific character each time in Excel

Getting tired by using Text to Column option to convert a Text into Column after a specific character each time? If so, then you can use a formula to split a Text into columns after each time a specific character appeared in the Text in Excel. Want to know how? Check the below example. Sample Text: Before starting our study today, I'm going to show a sample data to make clear that what exactly the data is and what we need: Image 1: A2 cell contains the Sample Data And we are going to do: Image 2: Convert the Text (A2) to Column (B2:F2) Solution: In cell B2 write the below formula and copy right to the F2 cell: = SUBSTITUTE( MID( SUBSTITUTE($A2," # ",REPT("-",LEN($A2))) , (COLUMN(A2)-1)*LEN($A2)+1,LEN($A2)) ,"-","") So, how this formula works? Well, we need to took a starting point after a certain number of characters and then add a sample character ("-") specific time LEN($A2). And finally delete the a...

ALT key not working for Keyboard Shortcut in Excel

It's a common problem for Microsoft Excel user. I suggest to use " / " instead of ALT key for quick solution. But it is not a permanent solution. In this article I will explain how to solve this problem.   Solution: Step 1: Go to: C:\Users\User_Name\AppData\Roaming\Microsoft\Excel (replace user_name with your user name). You can directly reach here by copy and pasting the following in Explorer address bar: %appdata%\Microsoft\Excel and see if is contains any files or folders. Step 2: If you find any file/s folder/s then move them to another location and make this folder blank. Step 3: Open the Excel program and try the ALT shortcut key now.

Get Numerical result by matching 3 conditions through SUMIFS, LOOKUP, SUMPRODUCT and SUM

In real life data, sometimes you need to get a result by matching 2 or more criteria. In most cases we use Data Filter, to count or get the result manually. But when your time is too short to do this, then you need to depend on Advance Formula. Advance formulas are those where 2 or more Excel Functions included. Sample Data: Assume that, we have a data table like below image: Image 1: Source Data Table We have to get the Working Days in below Data Table: Image 2: Target Data Table (1) SUMIFS: In I3 cell enter the below SUMIFS formula to get the result: =SUMIFS($D$2:$D$12,$A$2:$A$12,$F2,$B$2:$B$12,$G2,$C$2:$C$12,$H2) Image 3: Result through SUMIFS formula In SUMIFS formula first I have selected the "Working Days" column range $D$2:$D$12 from source data table, which I need to get. Then I have selected a range for First Criteria "Name" from the source data table as $A$2:$A$12 and mentioned the criteria as F2 ("Deb") fr...

How to enable Show page break option in MS Excel

Why Show page break option in MS Excel becomes disable? If your Excel Workbook is not in Normal view, that means Page Break Preview or Page Layout view or Custom Views activated, then your Show page breaks option might disable from File | Options | Advance | Show page breaks location. You cannot activate it from File | Options . Image 1: Show page breaks option disabled How to activate this Show page breaks option? First of all you need to set your Excel Workbook view as Normal. To do this you need to Click on View | Normal. Image 2: Normal view Now, if you check the File | Option | Advance | Show page breaks option, then you will find that this option is now activated. Image 3: Show page breaks option activated

How to Print background color or image of a print area in Microsoft Excel?

What is Background Color or Background Image in Microsoft Excel? For better data presentation, Microsoft Excel has an option to insert a background color or background image. So, that the report attracts others and looks beautiful. a sample has given below: Image 1: Microsoft Excel Background Image How to print the background color or background image? There is no simple way to print it as background color or background image. You need to make the background color or background image manually in Microsoft Excel. To do this, just follow the below steps: Step 1: Insert a "Rectangle" Drawing Shape from Insert menu and draw a rectangle. It is not necessary that you must draw the rectangle. You can choose others also like, oval, triangle etc. Image 2: Rectangle Step 2: Click on Format | Shape Fill | Picture. Image 3: Picture option Step 3: Insert a Picture.   Image 4: Picture inserted Step 4: Right Click on the Picture and Click on Format ...