d

Excel PowerPivot: WEEKDAY Function (DAX)

HTML4

WEEKDAY Function (DAX)

Syntax:

WEEKDAY(<date>, <return_type>)

Parameters:

date: A date in datetime format.

return_type: A number that determines the return value (An integer number from 1 to 7.)

Action:

Returns a number from 1 to 7 identifying the day of the week of a date. By default the day ranges from 1 (Sunday) to 7 (Saturday).

 

Excel Keyboard Shortcuts: Ctrl + 3

HTML4

Ctrl + 3

Hold the Ctrl key and press 3 (three numaric key)

Action:

Applies and removes italic formatting.

 

Excel Keyboard Shortcuts: Ctrl + 2

HTML4

Ctrl + 2

Hold the Ctrl key and press 2 (two numaric key)

Action:

Applies and removes Bold formatting.

 

How to Enable PowerPivot Tab in Excel 2013

HTML4

How to Enable PowerPivot Tab in Excel 2013

 

In defult PowerPivot tab is not enable.

 

 

Excel Keyboard Shortcut: Ctrl+1

HTML4

Ctrl + 1

Hold the Ctrl key and press 1 (one numaric key)

Action:

Display the Format Cell dialog box.

 

Excel Keyboard Shortcut : Ctrl + 0 (Zero)

HTML4

Ctrl + 0 (Zero)

Hold the Ctrl key and press 0 (zero numaric key)

Action:

Hides the selected columns.

 

Excel Keyboard Shortcuts Ctrl Combination

HTML4
Ctrl (with Numeric Key)
Crtl + 0 Ctrl + 5

Ctrl + 1

Ctrl + 6
Ctrl + 2 Ctrl + 7
Ctrl + 3 Ctrl + 8
Ctrl + 4 Ctrl + 9

Next .... Ctrl (with Alphabetical key)

 

Excel PowerPivot: CALENDARAUTO Function (DAX)

HTML4

Excel PowerPivot DAX Function & Features

CALENDARAUTO Function (DAX)

Syntax:

CALENDARAUTO([<fiscal_year_end_month>])

Parameters:

fiscal_year_end_month: Any DAX expression that returns an integer from 1 to 12. If omitted, defaults to the value specified in the calendar table template for the current user, if present; otherwise, defaults to 12.

Action:

Returns a table with a single column named “Date” that contains a contiguous set of dates. The range of dates is calculated automatically based on data in the model.

 

Video Tutorial: Analyze and Visualize Data with Excel


Video Tutorial: Analyze and Visualize Data with Excel







Get the full course at Microsoft Virtual Academy: https://www.microsoftvirtualacademy.c...

Excel PowerPovit: DATE Function (DAX)


HTML4

DATE Function (DAX)

Syntax:

DATE(<year>, <month>, <day>)

Parameters:

year: A number representing the year.

month :month is a number from 1 to 12, then it represents a month of the year. 1 represents January, 2 represents February, and so on until 12 that represents December.

day: day is a number from 1 to the last day of the given month then it represents a day of the month.

Action:

Returns the specified date in datetime format.

 

Excel PowerPivot: CALENDAR Function (DAX)


HTML4

CALENDAR Function (DAX)

Syntax:

CALENDAR(<start_date>, <end_date>)CALENDAR(<start_date>, <end_date>)

Parameters:

start_date: Any DAX expression that returns a datetime value.

end_date :Any DAX expression that returns a datetime value.

Action:

Returns a table with a single column named “Date” that contains a contiguous set of dates. The range of dates is from the specified start date to the specified end date, inclusive of those two dates.

 

Excel PowerPivot Keyboard Shortcut: Alt + Down arrow key


HTML4

Alt + Down arrow key

(Hold the Alt key and press Down arrow key)

Open the AutoFilter Menu Dailog Box.

 

Take Screen shoot / Screen clipping in Excel


HTML4

Take Screen shoot / Screen clipping in Excel

 

How to Take Screen shoot / Screen clipping in Excel

Lets start .

How to Print several worksheets at once

HTML4

How to Print several worksheets at once

 

I have 2 worksheets with containing the monthly sales data of South and North Zone and want to print both sheet at once

