How to create a dark matter box pattern in microsoft excel
You can add emphasis to selected cells in an Excel 2010 worksheet by changing the fill color or applying a pattern or gradient effect to the cells. If you’re using a black-and-white printer, restrict your color choices to light gray in the color palette and use a simple pattern for cells that contain text so that the text remains legible.
Applying a fill color
To choose a new fill color for a cell selection, follow these steps:
On the Home tab, in the Font group, click the Fill Color button’s drop-down menu.
The Fill Color palette appears.
Select the color you want to use in the drop-down palette.
Excel’s Live Preview lets you see what the cell selection looks like in a particular fill color when you move the mouse pointer over the color swatches before you click the desired color.
Adding patterns to cells
Follow these steps to choose a pattern for a cell selection:
Click the Font dialog box launcher on the Home tab (or press Ctrl+1).
The Font dialog box launcher is the small icon in the bottom-right corner of the Font group. The Format Cells dialog box appears.
Click the Fill tab.
Click a pattern swatch from the Pattern Style button’s drop-down menu.
Click a pattern color from the Pattern Color button’s drop-down palette.
The Sample box displays the selected pattern and color.
(Optional) To add a fill color to the background of the pattern, click its color swatch in the Background Color section.
Applying a gradient effect
To add a gradient effect to a cell selection, follow these steps:
Press Ctrl+1 to open the Format Cells dialog box and then click the Fill tab.
Click the Fill Effects button.
The Fill Effects dialog box appears, with controls that enable you to define the two colors to use, as well as shading style and variant.
Select the two colors you want to use in the Colors section.
Select one of the Shading Styles options to choose the type of gradient pattern you want to use; then click the variant that you want to use.
The Sample box displays the current selections.
Click OK two times to close both dialog boxes.
You can remove fill colors, patterns, and gradients assigned to a cell selection by clicking the No Fill option on the Fill Color button’s drop-down menu on the Home tab.
If you have historical time-based data, you can use it to create a forecast. When you create a forecast, Excel creates a new worksheet that contains both a table of the historical and predicted values and a chart that expresses this data. A forecast can help you predict things like future sales, inventory requirements, or consumer trends.
Information about how the forecast is calculated and options you can change can be found at the bottom of this article.
Create a forecast
In a worksheet, enter two data series that correspond to each other:
A series with date or time entries for the timeline
A series with corresponding values
These values will be predicted for future dates.
Note: The timeline requires consistent intervals between its data points. For example, monthly intervals with values on the 1st of every month, yearly intervals, or numerical intervals. It’s okay if your timeline series is missing up to 30% of the data points, or has several numbers with the same time stamp. The forecast will still be accurate. However, summarizing data before you create the forecast will produce more accurate forecast results.
Select both data series.
Tip: If you select a cell in one of your series, Excel automatically selects the rest of the data.
On the Data tab, in the Forecast group, click Forecast Sheet.
In the Create Forecast Worksheet box, pick either a line chart or a column chart for the visual representation of the forecast.
In the Forecast End box, pick an end date, and then click Create.
Excel creates a new worksheet that contains both a table of the historical and predicted values and a chart that expresses this data.
You’ll find the new worksheet just to the left (“in front of”) the sheet where you entered the data series.
Customize your forecast
If you want to change any advanced settings for your forecast, click Options.
You’ll find information about each of the options in the following table.
Pick the date for the forecast to begin. When you pick a date before the end of the historical data, only data prior to the start date are used in the prediction (this is sometimes referred to as “hindcasting”).
Starting your forecast before the last historical point gives you a sense of the prediction accuracy as you can compare the forecasted series to the actual data. However, if you start the forecast too early, the forecast generated won’t necessarily represent the forecast you’ll get using all the historical data. Using all of your historical data gives you a more accurate prediction.
If your data is seasonal, then starting a forecast before the last historical point is recommended.
Check or uncheck Confidence Interval to show or hide it. The confidence interval is the range surrounding each predicted value, in which 95% of future points are expected to fall, based on the forecast (with normal distribution). Confidence interval can help you figure out the accuracy of the prediction. A smaller interval implies more confidence in the prediction for the specific point. The default level of 95% confidence can be changed using the up or down arrows.
Seasonality is a number for the length (number of points) of the seasonal pattern and is automatically detected. For example, in a yearly sales cycle, with each point representing a month, the seasonality is 12. You can override the automatic detection by choosing Set Manually and then picking a number.
Note: When setting seasonality manually, avoid a value for less than 2 cycles of historical data. With less than 2 cycles, Excel cannot identify the seasonal components. And when the seasonality is not significant enough for the algorithm to detect, the prediction will revert to a linear trend.
Change the range used for your timeline here. This range needs to match the Values Range.
Change the range used for your value series here. This range needs to be identical to the Timeline Range.
Fill Missing Points Using
To handle missing points, Excel uses interpolation, meaning that a missing point will be completed as the weighted average of its neighboring points as long as fewer than 30% of the points are missing. To treat the missing points as zeros instead, click Zeros in the list.
Aggregate Duplicates Using
When your data contains multiple values with the same timestamp, Excel will average the values. To use another calculation method, such as Median or Count, pick the calculation you want from the list.
Include Forecast Statistics
Check this box if you want additional statistical information on the forecast included in a new worksheet. Doing this adds a table of statistics generated using the FORECAST.ETS.STAT function and includes measures, such as the smoothing coefficients (Alpha, Beta, Gamma), and error metrics (MASE, SMAPE, MAE, RMSE).
Formulas used in forecasting data
When you use a formula to create a forecast, it returns a table with the historical and predicted data, and a chart. The forecast predicts future values using your existing time-based data and the AAA version of the Exponential Smoothing (ETS) algorithm.
The table can contain the following columns, three of which are calculated columns:
Historical time column (your time-based data series)
Historical values column (your corresponding values data series)
Forecasted values column (calculated using FORECAST.ETS)
Two columns representing the confidence interval (calculated using FORECAST.ETS.CONFINT). These columns appear only when the Confidence Interval is checked in the Options section of the box..
Download a sample workbook
Need more help?
You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.
This article shows you how to automatically apply shading to every other row or column in a worksheet.
There are two ways to apply shading to alternate rows or columns —you can apply the shading by using a simple conditional formatting formula, or, you can apply a predefined Excel table style to your data.
One way to apply shading to alternate rows or columns in your worksheet is by creating a conditional formatting rule. This rule uses a formula to determine whether a row is even or odd numbered, and then applies the shading accordingly. The formula is shown here:
Note: If you want to apply shading to alternate columns instead of alternate rows, enter =MOD(COLUMN(),2)=0 instead.
On the worksheet, do one of the following:
To apply the shading to a specific range of cells, select the cells you want to format.
To apply the shading to the entire worksheet, click the Select All button.
On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click New Rule.
In the New Formatting Rule dialog box, under Select a Rule Type, click Use a formula to determine which cells to format.
In the Format values where this formula is true box, enter =MOD(ROW(),2)=0, as shown in the following illustration.
In the Format Cells dialog box, click the Fill tab.
Select the background or pattern color that you want to use for the shaded rows, and then click OK.
At this point, the color you just selected should appear in the Preview window in the New Formatting Rule dialog box.
To apply the formatting to the cells on your worksheet, click OK
Note: To view or edit the conditional formatting rule, on the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules.
Another way to quickly add shading or banding to alternate rows is by applying a predefined Excel table style. This is useful when you want to format a specific range of cells, and you want the additional benefits that you get with a table, such the ability to quickly display total rows or header rows in which filter drop-down lists automatically appear.
By default, banding is applied to the rows in a table to make the data easier to read. The automatic banding continues if you add or delete rows in the table.
If you find you want the table style without the table functionality, you can convert the table to a regular range of data. If you do this, however, you won’t get the automatic banding as you add more data to your range.
On the worksheet, select the range of cells that you want to format.
On the Home tab, in the Styles group, click Format as Table.
Under Light, Medium, or Dark, click the table style that you want to use.
Tip: Custom table styles are available under Custom after you create one or more of them. For information about how to create a custom table style, see Format an Excel table.
In the Format as Table dialog box, click OK.
Notice that the Banded Rows check box is selected by default in the Table Style Options group.
If you want to apply shading to alternate columns instead of alternate rows, you can clear this check box and select Banded Columns instead.
If you want to convert the Excel table back to a regular range of cells, click anywhere in the table to display the tools necessary for converting the table back to a range of data.
On the Design tab, in the Tools group, click Convert to Range.
Tip: You can also right-click the table, click Table, and then click Convert to Range.
Note: You cannot create custom conditional formatting rules to apply shading to alternate rows or columns in Excel for the web.
When you create a table in Excel for the web, by default, every other row in the table is shaded. The automatic banding continues if you add or delete rows in the table. However, you can apply shading to alternate columns. To do that:
Select any cell in the table.
Click the Table Design tab, and under Style Options, select the Banded Columns checkbox.
To remove shading from rows or columns, under Style Options, remove the checkbox next to Banded Rows or Banded Columns.
Need more help?
You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.
You can apply gridlines or borders to your Microsoft Excel worksheets. Gridlines are the faint, gray-blue lines you see onscreen that separate the rows and columns. (By default, gridlines appear onscreen but not in print.) Borders are the lines that appear around one or more sides of each cell.
To control how gridlines appear, display the Page Layout tab and then mark or clear the View and/or Print check boxes in the Sheet Options/Gridlines group. (There Gridlines check box on the View tab controls only the onscreen display, not the print setting.)
Borders can be any color or thickness you want. Borders always display onscreen and always print, regardless of settings. Borders are useful for helping the reader’s eyes follow the text across the printed page, and for identifying which parts of a spreadsheet go together logically.
The easiest way to apply and format borders is to use the Borders button’s drop-down list on the Home tab. Select the range of cells to which you want to apply the border, choose Home→Font→Borders. From the list of borders that appears, choose one that best represents the side(s) to which you want to apply the border.
The border will apply to the outside edges of the range you select. So, for example, choosing Top Border applies a top border only to the cells in the top row of the range, not to the top of every cell in that range.
To add a border on all sides of each cell in the range: Choose All Borders.
To remove the border from all sides of all cells in the selected range: Choose No Border.
To add borders on more than one side but not all sides: Repeat the process several times, each time choosing one individual side.
If you want to choose a specific color, style, or weight for the border, choose Home→Font→Borders→More Borders. The Format Cells dialog box appears with the Border tab displayed. In the Style area, click the desired line style. In the Color area, open the drop-down list and click the desired color. In the Presets area, click the preset for the sides you want to apply the border to: None, Outline, or Inside. Click OK.
If you want the border around each side of each cell, click both Outline and Inside.
If you choose a border style and color but don’t apply it to any sides of the range, that’s like selecting nothing at all.
Last Updated: June 4, 2020
wikiHow is a “wiki,” similar to Wikipedia, which means that many of our articles are co-written by multiple authors. To create this article, 9 people, some anonymous, worked to edit and improve it over time.
This article has been viewed 39,850 times.
You’ll learn to make the “One Sphere” pattern and image below, and the dozens of variations the file permits therefrom.
- Become familiar with the basic image to be created:
Start a new workbook by saving the previous workbook from How to Create an Equilateral Springs Pattern in Microsoft Excel under a new name. Save the workbook into a logical file folder.
- (dependent upon the tutorial data above)
The following was accomplished by setting Data worksheet cell D1457 to 26 decimals and entering the formula “=1-D7”. The desired result is 0, so Goal Seeking is done on that cell via Tools Goal seek, cell D1457, to value 0, by changing the value in cell D2 (Divisor). It’s set to do 200 calculations in Preferences at a tolerance of 15 decimal places but arrived at 26 zeroes somehow and an answer of 976,918,336,957,018 for Divisor (just barely within range). I set the Horizontal and Vertical Axes per Min and Max equations over columns F and G, adjusting outwards a smidgin. This is the result for S_Count = 2, after also using Grabber instead of copy for app Preview where I save files as JPGs, and using Copy Picture instead of Copy and Paste to the Saves worksheet, The horizontal aspect was decreased from the chart version to the Pasted Picture version in the final Pasted Picture format. Can anyone figure out the volume of these spheroids? If so, please write an article with your methods and include it here in this article’s Related wikiHows.
Today’s Author: Eric Patterson, a program manager on the Excel team. Eric is going to discuss applying pattern fills to chart data points and includes a sample add-in for this purpose.
Overview
In Excel 2007, the interface for applying Pattern fills to chart elements has been removed in favor of the interface for applying Picture and Texture fills. Charts formatted with pattern fills in previous versions of Excel will retain and display the pattern fills when opened in Excel 2007.
Here is an example of a chart with pattern fills applied:
While the interface for applying Pattern fills has been removed, support is still available via Excel’s object model. The FillFormat.Patterned Method can be used to apply pattern fills to chart elements, such as datapoints in a series. I will describe how the object model can be used to create the chart shown above. A sample add-in with an interface for applying pattern fills to chart elements also accompanies this blog entry.
The sample add-in with RibbonX and VBA code is here:
Applying Patterns using the Visual Basic Immediate Window
To demonstrate how pattern formatting can be applied, let’s start with a simple Pie Chart. After creating the chart, click on the Q1 Pie slice once to select the series, then a second time to select the Q1 data point. It should look like this:
While the Q1 datapoint is selected, launch the Visual Basic Editor by pressing ALT+F11. In the Visual Basic Editor, press CTRL+G to display and move to the Immediate Window.
In the Immediate Window, type the following and press enter: selection.fill.patterned (4)
This will apply the 4th indexed pattern to the selected chart element. At this point the chart will be updated to look like this:
Repeat the Process
To finish the chart, the above process can be repeated for the other 3 data points. Select them in turn, switch to the VBE and type the Selection.Fill.Patterned command for each of them. The indexes of the patterns that I used are 26, 15 and 12 for Q2, Q3 and Q4 respectively. At this point, the chart will look like this:
Sample Add-in
Next I will demonstrate how an add-in can be created to quickly apply Pattern Fills using a gallery control in Excel.
THE RIBBONX
We’ll start off with the RibbonX code. If you aren’t familiar with RibbonX, the short explanation is that it’s the XML that defines what the Ribbon will look like. Specifically for us, the RibbonX will define where we want to put our gallery, and the items in the gallery (including pictures). For more detailed information on RibbonX, see these links:
The RibbonX we will create will live in the document and travel with it. We will have to use a tool to get the RibbonX in the document. The one I’ll use for this example is called the “Office 2007 Custom UI Editor”. You can download it freely from here:
After you download and install the tool, boot it up and open the “PatternUI.xlam” document that accompanies this blog entry. You should see:
To create this, I started by using the Inset Icons command in the UI Editor and selected pictures of each of the pattern fills that I had previously created. I made sure to name the files such that they matched the indexes for the patterns in the object model.
Once the icons were added, I then typed in the Ribbon XML below that specifies which chart tab (ChartToolsFormat) and what the name of the group should be (Patterns). Within the Patterns group in the ribbon, I specified the items to be included in the gallery that corresponded to the icons that I had previously added. I chose to size the gallery to 8 columns and 7 rows.
Here is the bulk of the XML. The Gallery elements were repeated for all 54 elements:
After entering all of the Ribbon XML, I used the Generate Callbacks command in the UI Editor, which produced this callback:
This callback can then be entered into a module in the VBE.
VBA CODE
In the code module in VBA, just 2 lines of code need to be added to the callback. The first line is very simple error checking in case you attempt to use the gallery to apply formatting when an invalid object is selected. The second line is the same command that we typed into the Immediate pane above. In this instance, the argument for the Patterned Method is “index+1”. Index is the gallery index that is returned. This index is zero based so the “+1” is used to ensure that the gallery index matches the pattern index.
‘Callback for PatternGallery onAction
Sub InsertPattern(control As IRibbonControl, id As String, index As Integer)
On Error Resume Next
Selection.Fill.Patterned (index + 1)
End SubMake this an Add-in
The final step is to save this file as an addin using the Save As command.
The Finished Product
After defining the RibbonX and VBA code, opening up the add-in will show a pattern dropdown on the Chart Format tab when a chart is selected. To use it, you select a chart series or data point and then click on one of the gallery entries.
Pattern fills are primarily used for Black and White printing. If you are using pattern fills for this or other purposes we would like to hear from you.
Summary
The Outlook Rules Wizard allows you to “run a script” when you create a specific rule. As a developer, this allows you to perform an action on incoming mail that is not possible using the regular features of the Rules Wizard.
More Information
Although the Rules Wizard refers to the custom code as “script,” you must create the code in Outlook Visual Basic for Applications, not in Microsoft Visual Basic Scripting Edition (VBScript) or other scripting languages such as Microsoft JScript. Also, Outlook Visual Basic for Applications is not designed to be deployed, so deployment of this custom code requires manual configuration on each user’s computer. You cannot create the custom code in an Outlook COM Add-in. For additional information about limitations related to distributing Visual Basic for Applications projects, click the following article numbers to view the articles in the Microsoft Knowledge Base:
290779 Description of managing and distributing Outlook 2002 Visual Basic for Applications (VBA) projects
Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs.
If you have limited programming experience, you may want to contact a Microsoft Certified Partner or Microsoft Advisory Services.
For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site: Microsoft Support
To implement the custom code to process the message, create a subroutine in Visual Basic for Applications. The name of the subroutine does not matter, but it must accept one argument because the Rules Wizard will pass a mail message (MailItem) or meeting request (MeetingItem) to the subroutine. The argument must by of type MailItem or MeetingItem, otherwise the subroutine will not be available in the Rules Wizard. You cannot create one subroutine to handle both types of items by defining the argument to be of type Object. The following Outlook Visual Basic for Applications code illustrates how to create the subroutines:
You can put the subroutine in any module, including ThisOutlookSession, but if you move the subroutine to another module or change the subroutine’s name, you must modify the rule to point to the updated subroutine.
Summary
If you have a Microsoft Visual Basic for Applications macro that takes a long time to finish, you may want to give the user an indication that the macro is progressing correctly. This article describes how to create a progress bar with a user form in Microsoft Excel.
More Information
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure. However, they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.
Create the User Form
In the following example, a Visual Basic subroutine populates a large range of cells with a random number. The indicator shows you that the macro is running correctly.
Open a new workbook in Excel.
In Microsoft Office Excel 2003 and in earlier versions of Excel, click Tools, point to Macro, and then click Visual Basic Editor.
In Microsoft Office Excel 2007, click Visual Basic in the Code group on the Developer tab.
Note To display the Developer tab in the Ribbon, follow these steps:
- Start Excel 2007, click the Microsoft Office Button, and then click Excel Options.
- In the Excel Options dialog box, click Popular, and then click to select the Show Developer tab in the Ribbon check box.
On the Insert menu, click UserForm.
Draw a Label control on the user form.
Change the following properties of the Label control to the following values: |Property| Value| |—————|—————————–| |Caption| Now updating. Please wait. | Note If the Properties window is not visible, click Properties Window on the View menu.
Draw a Frame control on the user form.
Change the following properties of the Frame control to the following values: |Property |Value| |———|——————–| |Name FrameProgress|
Draw a Label control on the Frame control.
Change the following properties of the Label control to the following values: |Property| Value| |———-|—————————| |Name |LabelProgress| |BackColor |&H000000FF&| |SpecialEffect |fmSpecialEffectRaised|
Type the Macro Code
Double-click the user form to open the Code window for the user form.
In the module, type the following code for the UserForm_Activate event:
On the Insert menu, click Module.
In the Code window for the module, type the following code:
Return to Excel.
In Excel 2003 and in earlier versions of Excel, point to Macro on the Tools menu, and then click Macros.
In Excel 2007, click Macros in the Code group on the Developer tab.
In the Macro dialog box, click to select ShowUserForm, and then click Run.
A dialog box that has a red progress bar appears. The progress bar increases as the Main subroutine populates the cells on the worksheet.
The ShowUserForm subroutine shows the user form. The procedure that is attached to the Activate event of the user form calls the Main subroutine. The Main subroutine populates cells with random numbers. Additionally, the subroutine calls the UpdateProgressBar subroutine that updates the Label control on the user form.
Note When you use this technique, your macro takes just a bit longer to finish its intended tasks.
Одна учетная запись. Одна панель управления. Добро пожаловать на страницу учетной записи.
Нет учетной записи Microsoft?
Узнайте, как начать работу с продуктами Microsoft.
Безопасность
Изменяйте пароль, обновляйте секретные вопросы и поддерживайте актуальность важных сведений учетной записи.
Конфиденциальность
Просматривайте историю поиска, посещений веб-страниц, местоположений и многое другое.
Семья
Обезопасьте свою семью в Интернете и оставайтесь на связи, даже когда находитесь вдали друг от друга.
Платежи и выставление счетов
Обновляйте платежную информацию, просматривайте историю заказов, используйте подарочные карты и получайте помощь с оплатой.
Подписки
Быстро продлевайте подписки и управляйте службами Microsoft из единой панели.
Устройства
Найдите и заблокируйте утерянное или украденное устройство Windows 10, сотрите с него данные или запланируйте ремонт и получите помощь.
Справка
Получите помощь и советы экспертов по продуктам и службам Microsoft.
Войдите в учетную запись и начните работу
Пользуйтесь всеми любимыми продуктами и службами Microsoft с помощью единого входа. От Office и Windows до Xbox и Skype – одно имя пользователя и один пароль объединяют вас с самыми важными файлами, фотографиями, людьми и контентом.
Outlook
Почта и календарь в одном. Все, что нужно для эффективной работы и общения дома и в дороге.
Skype
Оставайтесь на связи с близкими на всех устройствах с помощью текстовых сообщений, голосовых и видеозвонков Skype.
Microsoft Edge
Быстрый браузер для эффективной работы в сети: с ним удобно искать информацию, узнавать новое и систематизировать закладки.
Интеллектуальные функции поиска помогают быстро и удобно находить все необходимое — ответы, новости, развлечения и многое другое.
Играйте в любимые игры где угодно. Играйте, общайтесь с друзьями и заходите в сообщества на Xbox One, компьютерах с Windows 10 и мобильных устройствах.
Office
Выполняйте важные задачи с Word, Excel, PowerPoint и не только. Каким будет ваш следующий успех с Office 365?
OneDrive
Бесплатно сохраняйте и просматривайте файлы и фотографии на своих устройствах. В учетной записи Microsoft доступно 5 ГБ хранилища, и вы сможете добавить больше при необходимости.
Windows
Найдите и заблокируйте утерянное или украденное устройство Windows 10, сотрите с него данные или запланируйте ремонт и получите помощь.
Mixer
Mixer — это место, где игроки собираются, чтобы развлекаться, праздновать победы и делиться лучшими моментами.
Microsoft Store
Воспользуйтесь лучшими предложениями Microsoft — от приложений для работы и творчества до игр и развлечений.
Кортана
Экономьте время и будьте организованными — Кортана помогает решать повседневные задачи, чтобы вы не отвлекались на мелочи.
Благодаря MSN полезная информация доступна в любое время.
How to control AutoComplete in Excel
The AutoComplete option in Microsoft Excel will automatically fill in data as you type, but it isn’t useful in every circumstance.
Fortunately, you can disable or enable AutoComplete whenever you like.
When You Should and Shouldn’t Use AutoComplete
This feature is helpful when entering data into a worksheet that contains lots of duplicates. With AutoComplete on, when you start typing, it will auto-fill the rest of the information from the context around it, to speed up data entry.
Say you’re entering the same name, address, or other information into multiple cells. Without AutoComplete, you’d have to retype the data or copy and paste it over and over, which wastes time.
For example, if you typed “Mary Washington” in the first cell and then many other things in the following ones, like “George” and “Harry,” you can type “Mary Washington” again a lot faster by just typing “M” and then pressing Enter so that Excel will auto-type the full name.
You can do this with any number of text entries in any cell in any series, meaning that you could then type “H” at the bottom to have Excel suggest “Harry,” and then type “M” again if you need to have that name auto-completed. There’s no need to copy or paste any data.
However, AutoComplete isn’t always your friend. If you don’t need to duplicate anything, it will still auto-suggest it each time you start typing something that shares the same first letter as the previous data, which can often be more of a bother than a help.
Enable/Disable AutoComplete in Excel
The steps for enabling or disabling AutoComplete in Microsoft Excel are different depending on the version you’re using:
In Excel 2019, 2016, 2013, and 2010
Navigate to the File > Options menu.
In the Excel Options window, open Advanced on the left.
Under the Editing Options section, toggle Enable AutoComplete for cell values on or off depending on whether you want to turn this feature on or disable it.
Click or tap OK to save the changes and continue using Excel.
In Excel 2007
Click the Office Button.
Choose Excel Options to bring up the Excel Options dialog box.
Choose Advanced in the pane to the left.
Click the box next to the Enable AutoComplete for cell values option box to turn this feature on or off.
Choose OK to close the dialog box and return to the worksheet.
In Excel 2003
Navigate to Tools > Options from the menu bar to open the Options dialog box.
Choose the Edit tab.
Toggle AutoComplete on/off with the checkmark box next to the Enable AutoComplete for cell values option.
Click OK to save the changes and return to the worksheet.
@howtogeek
May 6, 2016, 10:24am EDTAmong the new features in Microsoft Office 2016 are some improvements to the user interface. For example, they added a background image to the title bar in each Office program, and an improved dark theme. Customizing the background and theme is easy, and we’ll show you how to do it.
By default, the background image is clouds, but there are several other background images from which you can choose. You cannot add your own images, but if you don’t like any of the included images, you can choose to not have a background image on the title bar at all.
We’ll show you how to change the title bar background and theme in Word, but the procedure is the same in Excel, PowerPoint, and Outlook as well. To begin, click the “File” tab.
On the backstage screen, click “Options” in the list of items on the left.
The General screen displays by default. On the right side, in the Personalize your copy of Microsoft Office section, select an option from the “Office Background” drop-down list. If you don’t want a background image on the title bar, select “No Background”.
If you don’t see a background image on the title and the Office Background drop-down list is not available on the Options dialog box, that means you aren’t signed into your Microsoft account in Office. The Office Background feature is only available when you are signed into your Microsoft account. If you’ve signed in to Windows 10 using your Microsoft account, you should have access to the Office Background option, unless you specifically sign out of Office.
If you use a local account in Windows 10, or you’re using an earlier version of Windows, you can access the Office Background feature by signing into your Microsoft account in any Office program, using the “Sign in” link on the right side of the title bar.
To change the color theme, select an option from the “Office Theme” drop-down list. The Dark Gray and Black themes are now available as dark themes; however, the Black theme is only available to Office 365 subscribers. The Colorful theme is a different color in each program, such as blue in Word, green in Excel, and orange in PowerPoint.
Once you’ve made your changes, click “OK” to accept them and close the Options dialog box.
The newly selected background image (if any) and theme is applied to the title bar in the currently open Office program.
The selected background image and theme is applied to all Office programs. You cannot select a different image and theme for each program.
Scatter charts and line charts look very similar, especially when a scatter chart is displayed with connecting lines. However, the way each of these chart types plots data along the horizontal axis (also known as the x-axis) and the vertical axis (also known as the y-axis) is very different.
Note: For information about the different types of scatter and line charts, see Available chart types in Office.
Before you choose either of these chart types, you might want to learn more about the differences and find out when it’s better to use a scatter chart instead of a line chart, or the other way around.
The main difference between scatter and line charts is the way they plot data on the horizontal axis. For example, when you use the following worksheet data to create a scatter chart and a line chart, you can see that the data is distributed differently.
In a scatter chart, the daily rainfall values from column A are displayed as x values on the horizontal (x) axis, and the particulate values from column B are displayed as values on the vertical (y) axis. Often referred to as an xy chart, a scatter chart never displays categories on the horizontal axis.
A scatter chart always has two value axes to show one set of numerical data along a horizontal (value) axis and another set of numerical values along a vertical (value) axis. The chart displays points at the intersection of an x and y numerical value, combining these values into single data points. These data points may be distributed evenly or unevenly across the horizontal axis, depending on the data.
The first data point to appear in the scatter chart represents both a y value of 137 (particulate) and an x value of 1.9 (daily rainfall). These numbers represent the values in cell A9 and B9 on the worksheet.
In a line chart, however, the same daily rainfall and particulate values are displayed as two separate data points, which are evenly distributed along the horizontal axis. This is because a line chart only has one value axis (the vertical axis). The horizontal axis of a line chart only shows evenly spaced groupings (categories) of data. Because categories were not provided in the data, they were automatically generated, for example, 1, 2, 3, and so on.
This is a good example of when not to use a line chart.
A line chart distributes category data evenly along a horizontal (category) axis , and distributes all numerical value data along a vertical (value) axis.
The particulate y value of 137 (cell B9) and the daily rainfall x value of 1.9 (cell A9) are displayed as separate data points in the line chart. Neither of these data points is the first data point displayed in the chart — instead, the first data point for each data series refers to the values in the first data row on the worksheet (cell A2 and B2).
Axis type and scaling differences
Because the horizontal axis of a scatter chart is always a value axis, it can display numeric values or date values (such as days or hours) that are represented as numerical values. To display the numeric values along the horizontal axis with greater flexibility, you can change the scaling options on this axis the same way that you can change the scaling options of a vertical axis.
Because the horizontal axis of a line chart is a category axis, it can be only a text axis or a date axis. A text axis displays text only (non-numerical data or numerical categories that are not values) at evenly spaced intervals. A date axis displays dates in chronological order at specific intervals or base units, such as the number of days, months, or years, even if the dates on the worksheet are not in order or in the same base units.
The scaling options of a category axis are limited compared with the scaling options of a value axis. The available scaling options also depend on the type of axis that you use.
Scatter charts are commonly used for displaying and comparing numeric values, such as scientific, statistical, and engineering data. These charts are useful to show the relationships among the numeric values in several data series, and they can plot two groups of numbers as one series of xy coordinates.
Line charts can display continuous data over time, set against a common scale, and are therefore ideal for showing trends in data at equal intervals or over time. In a line chart, category data is distributed evenly along the horizontal axis, and all value data is distributed evenly along the vertical axis. As a general rule, use a line chart if your data has non-numeric x values — for numeric x values, it is usually better to use a scatter chart.
Consider using a scatter chart instead of a line chart if you want to:
Change the scale of the horizontal axis Because the horizontal axis of a scatter chart is a value axis, more scaling options are available.
Use a logarithmic scale on the horizontal axis You can turn the horizontal axis into a logarithmic scale.
Display worksheet data that includes pairs or grouped sets of values In a scatter chart, you can adjust the independent scales of the axes to reveal more information about the grouped values.
Show patterns in large sets of data Scatter charts are useful for illustrating the patterns in the data, for example by showing linear or non-linear trends, clusters, and outliers.
Compare large numbers of data points without regard to time The more data that you include in a scatter chart, the better the comparisons that you can make.
Consider using a line chart instead of a scatter chart if you want to:
Use text labels along the horizontal axis These text labels can represent evenly spaced values such as months, quarters, or fiscal years.
Use a small number of numerical labels along the horizontal axis If you use a few, evenly spaced numerical labels that represent a time interval, such as years, you can use a line chart.
Use a time scale along the horizontal axis If you want to display dates in chronological order at specific intervals or base units, such as the number of days, months, or years, even if the dates on the worksheet are not in order or in the same base units, use a line chart.
Excel’s power comes from its simplicity. At its core, Excel is three things: cells of data laid out in rows and columns, a powerful calculation engine, and a set of tools for working with the data. The result is an incredibly flexible app that hundreds of millions of people use daily in a wide variety of jobs and industries around the world.
Today, we’re pleased to announce four new artificial intelligence (AI) features that make Excel even more powerful:
- Ideas
- New data types
- Insert Data from Picture
- Dynamic arrays
Introducing intelligent suggestions with Ideas
Ideas is an AI-powered insights service that helps people take advantage of the full power of Office. Proactively surfacing suggestions that are tailored to the task at hand, Ideas helps users create professional documents, presentations, and spreadsheets in less time. In Excel, for instance, Ideas helps identify trends, patterns, and outliers in a data set—helping customers analyze and understand their data in seconds. Ideas will be generally available in Excel soon and will also begin rolling out in preview to other apps starting with PowerPoint Online. Simply click the lightning bolt icon in Excel or PowerPoint Online and Ideas will start making recommendations. Read more about Ideas in this support article.
Making new data types generally available
Excel has always been great at helping people make the most of numbers. But now Excel can do even more: It can recognize real-world concepts, starting with Stocks and Geography. This new AI-powered capability turns a single, flat piece of text into an interactive entity containing layers of rich information. For instance, by converting a list of countries in a workbook to “Geography” entities, customers can weave location data into an analysis of their own data. And this new capability—though deceptively simple—opens a whole new world of possibilities. As we add new data types over time, Excel’s rows, columns, cells, logic engine, and tools can be used to organize, analyze, and reason over any combination of numbers and sophisticated entities. The Stocks and Geography data types are rolling out to general availability next month. Read more about data types in this support article.
Saying goodbye to manual data entry
With Insert Data from Picture, you can take a picture of a printed data table with your Android device and convert that analog information into an Excel spreadsheet with a single click. New image recognition functionality automatically converts the picture to a fully editable table in Excel, eliminating the need for you to manually enter data. Insert Data from Picture will be available in preview for the Excel Android app soon.
Calculating with ease using dynamic arrays
With dynamic arrays, we continue to invest in making advanced formulas easier to use. Using dynamic arrays, any formula that returns an array of values will seamlessly “spill” into neighboring unoccupied cells, making it as easy to get an array of values returned as it is to work on a single cell. You can immediately harness the power of dynamic arrays by using one of the new FILTER, UNIQUE, SORT, SORTBY, SEQUENCE, SINGLE, and RANDARRAY functions to build spreadsheets that would previously have been nearly impossible. So now, rather than writing many complex formulas to solve a multi-cell problem, you can write one simple formula and get an array of values returned. Read more about dynamic arrays in this Tech Community blog.
Faster LOOKUP and MATCH
We’re not only adding new capabilities to Excel, we’re also continually improving the features customers already know and love. For example, we have invested in significant performance improvements for important lookup functions. We’re pleased to announce that VLOOKUP, HLOOKUP, and MATCH functions operating on large data sets will now execute in seconds instead of minutes. We’ve also improved performance on many key operations like copy/paste, undo, conditional formatting, cell editing, cell selection, filtering, file open, and programmability. Read more about these improvements and capabilities in this Tech Community blog.
We’re excited about these new features and hope you are, too. For us, Excel isn’t just a tool—it’s a way of life! And today’s announcements not only deliver incremental improvements in data handling and performance, they also push the app into new territory with new data types and AI-powered analysis services. We look forward to showing you more at Ignite this week and can’t wait to see what you do with it all. As always, we’d love to hear from you, so please send us your thoughts through UserVoice—and keep the conversation going by following Excel on Facebook and Twitter.
Availability
- Ideas will be available soon.
- New data types are rolling out to users of Excel in Office 365 (in the English language only) soon.
- Performance improvements are rolling out first to Excel in Office 365 starting today.
- Dynamic arrays is available in preview for users signed up for the Office 365 Insiders Program starting today.
- The Insert Data from Picture feature will be available in preview for users signed up for the Office 365 Insiders Program on the Android Excel app soon.
Editor’s note 2/28/2019:
Insert Data from Picture is now rolling out to Android users, coming soon to iOS.Editor’s note 9/24/2018:
This post has been edited with updated information on the availability of Ideas and new data types in Excel.Last updated: February 28, 2017
Using formulas like concatenate can greatly improve your working experience with Microsoft Excel, but the formatting of your data can be equally important as the formulas that you use within it. Learning how to fill a cell with color in Excel is beneficial when you need to visually separate certain types of data in a spreadsheet that you might not otherwise be able to distinguish from one another. The cell fill color makes it easy to identify like types of data that might not be physically located bear one another in your worksheet.
Excel spreadsheets can become very difficult to read as they expand to include more rows and columns. This is especially true of spreadsheets that are larger than your screen and require you to scroll in a direction that removes the column or row headings from view. One way to combat this problem with reading Excel data on your screen is to fill a cell with color. If you want to learn how to fill a cell with color in Excel, then maybe you have seen other people create multi-colored spreadsheets that consist of a number of different filled cells that run for the entire length of a row or column. While initially this might seem like an exercise that is simply meant to make a spreadsheet appear more attractive, it actually serves an important function by letting the document viewer know what row a particular piece of data is contained within.
How to Fill Color in Excel
Microsoft Excel 2010 includes a specific tool that you can use to fill a selected cell with a certain color. You can even choose the color that you want to use to fill that cell. That tool is accessed by clicking the Home tab at the top of the Excel window, and is circled in the image below.
For example, when I am creating a large spreadsheet, I like to use colors that are distinctly different, but are not so distracting that the document becomes hard to read. If the text in your cells is black, then you will want to avoid using darker fill colors. Sticking to colors like yellow, light green, light blue and orange will make it very easy for someone to recognize the different cells, but they will not have any difficulty reading the data within them. This is the most important part, because the data is still the reason that the spreadsheet exists in the first place.
To add color to the background of your cell, you must first click the cell to select it. Click the drop-down arrow to the right of the Fill color icon, then click the color that you want to apply to the selected cell. The background color will change to the color that you selected. If you want to know how to change fill color in Excel 2010, simply click the cell with the fill color that you want to change, then click the Fill color drop-down arrow and choose a different color.
If you are not able to change the fill color using this method, then there is some other formatting rule applied to your cell that you need to adjust. Read this article to learn about removing conditional formatting from Excel.
How to Fill a Row With Color in Excel or How to Fill a Column With Color in Excel
The process for applying color to a row or column in Excel is nearly the same as how to apply fill color in Excel to a single cell. Start by clicking the row or column label (either a letter or number) that you want to apply the fill color to. Once clicked, the entire row should be selected. Click the Fill color icon in the ribbon, then click the color that you want to apply to that row or column. Additionally, if you want to learn how to change the fill color in a row or column in Excel, simply select the filled column or row and use the Fill color icon to select a different color.
By using these methods to apply fill colors to your Excel spreadsheet, you can make it much easier to see which row or column a particular cell is included within. The image below is an example of a spreadsheet that has been completely colored in, which should give you an idea of what you can do with this tool.
Organizing data in this fashion is not particularly necessary when you are dealing with such a small amount of data but, for larger spreadsheets, it can make locating specific types of information much simpler.
One additional benefit to using fill colors in Excel is the ability to then sort based on those colors. Learn how to sort by fill color in Excel 2010 and take advantage of the formatting that you have applied to your cells.
Share this:
Disclaimer: Most of the pages on the internet include affiliate links, including some on this site.
Given that it’s typically used to crunch numbers, it’s no surprise that some creatives might avoid Microsoft Excel at all costs. However, some are using the spreadsheet software as a drawing tool to make amazing digital art. Compared to expensive imaging software such as Photoshop, Excel (or Google Sheets or a number of other spreadsheet applications) is often pre-installed on computers and has a surprising amount of artistic versatility hidden within its toolbar, rows, and columns.
From detailed vector illustrations to mosaic-like pixel art, read on for examples of incredible Excel art and how you can create your own.
Creating Vector Art on Microsoft Excel
Known as the “Michelangelo of Excel,” Japanese artist Tatsuo Horiuchi is perhaps the most celebrated Excel artist today. Now 78 years old, he first started experimenting with the data-driven software when he retired at age 60. By mastering Excel’s simple vector tools normally used for graphs, Horiuchi “paints” panoramic scenes of Japan’s natural landscape.
The artist recalls, “I never used Excel at work but I saw other people making pretty graphs and thought, ‘I could probably draw with that.’” He also aptly adds, “Graphics software is expensive but Excel comes pre-installed in most computers… And it has more functions and is easier to use than [Microsoft] Paint.”
Horiuchi entered and won the Excel autoshape contest in 2006, and has since earned world-wide recognition for his incredible artwork. See more of his impressive work on his website.
Similar to Horiuchi, Spanish artist Felipe Andres Velasquez Muñoz (aka Shukei) uses Excel’s graph-drawing tools to create detailed vector illustrations. His stunning pieces are “painted” with overlaid shapes made by plotting out graph points and filling them with colors of various tones and hues.
Check out his YouTube channel for video tutorials.
Creating Pixel Art on Microsoft Excel
As a form of digital art often used in video games, pixel art simplifies and breaks down images into their most basic, graphic form and color. Thanks to Excel’s customizable grid-like canvas, users can create artwork by coloring each cell however they like. Check out some examples below from artists who have paid homage to their favorite 8-bit video game characters.
If you create a single-series column, bar or line chart, all the data points in the data series are displayed the same color. And when you want to change the color for the data points, it always changes all the colors. If you need to use different colors for each data points to make the chart more beautiful and professional as following screenshots shown, do you have any good ideas to solve it?
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools : Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools : Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color ; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment.
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Vary colors by point for column / bar / line chart
Amazing! Using Efficient Tabs in Excel Like Chrome, Firefox and Safari!
Save 50% of your time, and reduce thousands of mouse clicks for you every day!
To color code each data points with different colors, the Vary colors by point feature in Excel can help you, please do with following steps:
1. Click one data column in the chart and right click to choose Format Data Series from the context menu, see screenshot:
2. In the Format Data Series dialog box, click Fill in the left pane, and then check Vary colors by point option from the right section, see screenshot:
Tip：If you are using Excel 2013, in the Format Data Series pane, click Fill & Line icon, and then check Vary colors by point option under FILL section, see screenshot:
3. And then click Close button to close the dialog, your will get the following different color data column chart.
4. If you don’t like the colors, you can change them to your need, please click Page Layout > Themes, and choose one theme you like.
5. And you will get the chart with different bar colors you need.
Note: This Vary colors by point option is also applied to bar chart and line chart in Excel.
This example teaches you how to get the date of a holiday for any year (2020, 2021, etc). If you are in a hurry, simply download the Excel file.
Before you start: the CHOOSE function returns a value from a list of values, based on a position number. For example, =CHOOSE(3,”Car”,”Train”,”Boat”,”Plane”) returns Boat. The WEEKDAY function returns a number from 1 (Sunday) to 7 (Saturday) representing the day of the week of a date.
1. This is what the spreadsheet looks like. If you enter a year into cell C2, Excel returns all the holidays for that year. Of course, New Year’s Day, Independence Day, Veteran’s Day and Christmas Day are easy.
2. All other holidays can be described in a similar way: the xth day in a month (except Memorial day which is slightly different). Let’s take a look at Thanksgiving Day. If you understand Thanksgiving Day, you understand all holidays. Thanksgiving is celebrated the 4th Thursday in November.
The calendar below helps you understand Thanksgiving Day 2020.
Explanation: DATE(C2,11,1) reduces to 11/1/2020. WEEKDAY(DATE(C2,11,1)) reduces to 1 (Sunday). Now the formula reduces to 11/1/2020 + 21 + CHOOSE(1, 4 ,3,2,1,0,6,5) = 11/1/2020 + 21 + 4 = 11/26/2020. We needed the 4 extra days because it takes 4 days until the first Thursday in November. From there, it takes another 21 days (3 weeks) until the 4rd Thursday in November. It doesn’t matter on which day November 1 falls, the CHOOSE function correctly adds the number of days until the first Thursday in November (notice the pattern in the list of values). From there, it always takes another 21 days until the 4rd Thursday in November. Therefore, this formula works for every year.
3. Let’s take a look at Martin Luther King Jr. Day. This formula is almost the same. Martin Luther King Jr. Day is celebrated the 3rd Monday in January. The first DATE function reduces to the first of January this time. The base position (0) in the list of values for the CHOOSE function is located at the second spot now (we are looking for a Monday)
The calendar below helps you understand Martin Luther King Jr. Day 2020.
Explanation: DATE(C2,1,1) reduces to 1/1/2020. WEEKDAY(DATE(C2,1,1)) reduces to 4 (Wednesday). Now the formula reduces to 1/1/2020 + 14 + CHOOSE(4,1,0,6, 5 ,4,3,2) = 1/1/2020 + 14 + 5 = 1/20/2020. We needed the 5 extra days because it takes 5 days until the first Monday in January.
This Quick Tip will show you—in just a few easy steps—how to make a useful isometric grid. You will learn how to use the Rectangular Grid Tool with the “SSR technique”, and in less than two minutes you’ll be ready to draw your isometric designs.
Are you a beginner to isometric designs? Then check out our awesome Isometric Generators available on GraphicRiver for fast effects!
Follow along with us over on our Envato Tuts+ YouTube channel. Video created by Andrei Stefan.
1. How to Create the Grid
Step 1
Open a new document. The dimensions will depend on what you will create on the grid we’ll make, and also the color mode. We’ll start now by selecting the Rectangular Grid Tool.
Step 2
Set the parameters of the Rectangle Grid Tool. Press Enter and set the values for Number under both Vertical Dividers and Horizontal Dividers to around 30. This value depends on the proportions of your project, so chose a number that suits your needs.
Step 3
Now, you have two alternatives. You can specify the exact values for the Width and Height in the previous step (which I don’t recommend). In this case, you must set the same value for both Width and Height to obtain a square grid. Or you can ignore those values and just drag with your mouse, holding Shift, to make one large square, a bit larger than your document (you will see later why).
What we are going to do next is called “the SSR method”. This is a method to make 3D isometric graphics from 2D. For our grid, we will use this technique for the top plane.
2. How to Scale the Grid
Select the grid and go to Object > Transform > Scale, check Non-Uniform, and set Vertical to 86.602%.
3. How to Shear the Grid
Keep the grid selected from now on. Go to Object > Transform > Shear and set the Angle to 30 degrees.
4. How to Rotate the Grid
Step 1
Finally, we have to rotate the grid. Object > Transform > Rotate and set an Angle of -30 degrees.
Step 2
Now that the lines are set, all you need to do is to make Guides out of them. Be sure you have the grid selected, and go to View > Guides > Make Guides (Control-5).
Conclusion
You now have a playground for your ideas, and this should take only two or three minutes. By taking the time out to use this technique, you can make some great-looking isometric illustrations and know that you’re using the right perspective. Have fun!
Isometric Generators From Envato Market
Cut out the hassle of creating isometric graphics from scratch. Explore our amazing collection of Isometric Generators from GraphicRiver to create realistic 3D maps and so much more!
Isometric Guides Grid Action
The action creates an angled Guides Grid at an angle of 30 degrees on the artboard (size 2000×2000 pixels). The size (x8, x16, x32, x64, x128) is indicated for the side isometric cells. This grid is designed to create illustrations using isometric projection.
Isometry Grid 2.0
This Photoshop action is especially designed to help artists to work with Isometry in Photoshop at the most popular pixel sizes: 16 / 32 / 48 / 64 / 80 px.
Learn more about isometric vector art with these tutorials and resources:
Tracking your invoices can be very easy. With this simple invoice tracking template, you can use whatever system you want to create and send invoices. Use PayPal, use one or more of our templates, or a combination of both. It doesn’t matter, because our invoice tracker provides a way to list all your invoices in a single Excel workbook. It even lets you show a Billing Statement for a single customer by using Excel’s built-in filtering feature. And it’s free.
Invoice Tracker
Download
Other Versions
Template Details
“No installation, no macros – just a simple spreadsheet”
Description
Our simple invoice tracker allows you to keep a list of all your customers and your invoices. You can choose to show all invoices or just the invoices for an individual customer.
Here are some of the cool things about this invoice tracking template .
- It shows an aging summary for all invoices or for a single client.
- You can use the Sorting and Filtering feature in Excel to order by date, or display only the invoices for a single customer.
- The Due Date for overdue invoices are highlighted red.
- When you mark the Status of an invoice as “Paid” or “Closed” it is grayed out – making it easy to see which invoices still need to be paid.
- Marking an invoice as a “Draft” keeps the amount from being shown in the aging report.
- Unlocked and no VBA
If you want something more automated, try our Invoice Manager spreadsheet.
How to use the Invoice Tracking Template
The instructions for using the invoice tracker are pretty simple:
- List your clients’ information in the Customers worksheet
- Delete the sample set of data from the cells with the gray borders.
- Start listing your invoices in the data table.
- Track the status of the invoice (“Draft”, “Sent”, “Partial”, “Paid”, “Closed”).
The spreadsheet uses no macros or VBA. The font colors in the data table are changed automatically using conditional formatting rules. The aging report is created using SUMIF formulas based on the Due Date.
If you are wondering how to organize your invoice files, read the Simple Invoicing article.
Sending a Billing Statement
This template can be used to send individual billing statements to customers. We have a separate billing statement template that you can use, but this spreadsheet also does the trick.
You do not want to send the invoice tracking spreadsheet itself to a customer! Why? Because the spreadsheet contains a list of all your customers and a list of all your invoices. You don’t want your clients seeing all that information.
Instead, the way to create a statement is to .
- Choose that customer from the drop-down list at the top.
- Filter the table to show only the invoices for that customer (using the filter drop-down box).
- Print the worksheet, or convert the statement to a PDF (remembering not to print the customers sheet as well).
- Send the printed copy of the statement, or email the PDF.
by SJ · July 30, 2013
VBA-Excel: Add Table and fill data to the Word document
To Add Table and fill data to the Word document using Microsoft Excel, you need to follow the steps below:
- Create the object of Microsoft Word
- Create a document object and add documents to it
- Make the MS Word visible
- Create a Range object.
- Create Table using Range object and define no of rows and columns.
- Get the Table object
- Enable the borders of table using table object.
- Fill the data in table
- Save the document
Create the object of Microsoft Word
Set objWord = CreateObject(“Word.Application”)
Create a document object and add documents to it
Set objDoc = objWord.Documents.Add
Make the MS Word Visible
Create a Range object.
Set objRange = objDoc.Range
Create Table using Range object and define no of rows and columns.
objDoc.Tables.Add objRange, intNoOfRows, intNoOfColumns
Get the Table object
Set objTable = objDoc.Tables(1)
Enable the borders of table using table object.
Fill the data in table
objTable.Cell(1, 1).Range.Text = “Sumit”
Save the Document
Complete Code:
Add Table and fill data to the Word document
In Microsoft Word, not only can you create documents with text and insert pictures into documents, but you can also create a chart or graph to add visual detail to documents.
There are two options for creating a chart or graph in Microsoft Word. Click a link below for details on how to use each option.
Create chart or graph directly in Microsoft Word
Like in Microsoft Excel, Microsoft Word provides the capability of creating a chart or graph and adding to your document. To create and insert a chart or graph directly in Microsoft Word, follow the steps below.
- Open the Microsoft Word program.
- In the Ribbon bar at the top, click the Insert tab.
- In the Illustrations section, click the Chart option.
- Once the Insert Chart window is open, select the type of chart or graph you want to create, then click the OK button.
- A basic version of the selected chart or graph type, with sample data, is added to the document. A Chart in Microsoft Word window will also open, which looks like a Microsoft Excel spreadsheet. In the spreadsheet window, add, remove, or modify the columns and rows of data to include the data points and values you want your chart to display.
- As you modify the columns, rows, and values in the spreadsheet window, the chart or graph in Microsoft Word will automatically update and display the new or changed data.
- When finished modifying the chart, close the spreadsheet window.
If you need to update the data in the chart after closing the spreadsheet window, you can re-open the spreadsheet window by right-clicking on the chart and selecting the Edit Data option.
Create chart or graph in Microsoft Excel and copy to Microsoft Word
Microsoft Excel provides more functionality and data manipulation capabilities than Microsoft Word provides when creating a chart or graph. If you need the increased capabilities of Excel to create a chart or graph, and then put it in a Microsoft Word document, follow the steps below.
- Open the Microsoft Excel and Microsoft Word programs.
- Create the chart or graph in Microsoft Excel.
- After the chart or graph is created and ready to be placed in the Word document, select the entire chart in Excel.
- Right-click on the chart or graph and select the Copy option. You can also press Ctrl+C on your keyboard to copy the chart or graph.
- In the Word document, place your mouse cursor where you want to add the chart or graph.
- Right-click and select the Paste option to place the chart or graph in the document. You can also press Ctrl+V on your keyboard to paste the chart or graph.
Using the process above, you cannot modify the chart or graph through Microsoft Word after placing it in the document.
Sandeep Agarwal
10 May 2014
Gridlines are faint lines that act like cell dividers in MS Excel. They distinguish cells from each other and make data in them more legible.
By default the gridlines are active on Excel. But depending on the kind of a data a worksheet contains, it may not have the gridlines. As a result, it could become difficult for you to read across the rows. Here’s how gridlines appear if you haven’t see them.
Let us learn few things that we should look at if the gridlines are missing and we want to bring them back.
1. Show Gridlines
MS Excel provides an option to hide gridlines for users who do not like them. In your case, the hide feature may have been activated by mistake.
If you want them to reappear, navigate to View tab and make sure the option Gridlines is checked under section Show.
2. White Gridlines
By default Excel assigns a greyish shade to the gridlines. Ensure that the color has not been changed to white. On a white background, white gridlines are bound to hide themselves.
Follow these steps to change the color to default again:-
Step 1: Go to File -> Options.
Step 2: Now click on Advanced and scroll to the section that reads Display options for this worksheet.
Step 3: From the dropdown for Gridline color choose the Automatic option. This is where you may select different colors if you wish to.
3. White Borders
Your gridlines may have the correct property set and they may also be marked for visibility. But, what if they are hidden by white colored cell borders. The best thing here is to remove the cell borders.
Step 1: Press Ctrl + A to select all the cells. Right-click and choose Format Cells.
Step 2: Go to the Border tab and make sure none of the borders are active.
4. Color Overlay
Sometimes you may highlight blocks of data by different colors to make them distinct from the rest. When colors are overlaid, gridlines hide under them. If you do not see any color there are chances that the overlay color selected is white.
Step 1: Press Ctrl + A to select all the cells.
Step 2: Go to Home tab and change the color fill option to No Fill.
5. Conditional Formatting
There are chances that some kind of conditional formatting has been applied on the worksheet to hide the gridlines.
So, navigate to Home -> Styles -> Conditional Formatting -> Clear Rules.
Note: Clearing rules will clear all other rules along with the one you are trying to remove. It is better to go to Manage Rules and find out from the details if there is such a setting. If yes, remove the specific formatting.
Bonus: Screen Properties
None of the above seem to work for you? Try and play around with the brightness and contrast of your screen. I know it sounds absurd but at times, this could well be the reason behind those missing gridlines.
Conclusion
Next time if you do not see gridlines on your worksheet you know what to do. Also, remember that these settings apply to one sheet (selected sheet) at a time.
Tip: You may apply the settings to multiple sheets at once. To do that hold the Ctrl key and select multiple tabs. Then try any of the above.
Last updated on 8 Feb, 2018
The above article may contain affiliate links which help support Guiding Tech. However, it does not affect our editorial integrity. The content remains unbiased and authentic.The following table summarizes the main features of the Excel 2007 Color Model. Additional details are available after the following summary table.
The color palette is exposed publicly, via the Workbook Palette property, so you can choose which colors to use when opening saved files in an earlier version of Excel.
CellFill is an abstract class representing a fill of a cell. There are some derived types representing the various fills which can be created.
Workbook palette
The Color palette has been exposed publicly, via the Workbook.Palette property, so that you can choose which colors to use when finding the closest matching color. The palette has an indexer to get and set the 56 colors in it, as well as a Reset method to reset it back to its preset state.
The color palette is analogous to the color dialog in Microsoft Excel 2007 UI. You can open this color dialog by navigating to Excel Options > Save > Colors .
Filling a cell
The derived types, representing the various fills which can be created, are as follows:
Represents one of the following:
- no color
- solid color
- pattern fill for a cell
It has background color info and a pattern color info which correspond directly to the color sections in the Fill tab of the Format Cells dialog of Excel. It also has a pattern style.
Represents a linear gradient fill. It has an angle, which is degrees clockwise of the left to right linear gradient, and a gradients stops collection which describes two or more color transitions along the length of the gradient.
Represents a rectangular gradient fill. It has top, left, right, and bottom values, which describe, in relative coordinates, the inner rectangle from which the gradient starts and goes out to the cell edges. It also has a gradient stops collection which describes two or more color transitions along the path from the inner rectangle to the cell edges.
You can create all possible fill types using static properties and methods on the CellFill class. They are as follows:
Represent a fill with no color, which allows a background image of the worksheet, if any, to show through.
Returns a CellFillPattern instance which has a pattern style of Solid and a background color set to the Color or WorkbookColorInfo specified in the method.
Returns a CellFillPattern instance which has the specified pattern style and the Color or WorkbookColorInfo values, specified for the background and pattern colors. These methods can also be used to create solid and no color fills.
Returns a CellFillLinearGradient instance with the specified angle and gradient stops.
Returns a CellFillRectangularGradient instance with the specified left, top, right, and bottom of the inner rectangle and gradient stops. If the inner rectangle values are not specified, the center of the cell is used as the inner rectangle.
Specifying a Color
Overview
You can specify a color (the color of Excel cells background, border, etc) using linear and rectangular gradients in cells. When workbooks with these gradients are saved in XLS file format and opened in Microsoft Excel 2007/2010, the gradients will be visible, but when these files are opened in Microsoft Excel 2003, the cell will be filled with the solid color from the first gradient stop.
These are the ways a color can be defined, as follows:
- The automatic color (which is the WindowText system color)
- Any user defined RGB color
- A theme color
If an RGB or a theme color is used, an optional tint can be applied to lighten or darken the color. This tint cannot be set directly in Microsoft Excel 2007 UI, but various colors in the color palette displayed to the user are actually theme colors with tints applied.
Theme Colors
Each workbook has 12 associated theme colors. They are the following:
Plus how to add back Shared Workbook feature
Collaboration is important in many work spaces. Since most projects require files to be accessed by a lot of users, collaborative setups have become necessary.
Microsoft Excel is one is just one of the many programs used by companies worldwide. As such, the need for shared workbooks is crucial.
In this post, you’ll learn how you and your team can collaborate using Excel 2016 and other versions of Excel online.
Share Excel Files Offline
If you have a local area connection, all users in the network can have access to any file. Not only that, any changes made to the file can be tracked. You can also set which users would have access to the file.
Start by saving the file in a location that can be accessed by everyone in your group. You can then set your file for collaboration.
Adding Back Shared Workbook Feature
Office 365 users will find the Shared Workbook feature hidden by default. This is because Microsoft encourages users to share workbooks online.
Here’s how you enable the feature. If you’re using an older version of Excel, you can skip to the succeeding section – Sharing a Document.
- Go to File > Options > Quick Access Toolbar.
- Under the Choose Commands From drop-down menu, select All Commands. Scroll through the list until you see Share Workbook (Legacy) and select that item.
- Click the Add button.
- Do the same for the following commands:
- Track Changes (Legacy)
- Protect Sharing (Legacy)
- Compare and Merge Workbooks
- Click OK when you’re done.
Sharing a Document
- On the Review tab, click the Share Workbook button.
Those who followed the steps in the Adding Back Shared Workbook Feature section will find the Share Workbook button (along with the other commands you added) in the Quick Access Toolbar instead.
- This will prompt a dialog box to appear. Tick the option that enables the shared workbooks feature.
- Head over to the Advanced tab and enable Track Changes and other settings you might want to turn on.
Protect Your File
The steps above give everyone access to your file. But it also gives everyone administrative access by default. Protecting your file means access to the file will be limited and tracked.
- Go to the Review tab and click the Protect and Share Workbook button.
- Tick the Sharing With Track Changes check box.
- You should also create a password otherwise anyone can revert the workbook to its original settings.
- Save your changes and exit.
Share Excel Files Online
While working offline is a great solution, you’re hampered by the necessity for a shared local drive. Working online is clearly the better choice. Here’s how you do it.
Using OneDrive
- Open the file and go to File > Share > Save to Cloud.
- Invite people you’d want to collaborate with by entering their email address. You can also set the permission settings.
- Click Share when you’re finished.
Using Google Drive
Google Drive is probably the easiest way to work on a project simultaneously. Anyone with a Google account can access files and edit as needed.
- Log into your account and go to Google Drive.
- Once you’re in, you can create a new file or upload an existing one.
- Once your document has been created or uploaded, click the Share button on the upper-right corner of the worksheet.
This will give you the option to share the spreadsheet by entering an email address or by sharing a link to your colleagues.
All changes are automatically tracked and saved. You can work on the document together in real-time which is very convenient.
Christopher Jan Benitez is a freelance writer for hire who provides actionable and useful web content to small businesses and startups. In his spare time, he religiously watches professional wrestling and finds solace in listening to ’80s speed metal. Read Christopher’s Full Bio
By Susanna Ray 11 December, 2019
She’s lived in a refugee camp since her family fled war when she was a little girl, but Grace Nshimiyumukiza has always wanted to be the one to help, not just be helped.
First she coached her six younger siblings through classes in Kakuma, one of the world’s largest refugee camps. Then she tutored friends who were struggling in a culture that prioritized childcare and chores for girls, and later she turned inward, teaching herself basic computer skills.
Now her efforts are official: The 23-year-old is a teacher for a program aiming to help 25,000 Kakuma residents become digitally savvy. The camp, where she’s lived for 17 years, is so vast she uses a motorbike to travel around to classrooms, explaining such concepts as what a mouse is before moving on to data entry and how to operate online businesses.
“Teaching has trained me and molded me to be bold,” Nshimiyumukiza says. “My goal for my students is to see them explore and grow into leaders who will stand without fear to fight for peace and development of not just our countries of origin but also the whole of Africa.”
The program in Kakuma is a partnership between UNHCR, the United Nations Refugee Agency, and Microsoft’s Tech for Social Impact organization. The company founded the unit in 2017 as a unique model to complement its philanthropy efforts and help charities get up to speed, amid the realization that although the nonprofit sector spends about $30 billion a year on technology, it still wasn’t participating fully in the digital transformation. Efforts include measures such as training refugees or giving volunteers email addresses to grander concepts like using artificial intelligence to deal with paperwork so Salvation Army caseworkers can have more face time with people who need their help.
“We’re living in this time of amazing technology, wealth and capability, and yet there are 124 million people with life-threatening food insecurity, and the most displaced refugees than any time since World War II,” says Justin Spelhaug, who heads up Tech for Social Impact. “And nonprofits that are on the front line of serving those people have very few resources in terms of technical staff, their dollars are constrained, and solutions haven’t been built for their scenarios. So they’re a decade or more behind in using technology to help with these incredibly important issues.”
While Microsoft has been engaged with charities since 1983 with the inception of its employee giving campaign and donates $1.5 billion a year in software and services through Microsoft Philanthropies, Spelhaug says, “we realized we needed to think about a different business model to serve their needs.”
So Microsoft Philanthropies brought its donation efforts together with commercial sales and business development to create Tech for Social Impact.
“Now here comes people who will train me on how to do it and then give me opportunity to train others,” says Grace Nshimiyumukiza. “It was like a dream come true.” (Photo by UNHCR/Hannah Maule-ffinch)
The program not only donates technology but also reinvests any profits made from discounted sales to larger charities back into the sector to help smaller ones. It gives free software to those with 10 or fewer employees, which make up 80% of the world’s 4 million nonprofit organizations. It helps nonprofits modify Microsoft software to meet their specific needs — such as fundraising and mobilizing volunteers — in part by connecting them with willing helpers in the company’s global partner ecosystem. And it offers free digital skills training.
That’s where Nshimiyumukiza (pronounced na-SHIM-ee-yoo-moo-KEE-zah) comes in.
Spelhaug recently visited Kakuma, the camp in northwestern Kenya that was built for 70,000 refugees and now hosts 190,000 from 20 countries including Rwanda, the Democratic Republic of the Congo, Burundi, Tanzania and Uganda. They live for years — even decades — in huts, some so fragile that a recent rainstorm nearly flattened the one Nshimiyumukiza lives in with her parents and siblings. Yet Spelhaug describes her as “full of optimism and hope despite the environment she lives in.”
Still, teaching herself how to use a computer, starting with how to log in and then how to create PowerPoint presentations, all while studying for her social-work diploma course, was “very hard,” Nshimiyumukiza says — so hard that it gave her an idea.
“Teaching has trained me and molded me to be bold,” says Grace Nshimiyumukiza. (Photo by UNHCR/Hannah Maule-ffinch)
“I wanted to train women in basic computer skills” to help them pursue higher education, she says. “But then it was not possible. I didn’t have all the resources. I just had the idea.”
She shared the concept with others in the camp as she went on to earn her bachelor’s and master’s degrees through online courses, and earlier this year UNHCR workers reached out to her about the Microsoft digital literacy program, which aims for half of its 25,000 intended students to be women and girls.
“Now here comes people who will train me on how to do it and then give me opportunity to train others,” Nshimiyumukiza says. “It was like a dream come true.”
Her first class was made up of 75 girls from the camp and its surrounding community. She taught them for an intense, week-long course that ran the gauntlet from learning how to turn on a computer to how to use Word, Excel and PowerPoint, along with data security. They all passed the test, stoking Nshimiyumukiza’s passion as she thought of her students potentially getting the chance to leave the camp and go on to teach others in their various home countries.
“My energy is always up, no matter how hard it seems,” she says. “Because we want to get youth and women and everybody who lives in Kakuma from a position whereby you depend, to a position where you become very independent. And they will stop relying on what they are given for free. The more you get things for yourself, the more you feel the power and the energy.”
Question
I was requested to add the functionality so that when the recipient receives this submitted infopath form, that they also have the ability to see the regular email history and attachment that led up to the submission of the infopath form. The recipient would get an email just as they would any other email that was forwarded (though the last message is the infopath form). I am using Infopath 2007. From what I see, the easiest solution might be as follows (though I have not been able to determine how to execute this):
Create a control in Infopath 2007 similar to the typical ‘File Attachment’. However, instead of the ‘File Attachment’ control, I would like to include a control that functions exactly the same as the Insert ‘Outlook Item’ button that is in Outlook. For those that are unfamilar with the ‘Outlook Item’ button, when clicked, it brings up a box which allows you to view your Inbox and select an email to attach.
Though execution of the above would be an improvement from where I am now, I would then like to take it to the next level. If executed properly, the Insert ‘Outlook Item’ button would just attach the email similar to any other Attachment. However, I would like to program Infopath so that when the Outlook Item is attached, the content of the email selected is embedded in the Infopath 2007 form that’s submitted.
Any help would be greatly appreciated.
Answers
Dan, this is the 2010 forum, so I’m moving your thread to the 2007 forum.
Dan, these requests appear to be outside the scope of what InfoPath can do, but I’ll see if I can help. To start off, was this request made of you by someone who knows InfoPath and expects this functionality to exist or someone just throwing a shot in the dark and hoping you could make it happen? I ask, because each component you mentioned does not exist in InfoPath, so I was wondering where that all came from. Anyway, here are my comments and clarification questions to help move us along:
- The regular email history and attachment that led up to the submission of the infopath form: You didn’t give any details about this, but it appears to be the entire driver for the solution. We need all the details about what “regular email history” means, the attachment you’re referencing, and how exactly someone even ever gets prompted to submit an InfoPath form.
- Extending the above question, where does this form exist? Is it in SharePoint? If so, what version of SharePoint? Is it a browser form or client form? How is it built? Where does it actually submit? You refer to “this submitted infopath form” as if it’s expected that the form be submitted into someone’s email, but this is not usually the case. Submit to Email is a submit option, but you normally submit to SharePoint for collaboration, storage, tracking, and auditing, data integration, data sharing, and archiving purposes. Can you explain all of the above for your scenario?
- The recipient would get an email just as they would any other email that was forwarded: What do you mean by “forwarded”? What emails are being forwarded? An InfoPath form submitted to email or submitted to SharePoint and referenced via workflow email is not a forward, so please clarify what this means.
- Create a control in Infopath 2007 similar to the typical ‘File Attachment’: By “create,” do you mean that you want to re-develop the InfoPath Designer so that it has a new control that doesn’t exist in the product? The “File Attachment” control is there, but it only prompts to attach files. You can attach emails if the email files are placed somewhere that is discoverable by InfoPath, but IP has no integration with Outlook from a data connection perspective for retrieving info. That Outlook button is specific to Outlook and doesn’t exist in InfoPath. What is the expectation here?
- Program Infopath so that when the Outlook Item is attached, the content of the email selected is embedded in the Infopath 2007 form: This is also well outside the scope of InfoPath’s functionality. You can attach files, but parsing them and then displaying the text inside the body of the form is not an available feature. You might be able to write custom code that runs inside the form and performs this action, but it’s not something that InfoPath itself can do.
- Overall, it sounds like someone (your manager perhaps?) has a very specific process that s/he uses in Outlook and wants to continue using it instead of moving to more advanced technology and doing it in a different way. Emails are not a good way to go, because they all reside separately in the PST files of each person instead of ONE copy existing in a public location that everyone (or selected individuals) can see at all times. I would suggest considering and perhaps recommending a completely different approach. The approach would be to determine the actual requirements instead of jumping straight to the solution, and then determine how the available tools can be used together to meet those requirements.
SharePoint Architect || Microsoft MVP || My Blog
Planet Technologies || SharePoint Task ForceMany investors and finance professionals work with public company stock quotes.
If that applies to you, here is a simple Google Sheets → Excel PowerQuery combination to simplify and streamline that process for you.
If you would like a copy of the Excel file — with the PowerQuery import (with modifications), you can get it here .
Set up Google Spreadsheet
- Set up a Google spreadsheet with the parameters you want. The image below highlights one example using the JM Smucker Company (SJM). Setting the symbol and Start Date as variables gives you the flexibility to change the symbol and generate the same data for a different company.
- So, I enter the symbol (“SJM”) and the Start Date (“January 1, 2000”). The end date is always today’s date — see image below.
Here is the completed GOOGLEFINANCE function:
=GOOGLEFINANCE(a2, “all”, B2, C2, “DAILY”)
- Cell A2 = SYMBOL
- “all” requests all available historical information
- Cell B2 = START DATE
- Cell C2 = END DATE
- “DAILY” — I want information returned on a DAILY basis (weekly, monthly are other options — Daily provides the most flexibility for analysis purposes)
Prepare Data to be Queried by Excel (PowerQuery)
1 — “Publish” the Google Spreadsheet
Next, I will publish the Google sheet so that I can import the data into Excel (using PowerQuery).
