Author has written 295 articles

Useful Excel keyboard shortcuts tutorial

Excel Shortcuts

Work with excel as efficient as you can using keyboard shortcuts instead of clicking the mouse. If you take one hand from the keyboard to click the mouse you loose precious time and energy. All you need to know is key shortcuts that do certain action you used to do with the mouse. Excel useful…

 

Sumifs and wildcard condition on column contents

how to use sumifs and wildcard

The most used formula in excel is SUMIFS, which, in my opinion, together with a wildcard condition, is the element which supports and ensures the existence and usage of excel application. Let’s imagine we have a spreadsheet which contains 2 columns: first one shows the sales revenues for a certain period and the second shows…

 

Variable sheet in excel formulas, calling dynamic sheet name

Dinamic variable sheet name in formulas

Sometimes we need to use in our excel formulas a certain sheet depending on different conditions. Calling variable sheet name in excel formulas For example, if we want to create an easy to use report which needs to apply consolidating formulas on data available on a certain sheet, depending on the user choice. Let’s say…

 

SUMIFS across multiple sheets

Sumifs across multiple sheets

First you have to define a SheetList range name (the range of sheet names being the sources of SUMIFS). You can input the name of the sheets in a separate sheet of your excel file. Select all the cells containing this sheet names, then go to the name box field (the upper left field where…

 

Set cell background color programmatically

The VBA code line you should use when you need to set a new background color to a cell is: Sheets(“Sheet1”).Range(“C3”).Interior.Color = RGB(255, 255, 0) RGB(255, 255, 0) = Yellow (full Red and full Green) Where RGB defines the color you need as a mix of 3 color:Red Green and Blue (RGB). Zero means no…

 

Hide an excel sheet so that nobody can see it

To hide an excel sheet so nobody could see it, like the results of a database query when you want just to show a summary based on it, select that sheet and do the following: Making an excel sheet very hidden Right click on the sheet name View Code In the VBA project window that…