Lets start .

Excel Short Cut Keys: Ctrl + Shift + " or Ctrl + D (Direct Copy Paste)


HTML4

Ctrl + Shift + " or Ctrl + D

 

Microsoft Excel Short Cut Keys: Ctrl + Shift + " or Ctrl + D

Hold the Ctrl key with Shift Key and " key or Ctrl key with D

Direct Copy Paste

Copies the value from the cell above to active cell or paste it to active cell or formula bar

 

 

Add a Total row in Excel Table

HTML4

Add a Total row in Excel Table

In default Excel Table Row total is not visible.

 

excel table

Excel Shortcut keys: Ctrl + Shift + " (Direct Copy Paste)

HTML4

Ctrl + Shift + "

 

Microsoft Excel Short Cut Keys:  Ctrl + Shift + "

Hold the Ctrl key with Shift Key and " key

Direct Copy and Paste

Copies the value from the cell above the active cell into the cell or the Formula Bar.

 

Excel Shortcut Key: Ctrl + T / Ctrl + L or Alt + N+T (Create a Table)

HTML4

Ctrl + T / Ctrl + L or Alt + N+T

Create a Table
Create a table to organize and analyze related data.
Tables make it easy to sort, filter and format data within a sheet.

<!-- RajExcel 300x250

For Create a Table - Step by Step Tutorial Click here
-->

Create a table in Excel

HTML4

Create a table in Excel worksheet

Tables make it easy to sort, filter, and format data within a sheet. Also you can organize, manage and analyze related data, table also included the built-in filtering, sorting, and row shading.

 

 

How to enable / disable Quick Analysis feature

HTML4

How to enable / disable Quick Analysis feature

When a data set is selected, Excel displays a Quick Analysis button in the lower right corner of the range. But this option can be disabled.

 

Excel Short cut key: Ctrl + Shift + & (Add borders)

HTML4

Ctrl + Shift + &

Outline border

Applies the border to the selected cells

 

 

