Excel - Spread Sheet

All about Microsoft Excel Tricks and Tips.

Shifting and Moving Column and Row by Grabbing

05 November 2016, 12:30 572

Grabbing the side of a column, or row (#movecol) below the A-B-C column headers, you will see a line when you move, shift, or copy column(s). When grabbling the left side of a column it must be below the row headers of the selected column(s) and the pointer arrow should just barely touch the left...

Excel Shortcuts for Cell Formating

16 October 2016, 11:37 480

MS Excel Formatting Without The Mouse: I thought today might be a good time for another installment of keyboard shortcuts... In MS Excel: Ctrl + Shift + ~ will apply the General Number format to the currently selected cell(s). Ctrl + Shift + $ will apply the Currency format, with 2 decimal places...

Setting a reference to the VBA Extensibility library

13 October 2016, 02:41 507

When your coding the VBE you have to set a reference to the VBA Extensibility library. This can be done manualy, but also with the following code. Sub MakeLibrary() On Error Resume Next 'if it already exits ThisWorkbook.VBProject.References _ .AddFromGuid...

Using Shift + F4 key to find Next Occurrence - Excel

13 October 2016, 02:39

Objective: Using Shift + F4 Key to find next occurance How is the Question ? Step 1. Use the Find option from the Edit menu as normal, specifying what you want to search for and then looking for the first occurrence. Step 2 When the first occurrence is displayed, Press the Esc key (or click on...

Adding description to the User Defined Functions - Excel VBA

13 October 2016, 02:35 508

One of the new features in Excel 2010 is the ability to provide argument descriptions for user-defined functions. These descriptions appear in Function Arguments dialog box -- which is displayed after you choose a function using the Insert Function dialog box. 1 -------------------------- Sub...

VBA Code - Select Start and Search Text in Text Box

13 October 2016, 02:27 648

VBA CODE FOR Select Start in Text Box: You need a 'Form' with 'Two Textbox' Private Sub Form_Load () Text1.Text = "Two of the peak human experiences" Text1.Text = Text1.Text & " are good food and classical music." End Sub Private Sub Form_Click () Dim Search, Where...

Precision as Displayed - VERY IMPORTANT

13 October 2016, 02:23 663

Precision as Displayed: Checking this box will force Excel to calculate to the number of decimal places that appear as a result of your formatting, and will permanently change any numbers stored in cells. You need to be sure you have thought through the full implications of this before using it....

Controlling Excel Calculations

13 October 2016, 02:18 455

Look at these options Tools -->Options-->Calculation tab. Calculation Settings Keep Changing Because a number of Excel's calculation settings work at the application level (they are the same for all open workbooks), and are set by the first workbook opened, they may appear to change randomly...

Populating ListBox in VBA - Excel

12 October 2016, 21:05 700

List Populating for multi-column: ListCount starts at 1 when counting ListBox items or List. But ListBox Indexing of those items or List starts at 0. In your example, you are trying to add an item or List in a Multi-Column ListBox. Take a look at below example: With me.ListBox1 .Add...

Multiple Conditions to Calculate Total - Count using Array Formula (Simpler Version)

12 October 2016, 20:50 781

Create a total based on multiple conditions: Use the following formula to calculate the total value of cells F5:F25, where B5:B25 contains "Northwind" and the range C5:C25 contains the region name "Western"....

Enter or Edit the same data on Multiple Worksheets

12 October 2016, 20:46 575

When you select a group of sheets, changes you make to a selection on the active sheet are reflected in the corresponding cells on all other selected sheets. Data on the other sheets may be replaced. Select the worksheets where you want to enter data. How? Select the cell or cell range where you...

Conditional Sum with More then two Conditions Posted by Aladin Akyurek from Mrexcel.com

12 October 2016, 20:41 593

COUNT (or COUNTA) is used for counting, SUM for summing when no condition involved. COUNTIF is used for counting, SUMIF for summing when a single condition involved. SUMPRODUCT or an array formula is used for counting as well as for summing when two or more conditions involved. I call these...

Comparing Two Strings - Using VBA strcomp() Function

12 October 2016, 18:37 494

Comparing two strings for equality can be tricky. The result of var1 = var2 where Var1 and var2 only differ in the use of capitals depends on the Option Compare Setting. This is risky business indeed. Setting Option Compare is a module level decision effecting statement level results. If a module...

Rank Formula in Excel

06 October 2016, 09:53 351

Using Rank and Countif Function in Excel to know the rank or the position of the Student or the particular detail.

Working WIth Array - Microsoft Excel

05 October 2016, 23:08 625

On 2002-03-15 16:21, Dave Patton wrote hot use Array to work out Average .

NCON INFRA