Searching for a spreadsheet solution for a work issue? Here are some 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: Formulas are mathematical equations that work in combination with data from other cells on the spreadsheet. Simple formulas are used to add or subtract numbers. Advanced formulas perform algebraic equations. Spreadsheet functions are formulas that are built into Excel.
There are several ways to change text data into numerical data, but using the VALUE function is usually the easiest solution. See Use Excel’s VALUE Function to Convert Text to Numbers. Conversely, if you want Excel to read numerical characters or a formula as text, just add an apostrophe (‘) at the beginning of the entry.
LUZ is a Brazilian company that produces and sells ready-to-use spreadsheets in Excel since 2013. Now, we are translating for English! See extra details about Microsoft Excel spreadsheets
Excel file formats: The XML-based and macro-enabled file format for Excel 2016, Excel 2013, Excel 2010, and Excel 2007. Stores VBA macro code or Excel 4.0 macro sheets (.xlm). .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 the Macintosh 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.
Some default data will be blank, for various reasons. If you need to delete these to maintain accuracy, especially when calculating the average value, the speedy way is to filter out all blank cells and delete them with one click. Choose the column you want to filter, go to Data->Filter, after the downward button shows, undo Select All and then pick up the last option, Blanks. All blank cells will show immediately. Go back to Home and click Delete directly, all of them will be removed.