Excel Short Cut Keys: Ctrl+ Shift + ( Unhide Rows

HTML4

Ctrl + Shift + (

 

Microsoft Excel Short Cut Keys:  Ctrl + Shift + (

Hold the Ctrl key with Shift Key and ( key

Unide Rows

Unhides any hidden rows within the selection.

 

 

Excel Makes Better Predictions in Office 2016

Microsoft revamps the software's forecasting features, enabling users to fine-tune their business predictions.




Excel 2016, part of Microsoft's newly released Office 2016 productivity software suite, has new built-in tools to help users peer a little further into the future.
Data forecasting is not new to Excel. Before the new version, "many used the FORECAST() sheet function, which performs a linear forecast or extended trendlines in chart properties to extrapolate forward," reminded Microsoft's Excel team in an Office Blogs post.

Excel 2013 /16 Short Cut Keys: Alt + AE (Text to column)



Text to Column

Microsoft Excel 2013 Short Cut Keys:  Alt + A+E
Hit the Alt key. Then type AE (one key at a time).


Text to Column
Split a single column of text into multiple columns.
For example, you can separate a columns of full name into separate first and last name columns.
You can choose how to split it up: fixed width or split at each comma, period, or other character.



Excel 2013/16 Short Cut Keys: Alt + AM (Remove duplicates rows)


Remove Duplicates rows

Microsoft Excel 2013/2016 Short Cut Keys:  Alt + A+M
Hit the Alt key. Then type AM (one key at a time).


Remove Duplicates
Delete duplicate rows from a sheet.
You can pick which columns should be checked for duplicate information.



Excel 2013/16 Short Cut Keys: Alt + AM (Remove duplicates rows)
Excel 2013/16 Short Cut Keys: Alt + AM (Remove duplicates rows)



Office 2016 for Mac update doesn't include fix for crashes under OS X El Capitan - Computer World



Microsoft on Tuesday updated Office 2016 for Mac with Security patches and a host of smaller Changes feature, but did not fix the problem users constant-crash on OS X Have Reported El Capitan, Apple's newest operating system.


Crash Report for Office 2016
Source (computerworld.com/article/2992488)

Instead, the company Told customers to sit tight and wait for Apple to ship an update for El Capitan, aka OS X 10.11, Strongly suggesting the mess That was Apple's Responsibility.


How to enable / disable Quick Analysis feature



When a data set is selected, Excel displays a Quick Analysis button in the lower right corner of the range. But this option can be disabled.




Excel 30 Years - The History of Excellence!



Product legendary Microsoft Office, which is used today by more than one million people worldwide, was created in 1985 to ... Apple



Add or remove a sheet background in Excel

In Microsoft Excel, you can use an image as page background display purposes only. A sheet background is not printed, and not retained in an individual worksheet or an item that is saved as a Web page.





Shortcut Key : Excel (Ctrl + C)


Shortcut Key : Excel (Ctrl + C)

Put a copy of the selection on the Clipboard so you can past it somewhere else.


Copy Data and Past as Picture in Excel

Sometime we need the excel table in picture format and required to past the picture in Excel, Word, PowerPoint or Outlook.
Most of the people using the screen snap shot or using the print screen.

Here we can do this without using screen shot or something else

select your data you want to be convert to picture


Shortcut Key : Excel 2013 (Ctrl + X)







Convert text to Capital-Case (Capitalizes all the letter in Text) in Excel



UPPER function
Capitalizes all the letter and any other letters in text that follow any character other than a letter in a text string. 













Syntax
UPPER(text)

The UPPER function syntax has the following arguments:




  • Text    Required. The Text want converted to uppercase. Text can be a reference or text string

Example:

Type your data in C3 cell

and then type the proper function in cell G3
=PROPER(C3)



Here is the result






Create a Forecast Chart in Excel 2016











If you have time based on the historical data it can be used to create a forecast. When a forecast is created, a new worksheet is created with a table of historical and projected values and a graph showing this. A forecast can help predict things like future sales, inventory requirements, or consumer trends

Microsoft's Excel Might Be The Most Dangerous Software On The Planet


I am sharing the very interested article regarding the Microsoft Excel. This article written by Tim Worstall and published in Feb 13, 2013 in Forbes.com. 




No, really, it’s possible that Microsoft‘s Excel is the most dangerous software on the planet. Yes, more dangerous than rogue code running a nuclear power plant, than the Stuxnet that was deliberately sent off to sabotage Iran‘s nuclear program, worse, even, than whatever rent in the fabric of space time led to the invention of Lolcats. Really, that serious.

There’s a danger at one level: it’s all become so complex and it’s handled in such a slapdash manner that no one is really on top of it anymore. And don’t just take it from me as an assertion, there are very serious people indeed warning about this:

Both the Switzerland-based Basel Committee on Banking Supervision1 (BCBS) and the Financial Services Authority2 (FSA) in the UK have recently made it clear that when relying on manual processes, desktop applications or key internal data flow systems such as spreadsheets, banks and insurers should have effective controls in place that are consistently applied to manage risks around incorrect, false or even fraudulent data. The citation by the BCBS is the first time that spreadsheet management has ever been specifically referenced at such a high level, a watermark in the approach to spreadsheet risk.

To give you and idea of how important this is here’s a great tale from James Kwak:

The issue is described in the appendix to JPMorgan’s internal investigative task force’s report. To summarize: JPMorgan’s Chief Investment Office needed a new value-at-risk (VaR) model for the synthetic credit portfolio (the one that blew up) and assigned a quantitative whiz (“a London-based quantitative expert, mathematician and model developer” who previously worked at a company that built analytical models) to create it. The new model “operated through a series of Excel spreadsheets, which had to be completed manually, by a process of copying and pasting data from one spreadsheet to another.” The internal Model Review Group identified this problem as well as a few others, but approved the model, while saying that it should be automated and another significant flaw should be fixed.** After the London Whale trade blew up, the Model Review Group discovered that the model had not been automated and found several other errors. Most spectacularly,

“After subtracting the old rate from the new rate, the spreadsheet divided by their sum instead of their average, as the modeler had intended. This error likely had the effect of muting volatility by a factor of two and of lowering the VaR . . .”


To translate that into the vernacular, the bank, JP Morgan, was running huge bets (tens of billions of dollars, what we might think of a golly gee gosh that’s a lot of money) in London. The way they were checking what they were doing was playing around in Excel. And not even in the Masters of the Universe style that we might hope, all integrated, automated and self-checking, but by cutting and pasting from one spreadsheet to another. And yes, they got one of the equations wrong as a result of which the bank lost several billion dollars (perhaps we might drop the gee here but it’s still golly gosh that’s a lot of money).

And the various financial market regulators are rather waking up to how these decisions are being made in the markets. And thus the warning at the top: guys, do you think you could pay a little more attention to the tools you are using to move these billions and tens of billions around? For as we can see getting it wrong can be painfully expensive.


So that’s one sense in which Excel could be dangerous: that we’ve tens of thousands, hundreds of thousands, of financiers and bankers throwing trillions of dollars around the markets on the basis of their incomplete spreadsheets and their ignorance of how they’re doing it wrong. Pretty scary really.

But there’s another deeper level of risk here. That very throwing of trillions a day around the markets (and it really is trillions a day: the foreign exchange market in London alone is over $2 trillion a day) is dependent upon the existence of Excel itself.

for continue reading click here

Convert text to Initial-Case (Capitalizes the first letter in Text) in Excel



PROPER function
Capitalizes the first letter and any other letters in text that follow any character other than a letter in a text string. Converts all other letters in lowercase.













Syntax
PROPER(text)

The PROPER function syntax has the following arguments:




  • Text    Required. Text enclosed in quotation marks, a formula that returns text, or a reference to a cell containing the text you want to partially capitalize.


Example:

Type your data in C3 cell

and then type the proper function in cell G3
=PROPER(C3)



Here is the result





Helpful Resources of PowerBI & DAX

Video: VLOOKUP: When and how to use it



Video: VLOOKUP: When and how to use it



















Microsoft introduces Microsoft Dynamics CRM updates 2016





Microsoft today announced new capabilities that will be delivered later this year in Microsoft Dynamics CRM 2016. The updates are designed to make the software easier to use and include even deeper integration with other Microsoft business applications such as Office 365, Word, Excel, onedrive, Cortana and Delve.


Excel Templates Free Download : Employee Attendance Record







This visual tracker will allow you to track the attendance of each of your employees at a glance


 Click here to download Employee Attendance Template

Microsoft PowerPivot for Excel 2010 free download








PowerPivot enables users of all levels of access and mashup data from virtually any source. Users can create their own compelling reports and analytical applications, easily share ideas and collaborate with colleagues via Microsoft Excel and SharePoint.


Supported Operating System

Windows 7, Windows Server 2003 R2 (32-bit x86), Windows Server 2003 R2 64-bit editions, Windows Server 2008 R2, Windows Server 2008 R2 Datacenter, Windows Vista Service Pack 2, Windows XP Service Pack 3
Windows 8
Windows 7
Windows Server 2008 R2 (64-bit)
Windows XP with SP3 (32-bit)
Windows Vista with SP2 (32-bit or 64-bit) *
Windows Server 2008 with SP2 (32-bit or 64-bit) *
Windows Server 2003 R2 (32-bit or 64-bit) with MSXML 6.0 installed +
Terminal Server and Windows on Windows (WOW) - which allow you to install the 32-bit versions of Office 2010 on 64-bit operating system-- are supported.


System Requirements

Only 32-bit Microsoft®Office 2010/PowerPivot for Microsoft® Excel 2010 is supported on 64-bit Windows Server 2003 R2

500 MHz 32-bit or 64-bit processor or higher
Minimum of 1 GB of RAM (2 GB or more is recommended.)
3.5 GB of available hard disk space
Internet access


Install Instructions

To open and interact with the content of these workbooks, do the following:

Follow the instructions to install Microsoft Office 2010 and PowerPivot for Excel found here.
Download and save these samples to a folder on your local computer.
Start Excel 2010.
On the File menu, click Open.
Open the sample file that you want to view.
To view the data embedded in the workbook, click PowerPivot Window Launch on the PowerPivot tab.

 Click here to Download


Recommended for You

LinkWithin

Related Posts Plugin for WordPress, Blogger...