Not all Excel 2016 worksheets come from templates. Many times, you need to create rather unique spreadsheets that aren't intended to function as standard models from which certain types of workbooks are generated. In fact, most of the spreadsheets that you create in Excel may be of this kind, especially if your business doesn't rely on the use of highly standardized financial statements and forms.
Planning your workbook
When creating a new workbook from scratch, you need to start by considering the layout and design of the data. When doing this mental planning, you may want to ask yourself some of the following questions:
Does the layout of the spreadsheet require the use of data tables (with both column and row headings) or lists (with column headings only)?
Do these data tables and lists need to be laid out on a single worksheet or can they be placed in the same relative position on multiple worksheets of the workbook (like pages of a book)?
Do the data tables in the spreadsheet use the same type of formulas?
Do some of the columns in the data lists in the spreadsheet get their input from formula calculation or do they get their input from other lists (called lookup tables) in the workbook?
Will any of the data in the spreadsheet be graphed, and will these charts appear in the same worksheet (referred to as embedded charts), or will they appear on separate worksheets in the workbook (called chart sheets)?
Does any of the data in the spreadsheet come from worksheets in separate workbook files?
How often will the data in the spreadsheet be updated or added to?
How much data will the spreadsheet ultimately hold?
Will the data in the spreadsheet be shared primarily in printed or online form?
All these questions are an attempt to get you to consider the basic purpose and function of the new spreadsheet before you start building it, so that you can come up with a design that is both economical and fully functional.
Economy
Economy is an important consideration because when you open a workbook, all its data is loaded into your computer's dynamic memory (known simply as memory). This may not pose any problems if the device you're running Excel 2016 on is one of the latest generation of PCs with more memory than you can conceive of using at one time, but it can pose quite a problem if you're running Excel on a small Windows tablet with a minimum of memory or smartphone with limited memory or share the workbook file with someone whose computer is not so well equipped. Also, depending on just how much data you cram into the workbook, you may even come to see Excel creep and crawl the more you work with it.
To help guard against this problem, make sure that you don't pad the data tables and lists in your workbook with extra empty "spacer" cells. Keep the tables as close together as possible on the same worksheet (with no more than a single blank column or row as a separator, which you can adjust to make as wide or high as you like) or — if the design allows — keep them in the same region of consecutive worksheets.
Functionality
Along with economy, you must pay attention to the functionality of the spreadsheet. This means that you need to allow for future growth when selecting the placement of its data tables, lists, and charts. This is especially important in the case of data lists because they have a tendency to grow longer and longer as you continue to add data, requiring more and more rows of the same few columns in the worksheet. This means that you should usually consider all the rows of the columns used in a data list as "off limits." In fact, always position charts and other supporting tables to the right of the list rather than somewhere below the last used row. This way, you can continue to add data to your list without ever having to stop and first move some unrelated element out of the way.
This spatial concern is not the same when placing a data table that will total the values both down the rows and across the columns table — for example, a sales table that sums your monthly sales by item with formulas that calculate monthly totals in the last row of the table and formulas that calculate item totals in the last column. In this table, you don't worry about having to move other elements, such as embedded charts or other supporting or unrelated data tables, because you use Excel's capability of expanding the rows and columns of the table from within.
As the table expands or contracts, surrounding elements move in relation to and with the table expansion and contraction. You do this kind of editing to the table because inserting new table rows and columns ahead of the formulas ensures that they can be included in the totaling calculations. In this way, the row and column of formulas in the data table acts as a boundary that floats with the expansion or contraction of its data but that keeps all other elements at bay.
Finalizing your workbook design
After you've more or less planned out where everything goes in your new spreadsheet, you're ready to start establishing the new tables and lists. Here are a few general pointers on how to set up a new data table that includes simple totaling calculations:
Enter the title of the data table in the first cell, which forms the left and top edges of the table.
Enter the row of column headings in the row below this cell, starting in the same column as the cell with the title of the table.
Enter the row headings down the first column of the table, starting in the first row that will contain data. (Doing this leaves a blank cell where the column of row headings intersects the row of column headings.)
Construct the first formula that sums columns of (still empty) cell entries in the last row of the table, and then copy that formula across all the rest of the table columns.
Construct the first formula that sums the rows of (still empty) cell entries in the last column of the table, and then copy that formula down the rest of the table rows.
Format the cells to hold the table values and then enter them in their cells, or enter the values to be calculated and then format their cells. (This is really your choice.)
When setting up a new data list in a new worksheet, enter the list name in the first cell of the table and then enter the row of column headings in the row below. Then, enter the first row of data beneath the appropriate column headings.
Opening new blank workbooks
Although you can open a new workbook from the Excel screen in the Backstage view when you first start the program that you can use in building a new spreadsheet from scratch, you will encounter occasions when you need to open your own blank workbook from within the Worksheet area itself. For example, if you launch Excel by opening an existing workbook that needs editing and then move on to building a new spreadsheet, you'll need to open a blank workbook (which you can do before or after closing the workbook with which you started Excel).
The easiest way to open a blank workbook is to press Ctrl+N. Excel responds by opening a new workbook, which is given a generic Book name with the next unused number (Book2, if you opened Excel with a blank Book1). You can also do the same thing in Backstage view by choosing File→New and then clicking the Blank Workbook thumbnail.
As soon as you open a blank workbook, Excel makes its document window active. To then return to another workbook that you have open (which you would do if you wanted to copy and paste some of its data into one of the blank worksheets), click its button on the Windows taskbar or press Alt+Tab until its file icon is selected in the dialog box that appears in the middle of the screen.
If you ever open a blank workbook by mistake, you can just close it right away by pressing Ctrl+W, choosing File→Close, or pressing Alt+FC. Excel then closes its document window and automatically returns you to the workbook window that was originally open at the time you mistakenly opened the blank workbook.