Excel Articles

Cheat Sheet / Updated 04-20-2022

As an integral part of the Ribbon interface used by the major applications included in Microsoft Office 2010, Excel gives you access to hot keys that can help you select program commands more quickly. As soon as you press the Alt key, Excel displays the mnemonic letter choices on the various tabs and command buttons on the Ribbon. Then, simply press the mnemonic (or not-so-mnemonic) letters to perform a particular task.

Cheat Sheet / Updated 04-18-2022

At first glance, you might have trouble making sense of the many menus, tabs, columns, and rows of the Excel 2010 user interface. This Cheat Sheet will help you navigate your way by showing you keystrokes for moving the cell cursor to a new cell, simple rules of data-entry etiquette, common causes of some formula error values, and a quick list of the best Excel 2010 features.

Cheat Sheet / Updated 03-28-2022

In Microsoft Excel 2019, you can use the keyboard to select cells and access hot key sequences that can greatly speed up the process of selecting program commands. Excel 2019 also makes it easy to set regional dates, share workbooks saved on your OneDrive, and add SmartArt graphics to your worksheets. In Excel 2019, you also can sort multiple fields in a data list, create a new pivot table, or add a description to a user-defined function. This Cheat Sheet is a handy reference to these Excel functions.

Cheat Sheet / Updated 03-21-2022

At first glance, you might have trouble making sense of the many menus, tabs, columns, and rows of the Excel 2016 user interface. However, you can figure out what you're doing by using keystrokes to move the cell cursor to a new cell, following simple rules of data-entry etiquette, discovering common causes of some formula error values, and a reading a quick list of the best Excel 2016 features.

Cheat Sheet / Updated 03-14-2022

At first glance, you might have a little trouble making sense of the Excel 2019 worksheet window with its many components. Just keep in mind that when you’re unsure of the purpose of a particular command button displayed in the Excel worksheet window, all you have to do is to position the mouse pointer on that button’s icon to have Excel display the button’s name, its shortcut keys (if it has them), and, often, a short description of the button’s function.

Cheat Sheet / Updated 03-10-2022

Companies and organizations are always interested in business intelligence — raw data that can be turned into actionable knowledge. This need for business intelligence manifests itself in many forms. Dashboards are reporting mechanisms that deliver business intelligence in a graphical form. Most data analysis benefits from a spreadsheet, so Excel is inherently part of any business-intelligence tool portfolio. This Cheat Sheet provides some useful information and tips for working with Excel dashboards and reports.

Cheat Sheet / Updated 03-09-2022

Microsoft Power Query has its own formula language and its own functions. Here are a handful of Power Query functions that will help you better massage and transform your data. These functions should prove to be some of the most useful in terms of data transformation.

Cheat Sheet / Updated 03-01-2022

Excel shortcut keys allow you to perform certain tasks using only the keyboard, the idea being that you increase your efficiency when you limit the number of instances your hands have to move back and forth from the keyboard to the mouse. Getting in the habit of using these shortcut keys can help you work more efficiently when using the Visual Basic Editor.

Cheat Sheet / Updated 01-27-2022

It’s easy to use Excel for many of your day-to-day number-crunching tasks, like determining your business’s average sale, computing classroom grades, or forecasting college expenses. Use this handy Cheat Sheet to discover great functions and tips to help you get the most out of Excel.

Article / Updated 01-10-2022

The following table provides a list of commonly used VBA statements that you might use when creating macros for Excel. For more information on a particular statement, consult Excel’s Help system. VBA Statement What It Does AppActivate Activates an application window Beep Sounds a tone via the computer's speaker Call Transfers control to another procedure ChDir Changes the current directory ChDrive Changes the current drive Close Closes a text file Const Declares a constant value Date Sets the current system date Declare Declares a reference to an external procedure in a Dynamic Link Library (DLL) DeleteSetting Deletes a section or key setting from an application's entry in the Windows Registry Dim Declares variables and (optionally) their data types Do-Loop Loops through a set of instructions End Used by itself, exits the program; also used to end a block of statements that begin with If, With, Sub, Function, Property, Type, or Select Erase Re-initializes an array Error Simulates a specific error condition Exit Do Exits a block of Do-Loop code Exit For Exits a block of For-Next code Exit Function Exits a Function procedure Exit Property Exits a property procedure Exit Sub Exits a subroutine procedure FileCopy Copies a file For Each-Next Loops through a set of instructions for each member of a collection For-Next Loops through a set of instructions a specific number of times Function Declares the name and arguments for a Function procedure Get Reads data from a text file GoSub...Return Branches to and returns from a procedure GoTo Branches to a specified statement within a procedure If-Then-Else Processes statements conditionally (the Else part is optional) Input # Reads data from a sequential text file Kill Deletes a file Let Assigns the value of an expression to a variable or property Line Input # Reads a line of data from a sequential text file Load Loads an object but doesn't show it Lock...Unlock Controls access to a text file Mid Replaces characters in a string with other characters MkDir Creates a new directory Name Renames a file or directory On Error Gives specific instructions for what to do in the case of an error On...GoSub Branches, based on a condition On...GoTo Branches, based on a condition Open Opens a text file Option Base Changes the default lower limit for arrays Option Compare Declares the default comparison mode when comparing strings Option Explicit Forces declaration of all variables in a module Option Private Indicates that an entire module is Private Print # Writes data to a sequential file Private Declares a local array or variable Property Get Declares the name and arguments of a Property Get procedure Property Let Declares the name and arguments of a Property Let procedure Property Set Declares the name and arguments of a Property Set procedure Public Declares a public array or variable Put Writes a variable to a text file RaiseEvent Fires a user-defined event Randomize Initializes the random number generator ReDim Changes the dimensions of an array Rem Specifies a line of comments (same as an apostrophe [']) Reset Closes all open text files Resume Resumes execution when an error-handling routine finishes RmDir Removes an empty directory SaveSetting Saves or creates an application entry in the Windows Registry Seek Sets the position for the next access in a text file Select Case Processes statements conditionally SendKeys Sends keystrokes to the active window Set Assigns an object reference to a variable or property SetAttr Changes attribute information for a file Static Declares variables at the procedure level so that the variables retain their values as long as the code is running and the project hasn't been reset. Stop Pauses the program Sub Declares the name and arguments of a Sub procedure Time Sets the system time Type Defines a custom data type Unload Removes an object from memory While...Wend Loops through a set of instructions as long as a certain condition remains true Width # Sets the output line width of a text file With Allows a shorthand way of accessing multiple properties for an object Write # Writes data to a sequential text file