In need of a spreadsheet solution for a practical issue? Here are a few Excel advices: For more than 20 years, until Excel 2007, Excel users could use only Simple Tables as a spreadsheet database. This type of database can come in two styles: horizontal and vertical. Horizontal spreadsheet databases have their dates in one title row, as shown in the figure below. Typically, the date columns use monthly “buckets,” but they could use any time period required. And the Codes column could contain General Ledger account numbers, SKUs, employee codes, whatever. To illustrate the power this database offers, check out my Excel dashboard templates, which get their data from this type of Excel database. To update your report each period in those templates, you just update the database, change the report date in one cell, then recalculate Excel. If you rely on Excel databases for your other reports and analyses, all your reports could update just as easily.
Spreadsheets are composed of columns and rows that create a grid of cells. Typically, each cell holds a single item of data. Here’s an explanation of the three types of data most commonly used in spreadsheet programs: Number data, also called values, is used in calculations. By default, numbers are right aligned in a cell. In addition to actual numbers, Excel also stores dates and times as numbers. Other spreadsheet programs treat dates and times as a separate data category. Problems arise when numbers are formatted as text data. This prevents them from being used in calculations.
Excel automatically recognizes dates entered in a familiar format. For example, if you enter 10/31, Oct 31, or 31 Oct, Excel returns the value in the default format 31-Oct. If you want to learn how to use dates with formulas, see Properly Enter Dates in Excel with the DATE Function.
LUZ is a Brazilian company that produces and sells ready-to-use spreadsheets in Excel since 2013. Now, we are translating for English! Read extra details about business spreadsheets
Excel file formats: The binary file format (BIFF12) for Excel 2010 and Excel 2007. .xls: The Excel 97 – Excel 2003 Binary file format (BIFF8).
Text file formats: .txt Saves a workbook as a tab-delimited text file for use on the MS-DOS operating system, and ensures that tab characters, line breaks, and other characters are interpreted correctly. Saves only the active sheet. .csv Saves a workbook as a comma-delimited text file for use on another Windows operating system, and ensures that tab characters, line breaks, and other characters are interpreted correctly. Saves only the active sheet.
Excel Tips and Tricks!
You may know the way to add one new row or column, but it really wastes a lot of time if you need to insert more than one of these by repeating this action X number of times. The best way is to drag and select X rows or columns (X is two or more) if you want to add X rows or columns above or left. Right click the highlighted rows or columns and choose Insert from the drop down menu. New rows will be inserted above the row or to the left of the column you first selected.
You may know how to activate the speedy search by using the shortcut Ctrl + F, but there are two main wild cards—Question Mark and Asterisk—used in Excel spreadsheets to activate a vague search. This is used when you are not sure about the target result. Question Mark stands for one character and Asterisk represents one or more characters. What if you need to search Question Mark and Asterisk as a target result? Don’t forget add a Wave Line in front.